Spring Boot Excel Export OOM: Replacing XSSFWorkbook with SXSSFWorkbook and Batch Processing

When an order export endpoint was asked to export 487,632 records, the JVM heap climbed from 600 MB to 1.9 GB before throwing java.lang.OutOfMemoryError: Java heap space. The root cause was two memory‑heavy assumptions: the code loaded all database rows into a List<Order> and then built the entire Excel workbook in memory with XSSFWorkbook. Each order existed simultaneously as a database entity, a DTO, an Excel row, and multiple cell objects, multiplying memory usage.

Problem: OOM with XSSFWorkbook

The original controller fetched all orders via orderService.queryAll(query), created an XSSFWorkbook, iterated over the list to create rows and cells, and finally wrote the workbook to the response output stream. With 500k rows and a dozen columns, the heap could not hold the combined object graph.

@GetMapping("/orders/export")
public void export(OrderQuery query, HttpServletResponse response) throws IOException {
    List<Order> orders = orderService.queryAll(query);
    XSSFWorkbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet("订单");
    int rowIndex = 0;
    Row header = sheet.createRow(rowIndex++);
    header.createCell(0).setCellValue("订单号");
    header.createCell(1).setCellValue("用户");
    header.createCell(2).setCellValue("金额");
    header.createCell(3).setCellValue("状态");
    header.createCell(4).setCellValue("下单时间");
    for (Order order : orders) {
        Row row = sheet.createRow(rowIndex++);
        row.createCell(0).setCellValue(order.getOrderNo());
        row.createCell(1).setCellValue(order.getUserName());
        row.createCell(2).setCellValue(order.getAmount().doubleValue());
        row.createCell(3).setCellValue(order.getStatus().name());
        row.createCell(4).setCellValue(order.getCreateTime().toString());
    }
    workbook.write(response.getOutputStream());
    workbook.close();
}

Solution 1: Streaming with SXSSFWorkbook

Apache POI provides SXSSFWorkbook, a low‑memory streaming implementation. It keeps only a sliding window of rows in memory (default 100, configurable) and flushes older rows to disk. Replacing new XSSFWorkbook() with new SXSSFWorkbook(500) limits in‑memory rows to the most recent 500.

try (SXSSFWorkbook workbook = new SXSSFWorkbook(500)) {
    Sheet sheet = workbook.createSheet("订单");
    // write data
}

POI documentation explicitly positions SXSSF for “large spreadsheets + limited heap space”. After this change, Excel‑related memory dropped dramatically, but the List<Order> still loaded all entities at once.

Solution 2: Cursor‑Based Batch Query with DTOs

To avoid loading 500k entities, the author introduced batch fetching using a cursor (keyset pagination) instead of offset pagination. Each batch fetches 2,000 rows ordered by descending ID, using the last seen ID as the cursor for the next batch. Only the columns needed for export are selected into a dedicated OrderExportDTO record, eliminating unused fields like remark, address, version, and associated entities.

public record OrderExportDTO(
    Long id,
    String orderNo,
    String userName,
    BigDecimal amount,
    String status,
    LocalDateTime createTime
) {}

The repository uses a custom JPQL query with a constructor expression:

@Query("""
    select new com.demo.export.OrderExportDTO(
        o.id,
        o.orderNo,
        o.userName,
        o.amount,
        o.status,
        o.createTime
    )
    from Order o
    where o.id < :lastId
      and o.createTime >= :startTime
      and o.createTime < :endTime
    order by o.id desc
""")
List<OrderExportDTO> findExportBatch(
    @Param("lastId") long lastId,
    @Param("startTime") LocalDateTime startTime,
    @Param("endTime") LocalDateTime endTime,
    Pageable pageable
);

The export loop becomes:

long lastId = Long.MAX_VALUE;
while (true) {
    List<OrderExportDTO> batch = repository.findExportBatch(
        lastId, query.startTime(), query.endTime(),
        PageRequest.of(0, BATCH_SIZE)
    );
    if (batch.isEmpty()) break;
    for (OrderExportDTO order : batch) {
        writeRow(sheet, rowIndex++, order);
    }
    lastId = batch.getLast().id();
}

Optimizations: Reusing Styles and Fixed Column Widths

To keep the streaming workbook lightweight, the author avoided expensive operations that force POI to retain data in memory:

No autoSizeColumn on large datasets.

No massive merged regions, complex styles, images, comments, or formulas.

Fixed column widths set once: sheet.setColumnWidth(0, 22 * 256); etc.

Cell styles created once and reused: CellStyle amountStyle = createAmountStyle(workbook); then applied per cell.

POI documentation warns that even with SXSSF, large numbers of merged regions and comments remain in memory.

Final Architecture: Async Export for Very Large Datasets

When the requirement grew to 1 million rows, synchronous HTTP export became impractical due to gateway timeouts, browser disconnects, and duplicate submissions. The team introduced an async task model:

Small exports (< 50k rows): synchronous download.

Large exports (>= 50k rows): create an export task, return taskId, generate Excel in background, upload to object storage, mark task SUCCESS with a download URL.

A simple export_task table tracks status, progress, and file location. This decouples export lifetime from the web request, survives server restarts, and allows rate limiting (e.g., max 2 concurrent exports per user).

Key Takeaways

If data volume grows 10×, memory should not grow 10×; identify objects that only need to exist briefly and stream them.

Replace XSSFWorkbook with SXSSFWorkbook for large Excel files.

Use cursor‑based pagination (keyset) instead of offset pagination for deep scans.

Project only required columns into DTOs; avoid loading full entities.

Reuse styles, set fixed column widths, and avoid features that break streaming.

For very large exports, move generation out of the request‑response cycle into an async task queue.