Resolving OutOfMemory Errors When Using Apache POI for Large Excel Exports

When processing large amounts of data with Apache POI, writing hundreds of thousands of rows to an Excel file can trigger an OutOfMemoryError even after increasing the JVM heap size.

The article explains that POI reads each row into a TreeMap -based HSSFRow structure, which keeps all rows in memory and therefore exhausts available memory as the data volume grows.

It presents the relevant POI source code that constructs HSSFWorkbook , reads records, and stores rows, illustrating the memory‑intensive path.

public HSSFWorkbook(DirectoryNode directory, boolean preserveNodes) throws IOException {
    super(directory);
    String workbookName = getWorkbookDirEntryName(directory);
    this.preserveNodes = preserveNodes;
    if (!preserveNodes) {
        clearDirectory();
    }
    _sheets = new ArrayList
(INITIAL_CAPACITY);
    names  = new ArrayList
(INITIAL_CAPACITY);
    InputStream stream = directory.createDocumentInputStream(workbookName);
    List
records = RecordFactory.createRecords(stream);
    workbook = InternalWorkbook.createWorkbook(records);
    setPropertiesFromWorkbook(workbook);
    int recOffset = workbook.getNumRecords();
    convertLabelRecords(records, recOffset);
    RecordStream rs = new RecordStream(records, recOffset);
    while (rs.hasNext()) {
        try {
            InternalSheet sheet = InternalSheet.createSheet(rs);
            _sheets.add(new HSSFSheet(this, sheet));
        } catch (UnsupportedBOFType eb) {
            log.log(POILogger.WARN, "Unsupported BOF found of type " + eb.getType());
        }
    }
    for (int i = 0; i < workbook.getNumNames(); ++i) {
        NameRecord nameRecord = workbook.getNameRecord(i);
        HSSFName name = new HSSFName(this, nameRecord, workbook.getNameCommentRecord(nameRecord));
        names.add(name);
    }
}

A second snippet shows how rows are added to the low‑level model, further confirming that each row object remains in memory.

private void addRow(HSSFRow row, boolean addLow) {
    _rows.put(Integer.valueOf(row.getRowNum()), row);
    if (addLow) {
        _sheet.addRow(row.getRowRecord());
    }
    boolean firstRow = _rows.size() == 1;
    if (row.getRowNum() > getLastRowNum() || firstRow) {
        _lastrow = row.getRowNum();
    }
    if (row.getRowNum() < getFirstRowNum() || firstRow) {
        _firstrow = row.getRowNum();
    }
}

To solve the memory problem, the article recommends using POI’s streaming API SXSSFWorkbook , which writes rows to temporary files and keeps only a configurable number of rows in memory, resulting in a saw‑tooth memory pattern that stays within limits.

package org.bird.poi;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URL;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.junit.Assert;
public class XSSFWriter {
    private static SXSSFWorkbook wb;
    public static void main(String[] args) throws IOException {
        wb = new SXSSFWorkbook(10000);
        Sheet sh = wb.createSheet();
        for (int rownum = 0; rownum < 100000; rownum++) {
            Row row = sh.createRow(rownum);
            for (int cellnum = 0; cellnum < 10; cellnum++) {
                Cell cell = row.createCell(cellnum);
                String address = new CellReference(cell).formatAsString();
                cell.setCellValue(address);
            }
        }
        // Rows with rownum < 90000 are flushed and not accessible
        for (int rownum = 0; rownum < 90000; rownum++) {
            Assert.assertNull(sh.getRow(rownum));
        }
        // The last 10000 rows are still in memory
        for (int rownum = 90000; rownum < 100000; rownum++) {
            Assert.assertNotNull(sh.getRow(rownum));
        }
        URL url = XSSFWriter.class.getClassLoader().getResource("");
        FileOutputStream out = new FileOutputStream(url.getPath() + File.separator + "writer.xlsx");
        wb.write(out);
        out.close();
        wb.dispose(); // dispose of temporary files
    }
}

By adopting SXSSFWorkbook , memory consumption remains steady, allowing Java backend services to generate very large Excel files without crashing.