如何使用POI处理百万级数据的Excel报表？

本文目录导读：

1. 核心问题：内存溢出
2. 场景一：写入百万级数据（使用 SXSSFWorkbook）
3. 场景二：读取百万级数据（使用 XSSFReader + SAX 解析）
4. 场景三：读取百万级 .xls 文件（HSSF + RecordFactory）
5. 通用优化策略（写入和读取）
6. 极端情况：真的需要读取/写入全量的百万行吗？

处理百万级数据的Excel报表,核心原则是：避免将全部数据加载到内存中，POI提供了两种模式：用户模式（UserModel，一次加载全部数据）和事件模式（EventModel，流式读取），对于百万级数据，必须使用SXSSF（ Streaming Usermodel API，用于写） 和XSSF with SAX (Event API，用于读)。

以下是详细的处理策略和代码示例。

核心问题：内存溢出

• HSSFWorkbook (Excel 97-2003 .xls)：最大行数65536，百万级数据无法存储。
• XSSFWorkbook (Excel 2007+ .xlsx)：虽然支持百万行，但会将整个XML数据结构加载到内存，轻松消耗数GB内存，极易触发OutOfMemoryError。
• SXSSFWorkbook：POI 3.8+ 提供，基于“滑动窗口”，它只保留窗口内的Row对象在内存中，超过窗口大小的行会被写入磁盘（临时文件），这是写入百万级数据的正确方式。
• XSSFReader + SAX：读取百万级数据的正确方式，不构建DOM树，而是边解析边处理。

写入百万级数据（使用 SXSSFWorkbook）

这是生成报表、导出数据的标准做法。

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import java.io.FileOutputStream;
import java.util.Random;
public class LargeExcelWriter {
    // 控制内存中保留的行数，防止过度刷盘影响性能
    private static final int WINDOW_SIZE = 100;
    public static void main(String[] args) throws Exception {
        long startTime = System.currentTimeMillis();
        // 1. 创建SXSSFWorkbook，指定窗口大小
        //    可以开启压缩临时文件（磁盘IO换内存）
        SXSSFWorkbook wb = new SXSSFWorkbook(WINDOW_SIZE);
        wb.setCompressTempFiles(true); // 有助于降低磁盘占用
        Sheet sheet = wb.createSheet("百万数据");
        // 2. 写入表头（可复用样式）
        Row headerRow = sheet.createRow(0);
        String[] headers = {"ID", "姓名", "数值列", "日期"};
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
        }
        // 3. 批量写入数据（模拟100万行）
        int totalRows = 1_000_000;
        Random random = new Random();
        for (int i = 1; i <= totalRows; i++) {
            Row row = sheet.createRow(i);
            row.createCell(0).setCellValue(i);
            row.createCell(1).setCellValue("用户_" + i);
            row.createCell(2).setCellValue(random.nextDouble() * 1000);
            // 性能优化：每10万行刷一次数据到磁盘（并清理JVM内存）
            if (i % 100_000 == 0) {
                System.out.println("已写入 " + i + " 行...");
                // 强制将内存中的行写入磁盘，但不会清除工作簿引用
                // 通常不需要手动调用，SXSSF会自动处理
            }
        }
        // 4. 写入到文件（务必使用 FileOutputStream，不要用 ByteArrayOutputStream 避免内存爆炸）
        try (FileOutputStream fos = new FileOutputStream("million_rows.xlsx")) {
            wb.write(fos);
        }
        System.out.println("写入完成！耗时：" + (System.currentTimeMillis() - startTime) + "ms");
        // 5. 清理临时文件
        wb.dispose();
    }
}

关键优化点

• setCompressTempFiles(true)：临时文件压缩，减少磁盘IO，但会略微增加CPU开销，通常推荐开启。
• 不要使用过多的样式：SXSSF每创建一个CellStyle都会占用内存，如果样式很多，考虑使用缓存池。
• 批量提交：每写一批行（如10万行）后，可以调用((SXSSFSheet)sheet).flushRows(0)强制刷盘，避免内存波动。

读取百万级数据（使用 XSSFReader + SAX 解析）

绝对不能使用XSSFWorkbook或WorkbookFactory创建对象。 必须使用SAX（Simple API for XML） 方式逐行读取xlsx文件，xlsx本质是一个ZIP包，里面的xml文件逐行包含数据。

