Runnable JUnit-test examples for jackson-dataformat-spreadsheet — 40+ self-contained Java files covering Excel read/write, cell styling, nested objects with @DataColumnGroup, Jackson annotation interop (@JsonView, @JsonAlias, MixIn, custom serializers), CSV ↔ XLSX format interop, conditional formatting / freeze pane / auto filter, Spring Boot REST integration, Apache POI fallback (multi-sheet, formulas, templates), and 100K+ row streaming. Each example is one file under src/main/java; run ./gradlew test to execute them all as JUnit tests.
The library extends Jackson's ObjectMapper so the same mapper.readValue / mapper.writeValue API used for JSON applies to Excel — see the library README for comparison vs Apache POI / Fesod / FastExcel and the full feature list.
| Jackson Spreadsheet | Apache POI (direct) | EasyExcel | |
|---|---|---|---|
| Lines to read/write Excel | 2-3 | 20+ | 3-5 |
| Learning curve | Low (Jackson annotations) | Steep (Sheet/Row/Cell API) | Low |
| Memory at 100K rows | ~128MB (streaming default) | ~500MB+ (XSSF) | ~200MB |
| Jackson ecosystem | Native (@JsonView, @JsonIgnore, custom serializers) | None | Limited |
| Spring Boot integration | Built-in (OutputStream/InputStream) | Manual | Easy |
| Template population | Yes (via POI Sheet) | Yes | Yes |
Key advantages:
- Annotation-driven mapping — no manual cell iteration
- Streaming by default — handles 100K+ rows without OutOfMemoryError
- Full Jackson feature set — @JsonView, @JsonUnwrapped, MixIn, custom serializers all work
- Falls back to POI when needed — multi-sheet, formulas, charts, templates
1. Add dependency
implementation("io.github.scndry:jackson-dataformat-spreadsheet:1.6.3")2. Define your model
@Data @DataGrid
public class Product {
private String name;
private int quantity;
private double price;
}3. Write and read Excel
var mapper = new SpreadsheetMapper();
// Export to Excel
mapper.writeValue(new File("products.xlsx"), products, Product.class);
// Import from Excel
List<Product> data = mapper.readValues(new File("products.xlsx"), Product.class);All examples are runnable as JUnit tests: ./gradlew test
| Example | Description |
|---|---|
| SimpleWriteExample | Export Java objects to Excel in one line |
| SimpleReadExample | Import Excel to Java objects in one line |
| Example | Description |
|---|---|
| BasicReadExample | Import all rows or first row into typed objects |
| MultiSheetReadExample | Read from specific sheet by name or index |
| StreamingReadExample | Stream large files row-by-row (100K+ rows, constant memory) |
| DateHandlingExample | Automatic date conversion (LocalDate, LocalDateTime, Date) |
| ErrorHandlingExample | Skip invalid rows, log errors with row location |
| Example | Description |
|---|---|
| BasicWriteExample | Export to file, OutputStream, named sheet, byte array |
| StyleWriteExample | Number formats, fonts, borders, fills, header styles |
| MergeWriteExample | Vertical cell merging with nested lists |
| SequenceWriteExample | Stream rows incrementally (database cursors, pagination) |
| HeaderCommentExample | Attach hover comments to header cells via @DataColumn(comment = ...) |
| Example | Description |
|---|---|
| NestedObjectExample | Flatten nested POJOs to columns, reconstruct on read |
| DataColumnGroupExample | Multi-row header — group flattened columns under a shared parent header |
| DataColumnGroupListExample | Multi-row header over List<NestedType> — group element columns, vertically merge outer fields |
| DataColumnGroupCascadeExample | @DataColumnGroup cascade slots — group-level style / merge defaults flow into child columns, leaf @DataColumn overrides |
| AttributeResolutionExample | Attribute resolution order — leaf → innermost group → outer group → declaring @DataGrid → enclosing @DataGrid |
| Example | Description |
|---|---|
| JacksonAnnotationExample | @JsonProperty, @JsonIgnore, @JsonPropertyOrder, enum mapping |
| JsonViewExample | Export different column subsets per audience |
| MixInExample | Export third-party classes without modifying source |
| JsonUnwrappedExample | Flatten nested objects with leaf field names as headers |
| CustomSerializerExample | Custom cell value conversion (Yes/No booleans) |
| NullHandlingExample | @JsonInclude(NON_NULL) — blank cells for null fields |
| JsonAliasReorderingExample | @JsonAlias + columnReordering(true) — accept legacy / alternate header names |
Compose SpreadsheetMapper with Jackson's CsvMapper — same POJO, two formats.
| Example | Description |
|---|---|
| Xlsx2CsvExample | XLSX → CSV (load all rows into memory) |
| StreamingXlsx2CsvExample | XLSX → CSV (constant memory, row-by-row streaming) |
| Csv2XlsxExample | CSV → XLSX (load all rows into memory) |
| StreamingCsv2XlsxExample | CSV → XLSX (constant memory, row-by-row streaming) |
| Example | Description |
|---|---|
| SimpleStylesExample | One-line type-based formatting (StylesBuilder.simple()) |
| CloneStyleExample | Inherit and extend cell styles |
| Example | Description |
|---|---|
| ConditionalFormattingExample | Highlight cells whose value matches a rule (column + style by name) |
| ConditionalFormattingColumnRefExample | Schema-aware row-relative column reference (e.g., price > minPrice per row) |
| ConditionalFormattingFormulaExample | Raw Excel formula passthrough — reference a config cell outside the data grid (POI integration) |
| ConditionalFormattingExpressionExample | Arbitrary boolean Excel formula for cross-column logic (AND, OR, ISBLANK) |
| ConditionalFormattingColorScaleExample | 3-color gradient visualization across a column's value range |
| ConditionalFormattingRangeExample | between / notBetween range comparison |
| ConditionalFormattingDateExample | Date type comparison (LocalDate auto-converts to Excel DATE() formula) |
| FreezePaneExample | Keep header row visible while scrolling |
| AutoFilterExample | Enable Excel's filter dropdown on the header row |
| Example | Description |
|---|---|
| ConfigurationExample | Origin, header, column reordering, blank row handling |
| Example | Description |
|---|---|
| POIIntegrationExample | Multi-sheet workbook, formulas, direct Sheet read |
| TemplateWriteExample | Populate pre-formatted Excel templates |
| Example | Description |
|---|---|
| ExcelController | REST API for Excel download and upload (synchronous, small payloads) |
| ExcelStreamingController | Large download via StreamingResponseBody + row-by-row upload via SheetMappingIterator |
| Example | Description |
|---|---|
| LargeFileExample | 100K+ rows, file-backed shared strings, encrypted store, POI fallback |
| BackWriteOomGuardExample | Risky vs safe field order around nested List<T> — back-write buffer triggers when outer fields trail the list |
Header row not found — First row must contain column headers matching field names. Use @DataColumn("Header Name") to override, or useHeader(false) if there is no header.
OutOfMemoryError on large files — Use StreamingReadExample for reads (row-by-row iteration). For writes, enable FILE_BACKED_SHARED_STRINGS (see LargeFileExample).
Column order mismatch — Enable columnReordering(true) to match columns by header name instead of position. Or use @JsonPropertyOrder to control output order.
Dates appear as numbers — Use StylesBuilder.simple() to auto-format date columns, or apply a custom date format via @DataColumn(style = "date").
"No @DataGrid annotation found" — The root POJO must be annotated with @DataGrid. This is what tells the mapper which class defines the spreadsheet schema.
Formula cells return the cached computed value — The reader binds the cached value (emitted when the formula was last evaluated by Excel/POI). To force re-evaluation, open the workbook with POI directly and invoke FormulaEvaluator.evaluateAll() before passing the Sheet to the mapper (see POIIntegrationExample).
ClassNotFoundException: org.h2.mvstore.MVStore — FILE_BACKED_SHARED_STRINGS requires H2 on the classpath. Add the dependency:
implementation("com.h2database:h2:2.2.224")./gradlew visualFixtures regenerates XLSX from each example and renders to PNG in build/visual-fixtures/ for visual review. Requires LibreOffice locally (brew install --cask libreoffice); the PNG step is skipped if soffice is not installed.
Limited to cell-embedded visuals (fill, font, border, format, merge). View-state features (autoFilter dropdown, freeze pane split) and width-dependent visuals don't render cleanly in headless PNG and rely on POI-based tests instead.
- Java 17+
- jackson-dataformat-spreadsheet 1.6.3
- Spring Boot 3.5 (web examples only)
- H2 (file-backed shared strings examples only)
Anonymous, aggregated usage tracking via Scarf. No personal information is collected.
