DEV Community: scndry

Writing 100K XLSX Rows in 150ms — Streaming Beyond SXSSF (2026)

scndry — Thu, 30 Apr 2026 22:46:05 +0000

Java's standard library for writing XLSX (Apache POI) hits a tradeoff at scale: SXSSF streams cells but still loads the OOXML scaffolding in memory; raw XSSF buffers everything.
jackson-dataformat-spreadsheet separates these two concerns — POI generates the scaffold once, then a StringBuilder streams cells directly into the worksheet zip entry. Result on JMH: 100K rows in ~144ms with ~180MB peak, ~7% faster than the closest alternative (FastExcel).

OOXML correctness — relationships, content types, namespace declarations, theme references, drawing rels. Get these wrong and Excel may refuse to open the file or report a recovery dialog. The complexity is bounded but the surface is large.
Per-cell throughput — <c r="A1" t="s"><v>0</v></c> repeated millions of times. This dominates write time at scale.

Yet most libraries solve both via a heavy object model (Apache POI's User Model — correct but slow) or hand-roll everything (fast but you own every OOXML edge case).

There's a middle path: let POI handle correctness, let StringBuilder handle the hot path, split them at the format boundary.

The Existing Spectrum

All POI (User Model — XSSFWorkbook):

✅ OOXML correctness handled
❌ Object allocation per cell (Cell, RichTextString, CellStyle reference chains)
❌ Whole workbook lives in memory until write()
POI's SXSSFWorkbook improves memory by flushing rows to disk, but per-cell API overhead remains

Hand-rolled XML:

✅ Maximum throughput
❌ You own every OOXML detail listed above
❌ Easy to ship a file that opens in some readers but breaks Excel
❌ POI version updates can shift the OOXML output you've replicated, forcing re-validation

FastExcel (dhatim) takes this hand-rolled path with discipline — maintaining its own OOXML correctness logic independent of POI. It's a different tradeoff: faster code path, but a separate compatibility surface to maintain.

The Split

The insight: OOXML correctness is a one-time cost per file. Relationships, metadata, theme — all bounded in size, written once.

Per-cell throughput scales with row count.

The split:

Let POI generate a complete XLSX scaffold with styles and an empty sheet. POI owns correctness.
Split sheet1.xml at the <sheetData> boundary into head, data, tail.
Stream <row> entries via StringBuilder at write time — the hottest path.
Stream sharedStrings.xml independently (one entry per unique string).
Copy every other zip entry (rels, theme, drawing, content types) verbatim from the scaffold.

POI owns OOXML correctness. StringBuilder owns per-cell throughput. The scaffold is the handoff between them.

Implementation Sketch

setSchema()
  ├─ XSSFWorkbook (styles + empty sheet) → temp file (the scaffold)
  └─ split sheet1.xml at <sheetData>:
        head, tail   (POI-generated; copied verbatim)
        data         (<row> entries — streamed at write time)

close()
  ├─ for each zip entry:
  │     sheet1.xml        → head + streamed rows + tail
  │     sharedStrings.xml → store-driven streaming
  │     others            → copied as-is
  └─ delete temp file

The cell write path itself, from SSMLSheetWriter:

@Override
public void writeString(final String value) {
    try {
        final int index = _cacheString(value);
        _appendCellStart("s").append(index);
        _appendCellEnd();
    } catch (IOException e) {
        throw new IllegalStateException(e);
    }
}

_appendCellStart("s") writes <c r="A1" t="s" s="0"><v>, _appendCellEnd() closes with </v></c>. No Cell object. No RichTextString allocation. Just text appended to a StringBuilder that flushes into the zip stream periodically.

Benchmarks (100K rows, mixed types, shared string table, JMH)

Approach	Time	Memory
`XSSFWorkbook` via Jackson layer (POI User Model)	318 ms	246 MB
`SXSSFWorkbook` direct (POI's streaming write)	268 ms	207 MB
Scaffold-based hybrid	144 ms	182 MB

~46% reduction in write time vs SXSSFWorkbook, with correctness still inherited from POI. Memory drops because POI's per-cell wrapper objects (Cell, RichTextString, CellStyle references) are no longer allocated — only the underlying values remain.

Why This Stays Correct

ECMA-376 fixes the structure of <worksheet>. <sheetData> is required, exactly once, at a specific position in the child sequence — POI has no choice but to emit it that way. The split point is backed by the spec, not by POI convention.

This shapes the safety story:

The variable part (<sheetData> contents) is what we replace.
The structural part (everything else) is copied verbatim from POI.
POI version bumps that change formatting (whitespace, namespace prefixes, optional metadata) get absorbed automatically — we copy whatever POI emits.
ECMA-376 evolution is tracked by POI before us; the scaffold reflects the new shape.

A DOM equivalence test in CI parses both outputs (scaffold-based and POI-direct), verifies their sheet1.xml and styles.xml structures match, and runs on every POI version bump — catches implementation bugs in the hand-rolled <sheetData> and any residual divergence before they ship.

The hybrid inherits correctness from POI (which inherits it from ECMA-376) and owns only the per-cell hot path.

Limitations

Applicability. The scaffold overhead — generating an empty XLSX via POI — is a fixed cost per file. The split earns its keep at scale; for small files this overhead can outweigh per-cell savings.

Style table is fixed at scaffold time (implementation-specific). Styles must be declared up-front. Adding a new style after the first cell write would mean re-emitting the styles part — currently unsupported here, though the pattern itself doesn't preclude it.

Why This Pattern Generalizes

The split works for any format where:

Correctness has bounded complexity — a fixed set of metadata pieces to get right
The hot path is repetitive — one record type repeated N times, dominating size and time
The hot path can be isolated structurally — a clear boundary in the format where the repetitive section lives

OOXML fits this pattern cleanly. Other formats with a "metadata + repeated records" shape can invite similar splits. SAX/StAX-only solutions either skip the correctness side or reimplement it. The hybrid acknowledges those are different problems and assigns each to the right tool.

It's the same intuition as letting an ORM build the schema while you write hot-path queries by hand: use the heavy abstraction where correctness matters, drop to the metal where throughput matters, and respect the boundary between them.

The Read Path

Reading uses the same insight without needing a split. StAX directly on OOXML XML, bypassing POI's User Model entirely for XLSX. The XML pull-parser tooling already aligns with Jackson's pull-token model — direct stream-to-token translation, no object materialization. Same principle: don't pay for an object model when you only need a stream of tokens.

The library that motivated this work is jackson-dataformat-spreadsheet — a Jackson dataformat module for Excel I/O, listed in FasterXML/jackson community modules. Apache 2.0.

Critique welcome — especially OOXML edge cases the split might mishandle. I'm specifically curious about scenarios where a hand-rolled <sheetData> could surprise Excel even when the surrounding scaffold is correct.

Reading Excel to Java POJOs — A Modern Alternative to Apache POI (2026)

scndry — Thu, 16 Apr 2026 10:42:41 +0000

Reading Excel into Java POJOs is normally a multi-step exercise with Apache POI: open the workbook, iterate rows, fetch cells by column index, cast each to its type, build the object yourself. jackson-dataformat-spreadsheet is a modern alternative — Jackson solves this same problem for JSON, and the library applies the same pattern to XLSX/XLS.

Workbook wb = new XSSFWorkbook(file);
Sheet sheet = wb.getSheetAt(0);
for (Row row : sheet) {
    String name = row.getCell(0).getStringCellValue();
    int qty = (int) row.getCell(1).getNumericCellValue();
    // ... 20 more fields
}

Cell by cell. Column index by column index. Cast by cast. For every single spreadsheet your application touches.

What if you could do this instead?

SpreadsheetMapper mapper = new SpreadsheetMapper();
List<Employee> employees = mapper.readValues(file, Employee.class);

That's it. Same API as Jackson's ObjectMapper. Because a spreadsheet row IS a JSON object.

Introducing jackson-dataformat-spreadsheet

A Jackson extension module that treats XLSX/XLS as just another data format — like JSON, CSV, or XML.

GitHub: jackson-dataformat-spreadsheet
Listed as a community data format module in the official FasterXML jackson repository.

Reading

@DataGrid
public class Employee {
    private String name;
    private String department;
    private int salary;
    // getters, setters
}

SpreadsheetMapper mapper = new SpreadsheetMapper();

// Single row
Employee first = mapper.readValue(file, Employee.class);

// All rows
List<Employee> all = mapper.readValues(file, Employee.class);

// Specific sheet
SheetInput<File> input = SheetInput.source(file, "Payroll");
List<Employee> payroll = mapper.readValues(input, Employee.class);

Writing

List<Employee> employees = ...;
mapper.writeValue(file, employees, Employee.class);

That produces a proper XLSX file with headers and typed cells.

Nested Objects — The Killer Feature

Spreadsheets are flat. POJOs are not. Most Excel libraries force you to flatten everything manually. This library does it automatically:

┌─────┬──────┬─────────┬────────────────┬─────────────┬────────┐
│ ID  │ NAME │ ZIPCODE │ ADDRESS LINE 1 │ DESIGNATION │ SALARY │
├─────┼──────┼─────────┼────────────────┼─────────────┼────────┤
│ 1   │ John │ 12345   │ 123 Main St.   │ CEO         │ 300000 │
└─────┴──────┴─────────┴────────────────┴─────────────┴────────┘

@DataGrid
class Employee {
    int id;
    String name;
    Address address;
    Employment employment;
}

class Address {
    String zipcode;
    String addressLine1;
}

class Employment {
    String designation;
    long salary;
}

No configuration needed. The nested POJO structure defines the column layout. Read and write — both directions work.

How It's Built

This isn't a POI wrapper. It extends Jackson's streaming layer directly:

SheetParser extends ParserMinimalBase — pulls tokens from StAX
SheetGenerator extends GeneratorBase — streaming cell writer
SpreadsheetFactory extends JsonFactory — creates parsers/generators

The default XLSX path bypasses POI's cell model entirely. The read path parses OOXML XML directly via StAX — no XMLBeans, no SAX callbacks, no intermediate DOM. The write path builds a POI skeleton for package metadata, then streams worksheet and shared strings via StringBuilder directly to ZipOutputStream.

Jackson (pull)       SheetParser (pull)      StAX (pull)
    │                      │                      │
    ├─ nextToken() ───────►├─ next() ────────────►├─ next()
    │◄─ VALUE_STRING ──────┤◄─ CELL_VALUE ────────┤◄─ START_ELEMENT

Performance

Benchmarked against popular alternatives on realistic data (100K rows, mixed types, JMH):

Read:

┌────────────────────────────┬───────────┬─────────┐
│          Library           │ Read Time │ Memory  │
├────────────────────────────┼───────────┼─────────┤
│ jackson-spreadsheet        │ 196 ms    │ 360 MB  │
├────────────────────────────┼───────────┼─────────┤
│ FastExcel                  │ 252 ms    │ 407 MB  │
├────────────────────────────┼───────────┼─────────┤
│ Fesod (formerly EasyExcel) │ 267 ms    │ 384 MB  │
├────────────────────────────┼───────────┼─────────┤
│ Poiji                      │ 848 ms    │ 2743 MB │
├────────────────────────────┼───────────┼─────────┤
│ Apache POI                 │ 1095 ms   │ 2225 MB │
└────────────────────────────┴───────────┴─────────┘

Write:

┌───────────────────────────┬────────────┬─────────┐
│          Library          │ Write Time │ Memory  │
├───────────────────────────┼────────────┼─────────┤
│ jackson-spreadsheet       │ 144 ms     │ 182 MB  │
├───────────────────────────┼────────────┼─────────┤
│ FastExcel                 │ 154 ms     │ 149 MB  │
├───────────────────────────┼────────────┼─────────┤
│ Apache POI                │ 268 ms     │ 207 MB  │
├───────────────────────────┼────────────┼─────────┤
│ Fesod                     │ 314 ms     │ 458 MB  │
└───────────────────────────┴────────────┴─────────┘

Fastest read AND write throughput. ~5.6x faster read than Apache POI, ~7% faster write than FastExcel. You don't trade performance for convenience — you get both.

Annotations

Control the schema with @DataGrid and @DataColumn:

@DataGrid
class Product {
    @DataColumn("Product Name")
    String name;

    @DataColumn(value = "Price", style = "currency")
    double price;

    @DataColumn(merge = OptBoolean.TRUE)
    String category;
}

Getting Started

Available on Maven Central:

<dependency>
    <groupId>io.github.scndry</groupId>
    <artifactId>jackson-dataformat-spreadsheet</artifactId>
    <version>1.6.0</version>
</dependency>

Requirements

Java 8+
Jackson 2.14.0+
Apache POI 4.1.1+ (Strict OOXML requires 5.1.0+)

Links

Feedback, issues, and stars welcome. If you've ever cursed at row.getCell(17).getStringCellValue(), this is for you.