Optimizing Excel Export with EasyExcel: Performance Improvements and Merged‑Cell Strategies

Hello, I am a top architect.

1. Introduction

Through this article you will learn how to improve performance when writing Excel files with EasyExcel and how to handle merged cells efficiently for large data sets.

Step‑by‑step performance tuning for EasyExcel.

Techniques for writing merged‑cell sheets quickly.

2. Writing without merged cells

2.1 Single‑batch write

When the data volume is small, you can query all records at once and write them in a single call.

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

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

This approach works for small data sets but can cause OOM when the record count reaches tens of thousands or more.

2.2 Paginated write

EasyExcel supports repeated writes to the same sheet, allowing you to page through the database and write each page sequentially.

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

Memory pressure is reduced, but exporting half a year of data may still take dozens of seconds, which is unacceptable for online interfaces.

2.3 Concurrent paginated write

By querying pages concurrently and writing them sequentially, you can overlap I/O and CPU work.

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 a bill has multiple detail rows, columns such as "票据编号" and "创建时间" need to be merged across those rows.

3.1 EasyExcel merge annotations

EasyExcel provides two annotations and equivalent WriteHandler interfaces.

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

3.1.1 @ContentLoopMerge

Merge every N rows in a column.

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

3.1.2 @OnceAbsoluteMerge

Merge a fixed rectangular region.

@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 implementation

LoopMergeStrategy and OnceAbsoluteMergeStrategy can be registered as WriteHandlers.

public static void mergeWrite() {
    String fileName = "/excel/mergeWrite.xlsx";
    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

Instead of merging during write, pre‑compute merge regions after paging the data and add them to the sheet before writing.

public class ExcelFillCellMergeStrategy implements CellWriteHandler {
    private int[] mergeColumnIndex;
    private int mergeRowIndex;
    // constructor and overridden methods omitted for brevity
    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        // compare current cell with the cell above and merge if equal
    }
}

This approach avoids per‑cell reads and repeated region modifications, greatly improving performance.

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

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

The pre‑created merge regions eliminate runtime merging overhead and significantly speed up large‑scale Excel exports.