如何使用apache poi在excel数据透视表中的行标签而不是列标签处生成总和、平均值等聚合? [英] How to generate aggregations like sum, average at row labels instead of column labels in excel pivot table using apache poi?

问题描述

我正在尝试使用 apache-poi 库生成一个 excel 数据透视表.

I am trying to produce an excel pivot table using apache-poi library.

基础数据

我想要什么

当我尝试添加多个聚合时，excel 将其分组为 values(您可以在行标签中看到第二张图片右侧的 values 属性)并放置 values> 在列中.我想在默认情况下生成一个带有行标签值的 excel.

When i try to add multiple aggregations, excel was grouping it into values(you can see the values attribute in right side of 2nd image in row labels) and placing values in columns. I want to generate an excel with values in the row labels by default.

但是，当我添加多个聚合(如 sum 和 average)(如下图所示)时，值 显示在列标签中.值可以拖到excel中的行标签，但默认情况下我需要行中的值.

But when i add multiple aggregations like sum and average(as shown in the below image), the values are shown in column labels. values can be dragged to row labels in excel but i need the values in rows by default.

但我可以在java中生成这个

这些聚合的代码

XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet sheet = wb.createSheet();

            //Create some data to build the pivot table on
            setCellData(sheet);
            XSSFPivotTable pivotTable = null;
            try {
                pivotTable = sheet.createPivotTable(new AreaReference("A1:I8", SpreadsheetVersion.EXCEL2007), new CellReference("M10"));
            } catch (Exception ex) {
                System.out.println("In sheet: " + ex);
            }
            //Configure the pivot table
            //Use first column as row label
            pivotTable.addRowLabel(0);
            pivotTable.addRowLabel(1);

            pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(2).setAxis(STAxis.AXIS_COL);
            pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(2).addNewItems();
            pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(2).getItems().addNewItem()
                    .setT(STItemType.DEFAULT);
            pivotTable.getCTPivotTableDefinition().addNewColFields().addNewField().setX(1);
           //this.addCalculatedColumnToPivotTable(pivotTable, "field1", "average of count ab", "'count'");
            //Sum up the second column
            pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 3);
            //Set the third column as filter
            pivotTable.addColumnLabel(DataConsolidateFunction.AVERAGE, 3);

但是数据透视表中没有函数可以将这些聚合添加到 apache-poi 库中的行标签中.

But there is no function in pivot table to add these aggregations in row labels in apache-poi library.

推荐答案

使用最新的apache poi 版本 4.1.0 不再需要使用low level用于添加列标签的底层 bean，因为有 XSSFPivotTable.addColLabel 现在.

Using the latest apache poi version 4.1.0 it is no more necessary to use the low level underlying beans for adding a column label since there is XSSFPivotTable.addColLabel now.

但是直到现在还没有任何addRowLabel(DataConsolidateFunction function, int columnIndex).addColumnLabel(DataConsolidateFunction function, int columnIndex) 将列和数据字段上的数据添加为列字段.因此，如果需要将行上的数据和数据字段作为行字段，我们需要更改它.

But there is not any addRowLabel(DataConsolidateFunction function, int columnIndex) until now. The addColumnLabel(DataConsolidateFunction function, int columnIndex) adds data on columns and data fields as col fields. So we need changing that if the need is having data on rows and data fields as row fields.

完整示例:

import java.io.FileOutputStream;

import org.apache.poi.ss.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.xssf.usermodel.*;

import java.util.GregorianCalendar;

class CreatePivotTable {

