When building admin or B2B back-office systems, Excel download and upload are pretty much required features.
In Java, Apache POI is the standard library, and at first I just implemented it with XSSFWorkbook without thinking too much.
On local with a few hundred test rows, it worked fine.
The problem showed up in production after real data piled up.
A user clicked download for tens of thousands of rows, the screen froze for a long time, and the server crashed with OutOfMemoryError: Java heap space.
Why XSSFWorkbook Eats So Much Memory
While looking into the cause, I read the Apache POI docs and a well-known Korean tech blog post from Woowa Bros (Baemin) about fixing large Excel downloads.
After reading them, I realized why my simple code blew up.
An .xlsx file is actually a compressed set of XML files inside.
The problem is that XSSFWorkbook turns every single row and cell into Java objects and holds the whole thing in JVM heap memory at once.
Even if the final file is only 10MB, the XML object tree in memory can easily grow to hundreds of MB or over 1GB.
If two or three users click download at the same time, the server memory simply cannot take it.
Switching Export to SXSSFWorkbook
Apache POI provides SXSSFWorkbook for writing large Excel files.
The idea behind it is actually pretty simple.
Instead of keeping every row in memory, it only keeps a fixed number of rows (like 100 or 1000) in memory.
Once it goes past that number, it flushes the older rows to temporary XML files on disk and clears them from heap memory.
So whether you export 100,000 rows or 500,000 rows, memory usage stays flat.
public void downloadExcel(List<OrderDto> orders, OutputStream out) throws IOException {
// Keep only 1000 rows in memory and flush older rows to temp files on disk
SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
workbook.setCompressTempFiles(true); // compress temp files
try {
Sheet sheet = workbook.createSheet("Orders");
// Create styles once outside the loop and reuse them
CellStyle headerStyle = createHeaderStyle(workbook);
CellStyle bodyStyle = createBodyStyle(workbook);
// Header row
Row headerRow = sheet.createRow(0);
String[] headers = {"Order No", "Product", "Amount", "Ordered At"};
for (int i = 0; i < headers.length; i++) {
Cell cell = headerRow.createCell(i);
cell.setCellValue(headers[i]);
cell.setCellStyle(headerStyle);
sheet.setColumnWidth(i, 5000); // fixed width
}
// Body rows
int rowNum = 1;
for (OrderDto order : orders) {
Row row = sheet.createRow(rowNum++);
Cell c0 = row.createCell(0);
c0.setCellValue(order.getOrderNo());
c0.setCellStyle(bodyStyle);
Cell c1 = row.createCell(1);
c1.setCellValue(order.getProductName());
c1.setCellStyle(bodyStyle);
Cell c2 = row.createCell(2);
c2.setCellValue(order.getAmount());
c2.setCellStyle(bodyStyle);
Cell c3 = row.createCell(3);
c3.setCellValue(order.getOrderedAt());
c3.setCellStyle(bodyStyle);
}
workbook.write(out);
} finally {
// Close stream and delete temp XML files so they do not pile up on disk
workbook.close();
workbook.dispose();
}
}
One more thing to watch out for is DB querying. If you load 100,000 rows into a List<OrderDto> all at once before writing the Excel file, memory will fill up during the DB query anyway.
For really large exports, you should fetch data in pages (like 1000 rows at a time) or stream it using something like MyBatis ResultHandler and write directly to the sheet.
Things I Got Stuck On While Switching
Just changing the class name from XSSFWorkbook to SXSSFWorkbook was not enough.
I got stuck on a few things while applying it.
Forgetting dispose() fills up disk space
SXSSFWorkbook saves memory by writing temporary XML files into a temp folder like /tmp.
Calling only workbook.close() closes the stream, but the temp files can stay behind on disk.
So you must call workbook.dispose() inside a finally block to clean up those temp files.
Never call createCellStyle() inside the loop
In my first version, I called workbook.createCellStyle() for every row.
Excel actually has a hard limit on cell styles (around 64,000), so with large data it either throws an error or breaks the file, while also eating a lot of memory.
As the Woowa Bros post also pointed out, you should create just the few styles you need (header, body, number format) once outside the loop and reuse them.
Give up on autoSizeColumn() for large files
I was originally using sheet.autoSizeColumn(i) to fit column widths nicely, and it immediately threw an error after switching to SXSSFWorkbook.
Because older rows are already flushed to disk, POI cannot check all rows to calculate the width.
You can turn on trackAllColumnsForAutoSizing(), but it makes exports noticeably slower because it checks every cell width.
For large Excel files, just setting a reasonable fixed width with setColumnWidth is much faster and simpler.
Handling Large Uploads with SAX Parsing + Batch Insert
After fixing downloads, large Excel uploads had the exact same problem.
If you read a big .xlsx file using WorkbookFactory.create(inputStream) or new XSSFWorkbook(inputStream), it loads the whole file into memory and triggers OOM again.
Since there is no SXSSF for reading, you have to use Apache POI’s SAX parsing approach.
By combining OPCPackage, XSSFReader, and XMLReader, you can read the XML from top to bottom row by row like a stream.
The code is definitely longer and less pretty than regular Workbook code, but memory usage stays very low.
Also, even if you read the Excel file without running out of memory, running single DB insert queries one by one for 50,000 rows takes forever.
So I buffered parsed rows in chunks (like 500 or 1000 rows) and inserted them together with JDBC batchUpdate.
public class ChunkedExcelRowHandler implements XSSFSheetXMLHandler.SheetContentsHandler {
private static final int BATCH_SIZE = 1000;
private final List<ProductRow> buffer = new ArrayList<>(BATCH_SIZE);
private final ProductBatchRepository batchRepository;
private ProductRow currentRow;
@Override
public void startRow(int rowNum) {
if (rowNum == 0) return; // skip header
currentRow = new ProductRow();
}
@Override
public void cell(String cellReference, String formattedValue, XSSFComment comment) {
if (currentRow == null) return;
// Map cell column (A, B, C...) to currentRow fields
currentRow.setByCellRef(cellReference, formattedValue);
}
@Override
public void endRow(int rowNum) {
if (rowNum == 0 || currentRow == null) return;
buffer.add(currentRow);
if (buffer.size() >= BATCH_SIZE) {
batchRepository.batchInsert(buffer);
buffer.clear();
}
}
public void flushRemaining() {
if (!buffer.isEmpty()) {
batchRepository.batchInsert(buffer);
buffer.clear();
}
}
}
Once I switched to streaming read and chunked batch insert, uploading tens of thousands of rows kept memory stable and ran much faster.
I didn’t expect an Excel feature to have this many details, but hitting OOM and fixing it with references like the Woowa Bros tech blog taught me a lot.
Understanding how POI handles XML inside heap memory also helped me handle other large file tasks much better afterward.
Code optimization is great, of course, but in real work it also made me ask whether users really need to export 100,000 rows synchronously every time.
Adding date range filters, offering CSV for huge exports, or generating files asynchronously in the background often makes life much easier for both the server and the users.