Huge Files with SXSSF Streaming

The Problem

XSSFWorkbook keeps the whole workbook in memory — a million-row export easily needs gigabytes of heap. SXSSFWorkbook keeps only a sliding window (default 100 rows) and spills the rest to temp files.

Streaming Write

import org.apache.poi.xssf.streaming.SXSSFWorkbook;

try (SXSSFWorkbook wb = new SXSSFWorkbook(100)) { // keep 100 rows in memory
    Sheet sheet = wb.createSheet("big");
    for (int r = 0; r < 1_000_000; r++) {
        Row row = sheet.createRow(r);
        row.createCell(0).setCellValue("row-" + r);
        row.createCell(1).setCellValue(r * 1.5);
    }
    try (FileOutputStream out = new FileOutputStream("big.xlsx")) {
        wb.write(out);
    }
    wb.dispose(); // deletes temp files — never forget this
}

Rules for SXSSF

• Always call dispose() (preferably in a finally block) or temp files pile up.
• Random access to flushed rows is impossible — write top-to-bottom, once.
• For huge reads, use the event (SAX) API or XSSFReader instead of XSSFWorkbook.

Full runnable version: LargeExcelStreamingExample in the poi-examples repo.