Streaming Reads: Event & SAX APIs

This content was generated with the assistance of AI and should be verified against the official documentation before being relied on in production.

The reference material consulted while preparing these pages is listed in the section bibliography.

Baseline date: 2026-10-09. This page was written against Apache POI 5.5.1 (the latest release on that date; the 6.0 line is announced but has not been released), Java 21 and later, Spring Boot 4.x on Spring Framework 7 and Jakarta EE 11. It is the reading counterpart of Streaming Writes with SXSSF: where SXSSF keeps only a window of rows in memory while writing, the event APIs described here keep only the current row in memory while reading. They are what you reach for when a catalogue upload such as the Bookshelf loans-report.xlsx has hundreds of thousands of rows and WorkbookFactory.create would exhaust the heap.

The usermodel API (Spreadsheet Usermodel Basics) parses the whole file into an object tree: every Row, Cell, style and string is a Java object, and all of them stay reachable until the workbook is closed. The event APIs do the opposite. They tokenise the file and call you back for each piece they recognise, then forget it. Nothing is kept unless your own code keeps it. The price is a lower-level, read-only, forward-only API with no random access, no formula evaluation and no editing.

When to leave the usermodel

  • Size. The usermodel needs a multiple of the file size in heap, because the XML is expanded into objects. A few tens of megabytes of .xlsx can already need hundreds of megabytes of heap. There is no fixed ratio: it depends on the number of cells, on how many strings are unique and on styles. Measure your own files (see Memory and time: how to measure).

  • Throughput. A SAX pass over a sheet avoids building the object model, so it is normally faster as well as smaller; measure it on your own data.

  • Read-only extraction. Import jobs, search indexing and data migration only need the values once. That is the ideal use of an event API.

  • Not when you need to edit. To change a workbook you need the usermodel (or its streaming writer SXSSF, which cannot read an existing file either). Reading with events and writing a new file with SXSSF is the usual large-file conversion pipeline.

The three event APIs

POI has one event API per file family. They share a philosophy, not a class hierarchy.

Format Entry classes Callback interface Jar

.xlsx, .xlsm (OOXML)

OPCPackage, XSSFReader, ReadOnlySharedStringsTable, StylesTable, XSSFSheetXMLHandler

XSSFSheetXMLHandler.SheetContentsHandler: startRow, cell, endRow (and headerFooter)

poi-ooxml

.xlsb (binary OOXML, read only)

OPCPackage, XSSFBReader, XSSFBSharedStringsTable, XSSFBStylesTable, XSSFBSheetHandler

The same SheetContentsHandler interface

poi-ooxml

.xls (BIFF8, OLE2)

POIFSFileSystem, HSSFEventFactory, HSSFRequest

HSSFListener.processRecord(Record)

poi

The XLSX and XLSB handlers deliberately reuse one callback interface, so code that consumes cells (a mapper to a Book record, for instance) works for both formats. The HSSF API is older, closer to the binary records and has a different shape.

How SAX reading of an .xlsx works

An .xlsx file is a ZIP package (see File Formats: OOXML & OLE2). A sheet part (/xl/worksheets/sheet1.xml) does not contain text: string cells hold an index into /xl/sharedStrings.xml, and numeric cells hold a number whose meaning (integer, percentage, date) depends on the style index stored in /xl/styles.xml. To turn a <c> element back into the text a person sees, the parser needs all three parts. The classes map onto that structure:

Class Responsibility

OPCPackage

Opens the ZIP and its relationships. Open it with PackageAccess.READ so nothing is written back and no temporary copy is made.

XSSFReader

Locates the parts through the package relationships: getSheetsData(), getStylesTable(), getSharedStringsTable(), getWorkbookData(), getThemesData().

ReadOnlySharedStringsTable

A compact reader for sharedStrings.xml. It keeps the plain strings in memory but not the rich-text runs that the usermodel SharedStringsTable builds. Rich-text formatting is lost, which does not matter for data extraction.

StylesTable

Gives XSSFSheetXMLHandler the number format of every style so a date serial number can be told from a plain number.

XSSFSheetXMLHandler

A SAX ContentHandler for one sheet part. It resolves shared strings, applies DataFormatter, and calls your SheetContentsHandler.

SheetContentsHandler

Your code. Receives rows and cells.

The sequence of calls for one sheet is shown below. Only the parts inside the loop run per row and per cell.

