Apache POI：能否以SXSSFSheet为数据源创建数据透视表？

问题根源 #

你遇到的报错本质是：POI的数据透视表（XSSFPivotTable）只能基于XSSFSheet创建，而SXSSFSheet是流式写入的实现——超过内存阈值的行会被写入临时文件，不会保存在底层XSSFWorkbook的内存结构中。当你通过workbook.getXSSFWorkbook()获取的XSSFWorkbook里，对应的数据源sheet仅包含内存中保留的少量行，并不是完整的70-80万行数据，所以创建透视表时会因为数据源区域无效/不完整而报错。

可行解决方案 #

方案1：调整SXSSF内存保留行数（仅适用于内存充足场景）

如果服务器内存足够，可以设置SXSSFWorkbook保留所有行在内存中，让底层XSSFWorkbook拿到完整的数据源：

// 设置保留所有行在内存（参数-1表示不限制，所有行都存在内存）
SXSSFWorkbook workbook = new SXSSFWorkbook(-1);
SXSSFSheet dataSheet = workbook.createSheet("Data");

// 写入所有70-80万行数据...

// 此时getXSSFWorkbook()能拿到完整的数据源sheet
XSSFWorkbook xssfWorkbook = workbook.getXSSFWorkbook();
XSSFSheet pivotSheet = xssfWorkbook.createSheet("Pivot sheet");

// 构造数据源区域（注意引用XSSFSheet的单元格）
XSSFSheet xssfDataSheet = xssfWorkbook.getSheet("Data");
AreaReference ar = new AreaReference(
    new CellReference(xssfDataSheet.getRow(0).getCell(0)),
    new CellReference(xssfDataSheet.getLastRowNum(), xssfDataSheet.getRow(0).getLastCellNum()-1),
    SpreadsheetVersion.EXCEL2007
);
CellReference cr = new CellReference(pivotSheet.getRow(0).getCell(0));

// 创建透视表
XSSFPivotTable pivotTable = pivotSheet.createPivotTable(ar, cr);

⚠️ 注意：70-80万行数据会占用大量内存，容易触发OOM，仅适合内存充足的场景。

方案2：先写入数据源到磁盘，再创建透视表（推荐）

利用SXSSF流式写大文件的优势，先把完整数据源写入磁盘，再用XSSFWorkbook打开文件创建透视表：

// 第一步：用SXSSF生成并写入数据源到临时文件
SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(1000); // 保留1000行在内存，其余写临时文件
SXSSFSheet dataSheet = sxssfWorkbook.createSheet("Data");

// 写入所有70-80万行数据...

// 写入到磁盘文件
File dataFile = new File("data_source.xlsx");
try (FileOutputStream fos = new FileOutputStream(dataFile)) {
    sxssfWorkbook.write(fos);
} finally {
    sxssfWorkbook.dispose(); // 清理临时文件，避免磁盘泄漏
}

// 第二步：用XSSFWorkbook打开数据源文件，创建透视表
OPCPackage pkg = OPCPackage.open(dataFile.getAbsolutePath(), PackageAccess.READ_WRITE);
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(pkg);

// 创建透视表工作表
XSSFSheet pivotSheet = xssfWorkbook.createSheet("Pivot sheet");

// 构造数据源区域
XSSFSheet xssfDataSheet = xssfWorkbook.getSheet("Data");
AreaReference ar = new AreaReference(
    new CellReference(xssfDataSheet.getRow(0).getCell(0)),
    new CellReference(xssfDataSheet.getLastRowNum(), xssfDataSheet.getRow(0).getLastCellNum()-1),
    SpreadsheetVersion.EXCEL2007
);
CellReference cr = new CellReference(0, 0); // 透视表起始位置A1

// 创建透视表
XSSFPivotTable pivotTable = pivotSheet.createPivotTable(ar, cr, xssfDataSheet);

// 示例：添加透视表字段
pivotTable.addRowLabel(0); // 第一列作为行标签
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 1); // 第二列作为求和值

// 写入最终文件
try (FileOutputStream fos = new FileOutputStream("final_with_pivot.xlsx")) {
    xssfWorkbook.write(fos);
} finally {
    xssfWorkbook.close();
    pkg.close();
}

这个方法的优势：

• 用SXSSF避免写数据时OOM
• 打开文件时POI按需读取数据，降低内存占用
• 透视表基于完整数据源创建，不会出现数据缺失或报错

关键注意点 #

• 使用SXSSFWorkbook后必须调用dispose()，清理临时文件
• 创建透视表时，AreaReference必须指向XSSFSheet的有效区域，不能直接用SXSSFSheet的行号/列号（SXSSFSheet的getLastRowNum()可能不准确）
• 超大规模数据优先选择方案2，兼顾流式写入和透视表功能

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