内存有限时如何用Apache POI向大数据Excel指定行列写入数据

直接使用SXSSFWorkbook(new XSSFWorkbook("/my.xlsx"))会将整个Excel文件加载到内存，依然会导致内存溢出。正确的做法是流式读取原有文件内容，同时逐行复制到新的SXSSFWorkbook中，最后在目标Sheet追加数据，全程不加载整个文件到内存。

核心思路 #

利用Apache POI的XSSFReader（SAX流式读取）解析原有Excel的XML结构，配合SXSSFWorkbook（流式写入）创建新文件，仅在内存中保留当前处理的行数据，最后在指定Sheet中追加新内容。

代码实现 #

1. 导入依赖 #

确保项目引入Apache POI相关依赖：poi、poi-ooxml、poi-ooxml-schemas。

2. 实现SAX处理器解析行数据 #

该处理器负责流式读取Excel的行和单元格数据，复制到SXSSF的Sheet中：

class SheetHandler extends DefaultHandler {
    private SharedStringsTable sst;
    private String lastContents;
    private boolean nextIsString;
    private Row currentRow;
    private Cell currentCell;
    private Sheet targetSheet;
    private int targetSheetIndex;
    private int currentSheetIndex;
    private boolean isTargetSheet;

    public SheetHandler(SharedStringsTable sst, Sheet targetSheet, int targetSheetIndex) {
        this.sst = sst;
        this.targetSheet = targetSheet;
        this.targetSheetIndex = targetSheetIndex;
    }

    @Override
    public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {
        switch (qName) {
            case "row":
                long rowNum = Long.parseLong(attributes.getValue("r"));
                currentRow = targetSheet.createRow((int) rowNum - 1);
                break;
            case "c":
                String cellType = attributes.getValue("t");
                nextIsString = cellType != null && cellType.equals("s");
                String cellRef = attributes.getValue("r");
                int colNum = CellReference.convertColStringToIndex(cellRef.replaceAll("[0-9]", ""));
                currentCell = currentRow.createCell(colNum);
                break;
        }
        lastContents = "";
    }

    @Override
    public void characters(char[] ch, int start, int length) throws SAXException {
        lastContents += new String(ch, start, length);
    }

    @Override
    public void endElement(String uri, String localName, String qName) throws SAXException {
        if (nextIsString) {
            int idx = Integer.parseInt(lastContents);
            lastContents = sst.getItemAt(idx).getString();
            nextIsString = false;
        }
        if ("v".equals(qName)) {
            currentCell.setCellValue(lastContents);
        }
    }

    public void setCurrentSheetIndex(int index) {
        this.currentSheetIndex = index;
        this.isTargetSheet = currentSheetIndex == targetSheetIndex;
    }

    public boolean isTargetSheet() {
        return isTargetSheet;
    }
}

3. 主逻辑：复制原数据+追加新内容 #

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.xml.sax.InputSource;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;

import java.io.*;
import java.util.Iterator;

public class ExcelAppendTool {
    public static void main(String[] args) throws Exception {
        String inputFile = "/my.xlsx";
        String outputFile = "/my_updated.xlsx";
        int targetSheetIdx = 1; // 对应原代码的getSheetAt(1)，即第2个Sheet

        // 初始化SXSSFWorkbook，设置内存缓存行数，超出则写入临时文件
        SXSSFWorkbook sxssfWb = new SXSSFWorkbook(100);
        sxssfWb.setCompressTempFiles(true);

        try (OPCPackage pkg = OPCPackage.open(new File(inputFile))) {
            XSSFReader reader = new XSSFReader(pkg);
            SharedStringsTable sst = reader.getSharedStringsTable();
            XMLReader xmlReader = XMLReaderFactory.createXMLReader();
            Iterator<InputStream> sheetStreams = reader.getSheetsData();
            int currentSheetIdx = 0;

            while (sheetStreams.hasNext()) {
                try (InputStream sheetStream = sheetStreams.next()) {
                    Sheet sxssfSheet = sxssfWb.createSheet();
                    SheetHandler handler = new SheetHandler(sst, sxssfSheet, targetSheetIdx);
                    handler.setCurrentSheetIndex(currentSheetIdx);
                    xmlReader.setContentHandler(handler);
                    xmlReader.parse(new InputSource(sheetStream));

                    // 在目标Sheet追加新数据
                    if (currentSheetIdx == targetSheetIdx) {
                        int lastRow = sxssfSheet.getLastRowNum();
                        Row newRow = sxssfSheet.createRow(lastRow + 1);
                        // 示例：添加自定义数据，可根据需求修改
                        newRow.createCell(0).setCellValue("新增数据列1");
                        newRow.createCell(1).setCellValue("新增数据列2");
                        newRow.createCell(2).setCellValue(System.currentTimeMillis());
                    }
                    currentSheetIdx++;
                }
            }

            // 写入最终文件
            try (FileOutputStream fos = new FileOutputStream(outputFile)) {
                sxssfWb.write(fos);
            }
        } finally {
            // 清理SXSSF生成的临时文件
            sxssfWb.dispose();
        }
    }
}

关键说明 #

• 内存控制：SXSSFWorkbook构造参数100表示内存中最多保留100行数据，超出部分自动写入磁盘临时文件，可根据可用内存调整该数值。
• 样式与公式：上述代码仅处理文本类型单元格，若需保留样式、公式或其他类型数据，需在SheetHandler中补充对应解析逻辑。
• 文件处理：方案生成新文件而非修改原文件，处理完成后可自行备份原文件并替换为新文件。
• 性能优化：开启setCompressTempFiles(true)可压缩临时文件，减少磁盘占用。

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