Apache POI Is Enterprise-Grade. That’s the Problem.
Apache POI is the Java library for reading and writing Microsoft Office files. It’s been around since 2001, it’s used by thousands of enterprise applications, and it covers the full XLSX specification — styles, formulas, charts, pivot tables, conditional formatting, everything. If you need to generate a spreadsheet in Java, POI is the answer. There isn’t really a second option.
The problem isn’t capability. POI can do anything Excel can do. The problem is that doing anything in POI takes a staggering amount of code. Creating a styled cell requires a CellStyle object, a Font object, a DataFormat object, and a CreationHelper. Setting a currency format means calling createHelper.createDataFormat().getFormat("$#,##0.00") and assigning the result to a style that you then assign to a cell. A two-row spreadsheet with formatted headers and currency values is 50+ lines of Java.
The Verbosity Is Real
Here’s what a simple formatted spreadsheet looks like in Apache POI:
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("Q1 Revenue");
// Create header style
CellStyle headerStyle = workbook.createCellStyle();
XSSFFont headerFont = workbook.createFont();
headerFont.setBold(true);
headerFont.setColor(IndexedColors.WHITE.getIndex());
headerFont.setFontHeightInPoints((short) 12);
headerStyle.setFont(headerFont);
headerStyle.setFillForegroundColor(new XSSFColor(
new byte[]{(byte) 0x1A, (byte) 0x1A, (byte) 0x2E}, null
));
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
headerStyle.setAlignment(HorizontalAlignment.CENTER);
// Create currency style
CellStyle currencyStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
currencyStyle.setDataFormat(
createHelper.createDataFormat().getFormat("$#,##0.00")
);
// Write headers
Row headerRow = sheet.createRow(0);
String[] headers = {"Company", "Region", "Revenue"};
for (int i = 0; i < headers.length; i++) {
Cell cell = headerRow.createCell(i);
cell.setCellValue(headers[i]);
cell.setCellStyle(headerStyle);
}
// Write data
Row row1 = sheet.createRow(1);
row1.createCell(0).setCellValue("Acme Corp");
row1.createCell(1).setCellValue("North America");
Cell revenueCell1 = row1.createCell(2);
revenueCell1.setCellValue(48500.00);
revenueCell1.setCellStyle(currencyStyle);
Row row2 = sheet.createRow(2);
row2.createCell(0).setCellValue("Globex Ltd");
row2.createCell(1).setCellValue("Europe");
Cell revenueCell2 = row2.createCell(2);
revenueCell2.setCellValue(37200.00);
revenueCell2.setCellStyle(currencyStyle);
// Write to output
FileOutputStream out = new FileOutputStream("report.xlsx");
workbook.write(out);
out.close();
workbook.close();
That’s 45 lines for two rows of data. Every abstraction is explicit — XSSFWorkbook, XSSFSheet, XSSFFont, CellStyle, CreationHelper, DataFormat, Row, Cell. Each style is a separate object that you create, configure, and assign. The color is a byte array. The font size is a cast to short. The output requires manual stream management.
This is idiomatic Java and idiomatic POI. It’s not poorly written code — it’s just what the API demands.
The Memory Problem
Apache POI’s standard API (XSSFWorkbook) loads the entire workbook into memory as a DOM tree. For small spreadsheets, this is fine. For large ones, it’s a problem.
A 100,000-row spreadsheet with 10 columns can easily consume 500 MB to 1 GB of heap space. The JVM’s garbage collector works overtime, and if your heap is too small, you get OutOfMemoryError. If your heap is large enough, you get GC pauses that stall your application.
POI offers a streaming alternative — SXSSFWorkbook — that writes rows to disk as they’re created, keeping only a sliding window in memory. But SXSSFWorkbook is write-only. You can’t go back and modify earlier rows. You can’t read from the workbook you’re writing. And the API is subtly different from XSSFWorkbook, so switching between them isn’t always straightforward.
For most spreadsheet generation use cases — reports, invoices, data exports — you’re writing sequentially and SXSSFWorkbook works. But you’re now managing two different POI APIs depending on the size of your data, and the streaming version has its own set of gotchas around temporary file cleanup and row flushing.
What POI Doesn’t Do (Easily)
POI is comprehensive for XLSX. But production spreadsheet generation often needs more than just XLSX.
-
CSV requires separate code. POI doesn’t write CSV. You’d use
OpenCSV,Apache Commons CSV, or manualStringBuilderlogic. That’s a completely separate code path for the same data. - No Markdown output. If you need a Markdown table, you write it yourself.
-
No formula evaluation for non-XLSX formats. POI has a formula evaluator (
FormulaEvaluator), but it’s designed for reading spreadsheets, not for generating computed values during export. Using it during generation adds complexity and doesn’t help with CSV output. - JVM required. Your Python service, your Go microservice, your Node.js frontend — none of them can call POI directly. You either expose POI behind a REST endpoint (building and hosting a Java service) or find separate libraries for each language.
-
Build tooling. POI has a non-trivial dependency tree —
poi,poi-ooxml,poi-ooxml-schemas,xmlbeans,commons-compress. Managing these in Maven or Gradle means keeping versions aligned and watching for conflicts with other Apache libraries in your project.
A Direct Comparison
Same spreadsheet, both approaches.
Apache POI (Java only, you host this):
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("Q1 Revenue");
CellStyle headerStyle = workbook.createCellStyle();
XSSFFont headerFont = workbook.createFont();
headerFont.setBold(true);
headerFont.setColor(IndexedColors.WHITE.getIndex());
headerFont.setFontHeightInPoints((short) 12);
headerStyle.setFont(headerFont);
headerStyle.setFillForegroundColor(new XSSFColor(
new byte[]{(byte) 0x1A, (byte) 0x1A, (byte) 0x2E}, null
));
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
headerStyle.setAlignment(HorizontalAlignment.CENTER);
CellStyle currencyStyle = workbook.createCellStyle();
currencyStyle.setDataFormat(
workbook.getCreationHelper().createDataFormat().getFormat("$#,##0.00")
);
Row headerRow = sheet.createRow(0);
String[] headers = {"Company", "Region", "Revenue"};
for (int i = 0; i < headers.length; i++) {
Cell cell = headerRow.createCell(i);
cell.setCellValue(headers[i]);
cell.setCellStyle(headerStyle);
}
Row row1 = sheet.createRow(1);
row1.createCell(0).setCellValue("Acme Corp");
row1.createCell(1).setCellValue("North America");
Cell c1 = row1.createCell(2);
c1.setCellValue(48500.00);
c1.setCellStyle(currencyStyle);
Row row2 = sheet.createRow(2);
row2.createCell(0).setCellValue("Globex Ltd");
row2.createCell(1).setCellValue("Europe");
Cell c2 = row2.createCell(2);
c2.setCellValue(37200.00);
c2.setCellStyle(currencyStyle);
workbook.write(new FileOutputStream("report.xlsx"));
workbook.close();Iteration Layer (any language, JSON in, spreadsheet out):
curl -X POST https://api.iterationlayer.com/sheet-generation/v1/generate \
-H "Authorization: Bearer $API_KEY" \
-H "Content-Type: application/json" \
-d '{
"format": "xlsx",
"styles": {
"header": {
"is_bold": true,
"font_size_in_pt": 12,
"background_color": "#1a1a2e",
"font_color": "#ffffff",
"horizontal_alignment": "center"
}
},
"sheets": [
{
"name": "Q1 Revenue",
"columns": [
{
"name": "Company"
},
{
"name": "Region"
},
{
"name": "Revenue"
}
],
"rows": [
[
{
"value": "Acme Corp"
},
{
"value": "North America"
},
{
"value": 48500.00,
"format": "currency",
"currency_code": "USD"
}
],
[
{
"value": "Globex Ltd"
},
{
"value": "Europe"
},
{
"value": 37200.00,
"format": "currency",
"currency_code": "EUR"
}
]
]
}
]
}'{
"success": true,
"data": {
"buffer": "UEsDBBQAAAAIAA...",
"mime_type": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
}
}import { IterationLayer } from "iterationlayer";
const client = new IterationLayer({
apiKey: "YOUR_API_KEY",
});
const result = await client.generateSheet({
format: "xlsx",
styles: {
header: {
is_bold: true,
font_size_in_pt: 12,
background_color: "#1a1a2e",
font_color: "#ffffff",
horizontal_alignment: "center",
},
},
sheets: [
{
name: "Q1 Revenue",
columns: [
{
name: "Company",
},
{
name: "Region",
},
{
name: "Revenue",
},
],
rows: [
[
{
value: "Acme Corp",
},
{
value: "North America",
},
{
value: 48500.00,
format: "currency",
currency_code: "USD",
},
],
[
{
value: "Globex Ltd",
},
{
value: "Europe",
},
{
value: 37200.00,
format: "currency",
currency_code: "EUR",
},
],
],
},
],
});{
"success": true,
"data": {
"buffer": "UEsDBBQAAAAIAA...",
"mime_type": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
}
}from iterationlayer import IterationLayer
client = IterationLayer(api_key="YOUR_API_KEY")
result = client.generate_sheet(
format="xlsx",
styles={
"header": {
"is_bold": True,
"font_size_in_pt": 12,
"background_color": "#1a1a2e",
"font_color": "#ffffff",
"horizontal_alignment": "center",
},
},
sheets=[
{
"name": "Q1 Revenue",
"columns": [
{
"name": "Company",
},
{
"name": "Region",
},
{
"name": "Revenue",
},
],
"rows": [
[
{
"value": "Acme Corp",
},
{
"value": "North America",
},
{
"value": 48500.00,
"format": "currency",
"currency_code": "USD",
},
],
[
{
"value": "Globex Ltd",
},
{
"value": "Europe",
},
{
"value": 37200.00,
"format": "currency",
"currency_code": "EUR",
},
],
],
},
],
){
"success": true,
"data": {
"buffer": "UEsDBBQAAAAIAA...",
"mime_type": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
}
}package main
import il "github.com/iterationlayer/sdk-go"
func main() {
client := il.NewClient("YOUR_API_KEY")
result, err := client.GenerateSheet(il.GenerateSheetRequest{
Format: "xlsx",
Styles: &il.SheetStyles{
Header: &il.CellStyle{
IsBold: true,
FontSizeInPt: 12,
BackgroundColor: "#1a1a2e",
FontColor: "#ffffff",
HorizontalAlignment: "center",
},
},
Sheets: []il.Sheet{
{
Name: "Q1 Revenue",
Columns: []il.SheetColumn{
{
Name: "Company",
},
{
Name: "Region",
},
{
Name: "Revenue",
},
},
Rows: []il.SheetRow{
{
{
Value: "Acme Corp",
},
{
Value: "North America",
},
{
Value: 48500.00,
Format: "currency",
CurrencyCode: "USD",
},
},
{
{
Value: "Globex Ltd",
},
{
Value: "Europe",
},
{
Value: 37200.00,
Format: "currency",
CurrencyCode: "EUR",
},
},
},
},
},
})
if err != nil {
panic(err)
}
_ = result
}{
"success": true,
"data": {
"buffer": "UEsDBBQAAAAIAA...",
"mime_type": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
}
}
The POI version creates a workbook, a sheet, a font, two cell styles, a creation helper, a data format, rows, and cells — each as a separate Java object with explicit configuration. The API version describes the same spreadsheet as JSON. Header styles are declared once. Currency formatting is "format": "currency" with a "currency_code" — not a raw format string fed through a CreationHelper.
No JVM. No dependency tree. No memory tuning. JSON in, spreadsheet out.
When Apache POI Is the Right Choice
POI is the right tool in specific situations. Pretending otherwise isn’t useful.
- You need the full XLSX specification. POI covers essentially everything Excel supports — charts, pivot tables, conditional formatting, data validation, VBA macros, custom XML parts. If you need any of these, POI is one of the few libraries that can do it.
- You need to read and modify existing spreadsheets. POI can open an XLSX file, change specific cells, add sheets, recalculate formulas, and save it. The Sheet Generation API generates new files; it doesn’t edit existing ones.
- Your entire stack is Java and you have the expertise. If your team writes Java, knows POI’s quirks, and has stable generation code in production, there’s no reason to migrate.
- Offline or air-gapped environments. If your data can’t leave your network, a cloud API isn’t an option.
- You process very large files and need fine-grained memory control. SXSSFWorkbook with explicit flushing gives you precise control over memory usage that an API call can’t match.
When an API Makes More Sense
For most teams — especially those not committed to the JVM — the API is less work.
- You don’t want to run a JVM for spreadsheet generation. If your main service is Python, Go, Node.js, or Elixir, adding a Java service just for XLSX export is significant overhead. The API is an HTTP call from any language.
- You need multiple output formats. XLSX, CSV, and Markdown from the same JSON payload. No separate code paths, no additional libraries.
-
You want less code. A JSON payload describing columns, rows, and styles replaces dozens of lines of
XSSFWorkbook,CellStyle,XSSFFont,CreationHelper,DataFormat,Row, andCellconstruction. -
You don’t want to manage memory for workbook generation. No heap tuning, no GC pauses, no
OutOfMemoryErrorfrom a large report. The API handles memory on its own infrastructure. -
You need formulas that work in CSV. The API evaluates
=SUM(B2:B4)server-side for non-XLSX formats. POI’s formula evaluator is complex to use during generation and doesn’t help with CSV export.
Get Started
Check the Sheet Generation docs for the full API reference — cell formats, style properties, formula support, cell merging, and multi-sheet workbooks. The TypeScript and Python SDKs handle authentication and response parsing.
Iteration Layer runs on EU infrastructure (Frankfurt), which matters if your data residency requirements rule out US-hosted services.