效率爆表!EasyExcel合并单元格这样实现才是yyds

一、引入

通过阅读本文，你将了解到：

• 使用EasyExcel写出Excel时，如何一步步提升性能；
• 写出有合并单元格的页签时，如何在更短的时间内写出更多的数据。

如果你第一次接触EasyExcel，可以先访问官网，按照指引掌握EasyExcel读写操作，然后再阅读本文。

如果你已经掌握了使用EasyExcel读写Excel，那么当你需要写出有合并单元格的大页签时，你会如何实现？

<!-- 本文引用的版本 --><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.1.1</version></dependency>

假设有个需求

假设你所在的公司需要开发一个功能：将数据库中票据表写出到Excel中，而且想在尽可能短的时间内（如30秒）写出几个月甚至一年内的数据（可能有几十万、上百万条记录），你会如何实现？

我们先来看看票据的一个简单模型：由一个头信息区、多条明细两部分组成，写出到Excel时样式如下。

import com.alibaba.excel.annotation.ExcelProperty;import lombok.AllArgsConstructor;import lombok.Getter;import lombok.NoArgsConstructor;import lombok.Setter;import java.math.BigDecimal;import java.util.Date;/** * 一张票据下有多项费用科目 */@Getter@Setter@NoArgsConstructor@AllArgsConstructorpublicclassBillExpenseDetail{@ExcelProperty("票据编号")private String number;@ExcelProperty("创建时间")private Date createDate;@ExcelProperty("收支方向")private String direction ;@ExcelProperty("总金额")private BigDecimal totalAmount;@ExcelProperty("名称规格")private String subject;@ExcelProperty("单价")private String price;@ExcelProperty("数量")private String quantity;@ExcelProperty("单位")private String unit;@ExcelProperty("金额")private BigDecimal amount;}

二、无合并单元格时

2.1 一次性查询写出

不考虑单元格合并时，你可能会这样实现：一次性查询所有数据，然后一次性写出。

// 查询所有数据privatestatic List<BillExpenseDetail> queryAll(){returnnew ArrayList<>();}publicstaticvoidsimpleWrite(){  String fileName = "/bill/simpleWrite.xlsx";try (ExcelWriter excelWriter = EasyExcel.write(fileName, BillExpenseDetail.class).build()) {    WriteSheet writeSheet = EasyExcel.writerSheet("票据").build();    excelWriter.write(queryAll(), writeSheet);  }}

这样实现有问题吗？数据量较少时没问题。

可是，当一次需要写出的数据有数万条甚至更多时，将所有数据一次性查询到内存中，当所有数据写出后，才能释放内存。这样可能导致很大的内存压力，甚至服务OOM。

有什么更好的办法吗？有。

2.2 分页查询写出

EasyExcel支持重复多次写单个或者多个Sheet页，我们可以多次分页查库获取数据，循环写入到一个Excel页签中。

// 查询数据总量privateintcount(){// 假设为100万return1000000;  }// 分页查询private List<BillExpenseDetail> pageQuery(int startIndex, int limit){returnnew ArrayList<>();  }publicvoidrepeatedWrite(){int count = count();int pageSize = 1000;int pageCount = count / pageSize;    pageCount = pageCount * pageSize < count ? pageCount + 1 : pageCount;    String fileName = "/bill/repeatedWrite.xlsx";try (ExcelWriter excelWriter = EasyExcel.write(fileName, BillExpenseDetail.class).build()) {// 写出到一个sheet页中，因此在for外面创建WriteSheet      WriteSheet writeSheet = EasyExcel.writerSheet("票据").build();// 逐页查询，追加写出for (int i = 0; i < pageCount; i++) {        List<BillExpenseDetail> detailList = pageQuery(i * pageSize, pageSize);        excelWriter.write(detailList, writeSheet);// help gc        detailList.clear();      }    }  }

现在，查询数据时内存压力减小了。可是等上线后，发现导出一个月的数据可能需要10秒，导出半年内数据时可能需要50秒或更长时间。如果是离线导出，耗时久点也能接受；如果是在线导出，可能接口响应超时。

有更高效的办法吗？

2.3 并发查询依次写出

查询一页数据写出后，再查询下一页，读与写是串行的。而且分页查询时页码越大，一次查询耗时越久。那么我们能否并发查询同时写出呢？当然可以。