sequenceDiagram participant App as Your code participant R as XSSFReader participant P as SAX XMLReader participant H as XSSFSheetXMLHandler participant S as SheetContentsHandler App->>R: new XSSFReader(pkg) App->>R: getStylesTable() and shared strings App->>R: getSheetsData().next() R-->>App: InputStream of one sheet App->>H: new XSSFSheetXMLHandler(styles, strings, S, formatter, false) App->>P: setContentHandler(H) then parse(stream) loop for every row element P->>H: startElement row H->>S: startRow(rowNum) loop for every cell element P->>H: startElement c, v, f, is P->>H: characters and endElement c H->>H: look up string and style, format value H->>S: cell(reference, formattedValue, comment) end P->>H: endElement row H->>S: endRow(rowNum) end P-->>App: parse returns, stream is consumed

Three consequences follow from the diagram:

  1. The sheet is a stream you can consume only once. A second pass needs a second getSheetsData() call.

  2. cell(...) is called only for cells that exist in the XML. Empty cells and entirely empty rows are skipped, so your handler has to compute gaps from the cell reference.

  3. The value you receive is already a formatted string, because XSSFSheetXMLHandler applies DataFormatter. The raw number or date is not delivered unless you override the handler (see Pitfalls).

A complete .xlsx reader

The example reads loans-report.xlsx, the large export produced in Streaming Writes with SXSSF, and counts the loans of every member. Its columns are Loan, ISBN, Member, Loaned on and Fee, so column C holds the member name; row 0 is the header. Only the sheet named Loans is parsed.

package bookshelf;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.LinkedHashMap;
import java.util.Map;

import javax.xml.parsers.ParserConfigurationException;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.util.XMLHelper;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;

/** Counts the loans of every member in loans-report.xlsx without loading the workbook into memory. */
public class LoansReportReader {

    /** Collects column C (member) of every data row. */
    static class LoanCounter implements SheetContentsHandler {
        final Map<String, Integer> loansPerMember = new LinkedHashMap<>();
        private int currentRow = -1;
        private String member;

        @Override
        public void startRow(int rowNum) {
            currentRow = rowNum;
            member = null;
        }

        @Override
        public void cell(String cellReference, String formattedValue, XSSFComment comment) {
            if (cellReference == null) {
                return; // some writers omit the r attribute; fall back to counting cells yourself
            }
            if (new CellReference(cellReference).getCol() == 2) {
                member = formattedValue;          // ignore the other columns
            }
        }

        @Override
        public void endRow(int rowNum) {
            if (rowNum == 0 || member == null || member.isBlank()) {
                return; // header row or empty row
            }
            loansPerMember.merge(member, 1, Integer::sum);
        }
    }

    public static Map<String, Integer> loansPerMember(File file)
            throws IOException, OpenXML4JException, SAXException, ParserConfigurationException {
        try (OPCPackage pkg = OPCPackage.open(file, PackageAccess.READ)) {
            XSSFReader reader = new XSSFReader(pkg);
            ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(pkg);
            StylesTable styles = reader.getStylesTable();
            LoanCounter counter = new LoanCounter();

            XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) reader.getSheetsData();
            while (sheets.hasNext()) {
                try (InputStream sheet = sheets.next()) {
                    if (!"Loans".equals(sheets.getSheetName())) {
                        continue;
                    }
                    ContentHandler handler = new XSSFSheetXMLHandler(
                            styles, strings, counter, new DataFormatter(), false);
                    XMLReader parser = XMLHelper.newXMLReader();
                    parser.setContentHandler(handler);
                    parser.parse(new InputSource(sheet));
                }
            }
            return counter.loansPerMember;
        }
    }

    public static void main(String[] args) throws Exception {
        loansPerMember(new File(args[0])).forEach((m, n) -> System.out.println(m + " -> " + n));
    }
}

Source: XLSX2CSV in poi-examples and the XSSFSheetXMLHandler Javadoc.

Running it against the 300,000-row loans-report.xlsx of the SXSSF page prints one line per member (member-1 -> 375, member-2 -> 375, …​). Points worth reading again in the code:

  • OPCPackage.open(file, PackageAccess.READ) takes a File, not an InputStream: with a File POI reads the ZIP entries on demand, while an InputStream has to be buffered completely into memory first. The same rule as for XSSFWorkbook.

  • XMLHelper.newXMLReader() returns a SAX parser with external entities and DTDs disabled. Do not create a SAXParserFactory yourself: an unsafe parser makes an uploaded file an XXE vector (see Introduction & Architecture for the security model).

  • SheetIterator.getSheetName() is only valid after next() and only for the sheet just returned. Skipping a sheet still means closing its stream, which the try-with-resources block does.

  • new DataFormatter() uses the JVM default locale. On a server, pass an explicit Locale so that 1234.5 is not 1.234,5 on one host and 1,234.5 on another.

  • The last argument of the handler constructor, formulasNotResults, selects what a formula cell reports: false the cached result (the default for data extraction), true the formula text such as SUM(D2:D400).

