Avoid OOM: EasyExcel Best Practices for Exporting Millions of Rows (with Ready‑to‑Use Helper)

Problem

Exporting large data sets (tens of thousands to millions of rows) with EasyExcel can cause OutOfMemoryError, CPU/IO blockage, request timeouts, and overall service resource exhaustion because the entire result set is loaded into memory before writing.

Solution: Pagination query + batch write

Split the data into pages, write each page to the Excel file, and release the page’s memory before processing the next page.

@GetMapping("/export/simple")
public void exportSimple(HttpServletResponse response) throws IOException {
    // ❗ Risk: full‑load query – OOM for >10k rows
    List<User> allUsers = userService.findAllUsers();
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    String fileName = URLEncoder.encode("用户列表", "UTF-8");
    response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
    EasyExcel.write(response.getOutputStream(), User.class)
            .sheet("用户信息")
            .doWrite(allUsers); // full write
}

The above code loads all rows into memory and should be avoided for data sets larger than ~10 000 rows.

Core helper: PageWriteExcelHelper

public class PageWriteExcelHelper<T> {
    public interface PageQuerySupplier<T> {
        List<T> getPage(int pageNum, int pageSize);
    }

    public static <T> void writeByPage(OutputStream outputStream,
                                      Class<T> head,
                                      int pageSize,
                                      int totalCount,
                                      PageQuerySupplier<T> supplier) {
        ExcelWriter excelWriter = EasyExcel.write(outputStream, head).build();
        WriteSheet writeSheet = EasyExcel.writerSheet("Sheet1").build();
        try {
            int totalPage = totalCount > 0 ? (int) Math.ceil((double) totalCount / pageSize) : 1;
            for (int pageNum = 1; pageNum <= totalPage; pageNum++) {
                List<T> pageData = supplier.getPage(pageNum, pageSize);
                excelWriter.write(pageData, writeSheet);
                pageData.clear(); // release memory
            }
        } finally {
            if (excelWriter != null) {
                excelWriter.finish(); // ensure resources are closed
            }
        }
    }
}

The helper defines a functional interface PageQuerySupplier for fetching a page of data, calculates the total number of pages, iterates over each page, writes it, and clears the list to free memory. The ExcelWriter is closed in a finally block.

Usage example

@GetMapping("/export/million")
public void exportMassiveData(HttpServletResponse response) throws IOException {
    int totalUsers = userService.countTotalUsers();
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    String fileName = URLEncoder.encode("百万用户数据", "UTF-8");
    response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
    PageWriteExcelHelper.writeByPage(
            response.getOutputStream(),
            User.class,
            2000,               // page size
            totalUsers,
            (pageNum, pageSize) -> userService.findByPage(pageNum, pageSize));
}

Enhanced utility: ExcelExporter

public class ExcelExporter {
    // 1. Pagination export (large data)
    public static <T> void exportByPage(HttpServletResponse response,
                                        String fileName,
                                        String sheetName,
                                        Class<T> dataModel,
                                        int pageSize,
                                        int totalCount,
                                        PageWriteExcelHelper.PageQuerySupplier<T> pageSupplier) {
        setupResponse(response, fileName);
        try (OutputStream out = response.getOutputStream()) {
            PageWriteExcelHelper.writeByPage(out, dataModel, pageSize, totalCount, pageSupplier);
        } catch (Exception e) {
            throw new RuntimeException("导出失败: " + e.getMessage(), e);
        }
    }

    // 2. Simple export (small data)
    public static <T> void exportSimple(HttpServletResponse response,
                                        String fileName,
                                        String sheetName,
                                        Class<T> dataModel,
                                        List<T> dataList) {
        setupResponse(response, fileName);
        try (OutputStream out = response.getOutputStream()) {
            EasyExcel.write(out, dataModel).sheet(sheetName).doWrite(dataList);
        } catch (Exception e) {
            throw new RuntimeException("导出失败: " + e.getMessage(), e);
        }
    }

    private static void setupResponse(HttpServletResponse response, String fileName) {
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("UTF-8");
            String encodedFileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + encodedFileName + ".xlsx");
        } catch (Exception e) {
            throw new RuntimeException("设置响应头失败", e);
        }
    }

    @FunctionalInterface
    public interface PageQuerySupplier<T> {
        List<T> getPage(int pageNum, int pageSize);
    }
}

ExcelExporter

provides a unified entry point for both simple and paginated exports, handles response‑header encoding, uses try‑with‑resources to guarantee stream closure, and wraps exceptions into a uniform RuntimeException.

Advanced techniques

Dynamic page size – calculate an optimal batch size based on an estimated row size and the JVM’s free memory, using only a safe percentage (e.g., 40%).

Asynchronous export with progress tracking – submit the export as a background task, store task status, update progress after each page, and expose separate endpoints for progress query and file download.

Multi‑sheet export – create multiple WriteSheet objects and reuse the pagination helper for each sheet.

Template‑based styled export – load a pre‑styled .xlsx template and fill data rows using excelWriter.fill for complex formatting.

Performance benchmark (JDK 17, 4‑core CPU, 4 GB RAM, MySQL 8.0)

5 k rows – Simple mode ~350 ms, Paginated mode ~400 ms, memory 32 MB vs 28 MB (similar).

5 × 10⁴ rows – Simple mode ~2.5 s with OOM risk, Paginated mode ~2.3 s, memory 210 MB vs 48 MB (memory reduced ≈ 77 %).

5 × 10⁵ rows – Simple mode OOM, Paginated mode ~10.8 s, memory ≈ 52 MB (simple mode fails).

5 × 10⁶ rows – Simple mode not applicable, Paginated mode ~82 s, memory ≈ 55 MB, throughput ≈ 61 k rows/s (stable).

Conclusion: The paginated mode keeps memory usage stable even for massive data sets and completely eliminates OOM risk.

Best‑practice checklist

Choose a page size that balances query count and memory pressure (1 000–5 000 rows, or compute dynamically).

Always close ExcelWriter with finish() in a finally block.

Clear each page’s list after writing ( pageData.clear()) to release memory.

Optimize database pagination (use indexes, avoid large offsets).

Log export duration, memory usage, and row count for tuning.

For operations >30 s, switch to asynchronous execution with progress feedback.

Encapsulate common logic in reusable helpers like ExcelExporter to reduce errors.