How to Export Millions of Records to Excel Efficiently with Alibaba EasyExcel

1. Introduction

Exporting data is a common feature in admin systems; large data sets cause memory overflow and page freezing. The author previously built a custom exporter 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 library can keep memory consumption at the KB level and avoids OOM, while processing about 1 000 000 rows with dozens of columns in roughly 70 seconds.

2. Example

2.1 Maven Dependency

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>1.1.1</version>
</dependency>

2.2 POJO Definition

package com.authorization.privilege.excel;

import java.util.Date;

public class User {
    private String uid;
    private String name;
    private Integer age;
    private Date birthday;
    // getters and setters omitted for brevity
}

2.3 Test Scenarios

Three data‑size scenarios are demonstrated:

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

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

Huge data (several million rows) – split across multiple sheets, each written in batches.

Each scenario includes a JUnit test method that creates an ExcelWriter, configures a Sheet and a Table with column headers, simulates data retrieval, and calls writer.write0(...) followed by writer.finish().

2.4 Production Code

A constant class defines PER_SHEET_ROW_COUNT (1 000 000) and PER_WRITE_ROW_COUNT (200 000). Service methods exportSysSystemExcel show how to stream data from MyBatis, set response headers, and write the Excel file for the three size categories.

package com.authorization.privilege.constant;

public class ExcelConstant {
    public static final Integer PER_SHEET_ROW_COUNT = 1000000;
    public static final Integer PER_WRITE_ROW_COUNT = 200000;
}

3. Conclusion

Real‑world performance depends on SQL efficiency; using simple single‑table queries and caching foreign‑key look‑ups in a map (e.g., with @MapKey) reduces query count and speeds up export. Counting rows with COUNT(1) can be slow on massive tables, so tuning MySQL buffers may be necessary.