How to Write an Excel File in Java with Apache POI

Apache POI writes real .xlsx files, and the two mistakes that make an export unusable are both resource mistakes rather than API mistakes: building a cell style inside the row loop, and holding every row in memory when a streaming workbook exists for exactly that reason.

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 is the modern .xlsx format. The poi artifact alone handles only the legacy .xls binary format, and pulling both is only necessary if you must read files in either.

Three workbook implementations:

Class	Format	Memory
HSSFWorkbook	.xls	whole workbook; 65,536-row limit
XSSFWorkbook	.xlsx	whole workbook in memory
SXSSFWorkbook	.xlsx	a sliding window of rows

A basic export

try (Workbook workbook = new XSSFWorkbook();
     OutputStream out = Files.newOutputStream(path)) {

    Sheet sheet = workbook.createSheet("Articles");

    Row header = sheet.createRow(0);
    header.createCell(0).setCellValue("ID");
    header.createCell(1).setCellValue("Title");
    header.createCell(2).setCellValue("Published");

    int rowNum = 1;
    for (Article article : articles) {
        Row row = sheet.createRow(rowNum++);
        row.createCell(0).setCellValue(article.id());
        row.createCell(1).setCellValue(article.title());
        row.createCell(2).setCellValue(article.publishedAt());
    }

    workbook.write(out);
}

setCellValue is overloaded for String, double, boolean, Date, LocalDate and LocalDateTime. There is no overload for int or long — both widen to double, which is exactly what the file format stores and which loses precision above 2^53. An identifier longer than about 15 digits has to be written as a string.

The write happens at workbook.write(out), so the whole document exists in memory until then.

Styles must be created once

// WRONG — one style object per row
for (Article article : articles) {
    CellStyle style = workbook.createCellStyle();   // leaks
    style.setDataFormat(format.getFormat("yyyy-mm-dd"));
    cell.setCellStyle(style);
}

A workbook has a hard limit of 64,000 cell styles, and POI does not deduplicate them. Creating one per row throws IllegalStateException partway through a large export, and long before that the file is bloated with thousands of identical style records.

Create each style once, outside the loop:

CreationHelper helper = workbook.getCreationHelper();

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

CellStyle headerStyle = workbook.createCellStyle();
Font bold = workbook.createFont();
bold.setBold(true);
headerStyle.setFont(bold);
headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

for (Article article : articles) {
    Cell cell = row.createCell(2);
    cell.setCellValue(article.publishedAt());
    cell.setCellStyle(dateStyle);      // the same object, reused
}

The same rule applies to Font and DataFormat. A small map from a style key to the created style is the usual way to keep this correct when the styling is conditional rather than fixed.

Dates are numbers with a format

There is no date type in the file format. A date is a number — days since 1899-12-30 — displayed according to the cell’s format string. A cell written with setCellValue(LocalDate) and no style shows as 45894.

That is why the date style above is not cosmetic: without it the export is wrong, not merely plain.

The epoch is 1899-12-30 rather than 1900-01-01 because the format deliberately reproduces a long-standing bug that treats 1900 as a leap year. POI’s conversion handles it, which is the argument for using setCellValue(LocalDate) rather than computing the serial number yourself. Dates before 1900 have no representation at all and come out as text.

Times are the fractional part of the same number, so 12:00 is 0.5. A duration longer than 24 hours therefore needs the [h]:mm format — plain h:mm wraps at midnight and silently shows 26 hours as 2.

Large exports: SXSSFWorkbook

try (SXSSFWorkbook workbook = new SXSSFWorkbook(100);   // keep 100 rows in memory
     OutputStream out = Files.newOutputStream(path)) {

    Sheet sheet = workbook.createSheet("Articles");

    int rowNum = 0;
    for (Article article : articles) {
        Row row = sheet.createRow(rowNum++);
        row.createCell(0).setCellValue(article.title());
    }

    workbook.write(out);
    workbook.dispose();     // deletes the temporary files
}

SXSSFWorkbook flushes rows to a temporary file as it goes, keeping only a window in memory. A hundred thousand rows fits in a few megabytes instead of a few hundred.

Two constraints come with it. A flushed row cannot be revisited — sheet.getRow(n) returns null once row n has left the window, so anything needing a second pass (auto-sizing columns, back-filling a total) has to be computed before the row is written. And dispose() must be called or the temporary files remain; it is not done by close().

autoSizeColumn is the usual casualty. It measures every cell in the column, so it needs them all in memory:

SXSSFSheet sheet = workbook.createSheet("Articles");
sheet.trackAllColumnsForAutoSizing();   // before writing any rows

