How to Write Data to Excel in Java (From a Database or JSON)

Exporting data to Excel from a Java application is a common backend task, whether the source is a database, JSON response, or another service. In this guide, we’ll show how to write an XLSX file in Java with Axyra Sheets for Java, turning structured data into a workbook that includes headers, data types, and basic formatting. For a short walkthrough of Axyra before diving in, see Quick Start.

Which approach to use when writing an Excel file in Java

Your data	Use	Why
A list of records, POJOs, or maps	`Sheet.importData`	Reflects the properties into columns, writes a header row, one JNI crossing per chunk
A numeric matrix	`Range.setNumbers`	Flat `double[]`, no boxing, no `CellValue` allocation — the fastest path in the API
Mixed types, known shape	`Range.setValues`	One crossing for the whole block
More rows than fit in memory	`StreamWorkbook`	Writes row by row to disk; flat memory regardless of row count

What all four have in common: never loop per cell. Every Cell call crosses the JNI boundary, so a million-cell loop is a million crossings.

Export data to Excel from a database

Map the ResultSet to records first, then hand the list over. Records are the best fit because importData reads their components in declaration order, which gives you deterministic column order for free.

import io.keikai.axyra.sheets.CellValue;
import io.keikai.axyra.sheets.ImportOptions;
import io.keikai.axyra.sheets.Sheet;
import io.keikai.axyra.sheets.Workbook;
import io.keikai.axyra.sheets.style.CellStyle;

import java.nio.file.Path;
import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

record Sale(String region, String product, LocalDate orderDate, double amount) {}

List<Sale> rows = new ArrayList<>();
try (PreparedStatement ps = conn.prepareStatement(
             "SELECT region, product, order_date, amount FROM sales WHERE quarter = ?")) {
    ps.setString(1, "Q3");
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            rows.add(new Sale(
                    rs.getString("region"),
                    rs.getString("product"),
                    rs.getObject("order_date", LocalDate.class),
                    rs.getDouble("amount")));
        }
    }
}

try (Workbook wb = Workbook.create()) {
    Sheet sheet = wb.createSheet("Q3 Sales");

    CellStyle headerStyle = CellStyle.builder().bold().build();

    int written = sheet.importData(0, 0, rows, ImportOptions.builder()
            .columns("region", "product", "orderDate", "amount")
            .headers(true)
            .columnLabels("Region", "Product", "Order Date", "Amount")
            .headerStyle(headerStyle)
            .build());

    System.out.println(written + " rows including the header");
    wb.save(Path.of("q3-sales.xlsx"));
}

importData returns the number of rows written, counting the header when it is enabled.

Pin the column list. Without .columns(...), every readable property is written: records in component order, JavaBeans in alphabetical property order, maps in the key order of the first map. Adding a field to the record then silently adds a column to next quarter’s export. Naming the columns makes that a compile-time concern instead of a surprise in a spreadsheet.

Dates need a number format

importData converts LocalDate, LocalDateTime, and java.util.Date to Excel date serials, but it does not apply a number format. Only the header row gets a style, and only the one you pass. So a date column arrives looking like 45900 until you format it:

CellStyle dateStyle = CellStyle.builder().numberFormat("yyyy-mm-dd").build();
CellStyle money = CellStyle.builder().numberFormat("#,##0.00").build();

int lastRow = rows.size();                      // header is row 0
sheet.range(1, 2, lastRow, 2).setStyle(dateStyle);   // column C
sheet.range(1, 3, lastRow, 3).setStyle(money);       // column D

Export JSON data to Excel

jackson-databind is already on the classpath — the library uses it across the JNI boundary — so deserialize into the same kind of record and reuse the path above.

import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.core.type.TypeReference;

ObjectMapper mapper = new ObjectMapper();

List<Sale> rows = mapper.readValue(json, new TypeReference<List<Sale>>() {});
sheet.importData(0, 0, rows, options);

java.time types need one more Jackson artifact. The library pulls in jackson-databind but not jackson-datatype-jsr310, so a record with a LocalDate component fails to deserialize until you add it and register the module:

<dependency>
    <groupId>com.fasterxml.jackson.datatype</groupId>
    <artifactId>jackson-datatype-jsr310</artifactId>
    <!-- match the jackson-databind version Axyra Sheets brings in;
         `mvn dependency:tree` shows it -->
    <version>2.22.3</version>
</dependency>

ObjectMapper mapper = new ObjectMapper()
        .registerModule(new com.fasterxml.jackson.datatype.jsr310.JavaTimeModule());

Import maps without a record

importData also accepts a List<Map<String, Object>>, such as the result of a generic JSON parse. Columns follow the key order of the first map; a key that appears only in later maps becomes an extra column, and a missing or null value becomes a blank cell. Pin the columns as you would for records:

import java.util.Map;

List<Map<String, Object>> maps = mapper.readValue(json,
        new TypeReference<List<Map<String, Object>>>() {});

sheet.importData(0, 0, maps, ImportOptions.builder()
        .columns("region", "product", "amount")
        .columnLabels("Region", "Product", "Amount")
        .build());

A JSON date parsed into a map is still a string, so it arrives as text. Bind to a record with a LocalDate component when the column must hold real dates.

If you need full control over each cell’s type, drive the columns yourself:

import com.fasterxml.jackson.databind.JsonNode;

JsonNode array = mapper.readTree(json);
String[] columns = { "region", "product", "amount" };

// Header.
CellValue[][] header = new CellValue[1][columns.length];
for (int c = 0; c < columns.length; c++) {
    header[0][c] = CellValue.text(columns[c]);
}
sheet.range(0, 0, 0, columns.length - 1).setValues(header);

