easyexcel-patterns

EasyExcel Excel Processing

Source:https://easyexcel.opensource.alibaba.com/
GitHub：https://github.com/alibaba/easyexcel

Capability Boundaries

✅ Strong Suits

1. Read — Listener mode (large files/streaming) vs synchronous mode (small files <1000 rows)
2. Write — Paged/batched writing/template filling/dynamic headers/custom styles/multiple writes
3. Million-level optimization — Batched writing + flush strategy + Sheet splitting
4. Template filling — Complex-format reports (functions/merged cells/charts)
5. Web export — Write directly to OutputStream (no temporary files)

❌ Out of Scope

1. Complex Word/PPT operations → Apache POI (few scenarios)
2. CSV files → commons-csv or OpenCSV

Read Mode Selection Decision

文件大小？
├── < 1000行 → 同步模式：doReadSync()
├── 1000~1万行 → 监听器模式，全量收集再处理
└── > 1万行 → 监听器模式 + 分批处理(每2000行flush一次)

Mistakes LLMs Most Commonly Make

#	Mistake	Correct Approach
1	Hand-writing POI Workbook/Sheet/Row/Cell boilerplate code	EasyExcel one line EasyExcel.read().sheet().doRead()
2	Loading a large file all at once into memory causes OOM	Listener mode, process every 2000 rows
3	Export does not close the stream or ExcelWriter	finally { excelWriter.finish() }
4	Not knowing about template filling	EasyExcel.write().withTemplate("template.xlsx").sheet().doFill(data)
5	Using inMemory(true) for template filling	⚠️ Only use inMemory(true) when exporting templates; never use it when filling data!
6	Querying all data at once for million-level export	Paged query (2000~5000 per batch) + batched writing
7	Web export writes a file first, then reads it into response	Directly EasyExcel.write(response.getOutputStream())
8	Reusing a singleton listener	A new listener must be created for each read

Core Patterns

Pattern 1: Synchronous read (small files <1000 rows)

// ✅ 小文件：同步读取，返回全部List
List<UserDTO> list = EasyExcel.read(file)
    .head(UserDTO.class)
    .sheet()
    .doReadSync();

Pattern 2: Listener read (large files, batched processing)

// ✅ 大文件：监听器模式，分批处理避免OOM
public class BatchExcelListener<T> extends AnalysisEventListener<T> {
    private static final int BATCH_COUNT = 2000;
    private final List<T> batch = new ArrayList<>(BATCH_COUNT);
    private final Consumer<List<T>> processor;  // 外部批量处理函数

    public BatchExcelListener(Consumer<List<T>> processor) {
        this.processor = processor;
    }

    @Override
    public void invoke(T data, AnalysisContext context) {
        batch.add(data);
        if (batch.size() >= BATCH_COUNT) {
            processor.accept(new ArrayList<>(batch));
            batch.clear();  // ← 关键：释放内存
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        if (!batch.isEmpty()) {
            processor.accept(new ArrayList<>(batch));
            batch.clear();
        }
    }
}

// 使用(每2000条批量存库)
EasyExcel.read(file, UserDTO.class, new BatchExcelListener<>(userService::batchInsert))
    .sheet()
    .doRead();

Pattern 3: Million-level writing (paged query + batched writing)

public void exportLargeData(HttpServletResponse response, int totalCount) {
    // ✅ 直接写 OutputStream(不写临时文件)
    try (ExcelWriter excelWriter = EasyExcel.write(
            response.getOutputStream(), UserExportDTO.class).build()) {

        WriteSheet writeSheet = EasyExcel.writerSheet("用户列表").build();
        int pageSize = 2000;
        int totalPage = (int) Math.ceil((double) totalCount / pageSize);

        for (int pageNum = 1; pageNum <= totalPage; pageNum++) {
            List<UserExportDTO> pageData = userService.findPage(pageNum, pageSize);
            excelWriter.write(pageData, writeSheet);
            pageData.clear();  // ← 分批释放
        }

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment;filename=users.xlsx");
    }
}

Pattern 4: Template filling (complex reports)

// ✅ 模板填充——先在Excel模板中定义变量 {name} {.name}
// 导出模板时用 inMemory(true)，填充数据时不用！

// 导出模板
EasyExcel.write(fileName, DemoData.class)
    .inMemory(true)  // ← 导出模板必须加！否则变量名被分片损坏
    .sheet("模板")
    .doWrite(fillData());

// 使用模板填充数据
try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
        .withTemplate(templateStream)
        .build()) {
    WriteSheet writeSheet = EasyExcel.writerSheet().build();
    FillConfig fillConfig = FillConfig.builder().forceNewRow(true).build();

    // 分页填充(避免OOM)
    for (int page = 1; ; page++) {
        List<?> data = getDataByPage(page, 2000);
        if (data.isEmpty()) break;
        excelWriter.fill(data, fillConfig, writeSheet);
        data.clear();
    }
}

Pattern 5: Entity definition

@Data
public class UserExportDTO {
    @ExcelProperty(value = "用户名", index = 0)  // 指定列顺序
    private String username;

    @ExcelProperty("年龄")
    @ContentStyle(dataFormat = 1)               // 整数格式
    private Integer age;

    @ExcelIgnore                                 // 不导出
    private String password;

    @ExcelProperty("创建时间")
    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")      // 日期格式
    private LocalDateTime createTime;

    @ExcelProperty("状态")
    @ContentStyle(dataFormat = 0x31)            // 自定义格式
    private String status;
}

Pattern 6: Asynchronous export (over 500,000 rows)

@GetMapping("/export/async")
public Result<String> triggerExport() {
    String taskId = "EXPORT_" + System.currentTimeMillis();
    asyncTaskExecutor.execute(() -> doExport(taskId));
    return Result.ok("导出已提交，taskId: " + taskId);
}

private void doExport(String taskId) {
    int total = userService.countTotal();
    try (ExcelWriter writer = EasyExcel.write(...).build()) {
        // 写入时更新进度(可通过Redis/cache查询)
        for (int i = 1; i <= totalPage; i++) {
            List<?> data = userService.findPage(i, 2000);
            writer.write(data, writeSheet);
            exportTaskService.updateProgress(taskId, i * 100 / totalPage);
        }
    }
}

Gotchas

1. Large files (>10,000 rows) must use listener mode — Synchronous mode loads everything at once and causes OOM
2. Spring beans cannot be autowired in a listener — Manual new requires passing parameters, or manually obtain from SpringContext
3. Template filling's {} placeholder syntax — {name}(single) or{.name}(list) two ways
4. @ExcelProperty's index starts from 0 — Ensure it corresponds to the Excel column
5. EasyExcel is based on reflection — Excel mapping becomes invalid after field names change
6. Date formatting requires @DateTimeFormat — Otherwise defaults to yyyy-MM-dd HH:mm:ss
7. finally { excelWriter.finish() } must be executed — Otherwise the file is corrupted
8. inMemory(true) Only use when exporting templates — Never use it for real data filling, otherwise all data resides in memory → OOM
9. A listener must be new for each read — Cannot reuse a singleton because its internal state is not cleared
10. Single Sheet limit is 1.04 million rows — If the limit is exceeded, split into multiple Sheets or switch to CSV
11. For web export, write directly to the OutputStream — Do not write to a file first and then read it; reduce IO
12. Use wrapper types (Integer) for entity class fields — Avoid null pointer issues with primitive types

Data Privacy

This skill does not collect, store, or transmit any user data.