Dense rows: a reusable adapter

Real import code rarely wants callbacks. It wants a List<String> per row, with empty cells as empty strings so the column positions stay stable. The adapter below turns the three callbacks into a callback per row and fills the gaps the parser leaves.

package bookshelf;

import java.util.ArrayList;
import java.util.List;
import java.util.function.BiConsumer;

import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler;
import org.apache.poi.xssf.usermodel.XSSFComment;

/** Turns SAX callbacks into one dense List per row: skipped (empty) cells become empty strings. */
public class RowCollector implements SheetContentsHandler {

    private final BiConsumer<Integer, List<String>> rowConsumer;
    private final List<String> current = new ArrayList<>();

    public RowCollector(BiConsumer<Integer, List<String>> rowConsumer) {
        this.rowConsumer = rowConsumer;
    }

    @Override
    public void startRow(int rowNum) {
        current.clear();
    }

    @Override
    public void cell(String cellReference, String formattedValue, XSSFComment comment) {
        int column = cellReference == null ? current.size() : new CellReference(cellReference).getCol();
        while (current.size() < column) {
            current.add(""); // the parser never reports empty cells, so fill the gap here
        }
        current.add(formattedValue == null ? "" : formattedValue);
    }

    @Override
    public void endRow(int rowNum) {
        rowConsumer.accept(rowNum, List.copyOf(current)); // 0-based row index, as in the usermodel
    }
}

Source: XLSX2CSV in poi-examples, which fills the same gaps with commas when it writes CSV.

Use it in place of LoanCounter in the previous example:

ContentHandler handler = new XSSFSheetXMLHandler(styles, strings,
        new RowCollector((rowNum, cells) -> System.out.println(rowNum + " " + cells)),
        new DataFormatter(Locale.ROOT), false);

For a sheet with a header row, a blank fifth column and a numeric sixth column the output looks like 1 [Ana, Dune, 2026-01-01, OPEN, , 12.5]. Mapping those lists to a Book or Loan record, validating them and reporting errors by row number is the subject of the section landing page’s import path; the event API only supplies the rows.

Handler callbacks and what they carry

Callback What it gives you

startRow(int rowNum)

The zero-based row index. Rows that contain no cells may never be reported, so row numbers are not consecutive.

cell(String cellReference, String formattedValue, XSSFComment comment)

The A1 reference ("D17"), the displayed text, and the cell comment when the handler was built with a comments table. cellReference can be null when a producer omitted the r attribute; the XML is then positional.

endRow(int rowNum)

Called after the last cell of the row. This is where a row-level consumer flushes its state.

headerFooter(String text, boolean isHeader, String tagName)

Default method that ignores headers and footers. Override it to receive the sheet’s header and footer text (tagName is oddHeader, evenFooter and so on).

The constructors of XSSFSheetXMLHandler have two shapes: with and without a Comments argument. Pass the comments table (XSSFReader.SheetIterator.getSheetComments()) when you need the cell comments; otherwise the comment parameter is always null. Comments are read into memory, one table per sheet.

Options of the event extractor

XSSFEventBasedExcelExtractor is the ready-made consumer of this API: it opens the package, runs the handler and returns the sheet text. ExtractorFactory returns it for an .xlsx only when you call ExtractorFactory.setThreadPrefersEventExtractors(true) (or setAllThreadsPreferEventExtractors(true)): the default is false, which gives the usermodel-based XSSFExcelExtractor.

import java.io.File;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.xssf.extractor.XSSFEventBasedExcelExtractor;

public class BookshelfTextDump {

    public static void main(String[] args) throws Exception {
        try (OPCPackage pkg = OPCPackage.open(new File(args[0]), PackageAccess.READ);
                XSSFEventBasedExcelExtractor extractor = new XSSFEventBasedExcelExtractor(pkg)) {
            extractor.setIncludeSheetNames(true);
            extractor.setFormulasNotResults(false); // cached results, not formula text
            System.out.println(extractor.getText());
        }
    }
}

Source: XSSFEventBasedExcelExtractor Javadoc and POI text extraction.

