Java Performance Guide: Large Excel Generation

Efficiently export millions of records using Apache POI's streaming capabilities.

1. The Challenge: OOM Error

Generating Excel files with millions of records (from a database, etc.) causes the JVM to run out of memory.

The Problem: java.lang.OutOfMemoryError: Java heap space

2. The Solution: SXSSFWorkbook

Apache POI provides the streaming API, **SXSSFWorkbook**, which is designed specifically for large Excel files.

  • Supported in POI 3.16+ and Java 1.7+.
  • Optimized for low memory footprint.
  • Writes data to temporary files (not in memory).

3. The Key: Window Size

The crucial step is setting the **Random Access Window Size** on the sheet.

SXSSFWorkbook sxssWb = new SXSSFWorkbook(100); sxssSheet.setRandomAccessWindowSize(100);

This flushes every 100 rows to disk, reducing heap usage.

4. Setup & Monitoring

Monitoring Tool

Use **VisualVM** to get real-time insights into memory usage and JVM performance.

Maven Dependencies (POI)

Ensure you include `poi`, `poi-ooxml`, `poi-ooxml-schemas`, and `xmlbeans` in your project dependencies.

⚠️ **Important Note:** SXSSF is *write-only*. You cannot modify cell values after they are written and flushed. For editing, use `XSSFWorkbook`.