求助：Java读取超30MB大XLSX文件并完整复制内容的技术问题

核心思路 #

用POI的SAX解析器逐行读取源Excel，同时搭配SXSSFWorkbook（流式写入）生成新文件，既避免内存溢出，又能完整保留空单元格。

关键实现步骤 #

• 解析单元格位置（如A1、B2）计算行号和列号，通过位置差补全空单元格
• 维护当前行状态，行切换时补全上一行的尾部空单元格
• 区分字符串（SST索引）、数字等单元格类型，正确提取值；空单元格直接写入空字符串

完整代码示例 #

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

import java.io.FileOutputStream;
import java.io.InputStream;

public class LargeExcelCopy {
    private SharedStringsTable sst;
    private SXSSFWorkbook targetWorkbook;
    private Sheet targetSheet;
    private Row currentTargetRow;
    private int currentRowNum = -1;
    private int lastColNum = -1;
    private String lastContents;
    private boolean isNextString;

    public void copy(String sourcePath, String targetPath) throws Exception {
        // 初始化流式写入工作簿，仅保留100行在内存，其余刷入磁盘
        targetWorkbook = new SXSSFWorkbook(100);
        targetSheet = targetWorkbook.createSheet("复制结果");

        OPCPackage pkg = OPCPackage.open(sourcePath);
        XSSFReader reader = new XSSFReader(pkg);
        sst = reader.getSharedStringsTable();

        XMLReader parser = XMLReaderFactory.createXMLReader();
        parser.setContentHandler(new ExcelHandler());

        // 读取第一个工作表（多表需遍历reader.getSheetsData()）
        InputStream sheetStream = reader.getSheetsData().next();
        parser.parse(new InputSource(sheetStream));
        sheetStream.close();

        // 写入并清理资源
        try (FileOutputStream out = new FileOutputStream(targetPath)) {
            targetWorkbook.write(out);
        } finally {
            targetWorkbook.dispose();
            pkg.close();
        }
    }

    private class ExcelHandler extends DefaultHandler {
        @Override
        public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
            lastContents = "";
            if ("c".equals(name)) {
                CellReference cr = new CellReference(attributes.getValue("r"));
                int rowNum = cr.getRow();
                int colNum = cr.getCol();

                // 行切换时补全上一行空单元格，创建新行
                if (rowNum != currentRowNum) {
                    if (currentRowNum != -1) {
                        fillRowEmptyCells(currentRowNum, lastColNum);
                    }
                    currentRowNum = rowNum;
                    currentTargetRow = targetSheet.createRow(currentRowNum);
                    lastColNum = -1;
                }

                // 补全当前行中间的空单元格
                if (colNum > lastColNum + 1) {
                    for (int i = lastColNum + 1; i < colNum; i++) {
                        currentTargetRow.createCell(i).setCellValue("");
                    }
                }
                lastColNum = colNum;

                // 判断是否为SST字符串类型
                isNextString = "s".equals(attributes.getValue("t"));
            }
        }

        @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 name) throws SAXException {
            if ("v".equals(name)) {
                String value = lastContents;
                if (isNextString) {
                    try {
                        int idx = Integer.parseInt(value);
                        value = sst.getItemAt(idx).getString();
                    } catch (NumberFormatException e) {
                        value = "";
                    }
                }
                currentTargetRow.createCell(lastColNum).setCellValue(value);
            }
        }

        // 补全行尾空单元格（可根据源表最大列数调整）
        private void fillRowEmptyCells(int rowNum, int lastCol) {
            // 示例：若已知源表最大列数为MAX_COL，取消注释以下代码
            // int MAX_COL = 20;
            // for (int i = lastCol + 1; i < MAX_COL; i++) {
            //     targetSheet.getRow(rowNum).createCell(i).setCellValue("");
            // }
        }
    }

    public static void main(String[] args) throws Exception {
        new LargeExcelCopy().copy("source.xlsx", "target.xlsx");
    }
}

转用C#的话，EPPlus的流式API更简洁，自动处理空单元格，内存占用极低。

代码示例 #

using OfficeOpenXml;
using System.IO;

class Program
{
    static void Main()
    {
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

        string sourcePath = "source.xlsx";
        string targetPath = "target.xlsx";

        using (var sourcePackage = new ExcelPackage(new FileInfo(sourcePath)))
        using (var targetPackage = new ExcelPackage(new FileInfo(targetPath)))
        {
            var sourceSheet = sourcePackage.Workbook.Worksheets[0];
            var targetSheet = targetPackage.Workbook.Worksheets.Add("复制结果");

            // 流式读取并复制整表，自动保留空单元格
            var sourceRange = sourceSheet.Cells[1, 1, sourceSheet.Dimension.End.Row, sourceSheet.Dimension.End.Column];
            targetSheet.Cells["1:" + sourceSheet.Dimension.End.Row].LoadFromRange(sourceRange, true);

            targetPackage.Save();
        }
    }
}

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