Setting Effect

setIncludeSheetNames(boolean)

Prints each sheet name before its rows (the default).

setFormulasNotResults(boolean)

true prints the formula text, false the cached result.

setIncludeCellComments(boolean)

Appends the comment of each cell.

setIncludeHeadersFooters(boolean)

Includes sheet headers and footers.

setIncludeTextBoxes(boolean)

Includes text in drawing text boxes.

setLocale(Locale)

Locale for number and date formatting.

The extractor returns the whole text as one String, so it is only as memory-friendly as the text it produces. For millions of rows write your own SheetContentsHandler and process each row as it arrives.

Reading .xlsb with XSSFBReader

Excel Binary Workbook (.xlsb) is an OPC package like .xlsx, but its parts hold binary records instead of XML. POI reads it through the org.apache.poi.xssf.binary package and the XSSFBReader, and it reuses the SheetContentsHandler interface, so the consuming code does not change. As of 5.5.1 the support is limited to reading cell text, shared strings, the styles needed for number formatting and comments:

package bookshelf;

import java.io.File;
import java.io.InputStream;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.binary.XSSFBSharedStringsTable;
import org.apache.poi.xssf.binary.XSSFBSheetHandler;
import org.apache.poi.xssf.binary.XSSFBStylesTable;
import org.apache.poi.xssf.eventusermodel.XSSFBReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler;
import org.apache.poi.xssf.usermodel.XSSFComment;

/** Prints every cell of every sheet of an .xlsb file. */
public class XlsbLoansReader {

    public static void main(String[] args) throws Exception {
        try (OPCPackage pkg = OPCPackage.open(new File(args[0]), PackageAccess.READ)) {
            XSSFBReader reader = new XSSFBReader(pkg);
            XSSFBSharedStringsTable strings = new XSSFBSharedStringsTable(pkg);
            XSSFBStylesTable styles = reader.getXSSFBStylesTable();

            SheetContentsHandler printer = new SheetContentsHandler() {
                @Override public void startRow(int rowNum) { System.out.println("row " + rowNum); }
                @Override public void endRow(int rowNum) { }
                @Override public void cell(String ref, String value, XSSFComment comment) {
                    System.out.println("  " + ref + " = " + value);
                }
            };

            XSSFBReader.SheetIterator sheets = (XSSFBReader.SheetIterator) reader.getSheetsData();
            while (sheets.hasNext()) {
                try (InputStream sheet = sheets.next()) {
                    System.out.println("sheet " + sheets.getSheetName());
                    new XSSFBSheetHandler(sheet, styles, sheets.getXSSFBSheetComments(), strings,
                            printer, new DataFormatter(), false).parse();
                }
            }
        }
    }
}

Source: XSSFBReader Javadoc and the POI source of XSSFBEventBasedExcelExtractor, which drives XSSFBReader and XSSFBSheetHandler the same way. The example compiles against POI 5.5.1; no .xlsb sample file was available to run it, so verify it against one of your own files.

What POI does not provide for .xlsb: there is no usermodel (XSSFWorkbook cannot open it), no writer and no formula evaluation. XSSFBReader only reads. To produce an .xlsb or to edit one you need Excel or another tool; the usual server-side answer is to ask users for .xlsx and to convert once with a desktop tool if only .xlsb is available.

The HSSF event model for .xls

The binary .xls format is a stream of records (BOFRecord, BoundSheetRecord, SSTRecord, LabelSSTRecord, NumberRecord, FormulaRecord, EOFRecord and about two hundred more). HSSFEventFactory reads that stream and hands each record to every HSSFListener registered in an HSSFRequest. The low-level API is faithful to the file but awkward: a listener sees records exactly as they were written, so it must reassemble strings, track the current sheet and notice rows with missing cells. POI ships two decorators for that:

Class What it adds

MissingRecordAwareHSSFListener

Wraps your listener and inserts dummy records: MissingRowDummyRecord for rows with no cells, MissingCellDummyRecord for gaps inside a row, and LastCellOfRowDummyRecord after the final cell of each row. This gives you the row structure the XML handler gives for free.

FormatTrackingHSSFListener

Tracks the FormatRecord and ExtendedFormatRecord of the workbook so formatNumberDateCell(CellValueRecordInterface) can return the string Excel would display for a number or a date.

Decorators are chained: request.addListenerForAllRecords(formatListener) where formatListener wraps MissingRecordAwareHSSFListener, which wraps your own listener. The Bookshelf example reads a legacy books-inventory.xls and prints one pipe-separated line per row:

