使用apache在同一个工作表中使用多个枢轴表
使用apache在同一个工作表中使用多个枢轴表
提问于 2022-08-23 02:52:40
如何在同一张excel中使用apache创建多个(两个)枢轴表。在下面的示例中，我可以在工作表中创建单个枢轴表。如何在同一张表中动态添加其他枢轴表？
/* 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);回答 1
Stack Overflow用户
发布于 2022-08-24 18:08:54
计算单页自上而下的表表示占用多少空间(行)是很复杂的。这主要取决于行标签的数据中有多少不同的项。要得到这个，还需要使用代码进行旋转。Apache POI不这样做。它只创建枢轴表描述。那就由Excel来旋转。因此，Apache不知道数据透视表表示在表中占用了多少空间。
可以计算每个枢轴表的行数如下：
- 4行为可能的报表筛选器(
- )每一行标签每一行1行，多行标签相乘唯一项
- 1行，用于具有数据合并函数
- 1行的列标签的标题
- 1行，用于总计
H 210f 211
这方面的主要任务是获取每行标签的唯一项。为了尽可能独立，我们应该获取数据源中每个数据列的唯一项。
另外，由于独立的原因，我们可以编程地获得数据源的使用数据范围。
完整示例:在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也不会将行或列移动到已更改的透视表中。如果更改后的透视表需要morre行，则GUI只会告诉pivot表不能重叠，并且失败。
因此，更好的方法是每个枢轴表使用一个工作表。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/73452646
复制相关文章
相似问题
腾讯云开发者
