Reading and Writing Excel in Java With Apache POI Safely

Apache POI is the long-standing Java library for Microsoft Office formats, and for Excel it is the default choice in server-side Java. It reads and writes xlsx and the older xls through a common interface, evaluates many formulas and supports styles, charts and more. It is also known for using a lot of memory and for an API that has several layers. This post covers the pieces that matter for data work: reading cells by type, writing a report, streaming large files and keeping the process safe with untrusted input. It is a spoke of reading and writing Excel from JavaScript, Java and C Sharp.

Dependencies

With Maven:

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

Use a current release (5.x) and check for security updates; POI has published fixes in the past. poi-ooxml brings in xlsx support and the older poi module for xls. Its XML parsing and logging pull in other libraries; keep them up to date.

Open a workbook

import org.apache.poi.ss.usermodel.*;
import java.io.File;

try (Workbook wb = WorkbookFactory.create(new File("orders.xlsx"))) {
    Sheet sheet = wb.getSheet("Orders");     // or wb.getSheetAt(0)
    DataFormatter fmt = new DataFormatter();
    for (Row row : sheet) {
        if (row.getRowNum() == 0) continue;  // header
        String order = fmt.formatCellValue(row.getCell(0));
        // ...
    }
}

WorkbookFactory.create chooses XSSFWorkbook for xlsx and HSSFWorkbook for xls by sniffing the content. Open from a File rather than an InputStream where you can; a file lets POI use less memory. Use try-with-resources: the workbook holds the file open. getSheet returns null if the name does not exist. Rows and cells are zero-based, unlike Excel's own numbering; getRowNum() of the first row is 0.

Row.getCell(i) returns null for cells that were never written, which is the most common cause of a NullPointerException. Use row.getCell(i, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL) or CREATE_NULL_AS_BLANK to state how to treat them, and null-check anyway.

Read cell values by type

A cell has a type: numeric, string, boolean, formula, blank or error. Asking for the wrong one throws IllegalStateException. Switch on the type:

Object value(Cell c) {
    if (c == null) return null;
    switch (c.getCellType()) {
        case STRING:  return c.getStringCellValue();
        case BOOLEAN: return c.getBooleanCellValue();
        case NUMERIC:
            if (DateUtil.isCellDateFormatted(c)) return c.getLocalDateTimeCellValue();
            return c.getNumericCellValue();
        case FORMULA: // cached result type
            switch (c.getCachedFormulaResultType()) {
                case STRING:  return c.getStringCellValue();
                case BOOLEAN: return c.getBooleanCellValue();
                case NUMERIC: return DateUtil.isCellDateFormatted(c)
                                  ? c.getLocalDateTimeCellValue() : c.getNumericCellValue();
                default: return null;
            }
        default: return null;
    }
}

Dates are numeric cells with a date format; DateUtil.isCellDateFormatted tells you, and getLocalDateTimeCellValue() converts. For a formula cell, getCellType() is FORMULA, and the cached result type says what the result is; the cached value is what the last program to save the file computed.

For a quick way to get "what Excel shows", use DataFormatter.formatCellValue(cell). It returns text formatted by the cell's number format, so 1234.5 with the format #,##0.00 becomes 1,234.50, and a text-formatted 00123 stays 00123. It is the safe default for reading identifiers, and the wrong choice for numbers you need to compute with, where getNumericCellValue returns the real value. A numeric cell holding 123 read as a double and printed becomes 123.0, a common surprise.

To evaluate formulas that have no cached value (a workbook written by POI itself), use a FormulaEvaluator:

FormulaEvaluator ev = wb.getCreationHelper().createFormulaEvaluator();
CellValue cv = ev.evaluate(cell);

POI supports many but not all Excel functions; unsupported ones throw NotImplementedException. Newer functions such as XLOOKUP may not be available in your version.

Merged cells and layout

sheet.getMergedRegions() lists merged ranges. In a merged region, only the top left cell has a value; the others are blank. If you read a report layout, fill values down yourself. Hidden rows are returned like any other (row.getZeroHeight() tells you). Defined names, tables and conditional formats are accessible through the model if you need them.

Write a workbook

