回复架构师获取资源
大家好，我是你们的朋友架构君，一个会写代码吟诗的架构师。
一、引入
通过阅读本文，你将了解到：
使用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
@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 一次性查询写出
不考虑单元格合并时，你可能会这样实现：一次性查询所有数据，然后一次性写出。
// 查询所有数据
private static List<BillExpenseDetail> 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页签中。
// 查询数据总量
private int count() {
// 假设为100万
return 1000000;
}
// 分页查询
private List<BillExpenseDetail> 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<BillExpenseDetail> detailList = pageQuery(i * pageSize, pageSize);
excelWriter.write(detailList, writeSheet);
// help gc
detailList.clear();
}
}
}
现在，查询数据时内存压力减小了。可是等上线后，发现导出一个月的数据可能需要10秒，导出半年内数据时可能需要50秒或更长时间。如果是离线导出，耗时久点也能接受；如果是在线导出，可能接口响应超时。
有更高效的办法吗？
2.3 并发查询依次写出
查询一页数据写出后，再查询下一页，读与写是串行的。而且分页查询时页码越大，一次查询耗时越久。那么我们能否并发查询同时写出呢？当然可以。
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<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) {
throw new RuntimeException(e);
}
}
}
}
三、合并单元格写出Excel
现在我们考虑如何实现合并单元格。因为一个票据有多个明细行（数量不确定），导出Excel时要将“票据编号”、“创建时间”等列跨行合并。该如何实现呢？
3.1 EasyExcel中实现
EasyExcel提供了两个创建合并单元格的注解，以及与注解等效的WriteHandler接口实现。定义下面的数据类，我们来试用一下。
@Getter
@Setter
public class DemoMergeData {
@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)
public class DemoMergeData {
@ExcelProperty("字符串")
private String string;
@ExcelProperty("日期")
private Date date;
@ExcelProperty("数字")
private Double doubleData;
}
效果如下：
3.1.3 WriteHandler实现
EasyExcel提供了与上面两个注解等效的WriteHandler实现，分别是OnceAbsoluteMergeStrategy和LoopMergeStrategy。使用方式如下：
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层面，每写一行数据，将合并列的单元格数据，与上一行的单元格数据比较。如果数据相同，就将当前行与上一行合并；如果上一行已被合并，则将当前行加入到合并区。
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<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 当前列
*/
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<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
*/
public class AddCellRangeWriteHandler implements SheetWriteHandler {
private final List<CellRangeAddress> rangeCellList;
public AddCellRangeWriteHandler(List<CellRangeAddress> 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);
}
}
}
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<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中需要合并的列的索引
final int[] 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) {
throw new 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到3
int endRowIndex = lineCount + count - 1;
for (int columnIndex : mergeColumnIndex) {
rangeCellList.add(new CellRangeAddress(startRowIndex, endRowIndex, columnIndex, columnIndex));
}
lineCount += count;
}
return rangeCellList;
}
该方式我已在工作中使用，性能确实有较大提升。感兴趣的小伙伴，也不妨一试。
来源：juejin.cn/post/7322156759443095561
这些年小编给你分享过的干货
转发在看就是最大的支持❤️
