Reading and Writing Excel Files in Java with Apache POI

Apache POI reads and writes Excel files, and most of its difficulty comes from the format rather than the library. A spreadsheet cell has no declared type, a date is a number wearing a format string, and a formula stores both an expression and a cached result that may disagree.

Written against Apache POI 5.2 and Java 17.

Dependencies

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.5</version>
</dependency>

poi-ooxml covers .xlsx and pulls in poi, which handles the older .xls. Take only poi if you genuinely never touch the modern format, but poi-ooxml is nearly always what you want, and mixing mismatched versions of the two is a common source of NoSuchMethodError.

The two workbook implementations matter:

• XSSFWorkbook, .xlsx, entirely in memory. Random access to any cell.
• SXSSFWorkbook, .xlsx, streaming writes. Keeps a window of rows and flushes the rest to disk.
• HSSFWorkbook, the legacy .xls binary format, capped at 65,536 rows.

Reading

public List<Note> read(Path path) throws IOException {
    List<Note> notes = new ArrayList<>();

    try (InputStream in = Files.newInputStream(path);
         Workbook workbook = WorkbookFactory.create(in)) {

        Sheet sheet = workbook.getSheetAt(0);
        DataFormatter formatter = new DataFormatter();

        for (Row row : sheet) {
            if (row.getRowNum() == 0) {
                continue;                       // header
            }
            String title = formatter.formatCellValue(row.getCell(0));
            if (title.isBlank()) {
                continue;                       // trailing blank row
            }
            notes.add(new Note(title, formatter.formatCellValue(row.getCell(1))));
        }
    }
    return notes;
}

WorkbookFactory.create sniffs the format, so one code path handles .xls and .xlsx.

DataFormatter is the tool most examples skip, and it solves the central problem: a cell has whatever type the person who typed it produced. A column of “IDs” contains numbers where someone typed digits and strings where someone pasted them, and getStringCellValue() on a numeric cell throws IllegalStateException.

formatCellValue returns what Excel displays: applying the cell’s format, so 42.0 formatted as an integer comes back "42" rather than "42.0", and a date comes back as the date. For reading user-supplied spreadsheets into strings. It is the correct default.

When you need typed values, ask the cell what it is:

static Object typedValue(Cell cell) {
    if (cell == null) {
        return null;
    }
    return switch (cell.getCellType()) {
        case STRING  -> cell.getStringCellValue();
        case BOOLEAN -> cell.getBooleanCellValue();
        case NUMERIC -> DateUtil.isCellDateFormatted(cell)
                ? cell.getLocalDateTimeCellValue()
                : cell.getNumericCellValue();
        case FORMULA -> cell.getCellFormula();
        case BLANK   -> null;
        default      -> null;
    };
}

row.getCell(n) returns null for a cell that was never touched, not a blank cell object. Every access needs the null check, or a sparse spreadsheet produces a NullPointerException on a row that looks fine in Excel. row.getCell(n, MissingCellPolicy.CREATE_NULL_AS_BLANK) returns a blank cell instead, which is often tidier.

Dates are numbers

There is no date type in a spreadsheet. A date is a NUMERIC cell holding days since an epoch, with a format string that makes it display as a date:

if (cell.getCellType() == CellType.NUMERIC && DateUtil.isCellDateFormatted(cell)) {
    LocalDateTime when = cell.getLocalDateTimeCellValue();
}

isCellDateFormatted inspects the format string. That means a cell someone formatted as plain number reads back as 45730.0, correctly, and a numeric column someone formatted as a date reads as a date even though it was meant to be a quantity. The spreadsheet contains no better information than that.

Two epochs exist: 1900 (Windows default) and 1904 (older Mac files). POI handles both from the workbook’s setting, so use getLocalDateTimeCellValue() rather than doing the arithmetic yourself.

And the 1900 epoch has a deliberate bug: Excel treats 1900 as a leap year for Lotus compatibility, so dates before 1 March 1900 are off by one. Rare, and worth knowing before you conclude POI is wrong.

Writing

public void write(Path path, List<Note> notes) throws IOException {
    try (Workbook workbook = new XSSFWorkbook()) {
        Sheet sheet = workbook.createSheet("Notes");

        CellStyle header = workbook.createCellStyle();
        Font bold = workbook.createFont();
        bold.setBold(true);
        header.setFont(bold);

        CellStyle dateStyle = workbook.createCellStyle();
        dateStyle.setDataFormat(workbook.getCreationHelper()
                .createDataFormat().getFormat("yyyy-mm-dd"));

        Row head = sheet.createRow(0);
        String[] columns = {"Title", "Category", "Created"};
        for (int i = 0; i < columns.length; i++) {
            Cell c = head.createCell(i);
            c.setCellValue(columns[i]);
            c.setCellStyle(header);
        }

        int r = 1;
        for (Note note : notes) {
            Row row = sheet.createRow(r++);
            row.createCell(0).setCellValue(note.title());
            row.createCell(1).setCellValue(note.category());

            Cell created = row.createCell(2);
            created.setCellValue(note.createdAt());
            created.setCellStyle(dateStyle);
        }

        for (int i = 0; i < columns.length; i++) {
            sheet.autoSizeColumn(i);
        }

        try (OutputStream out = Files.newOutputStream(path)) {
            workbook.write(out);
        }
    }
}

Three things that go wrong here.

Styles are workbook-level and must be created once. Creating a CellStyle inside the row loop hits the format’s hard limit: 64,000 styles in .xlsx, 4,000 in .xls — and throws partway through a large export. Create each style before the loop and reuse the reference.

