Optimizing Excel Export with EasyExcel: Performance, Pagination, Concurrency, and Cell‑Merging Strategies

1. Introduction

The article explains how to boost the performance of Excel generation using EasyExcel, especially when exporting massive datasets with merged cells. It outlines the goals: improving write speed and handling merged cells efficiently.

2. Writing Without Merged Cells

2.1 Simple One‑Time Write

A naive approach queries all data at once and writes it in a single batch, which works for small data but can cause OOM for millions of rows.

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

2.2 Pagination Write

To reduce memory pressure, data is fetched page by page and written incrementally.

private int count() { // assume 1,000,000 rows
    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()) {
        WriteSheet writeSheet = EasyExcel.writerSheet("票据").build();
        for (int i = 0; i < pageCount; i++) {
            List
detailList = pageQuery(i * pageSize, pageSize);
            excelWriter.write(detailList, writeSheet);
            detailList.clear(); // help GC
        }
    }
}

Even with pagination, exporting several months of data may still take dozens of seconds, which is unacceptable for online interfaces.

2.3 Concurrent Pagination Write

Queries are executed concurrently using a thread pool, and results are written sequentially to the same sheet.

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()) {
        WriteSheet writeSheet = EasyExcel.writerSheet("票据").build();
        for (Future
> future : futureList) {
            List
detailList = future.get();
            excelWriter.write(detailList, writeSheet);
            detailList.clear();
        }
    } catch (InterruptedException | ExecutionException e) {
        throw new RuntimeException(e);
    }
}

3. Writing With Merged Cells

When each bill has a variable number of detail rows, columns such as "票据编号" and "创建时间" need to be merged across those rows.

3.1 EasyExcel Built‑in Annotations

EasyExcel provides @ContentLoopMerge and @OnceAbsoluteMerge annotations to define merge regions.

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

3.1.1 @ContentLoopMerge

Merges every N rows in a specified column.

@ContentLoopMerge(eachRow = 2, columnExtend = 2)
@ExcelProperty("字符串")
private String string;

3.1.2 @OnceAbsoluteMerge

Creates a single merge region by specifying start/end row and column indices.

@OnceAbsoluteMerge(firstRowIndex = 1, lastRowIndex = 5, firstColumnIndex = 1, lastColumnIndex = 2)
public class DemoMergeData { ... }

3.1.3 WriteHandler Implementations

The same effects can be achieved by registering LoopMergeStrategy and OnceAbsoluteMergeStrategy as WriteHandlers.

LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 1, 0);
OnceAbsoluteMergeStrategy absoluteMergeStrategy = new OnceAbsoluteMergeStrategy(1, 5, 1, 2);
EasyExcel.write(fileName, DemoMergeData.class)
    .registerWriteHandler(loopMergeStrategy)
    .registerWriteHandler(absoluteMergeStrategy)
    .sheet("模板")
    .doWrite(data());

3.2 Custom Merge Strategy

Because the number of detail rows varies per bill, the built‑in annotations are insufficient. A custom strategy pre‑calculates merge regions based on grouping the data, then adds them to the sheet before writing.

public class AddCellRangeWriteHandler implements SheetWriteHandler {
    private final List
rangeCellList;
    public AddCellRangeWriteHandler(List
rangeCellList) {
        this.rangeCellList = rangeCellList == null ? Collections.emptyList() : rangeCellList;
    }
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        for (CellRangeAddress cellRangeAddress : this.rangeCellList) {
            sheet.addMergedRegionUnsafe(cellRangeAddress);
        }
    }
}

The export workflow becomes:

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;
        futureList.add(EXECUTOR_SERVICE.submit(() -> pageQuery(index * pageSize, pageSize)));
    }
    String fileName = "/bill/repeatedWrite.xlsx";
    try (ExcelWriter excelWriter = EasyExcel.write(fileName, BillExpenseDetail.class).build()) {
        int lineCount = 1; // header row
        int[] mergeColumnIndex = {0,1,2,3};
        for (Future
> future : futureList) {
            List
detailList = future.get();
            List
rangeCellList = createCellRange(detailList, mergeColumnIndex, lineCount);
            lineCount += detailList.size();
            WriteSheet writeSheet = EasyExcel.writerSheet("票据")
                .registerWriteHandler(new AddCellRangeWriteHandler(rangeCellList))
                .build();
            excelWriter.write(detailList, writeSheet);
            detailList.clear();
        }
    } catch (InterruptedException | ExecutionException e) {
        throw new RuntimeException(e);
    }
}

private List
createCellRange(List
detailList, int[] mergeColumnIndex, int lineCount) {
    if (detailList.isEmpty()) return Collections.emptyList();
    List
rangeCellList = new ArrayList<>();
    Map
groupMap = detailList.stream()
        .collect(Collectors.groupingBy(BillExpenseDetail::getNumber, Collectors.counting()));
    for (Map.Entry
entry : groupMap.entrySet()) {
        int count = entry.getValue().intValue();
        int startRowIndex = lineCount;
        int endRowIndex = lineCount + count - 1;
        for (int columnIndex : mergeColumnIndex) {
            rangeCellList.add(new CellRangeAddress(startRowIndex, endRowIndex, columnIndex, columnIndex));
        }
        lineCount += count;
    }
    return rangeCellList;
}

This pre‑creation of merge regions eliminates per‑cell checks, dramatically improving performance for large exports.

Conclusion

The article wraps up by encouraging readers to share the knowledge and join the architecture community for further learning.