easy-poi 一对多导出

1. 需求：

某一列上下两行单元格A,B值一样且这两个单元格， 前面所有列对应单元格值一样的话，

就对A,B 两个单元格进行纵向合并单元格

1. 核心思路：

先对数据集的国家，省份，城市...... id 身份证进行排序

国家一列，值相同就合并单元格(直接调用：2 是指的下标 是2 开始；0 是0列

PoiMergeCellUtil.mergeCells(sheet,2,0);

省份一列：

两行数据，前一列的值相同（国家列相同），且当前列对应值也相同就合并单元格

城市一列：

两行数据，第一列+第二列值相同(国家省份值相同)，且当前列对应值也相同就合并单元格

其他类似：

POM文件如下：

  <!-- EasyPoi 核心库 --><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-base</artifactId><version>4.2.0</version></dependency><!-- EasyPoi Web 支持 --><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-web</artifactId><version>4.2.0</version></dependency><!-- 如果需要使用注解 --><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-annotation</artifactId><version>4.2.0</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.2</version></dependency>

代码如下：

实体类：

package com.example.demo.entity;import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;
import org.springframework.util.ObjectUtils;import java.lang.reflect.Field;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;/*** @author guoyiguang* @description $* @date 2025/4/5$*/
@Data
public class Boy {@Excel(name = "国家",orderNum = "1")private String country;@Excel(name = "省份",orderNum = "2")private String province;@Excel(name = "城市",orderNum = "3")private String city;@Excel(name = "县",orderNum = "4")private String county;@Excel(name = "城镇",orderNum = "5")private String town; // 镇@Excel(name = "村",orderNum = "6")private String village; // 村@Excel(name = "街道",orderNum = "7")private String street;@Excel(name = "性别",orderNum = "8")private String sex;@Excel(name = "名称",orderNum = "9")private String name;@Excel(name = "出生年份",orderNum = "10")private String birthYear;@Excel(name = "出生月份份",orderNum = "11")private String birthMonth; //@Excel(name = "ID身份证",orderNum = "12")private String idCard; // 身份证标识}

模拟从数据库获取业务数据：

    public List<Boy> getBoysList(){List<Boy> boyList = new ArrayList<>();Boy boy = new Boy();boy.setCountry("中国");boy.setProvince("山西省");boy.setCity("晋中市");boy.setCounty("平遥县");boy.setTown("岳壁乡");boy.setVillage("金村");boy.setStreet("向阳街道");boy.setBirthYear("1990");boy.setBirthMonth("02");boy.setSex("男");boy.setName("张三1");boy.setIdCard("张三1");boyList.add(boy);Boy boy7 = new Boy();boy7.setCountry("中国");boy7.setProvince("山西省");boy7.setCity("晋中市");boy7.setCounty("平遥县");boy7.setTown("岳壁乡");boy7.setVillage("金村");boy7.setStreet("向阳街道");boy7.setBirthYear("1990");boy7.setBirthMonth("02");boy7.setSex("男");boy7.setName("张三1");boy7.setIdCard("张三111");boyList.add(boy7);Boy boy2 = new Boy();boy2.setCountry("中国");boy2.setProvince("山西省");boy2.setCity("晋中市");boy2.setCounty("平遥县");boy2.setTown("岳壁乡");boy2.setVillage("金村");boy2.setStreet("向阳街道-2");boy2.setBirthYear("1990");boy2.setBirthMonth("02");boy2.setSex("男");boy2.setName("张三2");boy2.setIdCard("张三2");boyList.add(boy2);Boy boy8 = new Boy();boy8.setCountry("中国");boy8.setProvince("山西省");boy8.setCity("晋中市");boy8.setCounty("平遥县");boy8.setTown("岳壁乡");boy8.setVillage("金村");boy8.setStreet("向阳街道-3");boy8.setBirthYear("1990");boy8.setBirthMonth("02");boy8.setSex("男");boy8.setName("张三3");boy8.setIdCard("张三33");boyList.add(boy8);Boy boy4 = new Boy();boy4.setCountry("中国");boy4.setProvince("陕西省");boy4.setCity("渭南市");boy4.setCounty("渭南县");boy4.setTown("渭南乡");boy4.setVillage("渭南村");boy4.setStreet("渭南向阳街道");boy4.setBirthYear("1990");boy4.setBirthMonth("02");boy4.setSex("男");boy4.setName("张三1");boy4.setIdCard("渭南张三1");boyList.add(boy4);Boy boy10 = new Boy();boy10.setCountry("中国");boy10.setProvince("陕西省");boy10.setCity("渭南市");boy10.setCounty("渭南县");boy10.setTown("渭南乡");boy10.setVillage("渭南村");boy10.setStreet("渭南向阳街道");boy10.setBirthYear("1990");boy10.setBirthMonth("02");boy10.setSex("男");boy10.setName("李四");boy10.setIdCard("渭南李四");boyList.add(boy10);Boy boy5 = new Boy();boy5.setCountry("中国");boy5.setProvince("陕西省");boy5.setCity("渭南市");boy5.setCounty("渭南县2");boy5.setTown("渭南乡2");boy5.setVillage("渭南村2");boy5.setStreet("渭南向阳街道");boy5.setBirthYear("1990");boy5.setBirthMonth("02");boy5.setSex("男");boy5.setName("张三1");boy5.setIdCard("渭南张三1");boyList.add(boy5);Boy boy9 = new Boy();boy9.setCountry("中国");boy9.setProvince("陕西省");boy9.setCity("咸阳市");boy9.setCounty("咸阳县2");boy9.setTown("咸阳乡2");boy9.setVillage("咸阳村2");boy9.setStreet("咸阳向阳街道");boy9.setBirthYear("1990");boy9.setBirthMonth("02");boy9.setSex("男");boy9.setName("张三1");boy9.setIdCard("咸阳张三1");boyList.add(boy9);Boy boy3 = new Boy();boy3.setCountry("美国");boy3.setProvince("美国省");boy3.setCity("美国市");boy3.setCounty("美国县");boy3.setTown("美国乡");boy3.setVillage("美国村");boy3.setStreet("美国街道");boy3.setBirthYear("1990");boy3.setBirthMonth("02");boy3.setSex("男");boy3.setName("美国张三2");boy3.setIdCard("美国张三2");boyList.add(boy3);Boy boy6 = new Boy();boy6.setCountry("美国");boy6.setProvince("美国省");boy6.setCity("美国市");boy6.setCounty("美国县");boy6.setTown("美国乡");boy6.setVillage("美国村-2");boy6.setStreet("美国街道");boy6.setBirthYear("1990");boy6.setBirthMonth("02");boy6.setSex("男");boy6.setName("美国张三2");boy6.setIdCard("美国张三2");boyList.add(boy6);return boyList;}

某一列两个单元格是否合并的工具方法：

    public void setMergeStartEndRow(LinkedList<Pair> list,int curRow,String preLastContent, String preCurContents,String lastContent,String curContent){if(!ObjectUtils.isEmpty(preLastContent) && !ObjectUtils.isEmpty(preCurContents) &&  preLastContent.equals(preCurContents)){if(lastContent.equals(curContent)){if(!CollectionUtils.isEmpty(list)){Pair lastPair = list.getLast();// 某一列要合并的单元格增加了一行if((int)lastPair.getValue() == curRow-1 ){Pair pair = list.removeLast();list.add(Pair.of(pair.getLeft(),curRow));}else{// 某一列这两行要合并list.add(Pair.of(curRow-1,curRow));}}else{// 某一列这两行要合并list.add(Pair.of(curRow-1,curRow));}}else{}}else{// 不相等不处理}}

测试方法：

 public void exportBoys(HttpServletResponse response) throws IOException {List<Boy> boysList = getBoysList();//  0 行，0列// TOTO 根据字段排序boysList.sort(Comparator.comparing(Boy::getCountry).thenComparing(Boy::getProvince).thenComparing(Boy::getCity).thenComparing(Boy::getCounty).thenComparing(Boy::getTown));// 第二列到第十列合并依据：两行数据前一列值相同（更准确的说法：某两行某一列，之前所有列对应的两行数据都相同）且两行数据当前列的value一样// eg//row1： 中国  北京市  海淀区  西二旗(当前列)//row2： 中国  北京市  海淀区  西二旗(当前列)// 核心代码：构建 sheet.addMergedRegion(new CellRangeAddress(startRow, endRow, column, column)); 的 startRow 和 endRowLinkedList<Pair> secondList = new LinkedList<>();LinkedList<Pair> list2 = new LinkedList<>();LinkedList<Pair> list3 = new LinkedList<>();LinkedList<Pair> list4 = new LinkedList<>();LinkedList<Pair> list5 = new LinkedList<>();LinkedList<Pair> list6 = new LinkedList<>();LinkedList<Pair> list7 = new LinkedList<>();LinkedList<Pair> list8 = new LinkedList<>();LinkedList<Pair> list9 = new LinkedList<>();LinkedList<Pair> list10 = new LinkedList<>();for(int row = 0;row <= boysList.size()-1;row++){if(row ==0 ){continue;}Boy curBoy = boysList.get(row);Boy lastBoy = boysList.get(row-1); // 上一行数据// 省份合并（要看前面国家和当前省份是否一样+当前行值和上一行值一样）setMergeStartEndRow(secondList,row,lastBoy.getCountry(),curBoy.getCountry(),lastBoy.getProvince(),curBoy.getProvince());//          城市合并（要看前面国家和前面省份是否一样(前面所有字段值都一样才合并)+当前行值和上一行值一样）setMergeStartEndRow(list2,row,lastBoy.getProvince(),curBoy.getProvince(),lastBoy.getCity(),curBoy.getCity());// 县合并setMergeStartEndRow(list3,row,lastBoy.getCity(),curBoy.getCity(),lastBoy.getCounty(),curBoy.getCounty());// 城镇合并setMergeStartEndRow(list4,row,lastBoy.getCounty(),curBoy.getCounty(),lastBoy.getTown(),curBoy.getTown());// 村合并()setMergeStartEndRow(list5,row,lastBoy.getTown(),curBoy.getTown(),lastBoy.getVillage(),curBoy.getVillage());// 街道合并setMergeStartEndRow(list6,row,lastBoy.getVillage(),curBoy.getVillage(),lastBoy.getStreet(),curBoy.getStreet());// 性别合并（城镇+村+街道 都一样才认为横向条件满足）setMergeStartEndRow(list7,row,getPrexStr(lastBoy.getTown(),lastBoy.getVillage(),lastBoy.getStreet()),getPrexStr(curBoy.getTown(),curBoy.getVillage(),curBoy.getStreet()),lastBoy.getSex(),curBoy.getSex());// name 合并 （城镇+村+街道+性别 都一样才认为横向条件满足）setMergeStartEndRow(list8,row,getPrexStr(lastBoy.getTown(),lastBoy.getVillage(),lastBoy.getStreet(),lastBoy.getSex()),getPrexStr(curBoy.getTown(),curBoy.getVillage(),curBoy.getStreet(),curBoy.getSex()),lastBoy.getName(),curBoy.getName());//年份合并setMergeStartEndRow(list9,row,lastBoy.getName(),curBoy.getName(),lastBoy.getBirthYear(),curBoy.getBirthYear());// 月份合并 （城镇+村+街道+性别+名称+年份 都一样才认为横向条件满足）setMergeStartEndRow(list10,row,getPrexStr(lastBoy.getTown(),lastBoy.getVillage(),lastBoy.getStreet(),lastBoy.getSex(),lastBoy.getName(),lastBoy.getBirthYear()),getPrexStr(curBoy.getTown(),curBoy.getVillage(),curBoy.getStreet(),curBoy.getSex(),curBoy.getName(),curBoy.getBirthYear()),lastBoy.getBirthMonth(),curBoy.getBirthMonth());}Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("标题", "副标题"), Boy.class, boysList);Sheet sheet = workbook.getSheet("副标题");// 第一列PoiMergeCellUtil.mergeCells(sheet,2,0);secondList.forEach(pair->{// 标题占了两行，+2sheet.addMergedRegion(new CellRangeAddress((int)pair.getLeft()+2, (int)pair.getValue()+2, 1, 1));});list2.forEach(pair->{// 标题占了两行，+2sheet.addMergedRegion(new CellRangeAddress((int)pair.getLeft()+2, (int)pair.getValue()+2, 2, 2));});list3.forEach(pair->{// 标题占了两行，+2sheet.addMergedRegion(new CellRangeAddress((int)pair.getLeft()+2, (int)pair.getValue()+2, 3, 3));});list4.forEach(pair->{// 标题占了两行，+2sheet.addMergedRegion(new CellRangeAddress((int)pair.getLeft()+2, (int)pair.getValue()+2, 4, 4));});list5.forEach(pair->{// 标题占了两行，+2sheet.addMergedRegion(new CellRangeAddress((int)pair.getLeft()+2, (int)pair.getValue()+2, 5, 5));});list6.forEach(pair->{// 标题占了两行，+2sheet.addMergedRegion(new CellRangeAddress((int)pair.getLeft()+2, (int)pair.getValue()+2, 6, 6));});list7.forEach(pair->{// 标题占了两行，+2sheet.addMergedRegion(new CellRangeAddress((int)pair.getLeft()+2, (int)pair.getValue()+2, 7, 7));});list8.forEach(pair->{// 标题占了两行，+2sheet.addMergedRegion(new CellRangeAddress((int)pair.getLeft()+2, (int)pair.getValue()+2, 8, 8));});list9.forEach(pair->{// 标题占了两行，+2sheet.addMergedRegion(new CellRangeAddress((int)pair.getLeft()+2, (int)pair.getValue()+2, 9, 9));});list10.forEach(pair->{// 标题占了两行，+2sheet.addMergedRegion(new CellRangeAddress((int)pair.getLeft()+2, (int)pair.getValue()+2, 10, 10));});response.setContentType("application/vnd.ms-excel");response.setHeader("Content-disposition", "attachment;filename=data.xlsx");workbook.write(response.getOutputStream());}

 // 获取前面列集合对应的值字符串
public String getPrexStr(String... strs){StringBuilder sb = new StringBuilder();for(String str:strs){if(!ObjectUtils.isEmpty(str)){sb.append("[");sb.append(str);sb.append("]");}else{sb.append("[");sb.append(str);sb.append("]");}}return sb.toString();}