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

问题描述

我正在使用JAVA中的apache poi创建数据透视表，它会生成如下所示的数据透视表，默认情况下所有行均已扩展
如何生成数据透视表，所有行都将从Java代码中折叠成下面的样子.

谢谢.
用于生成数据透视表的代码表格

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");

推荐答案

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

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.

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

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.

问题是apache poi在创建数据透视表时未分析内容.因此，它只需为每个枢纽字段添加与行在数据范围内一样多的默认"枢纽字段项即可.而且它仅创建一个非常基本的数据透视缓存.只要我们仅使用默认值，该方法就起作用，因为Excel然后会在渲染数据透视表时对其进行更正.但是，例如，如果我们需要除默认的折叠行标签之外的其他标签，则此操作将失败.

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.

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

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.

完整示例:

Excel:

代码:

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

 }
}

此代码需要ooxml-schemas-1.3.jar，如 apache poi常见问题解答.

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

结果:

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