package bookshelf;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.eventusermodel.FormatTrackingHSSFListener;
import org.apache.poi.hssf.eventusermodel.HSSFEventFactory;
import org.apache.poi.hssf.eventusermodel.HSSFListener;
import org.apache.poi.hssf.eventusermodel.HSSFRequest;
import org.apache.poi.hssf.eventusermodel.MissingRecordAwareHSSFListener;
import org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord;
import org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord;
import org.apache.poi.hssf.record.BoundSheetRecord;
import org.apache.poi.hssf.record.LabelSSTRecord;
import org.apache.poi.hssf.record.NumberRecord;
import org.apache.poi.hssf.record.Record;
import org.apache.poi.hssf.record.SSTRecord;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

/** Reads the title and price columns of a legacy books-inventory.xls using the HSSF event model. */
public class HssfLoansListener implements HSSFListener {

    private final List<String> row = new ArrayList<>();
    private SSTRecord sst;
    private FormatTrackingHSSFListener formatListener;

    @Override
    public void processRecord(Record record) {
        if (record instanceof BoundSheetRecord sheet) {
            System.out.println("sheet: " + sheet.getSheetname());
        } else if (record instanceof SSTRecord table) {
            sst = table;
        } else if (record instanceof LabelSSTRecord label) {
            put(label.getColumn(), sst.getString(label.getSSTIndex()).getString());
        } else if (record instanceof NumberRecord number) {
            put(number.getColumn(), formatListener.formatNumberDateCell(number));
        } else if (record instanceof MissingCellDummyRecord gap) {
            put(gap.getColumn(), "");
        } else if (record instanceof LastCellOfRowDummyRecord) {
            System.out.println(String.join(" | ", row));
            row.clear();
        }
    }

    private void put(int column, String text) {
        while (row.size() <= column) {
            row.add("");
        }
        row.set(column, text);
    }

    public void process(File file) throws IOException {
        try (POIFSFileSystem fs = new POIFSFileSystem(file, true)) {
            MissingRecordAwareHSSFListener missing = new MissingRecordAwareHSSFListener(this);
            formatListener = new FormatTrackingHSSFListener(missing);
            HSSFRequest request = new HSSFRequest();
            request.addListenerForAllRecords(formatListener);
            new HSSFEventFactory().processWorkbookEvents(request, fs);
        }
    }

    public static void main(String[] args) throws IOException {
        new HssfLoansListener().process(new File(args[0]));
    }
}

Source: XLS2CSVmra in poi-examples and the Event API section of the Spreadsheet How-To.

Notes for the HSSF listener:

  • The POIFSFileSystem(File, boolean readOnly) constructor with true opens the file read-only and reads blocks from it on demand instead of copying the whole file into the heap.

  • A real listener has to handle more record types than the example does: FormulaRecord (its string result arrives in a following StringRecord), LabelRecord (inline strings), BoolErrRecord, RKRecord (compressed numbers, written by older producers) and MulRKRecord. XLS2CSVmra in the official examples shows each of them.

  • The sheet is identified by order of appearance of the BOFRecord (type worksheet), and its name comes from the matching BoundSheetRecord, read earlier in the stream. Sheets that are hidden are still reported.

  • The .xls format caps a sheet at 65,536 rows, so the memory argument for events is much weaker than for .xlsx. The event model remains useful when a very wide or very old file would otherwise be loaded fully for one pass.

  • HSSFEventFactory and the record classes are in the poi jar, not in poi-ooxml.

Usermodel versus event APIs

Aspect Usermodel (XSSFWorkbook, HSSFWorkbook) Event / SAX (XSSFReader, HSSFEventFactory)

Memory

Whole file as objects; grows with file size and with the number of distinct styles and strings

Current row plus the shared strings table (all unique strings stay in memory) and the styles

Access

Random: getSheetAt, getRow, getCell, any order, any number of times

Forward only, one pass, callbacks in file order

Reads / writes

Both

Read only

Empty cells

getCell returns null or a blank cell by MissingCellPolicy

Never reported; you derive gaps from references

Values

Cell with CellType, numeric and date value, formula and cached result

Formatted String (XLSX/XLSB) or raw records (HSSF); raw values need a custom handler

Formulas

FormulaEvaluator available

Cached result or formula text only; no evaluation

Styles, merged regions, validations

Accessible through CellStyle, getMergedRegion, getDataValidations

Not delivered by the cell callbacks; parse the relevant XML elements yourself if you need them