完整示例代码

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.xml.sax.InputSource;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;
import java.io.InputStream;
public class LargeExcelReader {
    public static void main(String[] args) throws Exception {
        long startTime = System.currentTimeMillis();
        // 1. 打开 ZIP 包（OPCPackage）
        //    注意：路径要大文件，不要使用 new FileInputStream() 包装
        try (OPCPackage pkg = OPCPackage.open("million_rows.xlsx")) {
            XSSFReader r = new XSSFReader(pkg);
            SharedStringsTable sst = r.getSharedStringsTable();
            // 2. 获取第一个 Sheet 的输入流
            InputStream sheetStream = r.getSheetsData().next();
            // 3. 使用 SAX 解析
            XMLReader parser = XMLReaderFactory.createXMLReader();
            // 4. 核心：设置 Sheet 内容的处理器
            XSSFSheetXMLHandler handler = new XSSFSheetXMLHandler(
                    r.getStylesTable(),
                    null,  // 不需要读取注释
                    sst,
                    new SimpleSheetContentsHandler(), // 自定义处理器
                    false, // 是否格式化数字，false可以提高性能
                    false  // 是否输出空白单元格
            );
            parser.setContentHandler(handler);
            parser.parse(new InputSource(sheetStream));
        }
        System.out.println("读取完成！耗时：" + (System.currentTimeMillis() - startTime) + "ms");
    }
    // 自定义处理器：每个单元格的数据都会回调这里
    private static class SimpleSheetContentsHandler implements XSSFSheetXMLHandler.SheetContentsHandler {
        private int currentRow = 0;
        private int currentCol = -1;
        private StringBuilder sb = new StringBuilder();
        @Override
        public void startRow(int rowNum) {
            currentRow = rowNum;
            currentCol = -1;
            // 每开始一行，打印行号，可以做批量处理、数据库插入等
            // (这里仅为演示，实际场景应写入数据库或文件)
        }
        @Override
        public void endRow(int rowNum) {
            // 一行结束，通常提交一行的数据
            // 百万级数据，建议每N行批量写入数据库或文件，不要拼接字符串
            sb.append("Row: ").append(rowNum).append("\n");
            if (rowNum % 100000 == 0) {
                System.out.println("已处理行: " + rowNum);
            }
        }
        @Override
        public void cell(String cellReference, String formattedValue, XSSFComment comment) {
            // formattedValue 已经是字符串格式（基于Excel的格式）
            currentCol++;
            sb.append("  Column: ")
              .append(cellReference)
              .append(" -> ")
              .append(formattedValue)
              .append("\n");
        }
        @Override
        public void headerFooter(String text, boolean isHeader, String tagName) {
            // 忽略页眉页脚
        }
    }
}

读取注意事项

• OPCPackage.open(File)：直接打开文件，不要先读取到InputStream再传入，否则文件会自动解压到内存导致内存爆炸。
• SharedStringsTable：共享字符串表（xlsx里所有文本去重后存于此），解析完成后及时置为null帮助GC。
• 批量提交：在endRow或每读N行后，将数据写入数据库或中间文件，不要用List<Row>收集全部数据。
• 数字格式化：如果不需要精确的数字格式，设置true false来禁用格式化提升性能。

读取百万级 .xls 文件（HSSF + RecordFactory）

如果文件是旧版.xls（使用HSSF），可以使用EventUserModel。

import org.apache.poi.hssf.eventusermodel.*;
import org.apache.poi.hssf.record.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
public class LargeXlsReader {
    public static void main(String[] args) throws Exception {
        try (POIFSFileSystem fs = new POIFSFileSystem(new java.io.File("large.xls"));
             InputStream is = fs.createDocumentInputStream("Workbook")) {
            // 创建事件监听器
            HSSFRequest req = new HSSFRequest();
            req.addListenerForAllRecords(new HSSFRecordListener() {
                @Override
                public void processRecord(Record record) {
                    // 处理记录，只关注 RowRecord 和 CellRecord
                    if (record instanceof RowRecord) {
                        RowRecord row = (RowRecord) record;
                        // 处理行
                    } else if (record instanceof NumberRecord) {
                        NumberRecord cell = (NumberRecord) record;
                        // 处理数字单元格
                    } else if (record instanceof LabelRecord || record instanceof LabelSSTRecord) {
                        // 处理字符串单元格
                    }
                    // 注意：SSTRecord需要用HSSFWorkbook读取，但我们可以延迟加载
                }
            });
            // 解析
            new HSSFEventFactory().processWorkbookEvents(req, is);
        }
    }
}

通用优化策略（写入和读取）

极端情况：真的需要读取/写入全量的百万行吗？

• 分页导出：用户很少一次导出全部百万行，应当使用游标或分页，一次提取部分行。
• 直接生成CSV：如果对方系统也支持CSV，生成CSV可以绕开POI，直接使用BufferedWriter写文件，速度极快。
• 使用更高级的库：如 EasyExcel（阿里巴巴），底层也是SAX解析，但封装更友好、内存优化更好，对于读百万级数据，建议使用EasyExcel替代原始POI Event API。

• 写入：首选SXSSF（POI），配合窗口大小和压缩临时文件。
• 读取：避免XSSFWorkbook，必须使用XSSFReader + SAX（或直接使用EasyExcel）。
• 终极方案：如果是内部系统，考虑使用CSV或数据库直连导出，避免Excel处理瓶颈。