publicstaticfinal ExecutorService EXECUTOR_SERVICE = Executors.newFixedThreadPool(4);publicvoidrepeatedWrite(){// 并发分页查询数据int count = count();int pageSize = 1000;int pageCount = count / pageSize;    pageCount = pageCount * pageSize < count ? pageCount + 1 : pageCount;    List<Future<List<BillExpenseDetail>>> futureList = new ArrayList<>(pageCount);for (int i = 0; i < pageCount; i++) {int index = i;      Future<List<BillExpenseDetail>> submit = EXECUTOR_SERVICE.submit(        () -> pageQuery(index * pageSize, pageSize));      futureList.add(submit);    }    String fileName = "/bill/repeatedWrite.xlsx";try (ExcelWriter excelWriter = EasyExcel.write(fileName, BillExpenseDetail.class).build()) {// 写出到一个sheet页中      WriteSheet writeSheet = EasyExcel.writerSheet("票据").build();// 追加写for (Future<List<BillExpenseDetail>> future : futureList) {try {          List<BillExpenseDetail> detailList = future.get();          excelWriter.write(detailList, writeSheet);// help gc          detailList.clear();        } catch (InterruptedException | ExecutionException e) {thrownew RuntimeException(e);        }      }    }  }

三、合并单元格写出Excel

现在我们考虑如何实现合并单元格。因为一个票据有多个明细行（数量不确定），导出Excel时要将“票据编号”、“创建时间”等列跨行合并。该如何实现呢？

3.1 EasyExcel中实现

EasyExcel提供了两个创建合并单元格的注解，以及与注解等效的WriteHandler接口实现。定义下面的数据类，我们来试用一下。

@Getter@SetterpublicclassDemoMergeData{@ExcelProperty("字符串")private String string;@ExcelProperty("日期")private Date date;@ExcelProperty("数字")private Double doubleData;}

3.1.1 @ContentLoopMerge

先不使用@ContentLoopMerge，生成的Excel如下：

对第一列使用@ContentLoopMerge后，生成的Excel如下：

// 每两行合并一次，跨两列@ContentLoopMerge(eachRow = 2, columnExtend = 2)@ExcelProperty("字符串")private String string;

3.1.2 @OnceAbsoluteMerge

该注解通过指定合并区域行列索引，用来创建一个合并区域（不是循环创建）；单元格值取左上角单元格的。

@Getter@Setter// 将第2-6行的2-3列合并@OnceAbsoluteMerge(firstRowIndex =1, lastRowIndex = 5, firstColumnIndex = 1, lastColumnIndex = 2)publicclassDemoMergeData{@ExcelProperty("字符串")private String string;@ExcelProperty("日期")private Date date;@ExcelProperty("数字")private Double doubleData;}

效果如下：

3.1.3 WriteHandler实现

EasyExcel提供了与上面两个注解等效的WriteHandler实现，分别是OnceAbsoluteMergeStrategy和LoopMergeStrategy。使用方式如下：

publicstaticvoidmergeWrite(){  String fileName = "/excel/mergeWrite.xlsx";// 对第一列每隔2行合并一次，不跨列（第二个参数）  LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 1, 0);// 创建合并区：将第2-6行的2-3列合并  OnceAbsoluteMergeStrategy absoluteMergeStrategy = new OnceAbsoluteMergeStrategy(1, 5, 1, 2);  EasyExcel.write(fileName, DemoMergeData.class)      .registerWriteHandler(loopMergeStrategy)      .registerWriteHandler(absoluteMergeStrategy)      .sheet("模板")      .doWrite(data());}

3.2 自定义合并策略

3.2.1 网络上的常规实现

票据导出时因为每个票据的明细行数量不定，@ContentLoopMerge就不适用了。此时，我们自然想到去网上找找方案。比如，我找到了这篇博客《EasyExcel导出自定义合并单元格策略》：

• https://cloud.tencent.com/developer/article/1671316。

它的实现方式如下，核心逻辑为：

实现CellWriteHandler接口，在Cell层面，每写一行数据，将合并列的单元格数据，与上一行的单元格数据比较。如果数据相同，就将当前行与上一行合并；如果上一行已被合并，则将当前行加入到合并区。

import com.alibaba.excel.metadata.Head;import com.alibaba.excel.metadata.data.WriteCellData;import com.alibaba.excel.write.handler.CellWriteHandler;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.metadata.holder.WriteTableHolder;import java.util.List;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;publicclassExcelFillCellMergeStrategyimplementsCellWriteHandler{// 需要创建合并区的列privateint[] mergeColumnIndex;// 从第几行后开始合并，取列头行privateint mergeRowIndex;publicExcelFillCellMergeStrategy(){  }publicExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex){this.mergeRowIndex = mergeRowIndex;this.mergeColumnIndex = mergeColumnIndex;  }@OverridepublicvoidbeforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex,      Integer relativeRowIndex, Boolean isHead){  }@OverridepublicvoidafterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex,      Boolean isHead){  }@OverridepublicvoidafterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> 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   * @param cell             当前单元格   * @param curRowIndex      当前行   * @param curColIndex      当前列   */privatevoidmergeWithPrevRow(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();// 将当前单元格数据与上一个单元格数据比较    Boolean dataBool = preData.equals(curData);//此处需要注意：因为我是按照序号确定是否需要合并的，所以获取每一行第一列数据和上一行第一列数据进行比较，如果相等合并    Boolean bool = cell.getRow().getCell(0).getNumericCellValue() == cell.getSheet().getRow(curRowIndex - 1).getCell(0).getNumericCellValue();if (dataBool && bool) {      Sheet sheet = writeSheetHolder.getSheet();      List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();boolean isMerged = false;for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {        CellRangeAddress cellRangeAddr = mergeRegions.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);      }    }  }}

能实现我们导出票据的需求吗？能。但是试用后将会发现，这个实现性能不佳：

• 每写入一个单元格，都需要读取上一行，一边写入一边读取；
• 当上一行已经合并过了，本次写入需要修改合并区域，而且会反复修改；
• 比如，写出下图中第一个票据，写出3行，将读取3次，修改合并区域两次。

此外，网上还有一些基于RowWriteHandler接口的实现，也存在上面指出的性能问题。

3.2.2 我的实现

当我们分页查询票据记录后，可以按照合并自动进行分组，每组数量就是合并区域大小，合并区域位置可以通过行数累加来定位。

因此，写出Excel前就可以预知那些合并区域。如果在创建sheet页时就将这些区域一并创建，写出时就不用关注单元格合并了。岂不美哉！

预创建合并区：实现SheetWriteHandler接口，重写afterSheetCreate()，将合并区域加入到sheet中。

import com.alibaba.excel.write.handler.SheetWriteHandler;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;import java.util.Collections;import java.util.List;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.util.CellRangeAddress;/** * 添加合并区Handler */publicclassAddCellRangeWriteHandlerimplementsSheetWriteHandler{privatefinal List<CellRangeAddress> rangeCellList;publicAddCellRangeWriteHandler(List<CellRangeAddress> rangeCellList){this.rangeCellList = (rangeCellList == null) ? Collections.emptyList() : rangeCellList;  }publicvoidafterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder){    Sheet sheet = writeSheetHolder.getSheet();for (CellRangeAddress cellRangeAddress : this.rangeCellList) {      sheet.addMergedRegionUnsafe(cellRangeAddress);    }  }}

publicstaticfinal ExecutorService EXECUTOR_SERVICE = Executors.newFixedThreadPool(4);publicvoidrepeatedWrite(){// 并发分页查询数据int count = count();int pageSize = 1000;int pageCount = count / pageSize;    pageCount = pageCount * pageSize < count ? pageCount + 1 : pageCount;    List<Future<List<BillExpenseDetail>>> futureList = new ArrayList<>(pageCount);for (int i = 0; i < pageCount; i++) {int index = i;      Future<List<BillExpenseDetail>> submit = EXECUTOR_SERVICE.submit(        () -> pageQuery(index * pageSize, pageSize));      futureList.add(submit);    }// 追加写    String fileName = "/bill/repeatedWrite.xlsx";try (ExcelWriter excelWriter = EasyExcel.write(fileName, BillExpenseDetail.class).build()) {// 行计数，初始值取列头行数int lineCount = 1;// sheet中需要合并的列的索引finalint[] mergeColumnIndex = {0, 1, 2, 3};      WriteSheet writeSheet;for (Future<List<BillExpenseDetail>> future : futureList) {try {          List<BillExpenseDetail> detailList = future.get();          List<CellRangeAddress> rangeCellList = createCellRange(detailList, mergeColumnIndex, lineCount);          lineCount += detailList.size();// 写出到一个sheet页中，sheetName固定          writeSheet = EasyExcel.writerSheet("票据").registerWriteHandler(new AddCellRangeWriteHandler(rangeCellList)).build();          excelWriter.write(detailList, writeSheet);// 及时释放内存          detailList.clear();        } catch (InterruptedException | ExecutionException e) {thrownew RuntimeException(e);        }      }    }  }/**   * 生成合并区   *   * @param detailList       票据   * @param mergeColumnIndex sheet 中需要合并的列的索引   * @param lineCount        行计数（包括列头行）   * @return 合并区   */private List<CellRangeAddress> createCellRange(List<BillExpenseDetail> detailList, int[] mergeColumnIndex, int lineCount){if (detailList.isEmpty()) {return Collections.emptyList();    }    List<CellRangeAddress> rangeCellList = new ArrayList<>();    Map<String, Long> groupMap = detailList.stream().collect(Collectors.groupingBy(BillExpenseDetail::getNumber, Collectors.counting()));for (Map.Entry<String, Long> entry : groupMap.entrySet()) {int count = entry.getValue().intValue();int startRowIndex = lineCount;// 如合并第2到4行，共3行，行索引从1到3int endRowIndex = lineCount + count - 1;for (int columnIndex : mergeColumnIndex) {        rangeCellList.add(new CellRangeAddress(startRowIndex, endRowIndex, columnIndex, columnIndex));      }      lineCount += count;    }return rangeCellList;  }

该方式我已在工作中使用，性能确实有较大提升。感兴趣的小伙伴，也不妨一试。

来源：juejin.cn/post/7322156759443095561