处理超50个单文件25k行Excel的POI优化方案及GC溢出解决

Got it, let's tackle this GC overhead issue you're hitting with Apache POI when processing those large Excel files. I've dealt with similar high-volume Excel processing tasks before, so here are the most practical, proven solutions to get past this error:

This is the most critical fix for large Excel files. The standard XSSF API loads the entire workbook into memory, which is why you're hitting memory limits with 25k-row files multiplied by 50. SXSSF is POI's streaming implementation that only keeps a small window of rows in memory at a time, writing older rows to temporary disk storage automatically.

Here's a quick code snippet to get you started:

// Create SXSSFWorkbook with a window size of 100 rows (keeps only 100 in memory)
SXSSFWorkbook workbook = new SXSSFWorkbook(100);
try {
    SXSSFSheet sheet = workbook.getSheetAt(0);
    // Iterate through rows
    for (Row row : sheet) {
        // Extract your 3 columns here, skip empty cells efficiently
        Cell col1 = row.getCell(0, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
        Cell col2 = row.getCell(1, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
        Cell col3 = row.getCell(2, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
        
        // Process the data immediately (don't store all in an array if possible)
        processRowData(col1, col2, col3);
        
        // Clear row references explicitly to help GC
        row = null;
    }
} finally {
    // Clean up temporary files created by SXSSF
    workbook.dispose();
    workbook.close();
}

Note: Adjust the window size based on your available memory—smaller values mean less memory usage but slightly more disk I/O.

The GC overhead limit exceeded error means the JVM is spending too much time garbage collecting (over 98% of CPU time) but only recovering a tiny amount of memory. Tweaking your JVM args can give POI more breathing room:

• Increase the maximum heap size: Use -Xmx4g (gives 4GB of heap space; adjust based on your system's available RAM)
• Set initial heap size to match max for fewer resizes: -Xms4g
• Use a more efficient garbage collector like G1GC: -XX:+UseG1GC
• Last resort: Disable the GC overhead limit check with -XX:-UseGCOverheadLimit (not recommended long-term, but can help debug if other fixes aren't working)

Storing all 1.25 million rows (50 files × 25k rows) in a single array is unnecessary if you can process data incrementally:

• Process rows as you read them: Instead of saving every row to an array for later calculation, run your computations immediately after extracting the 3 columns. This eliminates the need for a massive in-memory dataset.
• Use primitive arrays instead of object lists: If you must store some data, use double[]/long[] instead of List<Double>/List<Long>—primitive types take up far less memory than wrapper objects.
• Batch processing: If you need to aggregate data, process in batches (e.g., 10k rows at a time) and flush intermediate results to disk or a database instead of holding everything in memory.

• Disable formula evaluation if you don't need it: SXSSF can skip formula calculations to save memory with workbook.setForceFormulaRecalculation(false);
• Avoid unnecessary Cell objects: Use Row.MissingCellPolicy.RETURN_BLANK_AS_NULL instead of creating empty Cell instances for missing columns.
• Close resources promptly: After processing each Excel file, make sure to call workbook.dispose() (for SXSSF) and workbook.close() to free up all associated resources immediately.

内容的提问来源于stack exchange，提问作者shiva allani