如何使用Apache POI在同一张Excel工作表中创建多个数据透视表

需求：

• 基于已有单个数据透视表的代码，在同一张工作表中动态添加第二个数据透视表
• 动态确定第二个数据透视表的起始位置（替换示例代码中的"A35"），位置需在第一个透视表结束之后

示例代码1（单个数据透视表实现） #

/* Read the input file that contains the data to pivot */
FileInputStream input_document = new FileInputStream(new File("inputFile.xlsx"));    
/* Create a POI XSSFWorkbook Object from the input file */
XSSFWorkbook my_xlsx_workbook = new XSSFWorkbook(input_document); 
/* Read Data to be Pivoted - we have only one worksheet */
XSSFSheet pivot_sheet=my_xlsx_workbook.createSheet();
/* Get the reference for Pivot Data */
AreaReference a=new AreaReference("A1:C51");
/* Find out where the Pivot Table needs to be placed */
CellReference b=new CellReference("I5");
/* Create Pivot Table */
XSSFPivotTable pivotTable = pivot_sheet.createPivotTable(a,b,sheet);
/* Add filters */
pivotTable.addReportFilter(0);
pivotTable.addRowLabel(1);
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 2); 
/* Write Pivot Table to File */
FileOutputStream output_file = new FileOutputStream(new File("POI_XLS_Pivot_Example.xlsx")); 
my_xlsx_workbook.write(output_file);
input_document.close(); 

补充场景及示例代码2 #

以下代码已生成第一个数据透视表，需在同一张工作表中第一个透视表之后生成第二个，核心问题是如何动态获取第二个表的CellReference（示例中为"A35"）：

//dynamic cell
int firstRow = dataSheet.getFirstRowNum();
int lastRow = dataSheet.getLastRowNum();
int firstCol = dataSheet.getRow(0).getFirstCellNum();
int lastCol = dataSheet.getRow(0).getLastCellNum();

CellReference topLeft = new CellReference(firstRow, firstCol);
CellReference botRight = new CellReference(lastRow, lastCol - 1);
AreaReference ar = new AreaReference(topLeft, botRight,SpreadsheetVersion.EXCEL2007);
/* Find out where the Pivot Table needs to be placed */
CellReference cr=new CellReference("A5");
// CellReference cr = new CellReference(firstRow + 4, lastCol + 1);
/* Create Pivot Table */
XSSFPivotTable pivotTable = sheet.createPivotTable(ar, cr, dataSheet);
/* Add filters - first pivot table */
pivotTable.addRowLabel(11);
pivotTable.addRowLabel(8);
pivotTable.addColLabel(15);
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 77);

/* Second Pivot table dynamic generation */
CellReference cr2=new CellReference("A35");//How to get dynamically this cellReference "A35" value based on the first pivot table generation
XSSFPivotTable pivotTable2 = sheet.createPivotTable(ar, cr2, dataSheet);
/* Add filters */
pivotTable2.addRowLabel(11);
pivotTable2.addColLabel(15);
pivotTable2.addColumnLabel(DataConsolidateFunction.SUM, 77);

解决方案 #

方法1：遍历工作表获取最后有内容的行（推荐） #

创建完第一个透视表后，直接读取工作表的最后使用行号，预留间隔后设置第二个表的起始位置，逻辑简单且兼容性好：

/* 创建完第一个透视表后，获取其结束位置 */
int lastUsedRow = sheet.getLastRowNum();
// 预留2行间隔，第二个表从lastUsedRow+3行开始，列保持为A列（索引0）
CellReference cr2 = new CellReference(lastUsedRow + 3, 0);

/* Second Pivot table dynamic generation */
XSSFPivotTable pivotTable2 = sheet.createPivotTable(ar, cr2, dataSheet);
/* Add filters */
pivotTable2.addRowLabel(11);
pivotTable2.addColLabel(15);
pivotTable2.addColumnLabel(DataConsolidateFunction.SUM, 77);

方法2：通过透视表内部XML结构获取精准区域（适合高精度需求） #

利用Apache POI底层的XML结构提取透视表的实际占用区域，需注意不同POI版本的结构差异：

// 获取第一个透视表的底层XML定义
CTPivotTableDefinition pivotDef = pivotTable.getCTPivotTableDefinition();
// 提取透视表的位置范围
CTPivotArea pivotArea = pivotDef.getLocation();
int pivotEndRow = pivotArea.getReference().getLastRow();
int pivotEndCol = pivotArea.getReference().getLastCol();

// 设置第二个表在透视表结束行+3的位置
CellReference cr2 = new CellReference(pivotEndRow + 3, 0);

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