前言
本文想要达到以及最终实现的效果：
要实现这种效果，包含两个部分的操作：
1. 动态表头
EasyExcel 生成 Excel 时要使表头有合并效果，可以采用**注解和非注解（动态表头）**的方法。因为要导出的数据没有实体类，所以采用动态表头的方法。表头用一个List<List<String>>对象来封装，其中外层 List 表示每一列的表头，内层 List 表示同一列的多行表头，当同一列或同一行的数据相同时，会进行表头的合并。
2. 数据合并
数据合并本文实现了两种方法：一是相邻行的数据相同时自动合并，二是指定要合并的行列范围。具体思路请看代码中的注释。合并相同数据行的方法与目标结果有些微差别：
pom.xml
<dependencies><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.12</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.6</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.22</version></dependency></dependencies>
MergeSameRowsStrategy
package com.kuang;import com.alibaba.excel.metadata.CellData;import com.alibaba.excel.metadata.Head;import com.alibaba.excel.write.handler.CellWriteHandler;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.metadata.holder.WriteTableHolder;import lombok.Data;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellType;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.util.CellRangeAddress;import java.util.List;/*** 合并相同数据行策略** @author wangbo* @since 01月25日 10:16*/@Datapublic class MergeSameRowsStrategy implements CellWriteHandler {private int[] mergeColumnIndex;private int mergeRowIndex;public MergeSameRowsStrategy() {}public MergeSameRowsStrategy(int mergeRowIndex, int[] mergeColumnIndex) {this.mergeRowIndex = mergeRowIndex;this.mergeColumnIndex = mergeColumnIndex;}@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {// 当前行int curRowIndex = cell.getRowIndex();// 当前列int curColIndex = cell.getColumnIndex();if (curRowIndex > mergeRowIndex) {for (int i = 0; i < mergeColumnIndex.length; i++) {if (curColIndex == mergeColumnIndex[i]) {mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);break;}}}}/*** 当前单元格向上合并** @param writeSheetHolder sheet保持对象* @param cell 当前单元格* @param curRowIndex当前行* @param curColIndex当前列*/private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {// 获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();// 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行if (curData.equals(preData)) {Sheet sheet = writeSheetHolder.getSheet();List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();boolean isMerged = false;for (int i = 0; i < mergedRegions.size() && !isMerged; i++) {CellRangeAddress cellRangeAddr = mergedRegions.get(i);// 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {sheet.removeMergedRegion(i);cellRangeAddr.setLastRow(curRowIndex);sheet.addMergedRegion(cellRangeAddr);isMerged = true;}}// 若上一个单元格未被合并，则新增合并单元if (!isMerged) {CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);sheet.addMergedRegion(cellRangeAddress);}}}}
AssignRowsAndColumnsToMergeStrategy
import com.alibaba.excel.metadata.Head;import com.alibaba.excel.write.merge.AbstractMergeStrategy;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.util.CellRangeAddress;import java.util.List;/*** 指定合并行列范围策略** @author wangbo* @since 01月21日 20:31*/public class AssignRowsAndColumnsToMergeStrategy extends AbstractMergeStrategy {// 合并坐标集合private List<CellRangeAddress> cellRangeAddresses;// 构造public AssignRowsAndColumnsToMergeStrategy() {}public AssignRowsAndColumnsToMergeStrategy(List<CellRangeAddress> cellRangeAddresses) {this.cellRangeAddresses = cellRangeAddresses;}/*** 合并操作：对每个单元格执行！！！** @param sheet sheet对象* @param cell 当前单元格* @param head 表头对象* @param relativeRowIndex 相关行索引*/@Overrideprotected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {/** 合并单元格** 由于merge()方法会在写每个单元格(cell)时执行，因此需要保证合并策略只被添加一次。否则如果每个单元格都添加一次* 合并策略，则会造成重复合并。例如合并A2:A3,当cell为A2时，合并A2:A3，但是当cell为A3时，又要合并A2:A3，而此时* 的A2已经是之前的A2和A3合并后的结果了。* 由于此处的表头占了两行，因此数据单元格是从(2, 0)开始的，所以就对这个单元格(cell.getRowIndex() == 2 && cell.getColumnIndex() == 0)* 添加一次合并策略就可以了。如果表头只有一行，则判断条件改为「cell.getRowIndex() == 1 && cell.getColumnIndex() == 0」就可以了。*/if (cell.getRowIndex() == 2 && cell.getColumnIndex() == 0) {for (CellRangeAddress item : cellRangeAddresses) {sheet.addMergedRegion(item);}}/** 如果不作判断，可以使用addMergedRegionUnsafe()方法，* 这样生成的Excel文件可以打开，只是打开时会提示内容有问题，修复后可以打开*/// for (CellRangeAddress item : cellRangeAddresses) {//sheet.addMergedRegionUnsafe(item);// }}}
MergeTest
import com.alibaba.excel.EasyExcel;import com.kuang.MergeSameRowsStrategy;import org.apache.poi.ss.util.CellRangeAddress;import org.junit.Test;import java.util.ArrayList;import java.util.Arrays;import java.util.List;/*** @author wangbo* @since 01月21日 20:29*/public class MergeTest {private static String fileName = "D:\\" + System.currentTimeMillis() + ".xlsx";@Testpublic void test01() {EasyExcel.write(fileName).head(header())// 合并策略：合并相同数据的行。第一个参数表示从哪一行开始进行合并，由于表头占了两行，因此从第2行开始（索引从0开始）// 第二个参数是指定哪些列要进行合并.registerWriteHandler(new MergeSameRowsStrategy(2, new int[]{0, 1, 2, 3, 8, 9}))// 注意：需要先调用registerWriteHandler()再调用sheet()方法才能使合并策略生效！！！.sheet("模板").doWrite(data());}@Testpublic void test02() {// 合并策略：指定要合并的行列范围int[][] toMergeRows = {{2, 3}, {4, 6}};int[] toMergeColumns = {0, 1, 2, 3, 8, 9};List<CellRangeAddress> list = new ArrayList<>();for (int[] toMergeRow : toMergeRows) {for (int toMergeColumn : toMergeColumns) {list.add(new CellRangeAddress(toMergeRow[0], toMergeRow[1], toMergeColumn, toMergeColumn));}}EasyExcel.write(fileName).head(header()).registerWriteHandler(new AssignRowsAndColumnsToMergeStrategy(list)).sheet("模板").doWrite(data());}/*** 创建表头*/private List<List<String>> header() {List<List<String>> headers = new ArrayList<>();headers.add(Arrays.asList("提交人用户名", "提交人用户名"));headers.add(Arrays.asList("提交人姓名", "提交人姓名"));headers.add(Arrays.asList("创建时间", "创建时间"));headers.add(Arrays.asList("更新时间", "更新时间"));headers.add(Arrays.asList("学习经历", "时间"));headers.add(Arrays.asList("学习经历", "学校"));headers.add(Arrays.asList("学习经历", "专业"));headers.add(Arrays.asList("学习经历", "学位"));headers.add(Arrays.asList("工作单位", "工作单位"));headers.add(Arrays.asList("国籍", "国籍"));headers.add(Arrays.asList("获奖经历", "时间"));headers.add(Arrays.asList("获奖经历", "何种奖励"));return headers;}/*** 创建数据*/private List<List<Object>> data() {List<List<Object>> data = new ArrayList<>();data.add(Arrays.asList("fengqingyang", "风清扬", "-01-25 11:08", "-01-25 11:08",".9 ~ .7", "华山派", "剑宗", "剑宗高手", "隐居思过崖", "中国", ".12", "华山剑法高手"));data.add(Arrays.asList("fengqingyang", "风清扬", "-01-25 11:08", "-01-25 11:08",".9 ~ .7", "独孤求败", "独孤剑法", "剑术通神", "隐居思过崖", "中国", ".12", "剑法高手"));data.add(Arrays.asList("linghuchong", "令狐冲", "-01-25 12:08", "-01-25 12:08",".9 ~ 2024.7", "华山派", "气宗", "气宗庸手", "漂泊江湖", "中国", ".12", "华山剑法庸手"));data.add(Arrays.asList("linghuchong", "令狐冲", "-01-25 12:08", "-01-25 12:08","2024.9 ~ 2027.7", "风清扬", "独孤剑法", "剑法高手", "漂泊江湖", "中国", "2025.12", "剑法高手"));data.add(Arrays.asList("linghuchong", "令狐冲", "-01-25 12:08", "-01-25 12:08","2027.9 ~ 2030.7", "少林寺", "易筋经", "内功高手", "漂泊江湖", "中国", "2029.12", "内功高手"));return data;}}