That tracking has its own memory cost, which partly defeats the point. Setting an explicit width is cheaper and more predictable:

sheet.setColumnWidth(1, 40 * 256);      // width is in 1/256ths of a character

Formulas

Cell total = row.createCell(3);
total.setCellFormula("SUM(C2:C100)");

POI writes the formula string and does not compute a value, so a consumer reading the file with POI sees no cached result. Spreadsheet applications recalculate on open. To force it:

workbook.setForceFormulaRecalculation(true);

Or evaluate before writing, which is not available on SXSSFWorkbook for flushed rows:

workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();

Serving the file from a web application

An export is usually downloaded rather than written to disk, and the reflex — build the whole workbook into a ByteArrayOutputStream, then send the bytes — puts the entire file in heap twice. Write straight to the response instead:

@GetMapping("/export.xlsx")
public void export(HttpServletResponse response) throws IOException {
    response.setContentType(
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setHeader(HttpHeaders.CONTENT_DISPOSITION,
            ContentDisposition.attachment().filename("articles.xlsx").build().toString());

    try (SXSSFWorkbook workbook = new SXSSFWorkbook(100)) {
        writeSheet(workbook, articles);
        workbook.write(response.getOutputStream());
        workbook.dispose();
    }
}

The content type is that whole string; sending application/vnd.ms-excel for an .xlsx file makes some clients refuse to open it, and application/octet-stream loses the icon and the file association.

One consequence of streaming directly: the response has already started by the time a failure can occur, so an exception halfway through cannot become a clean error page. The client receives a truncated file. Where that matters, generate to a temporary file first, then stream it — the memory cost is bounded either way, and only then can a failure produce a proper error.

Reading back what you wrote

Verifying an export in a test is worth the few lines, because most of the defects above produce a file that opens rather than one that fails:

try (Workbook workbook = WorkbookFactory.create(Files.newInputStream(path))) {
    Cell cell = workbook.getSheetAt(0).getRow(1).getCell(2);
    assertThat(cell.getCellType()).isEqualTo(CellType.NUMERIC);
    assertThat(DateUtil.isCellDateFormatted(cell)).isTrue();
}

DateUtil.isCellDateFormatted is the check that catches the unstyled-date bug, and it is the only way to distinguish a date from an ordinary number, because at the file-format level there is no difference.

Modifying an existing file

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

    Sheet sheet = workbook.getSheetAt(0);
    Row row = sheet.getRow(5);
    Cell cell = row.getCell(2, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
    cell.setCellValue("updated");

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

Two hazards. sheet.getRow(n) and row.getCell(n) return null for a row or cell that was never written — an empty-looking spreadsheet has no objects for its blank cells — so the missing-cell policy or a null check is mandatory.

And writing back to the same path while the workbook is open truncates the file the workbook is reading from. Write to a temporary file and move it over the original, which is also what makes the update atomic.

SXSSFWorkbook cannot read at all. Editing means XSSFWorkbook, with the memory that implies.

Related: reading Excel files and CSV export, which is a better format when the consumer is a program rather than a person. More in the Java guides.

Frequently asked questions

Which workbook class should I use?

XSSFWorkbook for ordinary exports, SXSSFWorkbook when the row count is large or unbounded. HSSFWorkbook only for legacy .xls, which caps at 65,536 rows.

Why does my export throw after a few thousand rows?

Almost always a CellStyle created inside the loop. A workbook allows about 64,000 styles and POI does not deduplicate them.

Why is my date showing as a number?

Excel stores dates as numbers and relies on the cell format to display them. Apply a CellStyle with a date DataFormat.

Why is a long identifier wrong in the output?

Numeric cells are doubles, so precision is lost above 2^53. Write long identifiers as strings.

Does SXSSFWorkbook let me go back and edit a row?

No. Rows outside the window have been flushed and getRow returns null. Compute anything that needs a second pass before writing.

Do I need to call dispose()?

Yes, on SXSSFWorkbook. close() does not remove the temporary files it spilled rows into.

Why does autoSizeColumn not work with SXSSF?

It measures every cell, which requires them all in memory. Call trackAllColumnsForAutoSizing() first, or set explicit widths — the latter is cheaper.

Why is my formula cell empty when I read the file back?

POI writes the formula without computing a value. Set setForceFormulaRecalculation(true), or run a FormulaEvaluator before writing.

Why do I get a NullPointerException reading an existing sheet?

Rows and cells that were never written do not exist as objects. Use Row.MissingCellPolicy.CREATE_NULL_AS_BLANK or check for null.

Can I write back to the file I am reading?

Not while the workbook is open on it — the output stream truncates it. Write to a temporary file and move it over the original.