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();}