A date needs both a value and a style. setCellValue(LocalDateTime) stores the number; without a date format the cell displays 45730. This is the single most common “my dates are numbers” report, and it is a missing style rather than a missing conversion.

autoSizeColumn is expensive. It measures every cell in the column with the font metrics, so on a large sheet it can take longer than writing the data. Call it after populating, on the few columns that need it, or set explicit widths.

Large files

An XSSFWorkbook holds every cell as an object. A spreadsheet of a few hundred thousand rows becomes a heap problem well before the file becomes large, because the in-memory representation is roughly an order of magnitude bigger than the compressed XML on disk.

Writing: SXSSFWorkbook keeps a sliding window of rows and flushes the rest to a temporary file:

try (SXSSFWorkbook workbook = new SXSSFWorkbook(100)) {   // keep 100 rows in memory
    Sheet sheet = workbook.createSheet("Export");
    for (int r = 0; r < 1_000_000; r++) {
        Row row = sheet.createRow(r);
        row.createCell(0).setCellValue("row " + r);
    }
    try (OutputStream out = Files.newOutputStream(path)) {
        workbook.write(out);
    }
    workbook.dispose();      // delete the temporary files
}

The constraint is what makes it work: rows already flushed cannot be accessed. No going back to fix a header, no autoSizeColumn over the whole sheet. Write forward only.

dispose() deletes the backing temp files. Without it they accumulate in the system temp directory until the JVM exits, a slow disk leak in a long-running service.

Reading large files needs the event API rather than the user model:

try (OPCPackage pkg = OPCPackage.open(path.toFile(), PackageAccess.READ)) {
    XSSFReader reader = new XSSFReader(pkg);
    // SAX handler over sheet XML — constant memory
}

More code, and the only approach that reads a million rows in a bounded heap. POI also ships sax.XLSX2CSV as a worked example of it.

Formulas

Cell cell = row.getCell(3);
if (cell.getCellType() == CellType.FORMULA) {
    cell.getCellFormula();               // "SUM(A1:A10)" — the expression
    cell.getNumericCellValue();          // the CACHED result Excel last computed
}

A formula cell stores both the expression and the value Excel calculated when it last saved. If the file was produced by something other than Excel, including POI, that cache may be absent or stale.

To compute values yourself:

FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
CellValue value = evaluator.evaluate(cell);      // does not modify the cell
double result = value.getNumberValue();

evaluator.evaluateAll();                          // recompute the whole workbook

POI implements many Excel functions but not all. An unsupported one throws NotImplementedException naming the function, which is at least a clear failure. If a workbook you write contains formulas, either call evaluateAll() before saving or set workbook.setForceFormulaRecalculation(true) so Excel recalculates on open: otherwise the file displays empty cells until someone edits it.

Reading an upload safely

A spreadsheet from a user is untrusted input:

@PostMapping("/import")
public ImportResult importFile(@RequestParam MultipartFile file) throws IOException {
    if (file.getSize() > 10 * 1024 * 1024) {
        throw new PayloadTooLargeException("10 MB maximum");
    }

    IOUtils.setByteArrayMaxOverride(50 * 1024 * 1024);   // cap POI's own allocations

    try (Workbook workbook = WorkbookFactory.create(file.getInputStream())) {
        Sheet sheet = workbook.getSheetAt(0);
        if (sheet.getLastRowNum() > 50_000) {
            throw new PayloadTooLargeException("50,000 rows maximum");
        }
        // ...
    }
}

.xlsx is a zip archive, so a small upload can expand enormously, a zip bomb. POI has built-in ratio checks and setByteArrayMaxOverride bounds its allocations further. Cap the file size, the row count and the column count, and remember that getLastRowNum() reflects rows Excel has touched, which can be far more than rows containing data.

Frequently asked questions

Which workbook class should I use?

XSSFWorkbook for .xlsx you read randomly, SXSSFWorkbook for writing large files, HSSFWorkbook only for legacy .xls. WorkbookFactory.create picks automatically when reading.

Why does getStringCellValue throw IllegalStateException?

The cell is numeric. Use DataFormatter .formatCellValue() for a displayed string, or switch on getCellType().

Why is getCell returning null?

A cell never touched has no object. Null-check every access, or pass MissingCellPolicy.CREATE_NULL_AS_BLANK.

Why do my dates appear as numbers?

The cell value was set without a date CellStyle. A date is a number plus a format; both are required.

How do I tell whether a numeric cell is a date?

DateUtil.isCellDateFormatted(cell), which inspects the format string. There is no date type to ask about.

Why does a large export run out of memory?

XSSFWorkbook holds every cell in memory. Use SXSSFWorkbook with a row window, and call dispose() to remove its temp files.

Why can I not modify an earlier row with SXSSFWorkbook?

Rows outside the window have been flushed to disk. Streaming is forward-only by design.

Why is my export so slow?

Usually autoSizeColumn, which measures every cell with font metrics. Call it on the columns that need it, or set widths explicitly.

Why does POI return a stale formula result?

It reads the value Excel cached at last save. Use a FormulaEvaluator, or set setForceFormulaRecalculation(true) so Excel recalculates on open.

Is reading an uploaded spreadsheet safe?

Not by default. .xlsx is a zip and can expand enormously. Cap file size, rows and columns, and use IOUtils.setByteArrayMaxOverride.

Where should I go next?

Parsing a string to a date covers the date handling these imports feed into, and the Java guides cover the rest.