How to Export Massive Excel Files Efficiently with Alibaba EasyExcel in Java

1. Introduction

Export is a common feature in backend systems; large data sets cause memory overflow and page freeze. The author previously built a custom export using batch queries and SXSSFWorkbook, but it had drawbacks such as an unfriendly API, high memory usage, and edge‑case bugs.

Alibaba's open‑source EasyExcel can keep memory usage at the KB level, avoid OOM, and process about 1 000 000 rows with dozens of columns in ~70 seconds.

Therefore the author switched to EasyExcel, noting that the previous implementation used design patterns like Facade, Template Method, and delegation.

https://github.com/alibaba/easyexcel

2. Example

2.1 Maven Dependency

<!-- Alibaba Open Source EXCEL -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>1.1.1</version>
</dependency>

2.2 POJO

package com.authorization.privilege.excel;

import java.util.Date;

public class User {
    private String uid;
    private String name;
    private Integer age;
    private Date birthday;

    public User() {}

    public User(String uid, String name, Integer age, Date birthday) {
        this.uid = uid;
        this.name = name;
        this.age = age;
        this.birthday = birthday;
    }

    // getters and setters omitted for brevity
}

2.3 Test Scenarios

2.3.1 Small data (≤200 000 rows): write all rows to a single sheet in one call.

/**
 * For a relatively small record count (≈200k) fetch all data at once and write to a single Excel sheet.
 * Ensure the record count per fetch is not too large to avoid OOM.
 */
@Test
public void writeExcelOneSheetOnceWrite() throws IOException {
    OutputStream out = new FileOutputStream("E:\\temp\\withoutHead1.xlsx");
    ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
    Sheet sheet = new Sheet(1, 0);
    sheet.setSheetName("sheet1");
    Table table = new Table(1);
    List<List<String>> titles = new ArrayList<>();
    titles.add(Arrays.asList("用户ID"));
    titles.add(Arrays.asList("名称"));
    titles.add(Arrays.asList("年龄"));
    titles.add(Arrays.asList("生日"));
    table.setHead(titles);
    List<List<String>> userList = new ArrayList<>();
    for (int i = 0; i < 100; i++) {
        userList.add(Arrays.asList("ID_" + i, "小明" + i, String.valueOf(i), new Date().toString()));
    }
    writer.write0(userList, sheet, table);
    writer.finish();
}

2.3.2 Medium data (≤1 000 000 rows): write to one sheet in batches.

/**
 * For up to 1 000 000 records, fetch and write data in multiple batches to a single sheet.
 * Each batch size is controlled by PER_WRITE_ROW_COUNT.
 */
@Test
public void writeExcelOneSheetMoreWrite() throws IOException {
    OutputStream out = new FileOutputStream("E:\\temp\\withoutHead2.xlsx");
    ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
    Sheet sheet = new Sheet(1, 0);
    sheet.setSheetName("sheet1");
    Table table = new Table(1);
    List<List<String>> titles = new ArrayList<>();
    titles.add(Arrays.asList("用户ID"));
    titles.add(Arrays.asList("名称"));
    titles.add(Arrays.asList("年龄"));
    titles.add(Arrays.asList("生日"));
    table.setHead(titles);
    Integer totalRowCount = 50;
    Integer pageSize = 20;
    Integer writeCount = totalRowCount % pageSize == 0 ? (totalRowCount / pageSize) : (totalRowCount / pageSize + 1);
    for (int i = 0; i < writeCount; i++) {
        List<List<String>> userList = new ArrayList<>();
        if (i < writeCount - 1) {
            for (int j = 0; j < pageSize; j++) {
                userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));
            }
        } else {
            Integer lastWriteRowCount = totalRowCount - (writeCount - 1) * pageSize;
            for (int j = 0; j < lastWriteRowCount; j++) {
                userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));
            }
        }
        writer.write0(userList, sheet, table);
    }
    writer.finish();
}

2.3.3 Very large data (several million rows): write to multiple sheets, each sheet written in batches.

/**
 * For several million records, split data across multiple sheets.
 * Each sheet receives PER_SHEET_ROW_COUNT rows, written in batches of PER_WRITE_ROW_COUNT.
 */
@Test
public void writeExcelMoreSheetMoreWrite() throws IOException {
    OutputStream out = new FileOutputStream("E:\\temp\\withoutHead3.xlsx");
    ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
    String sheetName = "测试SHEET";
    Table table = new Table(1);
    List<List<String>> titles = new ArrayList<>();
    titles.add(Arrays.asList("用户ID"));
    titles.add(Arrays.asList("名称"));
    titles.add(Arrays.asList("年龄"));
    titles.add(Arrays.asList("生日"));
    table.setHead(titles);
    Integer totalRowCount = 250;
    Integer perSheetRowCount = 100;
    Integer pageSize = 20;
    Integer sheetCount = totalRowCount % perSheetRowCount == 0 ? (totalRowCount / perSheetRowCount) : (totalRowCount / perSheetRowCount + 1);
    Integer previousSheetWriteCount = perSheetRowCount / pageSize;
    Integer lastSheetWriteCount = totalRowCount % perSheetRowCount == 0 ? previousSheetWriteCount : (totalRowCount % perSheetRowCount % pageSize == 0 ? totalRowCount % perSheetRowCount / pageSize : totalRowCount % perSheetRowCount / pageSize + 1);
    for (int i = 0; i < sheetCount; i++) {
        Sheet sheet = new Sheet(i, 0);
        sheet.setSheetName(sheetName + i);
        int maxWrite = (i != sheetCount - 1) ? previousSheetWriteCount : lastSheetWriteCount;
        for (int j = 0; j < maxWrite; j++) {
            List<List<String>> dataList = new ArrayList<>();
            for (int k = 0; k < pageSize; k++) {
                dataList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));
            }
            writer.write0(dataList, sheet, table);
        }
    }
    writer.finish();
}

2.4 Production Code

2.4.0 Excel Constants

package com.authorization.privilege.constant;

public class ExcelConstant {
    /** rows per sheet, 1 000 000 */
    public static final Integer PER_SHEET_ROW_COUNT = 1000000;
    /** rows per write, 200 000 */
    public static final Integer PER_WRITE_ROW_COUNT = 200000;
}

Export methods for small, medium, and large data sets follow the same pattern: create an ExcelWriter, configure a Sheet and Table, page through data with PageHelper (or manual loops), write the data, finish the writer, and stream the file to the HTTP response.

3. Conclusion

Testing with 1 000 000 rows and 18 fields took about 70 seconds; actual performance depends on SQL efficiency. Recommendations include using single‑table queries for pagination and caching foreign‑key lookups in a map (e.g., via @MapKey) to reduce query count.

When counting rows on very large tables, adjusting MySQL buffer parameters can speed up the COUNT(1) query. Limit‑based pagination may exhibit inconsistent performance and warrants further investigation.