客户端分批传数据，如何读取大型XLSX并追加行避免OOM？

Great question—dealing with large Excel files in chunks while avoiding Out-of-Memory (OOM) errors is a super common pain point when working with Apache POI. Let’s break down the best approaches for your exact scenario, where you’re receiving 200k rows in 5 batches of 40k each:

The biggest mistake here is generating a separate Excel file for each batch. Excel files (XLSX) aren’t designed for easy appending—they’re compressed XML packages, so any "append" operation actually requires rewriting the entire file, which is slow and risky for OOM. Instead:

1. Store incoming batches temporarily on the server:

   • If your server has enough memory (200k rows with typical fields will only take ~20-50MB), you can accumulate rows in a List<List<Object>> or a similar in-memory structure.
   • For extra safety (or if you expect even larger datasets later), append each batch to a CSV file on disk. CSV is plain text, so appending is O(1) fast, and it uses way less storage than Excel.

2. Generate the final XLSX file once all batches are received:
   Use SXSSFWorkbook (Apache POI’s streaming workbook) to write the full dataset without OOM. It keeps only a small portion of rows in memory (configurable) and spills the rest to temporary disk files.

Example Code for Final Excel Generation #

// Assume we've collected all 200k rows into an Iterable<List<Object>> (or read from CSV)
SXSSFWorkbook workbook = new SXSSFWorkbook(1000); // Keep 1000 rows in memory at a time
Sheet dataSheet = workbook.createSheet("Full Dataset");

int rowIndex = 0;
for (List<Object> rowData : allCollectedRows) {
    Row row = dataSheet.createRow(rowIndex++);
    int colIndex = 0;
    for (Object cellValue : rowData) {
        Cell cell = row.createCell(colIndex++);
        // Handle different data types as needed
        if (cellValue instanceof String) {
            cell.setCellValue((String) cellValue);
        } else if (cellValue instanceof Number) {
            cell.setCellValue(((Number) cellValue).doubleValue());
        }
        // Add boolean, date handling if required
    }
}

// Write to final file
try (FileOutputStream outputStream = new FileOutputStream("final_large_dataset.xlsx")) {
    workbook.write(outputStream);
} finally {
    workbook.dispose(); // Critical: Clean up temporary disk files
}

If you must update the Excel file after each batch (e.g., real-time progress tracking), you’ll need to:

1. Stream-read the existing Excel file to avoid loading the entire thing into memory.
2. Use SXSSFWorkbook to write a new file that combines the old content + new batch.

This is less efficient (you’re rewriting the file every time), but it works without OOM.

Example Code for Batch Appending #

String existingExcelPath = "current_dataset.xlsx";
List<List<Object>> newBatchRows = ...; // 40k rows from the latest POST

// Stream-read the existing file and write to a new SXSSFWorkbook
try (OPCPackage pkg = OPCPackage.open(existingExcelPath);
     SXSSFWorkbook newWorkbook = new SXSSFWorkbook(1000)) {
    
    XSSFReader reader = new XSSFReader(pkg);
    SharedStringsTable sharedStrings = reader.getSharedStringsTable();

    // Copy existing sheets to the new workbook
    XSSFReader.SheetIterator sheetIterator = (XSSFReader.SheetIterator) reader.getSheetsData();
    while (sheetIterator.hasNext()) {
        InputStream sheetStream = sheetIterator.next();
        Sheet newSheet = newWorkbook.createSheet(sheetIterator.getSheetName());

        // Parse the existing sheet row-by-row
        XMLReader xmlParser = XMLReaderFactory.createXMLReader();
        xmlParser.setContentHandler(new XSSFSheetXMLHandler(sharedStrings, null,
            new XSSFSheetXMLHandler.SheetContentsHandler() {
                private Row currentRow;
                private int currentCol;

                @Override
                public void startRow(int rowNum) {
                    currentRow = newSheet.createRow(rowNum);
                    currentCol = 0;
                }

                @Override
                public void cell(String cellRef, String formattedValue, XSSFComment comment) {
                    if (currentRow != null) {
                        Cell cell = currentRow.createCell(currentCol++);
                        cell.setCellValue(formattedValue);
                    }
                }
            }, false));
        
        xmlParser.parse(new InputSource(sheetStream));
        sheetStream.close();
    }

    // Append the new batch rows to the first sheet
    Sheet targetSheet = newWorkbook.getSheetAt(0);
    int lastRowNum = targetSheet.getLastRowNum();
    for (List<Object> rowData : newBatchRows) {
        Row row = targetSheet.createRow(++lastRowNum);
        int colIndex = 0;
        for (Object cellValue : rowData) {
            Cell cell = row.createCell(colIndex++);
            // Same data type handling as before
            if (cellValue instanceof String) cell.setCellValue((String) cellValue);
            else if (cellValue instanceof Number) cell.setCellValue(((Number) cellValue).doubleValue());
        }
    }

    // Overwrite the existing file (or write to a temp file then swap)
    try (FileOutputStream out = new FileOutputStream(existingExcelPath)) {
        newWorkbook.write(out);
    }
    newWorkbook.dispose();
} catch (Exception e) {
    e.printStackTrace();
}

• Always call SXSSFWorkbook.dispose(): This cleans up the temporary disk files created by SXSSF—forgetting this will bloat your server’s disk over time.
• Tune the in-memory row count: The SXSSFWorkbook(int rowAccessWindowSize) constructor lets you balance memory usage and disk IO. 1000 rows is a safe default.
• Avoid loading full XLSX files into memory: Never use XSSFWorkbook directly for large files—it loads the entire XML structure into RAM, which triggers OOM.
• Prefer CSV for temporary storage: It’s faster to append to, uses less space, and is easier to debug than partial Excel files.

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