# 一、引入通过阅读本文，你将了解到： - 使用EasyExcel写出Excel时，如何一步步提升性能； - 写出有合并单元格的页签时，如何在更短的时间内写出更多的数据。如果你第一次接触EasyExcel，可以先访问官网，按照指引掌握EasyExcel读写操作，然后再阅读本文。
如果你已经掌握了使用EasyExcel读写Excel，那么当你需要写出有合并单元格的大页签时，你会如何实现？ >github地址：https://github.com/alibaba/easyexcel
官方网站: https://easyexcel.opensource.alibaba.com/
语雀文档：https://www.yuque.com/easyexcel/doc ```java com.alibaba easyexcel 3.1.1 ``` ## 假设有个需求假设你所在的公司需要开发一个功能：将数据库中**票据表**写出到Excel中，而且想在尽可能短的时间内（如30秒）写出几个月甚至一年内的数据（可能有几十万、上百万条记录），你会如何实现？
我们先来看看票据的一个简单模型：由一个头信息区、多条明细两部分组成，写出到Excel时样式如下。 ![image.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/fc5453e7da194ecc9f3187c2aacc21d7~tplv-k3u1fbpfcp-jj-mark:0:0:0:0:q75.image#?w=819&h=525&s=447756&e=png&b=faf6f5) ![image.png](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/d9f2d1eecb3145a88cf5fc7d72d165aa~tplv-k3u1fbpfcp-jj-mark:0:0:0:0:q75.image#?w=811&h=217&s=30404&e=png&b=fcfbfb) ```java 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 @AllArgsConstructor public class BillExpenseDetail { @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 一次性查询写出不考虑单元格合并时，你可能会这样实现：一次性查询所有数据，然后一次性写出。 ```java // 查询所有数据 private static List queryAll() { return new ArrayList<>(); } public static void simpleWrite() { 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页签中。 ```java // 查询数据总量 private int count() { // 假设为100万 return 1000000; } // 分页查询 private List pageQuery(int startIndex, int limit) { return new ArrayList<>(); } public void repeatedWrite() { 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 detailList = pageQuery(i * pageSize, pageSize); excelWriter.write(detailList, writeSheet); // help gc detailList.clear(); } } } ``` 现在，查询数据时内存压力减小了。可是等上线后，**发现导出一个月的数据可能需要10秒，导出半年内数据时可能需要50秒或更长时间**。如果是离线导出，耗时久点也能接受；**如果是在线导出，可能接口响应超时**。
有更高效的办法吗？ ## 2.3 并发查询依次写出 **查询一页数据写出后，再查询下一页，读与写是串行的**。而且分页查询时页码越大，一次查询耗时越久。那么我们能否并发查询同时写出呢？当然可以。 ```java public static final ExecutorService EXECUTOR_SERVICE = Executors.newFixedThreadPool(4); public void repeatedWrite() { // 并发分页查询数据 int count = count(); int pageSize = 1000; int pageCount = count / pageSize; pageCount = pageCount * pageSize < count ? pageCount + 1 : pageCount; List>> futureList = new ArrayList<>(pageCount); for (int i = 0; i < pageCount; i++) { int index = i; Future> 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> future : futureList) { try { List detailList = future.get(); excelWriter.write(detailList, writeSheet); // help gc detailList.clear(); } catch (InterruptedException | ExecutionException e) { throw new RuntimeException(e); } } } } ``` # 三、合并单元格写出Excel 现在我们考虑如何实现合并单元格。因为一个票据有多个明细行（数量不确定），导出Excel时要将“票据编号”、“创建时间”等列跨行合并。该如何实现呢？ ## 3.1 EasyExcel中实现 EasyExcel提供了两个创建合并单元格的注解，以及与注解等效的WriteHandler接口实现。定义下面的数据类，我们来试用一下。 ```java @Getter @Setter public class DemoMergeData { @ExcelProperty("字符串") private String string; @ExcelProperty("日期") private Date date; @ExcelProperty("数字") private Double doubleData; } ``` ### 3.1.1 @ContentLoopMerge ![image.png](https://p9-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/f34608bb644f4b2a941c6b46b1f3c221~tplv-k3u1fbpfcp-jj-mark:0:0:0:0:q75.image#?w=655&h=239&s=31688&e=png&b=2b2b2b)
先不使用@ContentLoopMerge，生成的Excel如下：
![image.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/063a2d2ac6144f2fbe9bb08ce0268f5b~tplv-k3u1fbpfcp-jj-mark:0:0:0:0:q75.image#?w=410&h=308&s=27433&e=png&b=f7f6f6)
对第一列使用@ContentLoopMerge后，生成的Excel如下： ```java // 每两行合并一次，跨两列 @ContentLoopMerge(eachRow = 2, columnExtend = 2) @ExcelProperty("字符串") private String string; ``` ![image.png](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/e282d5cc5cfe45a78f9d6abfdb80c630~tplv-k3u1fbpfcp-jj-mark:0:0:0:0:q75.image#?w=396&h=331&s=17173&e=png&b=f9f9f9)
### 3.1.2 @OnceAbsoluteMerge 该注解通过指定合并区域行列索引，用来创建一个合并区域（不是循环创建）；单元格值取左上角单元格的。
![image.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/470a5957c7ab4969901cc51af57eed93~tplv-k3u1fbpfcp-jj-mark:0:0:0:0:q75.image#?w=604&h=340&s=38976&e=png&b=2c2c2c) ```java @Getter @Setter // 将第2-6行的2-3列合并 @OnceAbsoluteMerge(firstRowIndex =1, lastRowIndex = 5, firstColumnIndex = 1, lastColumnIndex = 2) public class DemoMergeData { @ExcelProperty("字符串") private String string; @ExcelProperty("日期") private Date date; @ExcelProperty("数字") private Double doubleData; } ``` 效果如下：
![image.png](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/6c9f4fed34b640b3ad598f52ded66ff5~tplv-k3u1fbpfcp-jj-mark:0:0:0:0:q75.image#?w=461&h=321&s=24271&e=png&b=f8f7f7) ### 3.1.3 WriteHandler实现 EasyExcel提供了与上面两个注解等效的WriteHandler实现，分别是`OnceAbsoluteMergeStrategy`和`LoopMergeStrategy`。使用方式如下： ```java public static void mergeWrite() { 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层面，每写一行数据，将合并列的单元格数据，与上一行的单元格数据比较。如果数据相同，就将当前行与上一行合并；如果上一行已被合并，则将当前行加入到合并区。` ```java 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; public class ExcelFillCellMergeStrategy implements CellWriteHandler { // 需要创建合并区的列 private int[] mergeColumnIndex; // 从第几行后开始合并，取列头行 private int mergeRowIndex; public ExcelFillCellMergeStrategy() { } public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) { this.mergeRowIndex = mergeRowIndex; this.mergeColumnIndex = mergeColumnIndex; } @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List> 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 当前列 */ 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(); // 将当前单元格数据与上一个单元格数据比较 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 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次，修改合并区域两次。 ![image.png](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/d9f2d1eecb3145a88cf5fc7d72d165aa~tplv-k3u1fbpfcp-jj-mark:0:0:0:0:q75.image#?w=811&h=217&s=30404&e=png&b=fcfbfb) 此外，网上还有一些**基于RowWriteHandler接口的实现，也存在上面指出的性能问题。** ### 3.2.2 我的实现当我们分页查询票据记录后，可以按照合并自动进行分组，每组数量就是合并区域大小，合并区域位置可以通过行数累加来定位。因此。写出Excel前就可以预知那些合并区域。如果在创建sheet页时就将这些区域一并创建，写出时就不用关注单元格合并了。岂不美哉！
**预创建合并区**：实现SheetWriteHandler接口，重写afterSheetCreate()，将合并区域加入到sheet中。 ![image.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/3c62511f28e94068b731dfa324501d1e~tplv-k3u1fbpfcp-jj-mark:0:0:0:0:q75.image#?w=1135&h=424&s=54955&e=png&b=2b2b2b) ```java 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 */ public class AddCellRangeWriteHandler implements SheetWriteHandler { private final List rangeCellList; public AddCellRangeWriteHandler(List rangeCellList) { this.rangeCellList = (rangeCellList == null) ? Collections.emptyList() : rangeCellList; } public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { Sheet sheet = writeSheetHolder.getSheet(); for (CellRangeAddress cellRangeAddress : this.rangeCellList) { sheet.addMergedRegionUnsafe(cellRangeAddress); } } } ``` ```java public static final ExecutorService EXECUTOR_SERVICE = Executors.newFixedThreadPool(4); public void repeatedWrite() { // 并发分页查询数据 int count = count(); int pageSize = 1000; int pageCount = count / pageSize; pageCount = pageCount * pageSize < count ? pageCount + 1 : pageCount; List>> futureList = new ArrayList<>(pageCount); for (int i = 0; i < pageCount; i++) { int index = i; Future> 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中需要合并的列的索引 final int[] mergeColumnIndex = {0, 1, 2, 3}; WriteSheet writeSheet; for (Future> future : futureList) { try { List detailList = future.get(); List 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) { throw new RuntimeException(e); } } } } /** * 生成合并区 * * @param detailList 票据 * @param mergeColumnIndex sheet 中需要合并的列的索引 * @param lineCount 行计数（包括列头行） * @return 合并区 */ private List createCellRange(List detailList, int[] mergeColumnIndex, int lineCount) { if (detailList.isEmpty()) { return Collections.emptyList(); } List rangeCellList = new ArrayList<>(); // 使用LinkedHashMap，与detailList保持同顺序 LinkedHashMap groupMap = detailList.stream().collect(Collectors.groupingBy(BillExpenseDetail::getNumber, LinkedHashMap::new, Collectors.counting())); for (Map.Entry entry : groupMap.entrySet()) { int count = entry.getValue().intValue(); int startRowIndex = lineCount; // 如合并第2到4行，共3行，行索引从1到3 int endRowIndex = lineCount + count - 1; for (int columnIndex : mergeColumnIndex) { rangeCellList.add(new CellRangeAddress(startRowIndex, endRowIndex, columnIndex, columnIndex)); } lineCount += count; } return rangeCellList; } ``` 该方式我已在工作中使用，性能确实有较大提升。感兴趣的小伙伴，也不妨一试。