try (XSSFWorkbook wb = new XSSFWorkbook();
     FileOutputStream out = new FileOutputStream("report.xlsx")) {

    Sheet sheet = wb.createSheet("Report");
    CellStyle head = wb.createCellStyle();
    Font bold = wb.createFont(); bold.setBold(true); head.setFont(bold);
    CellStyle money = wb.createCellStyle();
    money.setDataFormat(wb.createDataFormat().getFormat("#,##0.00"));
    CellStyle date = wb.createCellStyle();
    date.setDataFormat(wb.createDataFormat().getFormat("yyyy-mm-dd"));

    Row h = sheet.createRow(0);
    String[] cols = {"Order", "Date", "Amount"};
    for (int i = 0; i < cols.length; i++) { Cell c = h.createCell(i); c.setCellValue(cols[i]); c.setCellStyle(head); }

    Row r = sheet.createRow(1);
    r.createCell(0).setCellValue("0007");
    Cell d = r.createCell(1); d.setCellValue(java.time.LocalDate.of(2025, 3, 14)); d.setCellStyle(date);
    Cell m = r.createCell(2); m.setCellValue(1234.5); m.setCellStyle(money);

    sheet.createFreezePane(0, 1);
    sheet.setAutoFilter(new org.apache.poi.ss.util.CellRangeAddress(0, 1, 0, 2));
    wb.write(out);
}

Create each style once and reuse it. A style per cell makes a huge style table and eventually fails at the format limit of the file (reducing workbook size by removing unused styles). Write identifiers as strings and numbers as numbers. Auto-sizing columns (sheet.autoSizeColumn(i)) is slow on large sheets and needs the font metrics of the machine; set widths by hand for big reports.

Stream large outputs with SXSSF

XSSFWorkbook keeps every row in memory, which fails for hundreds of thousands of rows. SXSSFWorkbook keeps a sliding window of rows in memory and flushes older ones to temporary files:

try (SXSSFWorkbook wb = new SXSSFWorkbook(100)) {     // keep 100 rows in memory
    Sheet sheet = wb.createSheet("Big");
    for (int i = 0; i < 1_000_000; i++) {
        Row row = sheet.createRow(i);
        row.createCell(0).setCellValue(i);
        row.createCell(1).setCellValue("x" + i);
    }
    try (FileOutputStream out = new FileOutputStream("big.xlsx")) { wb.write(out); }
    wb.dispose();                                      // delete the temp files
}

Rows outside the window cannot be read or changed. Call dispose() to remove temporary files, or they accumulate. A worksheet holds at most 1,048,576 rows, so the loop above is near the limit. Auto-sizing columns needs tracking (trackAllColumnsForAutoSizing) and costs more. Shared strings are inline by default in SXSSF, which keeps memory low.

Read large inputs

The user model (XSSFWorkbook) loads the whole file and is heavy. Rough guidance: it needs many times the file size in memory. For large inputs use the event model: XSSFReader with a SAX handler, or XSSFSheetXMLHandler with a SheetContentsHandler receiving rows as they are parsed. It is more code, and constant memory. A community library, excel-streaming-reader, wraps the same idea in a simpler row iterator. Choose it if you want streaming without writing the handler. Whichever you pick, process rows and discard them (reading a 500 MB Excel file without running out of memory discusses the memory arithmetic).

Safety with untrusted files

POI protects against zip bombs and oversized XML with limits that you can configure. If your application reads uploads, set them deliberately:

ZipSecureFile.setMinInflateRatio(0.01);              // reject suspiciously high compression
ZipSecureFile.setMaxEntrySize(500_000_000L);         // bytes per entry
IOUtils.setByteArrayMaxOverride(200_000_000);        // limit for in-memory byte arrays

Defaults exist and have changed between versions. Raising them to "make a big file work" disables the protection, so raise them only for files you trust. Also: cap the upload size at the web layer, run the parsing with a time limit, never load files from paths given by the user without validation, and validate the contents against the expected structure. Keep POI updated, since parser vulnerabilities are fixed in releases.

Check the result

Compare the row count and a numeric total with the workbook. Test a formula cell, a date, an empty cell, a merged cell, a text-formatted number and a cell with a leading apostrophe. Open the written file in Excel, not only with POI, since POI is more tolerant than Excel. Watch for the 0.0 and scientific notation traps when turning numeric cells into strings.

When Apache POI is more than the job needs

You need only the data of a CSV-like sheet. A lighter library such as FastExcel reader, or converting to CSV once, may suit.

You process very large files regularly. Convert once to a better format and read that (converting a huge Excel file to CSV or Parquet).

You need only to write a simple export. A CSV may be enough, with no library at all.

Licensing or footprint matter. POI is Apache-licensed and large; for a small service the dependency tree counts.

See the Apache POI components overview and its page on limitations and zip bomb detection. To convert a small result to JSON, use the CSV to JSON converter.