// Body, in one crossing.
CellValue[][] body = new CellValue[array.size()][columns.length];
for (int r = 0; r < array.size(); r++) {
    JsonNode node = array.get(r);
    for (int c = 0; c < columns.length; c++) {
        JsonNode field = node.get(columns[c]);
        body[r][c] = field == null || field.isNull() ? CellValue.blank()
                : field.isNumber() ? CellValue.number(field.asDouble())
                : field.isBoolean() ? CellValue.bool(field.asBoolean())
                : CellValue.text(field.asText());
    }
}
sheet.range(1, 0, array.size(), columns.length - 1).setValues(body);

setValues requires the array shape to match the range exactly, which is why the row count is computed rather than guessed.

The fast path for numbers

When the block is entirely numeric — a measurement matrix, a pivot of aggregates — skip CellValue altogether. setNumbers takes a flat, row-major double[]:

int rowCount = 10_000, colCount = 12;
double[] flat = new double[rowCount * colCount];
// fill row-major: flat[r * colCount + c]

sheet.range(1, 0, rowCount, colCount - 1).setNumbers(flat);

This is the fastest way to get numbers into a sheet and the right choice for loading a matrix out of a database.

When the export is too big to hold in memory

Workbook builds the whole book in memory before writing a byte — the right trade when you need to read, edit, recalculate, or lay out, and the wrong one when you are pouring a result set into a file. StreamWorkbook writes each row as it arrives, so a million rows costs the same memory as ten.

import io.keikai.axyra.sheets.io.StreamStyle;
import io.keikai.axyra.sheets.io.StreamWorkbook;

try (StreamWorkbook out = StreamWorkbook.create(Path.of("big-export.xlsx"))) {
    int header = out.addStyle(StreamStyle.defaults().withBold());
    int money  = out.addStyle(StreamStyle.defaults().withNumberFormat("#,##0.00"));
    int date   = out.addStyle(StreamStyle.defaults().withNumberFormat("yyyy-mm-dd"));

    out.startSheet("Sales");

    out.row(0).addText("Region", header)
              .addText("Order Date", header)
              .addText("Amount", header)
              .commit();

    int r = 1;
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            out.row(r++)
               .addText(rs.getString("region"))
               .addDate(rs.getObject("order_date", LocalDate.class), date)
               .addNumber(rs.getDouble("amount"), money)
               .commit();
        }
    }
}

What you give up in exchange for flat memory:

Forward-only. Rows go out in ascending index order and never come back; writing row 5 after row 9 throws.
Values, number formats, bold and italic only. No charts, images, merges, conditional formats, comments, or validation — those need the whole-workbook model.
No recalculation. Formulas are written verbatim. If the consumer must see a number before opening the file in Excel, supply the cached value with setFormula(column, formula, cachedValue, style).
Styles are registered up front. addStyle returns an int id; pass the id to the cell writers.
Commit every row. commit() is what actually writes the row; a row you never commit is silently dropped, with no error.

See Streaming Large Files for the full comparison, including the columnar backend for holding big sheets in memory.

Common mistakes when writing XLSX files in Java

Symptom	Cause
Export takes minutes	A per-cell loop. Use `importData`, `setValues`, or `setNumbers`.
A date column shows `45900`	`importData` writes serials but applies no format. Style the column.
A date column from JSON is text	Values parsed into a `Map` stay strings. Bind to a record with a `LocalDate` component.
A new column appeared in the export after a code change	`.columns(...)` was not pinned, so a new field became a new column.
`AxyraException` from `importData`	Mixed element types in the list, or an unknown name in `.columns(...)`.
`setValues` throws	The array shape does not match the range.
Streaming writer throws on a row	Rows must be written in ascending order; going back to an earlier row throws.
A row is missing from the streamed file	The row was never committed. A row without `commit()` is silently discarded.
Formulas are blank in the streamed file	The streaming writer never recalculates. Supply a cached value.

Frequently asked questions

How do I write an XLSX file in Java?

Create a workbook with Workbook.create(), add a sheet with createSheet, and write your data in bulk with importData, setValues, or setNumbers depending on its shape. Then call wb.save(Path.of("output.xlsx")). Open the workbook in a try-with-resources block so its native memory is released.

How do I export data from a database to Excel in Java?

Map the ResultSet to a list of records, then pass the list to sheet.importData with the column list pinned through .columns(...). If the result set is too large to hold in memory, write it row by row with StreamWorkbook instead.

How do I write a very large Excel file in Java without running out of memory?

Use StreamWorkbook, which writes each row to disk as it arrives, so memory use stays flat regardless of row count. In exchange, rows must be written in ascending order, formulas are not recalculated, and formatting is limited to values, number formats, bold, and italic.

Why does a date column show a number like 45900?

Excel stores dates as serial numbers, and importData writes the serial without applying a number format. Apply a date format such as yyyy-mm-dd to the column with setStyle.

Can I read and write Excel files in Java with the same library?

Yes. Axyra Sheets uses the same Workbook API for both directions: you can open an existing file, read or edit its values, and save it again. See How to Read an Excel File in Java for the reading side.

Next steps

How to Create Excel Reports Automatically in Java — fill a designed template instead of building layout in code
Cells and Ranges — the importData reference
Streaming Large Files — streaming writer and columnar backend
Styles and Formats — number format codes

Beyond Basic Data Export

Writing an XLSX file in Java is often only one half of the job, because many applications also read Excel files back in. Once the data is in a workbook, Axyra can also handle formulas, formatting, charts, pivot tables, and PDF or image rendering. This lets the same workflow grow from a simple data export into more complete reporting and spreadsheet automation.

Get code at GitHub

Twitter Facebook LinkedIn