使用apachepoi在同一工作表中创建多个透视表

Question

如何在excel的同一张表中使用apache POI创建多(两(个数据透视表。在下面的例子中，我可以在工作表中创建一个数据透视表。如何在同一工作表中动态添加其他透视表？

/* 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(); 

------编辑---------在下面的代码中，生成了第一个透视表，第二个透视表也应该显示在第一个透视图之后的同一张表上。如何知道根据第一个数据透视表的结束位置放置第二个数据透视图的cellReference。在下面的代码示例中；A35"；为了显示第二个数据透视表，如何将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);

Answer 1

计算数据透视表在工作表中自上而下的行所占用的空间很复杂。它主要取决于行标签的数据中有多少不同的项。为了得到这个，需要额外使用代码进行数据透视。Apache POI并没有这样做。它只创建了透视表描述。Excel则进行数据透视。因此Apache POI不知道数据透视表表示在工作表中占用了多少空间。

可以按如下方式计算每个数据透视表的行数：

• 顶部的4行用于可能的报表筛选器
• 每个行标签每个唯一项目1行，多个行标签相乘唯一项目
• 1行用于列标签的标题
• 1行用于具有数据合并的列标签的标题函数
• 总计为1行

主要任务是获取每行标签的唯一项。为了尽可能独立，我们应该获得数据源中每个数据列的唯一项。

另外，出于独立性的原因，我们可以通过编程获得数据源的已使用数据范围。

完整示例：需要ExcelSource.xlsx的第一张工作表中的数据源，该数据源具有一个标题行、三列文本数据和两列数字数据。

示例：

Company     Country     Product     Count     Amount
Company1    Country1    Product1    1,083     84,474.00 
Company1    Country1    Product2    1,692     11,844.00 
Company1    Country1    Product3    1,431     77,274.00 
Company1    Country1    Product4      296    -25,752.00 
Company1    Country2    Product1      996    -26,892.00 
Company1    Country2    Product2    1,315    -69,695.00 
Company1    Country2    Product3      480       -480.00 
Company1    Country2    Product4      390    -17,550.00 
Company1    Country3    Product1    1,363   -118,581.00 
Company1    Country3    Product2      419     40,224.00 
Company1    Country3    Product3    1,990   -103,480.00 
Company1    Country3    Product4      307    -19,341.00 
Company2    Country1    Product1    1,824    -80,256.00 
...

代码：

import java.io.FileOutputStream;
import java.io.FileInputStream;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import java.util.Locale;
import java.util.Map;
import java.util.HashMap;
import java.util.TreeSet;
class CreatePivotTableDefault {

static int getLastFilledRow(Sheet sheet, int col) {
int lastStoredRowNum = sheet.getLastRowNum();
for (int r = lastStoredRowNum; r >= 0; r--) {
Row row = sheet.getRow(r);
if (row != null) {
Cell cell = row.getCell(col);
if (cell != null && cell.getCellType() != CellType.BLANK) return row.getRowNum();  
}       
}
return -1; // the sheet is empty in that col 
}

static int getLastFilledColumn(Sheet sheet, int rowIdx) {
int lastStoredCellNum = sheet.getRow(rowIdx).getLastCellNum();
Row row = sheet.getRow(rowIdx);
if (row != null) {
for (int c = lastStoredCellNum; c >= 0; c--) {
Cell cell = row.getCell(c);
if (cell != null && cell.getCellType() != CellType.BLANK) return cell.getColumnIndex();  
}       
}
return -1; // the sheet is empty in that row
}

static Map<Integer, String> getHeadings(Sheet sheet) {
DataFormatter dataFormatter = new DataFormatter(new Locale("en", "US"));
dataFormatter.setUseCachedValuesForFormulaCells(true);
int firstRow = sheet.getFirstRowNum();
int firstCol = sheet.getRow(firstRow).getFirstCellNum();
int lastCol = getLastFilledColumn(sheet, firstRow);   
Map<Integer, String> headings = new HashMap<Integer, String>();
Row row = sheet.getRow(firstRow);
if (row != null) {
for (int c = firstCol; c <= lastCol; c++) {
Cell cell = row.getCell(c);
headings.put(c, dataFormatter.formatCellValue(cell));      
}
}
return headings;
}

static Map<Integer, String> getDataFormats(Sheet sheet, int headerRows) {
int firstRow = sheet.getFirstRowNum();
int firstCol = sheet.getRow(firstRow).getFirstCellNum();
int lastCol = getLastFilledColumn(sheet, firstRow);  
Map<Integer, String> dataFormats = new HashMap<Integer, String>();
Row row = sheet.getRow(firstRow + headerRows);
if (row != null) {
for (int c = firstCol; c <= lastCol; c++) {
Cell cell = row.getCell(c);
if (cell != null) {
dataFormats.put(c, cell.getCellStyle().getDataFormatString());
}     
}
}
return dataFormats;
}

static Map<Integer, TreeSet<String>> getUniqueItems(Sheet sheet, int headerRows) {
DataFormatter dataFormatter = new DataFormatter(new Locale("en", "US"));
dataFormatter.setUseCachedValuesForFormulaCells(true);
int firstRow = sheet.getFirstRowNum();
int firstCol = sheet.getRow(firstRow).getFirstCellNum();
int lastCol = getLastFilledColumn(sheet, firstRow);  
int lastRow = getLastFilledRow(sheet, firstCol);
Map<Integer, TreeSet<String>> uniqueItemsMap = new HashMap<Integer, TreeSet<String>>();
for (int c = firstCol; c <= lastCol; c++) {
TreeSet<String> uniqueItems = new TreeSet<String>(String.CASE_INSENSITIVE_ORDER);    
for (int r = firstRow + headerRows; r <= lastRow; r++) {
Row row = sheet.getRow(r);
if (row != null) {
Cell cell = row.getCell(c);
uniqueItems.add(dataFormatter.formatCellValue(cell));      
}
uniqueItemsMap.put(c, uniqueItems);
}  
}
return uniqueItemsMap;
}

static AreaReference getUsedDataRange(Sheet sheet) {
int firstRow = sheet.getFirstRowNum();
int firstCol = sheet.getRow(firstRow).getFirstCellNum();
int lastCol = getLastFilledColumn(sheet, firstRow);  
int lastRow = getLastFilledRow(sheet, firstCol);
AreaReference usedDataRange = null;
try {
usedDataRange = new AreaReference( // this might fail if firstRow, firstCol, lastRow and/or lastCol are -1
new CellReference(firstRow, firstCol), 
new CellReference(lastRow, lastCol), 
SpreadsheetVersion.EXCEL2007
);
} catch (Exception ex) {
// do nothing simply return usedDataRange as null
}
return usedDataRange;     
}
public static void main(String[] args) throws Exception {
try (XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream("./ExcelSource.xlsx")); 
FileOutputStream fileout = new FileOutputStream("./ExcelResult.xlsx") ) {
XSSFSheet dataSheet = workbook.getSheetAt(0);

Map<Integer, String> headings = getHeadings(dataSheet);
Map<Integer, String> dataFormats = getDataFormats(dataSheet, 1);

Map<Integer, TreeSet<String>> uniqueItems = getUniqueItems(dataSheet, 1);

AreaReference usedDataRange = getUsedDataRange(dataSheet);
int firstCol = usedDataRange.getFirstCell().getCol();
XSSFSheet pivotSheet = workbook.createSheet("Pivot"); 

int startRow = 4; // row 0 to 3: 4 rows for report filters
int endRow = startRow;
CellReference pivotTableLocation = new CellReference(startRow, 0);
XSSFPivotTable pivotTable = pivotSheet.createPivotTable(usedDataRange, pivotTableLocation, dataSheet);
pivotTable.addReportFilter(0); // does not need a row additional
pivotTable.addRowLabel(1); endRow += uniqueItems.get(firstCol + 1).size(); // needs one row per unique item per row label
pivotTable.addRowLabel(2); endRow += uniqueItems.get(firstCol + 1).size() * uniqueItems.get(firstCol + 2).size(); // needs one row per unique item per row label
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 3, "Sum " + headings.get(firstCol + 3), dataFormats.get(firstCol + 3)); 
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 4, "Sum " + headings.get(firstCol + 4), dataFormats.get(firstCol + 4)); endRow += 1; // needs one row for the heading
endRow += 1; // one row for the totals

startRow = endRow + 1 + 4; // one row distance + 4 rows for report filters
endRow = startRow;
pivotTableLocation = new CellReference(startRow, 0);
pivotTable = pivotSheet.createPivotTable(usedDataRange, pivotTableLocation, dataSheet);
pivotTable.addRowLabel(2); endRow += uniqueItems.get(firstCol + 2).size(); // needs one row per unique item per row label
pivotTable.addRowLabel(1); endRow += uniqueItems.get(firstCol + 2).size() * uniqueItems.get(firstCol + 1).size(); // needs one row per unique item per row label
pivotTable.addColLabel(0); endRow += 1; // needs one row for the heading
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 3, "Sum " + headings.get(firstCol + 3), dataFormats.get(firstCol + 3)); 
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 4, "Sum " + headings.get(firstCol + 4), dataFormats.get(firstCol + 4)); endRow += 1; // needs one row for the heading
endRow += 1; // one row for the totals

startRow = endRow + 1 + 4; // one row distance + 4 rows for report filters
endRow = startRow;
pivotTableLocation = new CellReference(startRow, 0);
pivotTable = pivotSheet.createPivotTable(usedDataRange, pivotTableLocation, dataSheet);
pivotTable.addReportFilter(2); // does not need a row additional
pivotTable.addRowLabel(1); endRow += uniqueItems.get(firstCol + 0).size(); // needs one row per unique item per row label
pivotTable.addColLabel(0); endRow += 1; // needs one row for the heading
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 3, "Sum " + headings.get(firstCol + 3), dataFormats.get(firstCol + 3)); 
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 4, "Sum " + headings.get(firstCol + 4), dataFormats.get(firstCol + 4)); endRow += 1; // needs one row for the heading
endRow += 1; // one row for the totals

System.out.println(endRow);

workbook.write(fileout);
}
}
}

总的来说，在一个工作表中放置多个数据透视表不是一个好主意。最终用户可能希望更改Excel GUI中的数据透视表设置。但这将很快失败，因为即使GUI也不会为更改后的透视表移动行或列。如果更改后的数据透视表需要更多行，则GUI仅告诉数据透视表不能重叠并失败。

所以更好的方法是每个数据透视表使用一个工作表。