如何从java折叠数据透视表中的所有字段? [英] How to collapse all fields in pivot table from java?

问题描述

我正在 JAVA 中使用 apache poi 创建一个数据透视表，它生成一个如下所示的数据透视表，所有行默认展开
如何生成数据透视表，所有行都将像下面这样从 Java 代码折叠起来.

预先感谢您.
用于生成数据透视的代码表

 AreaReference a=new AreaReference("A1:G5667", null);CellReference b=new CellReference("A1");XSSFSheet pivot_sheet=workbook.createSheet("Pivot_table");XSSFPivotTable pivotTable = pivot_sheet.createPivotTable(a,b,spreadsheet);pivotTable.addRowLabel(6);pivotTable.addRowLabel(0);pivotTable.addRowLabel(2);pivotTable.addRowLabel(3);pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 4,"Sum");

解决方案

您提供的代码不能导致第一个显示结果，因为它没有设置不同的列标签.它只添加列 E 作为数据合并列.

但我还是会尝试回答.所以我假设列 G 应该是第一行标签.A列为第二行标签，折叠起来，C列为第三行标签.但列 D 是包含APR 2018"、MAY 2018"、JUN 2018"的列，然后应为列标签.

问题是 apache poi 在创建数据透视表时没有分析内容.因此，它只是为每个数据透视字段添加尽可能多的默认"数据透视字段项，因为行在数据范围内.它只创建一个非常基本的枢轴缓存.只要我们只使用默认值，就可以工作，因为 Excel 然后在渲染数据透视表时进行更正.但是，如果我们需要除默认值之外的其他内容，例如折叠行标签，那么这将失败.

因此，我们需要 A 列中的唯一内容，以便计算所需的数据透视项并正确创建数据透视缓存.然后我们需要将尽可能多的枢轴字段项从默认"更改为实际枢轴字段项，因为 A 列中的内容不同.这些枢轴字段项必须设置属性 x 指向枢轴缓存.并且必须正确创建枢轴缓存，使其具有 A 列中的单个唯一内容.此外，属性 sd 必须设置为 false，这表明该项目的详细信息已隐藏.

完整示例:

Excel:

代码:

import org.apache.poi.xssf.usermodel.*;导入 org.apache.poi.ss.usermodel.*;导入 org.apache.poi.ss.util.*;导入 org.apache.poi.ss.SpreadsheetVersion;导入 java.io.FileOutputStream;导入 java.io.FileInputStream;导入 java.util.List;导入 java.util.Set;导入 java.util.HashSet;类 ExcelPivotTableTest {public static void main(String[] args) 抛出异常{XSSFWorkbook 工作簿 = (XSSFWorkbook)WorkbookFactory.create(new FileInputStream("PivotExample.xlsx"));XSSFSheet dataSheet = workbook.getSheet("Data");XSSFSheet pivotSheet = workbook.createSheet("Pivot");AreaReference a = new AreaReference("A1:G" + (dataSheet.getLastRowNum() + 1), SpreadsheetVersion.EXCEL2007);CellReference b = new CellReference("A1");XSSFPivotTable pivotTable = pivotSheet.createPivotTable(a, b, dataSheet);pivotTable.addRowLabel(6);//G列作为第一行标签pivotTable.addRowLabel(0);//A列作为第二行标签 - 应折叠//我们需要A列中的唯一内容来创建数据透视缓存设置<字符串>colAValues = new HashSet();for (int r = 1; r < dataSheet.getLastRowNum() + 1; r++) {行行 = dataSheet.getRow(r);如果(行！= null){单元格单元格 = row.getCell(0);如果(单元格！= null){colAValues.add(cell.toString());}}}//现在遍历第一个枢轴字段的所有枢轴项列表项目列表 =pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(0).getItems().getItemList();int i = 0;org.openxmlformats.schemas.spreadsheetml.x2006.main.CTItem item = null;for (String value : colAValues) {//只要有不同的A列值item = itemList.get(i);item.unsetT();//取消设置类型默认"item.setX(i++);//设置xpivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(0).getSharedItems().addNewS().setV(value);//创建枢轴缓存条目item.setSd(false);//set sd false = 表示隐藏此项的详细信息}while (i < itemList.size()) {item = itemList.get(i++);item.setSd(false);}pivotTable.addRowLabel(2);//C列作为第三行标签pivotTable.addRowLabel(3);//D列作为行标签 - 应改为列标签//将D列更改为col标签pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(3).setAxis(org.openxmlformats.schemas.spreadsheetml.x2006.main.STAxis.AXIS_COL);//AXIS_COL//从RowFields中删除D列pivotTable.getCTPivotTableDefinition().getRowFields().removeField(3);pivotTable.getCTPivotTableDefinition().getRowFields().setCount(3);//为D列创建ColFieldspivotTable.getCTPivotTableDefinition().addNewColFields().addNewField().setX(3);pivotTable.getCTPivotTableDefinition().getColFields().setCount(1);pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 4, "Sum");workbook.write(new FileOutputStream("PivotExample_New.xlsx"));workbook.close();}}

此代码需要 ooxml-schemas-1.3.jar，如

I am creating a pivot table using apache poi in JAVA and it generates a pivot table like below with all rows expanded by default
How do I generate Pivote table will all rows collapsed like below from java Code.

Thank you in advance.
Code used for generating pivot table

            AreaReference a=new AreaReference("A1:G5667", null);
            CellReference b=new CellReference("A1");
            XSSFSheet pivot_sheet=workbook.createSheet("Pivot_table");
            XSSFPivotTable pivotTable = pivot_sheet.createPivotTable(a,b,spreadsheet);
            pivotTable.addRowLabel(6);
            pivotTable.addRowLabel(0);
            pivotTable.addRowLabel(2);
            pivotTable.addRowLabel(3);
            pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 4,"Sum");

解决方案

The code you are providing cannot lead to the first displayed result since it does not set different column labels. It only adds column E as data consolidating column.

But I will nevertheless try answering. So I assume that column G shall be first row label. Column A shall be second row label, which shall be collapsed, Column C shall be the third row label. But column D is the column containing the "APR 2018", "MAY 2018", "JUN 2018" and shall be column label then.

The problem is that apache poi is not analyzing the content while creating the pivot table. So it simply adds as much "default" pivot field items for each pivot field as rows are in the data range. And it only creates a very rudimentary pivot cache. That works as long as we only use defaults since Excel then does correcting this while rendering the pivot table. But if we need others than the default, collapsing row labels for example, then this fails.

So we need unique contents in column A for having a count of needed pivot items and for creating the pivot cache properly. Then we need changing as much pivot field items from "default" to real pivot field items as different contents are in column A. Those pivot field items must have attribute x set which points to the pivot cache. And the pivot cache must be created properly having the single unique contents in column A. Also the attribute sd must be set false which indicates that the details are hidden for this item.

Complete Example:

Excel:

Code:

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

import java.io.FileOutputStream;
import java.io.FileInputStream;

import java.util.List;
import java.util.Set;
import java.util.HashSet;

class ExcelPivotTableTest {

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

  XSSFWorkbook workbook = (XSSFWorkbook)WorkbookFactory.create(new FileInputStream("PivotExample.xlsx"));
  XSSFSheet dataSheet = workbook.getSheet("Data");

  XSSFSheet pivotSheet = workbook.createSheet("Pivot");

  AreaReference a = new AreaReference("A1:G" + (dataSheet.getLastRowNum() + 1), SpreadsheetVersion.EXCEL2007);
  CellReference b = new CellReference("A1");

  XSSFPivotTable pivotTable = pivotSheet.createPivotTable(a, b, dataSheet);

  pivotTable.addRowLabel(6); //column G as first row label

  pivotTable.addRowLabel(0); //column A as second row label - shall be collapsed

  //we need unique contents in column A for creating the pivot cache
  Set<String> colAValues = new HashSet<String>();
  for (int r = 1; r < dataSheet.getLastRowNum() + 1; r++) {
   Row row = dataSheet.getRow(r);
   if (row != null) {
    Cell cell = row.getCell(0);
    if (cell != null) {
     colAValues.add(cell.toString());
    }
   }
  }

  //now go through all pivot items of first pivot field 
  List<org.openxmlformats.schemas.spreadsheetml.x2006.main.CTItem> itemList = 
   pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(0).getItems().getItemList();
  int i = 0; 
  org.openxmlformats.schemas.spreadsheetml.x2006.main.CTItem item = null;
  for (String value : colAValues) { //as long as there are different column A values
   item = itemList.get(i);
   item.unsetT(); //unset the type "default"
   item.setX(i++); //set x
   pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields()
    .getCacheFieldArray(0).getSharedItems().addNewS().setV(value); //create pivot cache entry
   item.setSd(false); //set sd false = indicates that the details are hidden for this item
  }
  while (i < itemList.size()) {
   item = itemList.get(i++);
   item.setSd(false);
  }


  pivotTable.addRowLabel(2); //column C as third row label

  pivotTable.addRowLabel(3); //column D as row label - shall be column label instead
  //do changing column D to a col label
  pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(3)
   .setAxis(org.openxmlformats.schemas.spreadsheetml.x2006.main.STAxis.AXIS_COL); //AXIS_COL
  //remove column D from RowFields
  pivotTable.getCTPivotTableDefinition().getRowFields().removeField(3); 
  pivotTable.getCTPivotTableDefinition().getRowFields().setCount(3);
  //create ColFields for column D
  pivotTable.getCTPivotTableDefinition().addNewColFields().addNewField().setX(3); 
  pivotTable.getCTPivotTableDefinition().getColFields().setCount(1);

  pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 4, "Sum");

  workbook.write(new FileOutputStream("PivotExample_New.xlsx"));
  workbook.close();

 }
}

This code needs ooxml-schemas-1.3.jar as mentioned in apache poi FAQ.

Result:

这篇关于如何从java折叠数据透视表中的所有字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！