如何使用Apache POI创建跨工作簿数据源的数据透视表

核心矛盾 #

使用Apache POI创建跨独立工作簿的透视表时，AreaReference类仅支持传入工作表名称字符串，不支持直接传入工作簿对象参数，直接调用默认的createPivotTable方法无法绑定外部数据源。

现有示例代码：

/** Data source sheet */
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet dataSourceSheet = wb.createSheet("dataSourceSheet");

CellReference position = new CellReference(0, 0, true, true);
AreaReference source = new AreaReference(
        new CellReference("dataSourceSheet", 0, 0, true, true),
        new CellReference("dataSourceSheet", 100, 100, true, true),
        null
);

/** Pivot */
XSSFWorkbook wbPivot = new XSSFWorkbook();
XSSFSheet pivotSheet = wbPivot.createSheet("myPivot");
// 此处无法正常关联独立工作簿的数据源
XSSFPivotTable pivotTable = pivotSheet.createPivotTable(source, position);

实现说明 #

• 问题根因：POI默认的透视表创建逻辑，会默认将AreaReference定义的单元格范围匹配到透视表当前所属的Workbook实例，不会识别范围字符串中引用的其他独立工作簿
• 跨工作簿数据源绑定的实现路径：
  • 先将独立的数据源工作簿保存为可访问的物理文件，获取外部引用路径
  • 调用createPivotTable方法生成透视表基础结构，拿到对应缓存定义对象
  • 修改缓存定义的数据源属性，将原本指向当前工作簿的数据源地址替换为外部独立工作簿的实际路径
  • 为缓存定义添加外部工作簿的关系映射，保证生成的Excel文件能正确识别跨工作簿的数据源关联

注意：打开生成的透视表文件时，需保证配置的外部数据源路径为当前设备可正常访问的有效路径，否则无法加载数据源完成展示与刷新。

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