Code size

Short and readable

Longer; state machines and manual gap handling

Best for

Files up to a few tens of MB, editing, templates, random access

Large uploads, one-pass extraction, indexing, migrations

Memory and time: how to measure

POI does not publish a memory-per-cell figure, and any number depends on content (a million short numbers is not a million long distinct strings), so the honest advice is to measure with your own files. A reliable way:

  1. Run the read in a separate JVM with a fixed heap, for example with the -Xmx512m option on the java command line. If the usermodel version fails with OutOfMemoryError at -Xmx512m and the event version passes, the event API is justified. This is the cheapest and most telling experiment.

  2. Watch the heap, not the total: enable GC logging (-Xlog:gc*) and note the live set after a full GC while the file is being read. For the event API the live set is dominated by the shared strings table.

  3. Record the time of the parse alone, with a warmed-up JVM, on a file the size of your biggest expected upload.

  4. Repeat with a file with many unique strings (names, ISBNs) and one with few (statuses): the shared strings table is the main variable in the event approach.

  5. Use a heap dump (jcmd <pid> GC.heap_dump) or Java Flight Recorder to see which classes hold the memory before guessing.

Pitfalls

Pitfall What to do

Memory still grows with the shared strings table

ReadOnlySharedStringsTable loads every unique string. A file with millions of unique values can still be large even when read by events. Process in a separate worker with a bounded heap, and reject uploads by size and row count.

Dates arrive formatted, not as dates

The value is the displayed text (2026-01-01), in the format and locale of the cell. To get a LocalDate, either parse that text with the same pattern or write a custom ContentHandler subclass that reads the raw <v> content and calls DateUtil.isADateFormat on the style. Dates stored as text stay text.

Locale-dependent output

DataFormatter is locale-sensitive. Always construct it with an explicit Locale.

Empty cells and rows are missing

Compute gaps from the column of cellReference, as RowCollector does. Row indices may skip.

Stream consumed once

Open the sheet again for a second pass, or collect what you need on the first one.

Not closing the package or streams

Use try-with-resources for OPCPackage and each sheet InputStream; otherwise file handles leak on the server.

Unsafe XML parsers

Use XMLHelper.newXMLReader() so DTDs and external entities are disabled. POI additionally enforces ZipSecureFile limits on the ZIP when the package is opened (compression ratio and maximum entry size), which is the first defence against zip bombs in uploads.

No Log4j provider

POI 5.x logs through the Log4j 2 API. Without a provider on the classpath, as in a bare example, an ERROR Log4j API could not find a logging provider line appears once. Add log4j-core or the log4j-to-slf4j bridge (see Setup & Dependencies).

Mixing the two models

Do not open the same large file with both XSSFWorkbook and the event API in one request; the usermodel defeats the purpose.

What POI does not provide here

  • No random access or backtracking. Looking up a cell in another sheet while reading a formula needs a separate pass or a lookup structure you build.

  • No formula evaluation. Only cached results or formula text are available.

  • No object mapping or validation. There is no @Column mapping; the section’s import pages build the mapper on top of rows like the ones above.

  • No event API for Word. The .docx and .doc files have usermodel APIs only; Word extraction for large files is done with the extractors, covered later in the section.

  • No official event-model guide for .xlsb. Support is limited to the Javadoc and the example classes.

  • Internal details are only in the Javadoc and the examples. The official spreadsheet guide documents the event API with a short how-to section; the behaviour of XSSFSheetXMLHandler internals (for instance how inline strings, rich text and t="str" cells are mapped) has to be read from the source.

Changes between versions

Version Change relevant to event reading

4.x → 5.x

POI 5.x logs through the Log4j 2 API instead of its own logger, and XML parsing goes through the hardened XMLHelper. The SheetContentsHandler interface of 5.5.1 declares startRow, endRow and cell, plus the default methods headerFooter and endSheet, so older handlers that implement only the three abstract methods still compile.

5.5.1 (as of 2026-10-09)

The latest release. The event API for .xlsx, .xlsb and .xls is the one described on this page.

6.0 (planned, not released as of 2026-10-09)

The announced 6.0 line removes APIs deprecated in 5.x and changes DataFormatter to use cached formula values by default. If you rely on formula text or results in handlers, recheck the formulasNotResults behaviour and run your tests against the milestone when it becomes available. These notes are expectations taken from the project site, not shipped behaviour.

For the history of the whole section, see the migration page, covered later in the section.

Related pages

References