 public static void main(String[] args) throws Exception {

  try (Workbook workbook = new XSSFWorkbook(); 
       FileOutputStream fileout = new FileOutputStream("Excel.xlsx") ) {

   DataFormat format = workbook.createDataFormat();
   CellStyle dateStyle = workbook.createCellStyle();
   dateStyle.setDataFormat(format.getFormat("M\\/d\\/yy"));

   Sheet sheet = workbook.createSheet();

   String[] headers = new String[]{"Column1", "Column2", "Date", "Count"};
   Row row = sheet.createRow(0);
   Cell cell;
   for (int c = 0; c < headers.length; c++) {
    cell = row.createCell(c); cell.setCellValue(headers[c]);
   }

   Object[][] data = new Object[][]{
    new Object[]{"A", "B", new GregorianCalendar(2019, 0, 1), 2d},
    new Object[]{"A", "B", new GregorianCalendar(2019, 0, 1), 4d},
    new Object[]{"A", "B", new GregorianCalendar(2019, 0, 2), 1d},
    new Object[]{"A", "B", new GregorianCalendar(2019, 0, 2), 7d},
    new Object[]{"A", "C", new GregorianCalendar(2019, 0, 1), 5d},
    new Object[]{"A", "C", new GregorianCalendar(2019, 0, 1), 5d},
    new Object[]{"A", "C", new GregorianCalendar(2019, 0, 2), 2d},
    new Object[]{"A", "C", new GregorianCalendar(2019, 0, 2), 8d}
   };
   for (int r = 0; r < data.length; r++) {
    row = sheet.createRow(r+1);
    Object[] rowData = data[r];
    for (int c = 0; c < rowData.length; c++) {
     cell = row.createCell(c);
     if (rowData[c] instanceof String) {
      cell.setCellValue((String)rowData[c]);
     } else if (rowData[c] instanceof GregorianCalendar) {
      cell.setCellValue((GregorianCalendar)rowData[c]);
      cell.setCellStyle(dateStyle);
     } else if (rowData[c] instanceof Double) {
      cell.setCellValue((Double)rowData[c]);
     }
    }
   }

   XSSFPivotTable pivotTable = ((XSSFSheet)sheet).createPivotTable(new AreaReference("A1:D9", SpreadsheetVersion.EXCEL2007), new CellReference("M10"));

   pivotTable.addRowLabel(0);
   pivotTable.addRowLabel(1);

   pivotTable.addColLabel(2);

   // the default sets data on columns and data fields as col fields
   pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 3);
   pivotTable.addColumnLabel(DataConsolidateFunction.AVERAGE, 3);

   // now changing from the default
   // set dataOnRows
   pivotTable.getCTPivotTableDefinition().setDataOnRows(true);

   // add a new row field for data fields 
   pivotTable.getCTPivotTableDefinition().getRowFields().addNewField().setX(-2);
   pivotTable.getCTPivotTableDefinition().getRowFields().setCount(3);

   // remove data fields from col fields
   pivotTable.getCTPivotTableDefinition().getColFields().removeField(1);
   pivotTable.getCTPivotTableDefinition().getColFields().setCount(1);

   workbook.write(fileout);

  }

 }
}

rowFields 或 colFields 中 field 元素中的 x 属性通常指定pivotField 项目值.但是 dataFields 可能与 pivotField 没有直接关系.

The x attribute in a field element in either rowFields or colFields normally specifies the index to a pivotField item value. But dataFields might not be directly related to pivotFields.

如果只有一个 dataField，那么只有一种可能性可以显示.所以不需要 field 元素.

If there is only one dataField then there is only one possibility for showing that. So no field element is needed for that.

但是如果有多个dataField，那么在数据透视表的GUI 视图中会有一个名为Values 的附加字段.在数据透视表 XML 中，附加字段使用 -2 编制索引.

But if there are multiple dataFields, then there is a additional field named Values in pivot table's GUI view. In the pivot tables XML that additional field is indexed using -2.

所以pivotTableDefinition中的dataOnRows决定了dataFields是否显示在行上(dataOnRows = "true")或在列上(默认).以及 rowFields 或 colFields 中 field 元素中的 x 属性 -2指定附加 Values 字段在字段列表中的排列位置.

So dataOnRows in pivotTableDefinition determines whether dataFields are shown on rows (dataOnRows = "true") or on columns (default). And the x attribute -2 in a field element in either rowFields or colFields specifies where the additional Values field is arranged in the list of fields.

这篇关于如何使用apache poi在excel数据透视表中的行标签而不是列标签处生成总和、平均值等聚合?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！