如何在Apache POI数据透视表报表过滤器中设置默认值 [英] How to set default value in Apache POI pivot table report filter

问题描述

我有一个包含数据的工作表，我正在尝试创建一个包含报表过滤器的数据透视表。我想将默认值设置为报表过滤器。

I have a worksheet with data in it, I am trying to create a pivot table with report filter. I want to set default value to the report filter.

pivotTable.addReportFilter(13);

列包含0和1，我想在报告过滤器中将0设置为我的默认值。

column contains 0's and 1's, I would like to set 0 as my default value in the report filter.

推荐答案

首先，这个问题在现在被问到的一般情况下是无法回答的。到目前为止，使用apache poi创建数据透视表是处于测试状态。因此，我们不仅需要高级apache poi API，还需要底层的低级对象。我们需要确切知道数据透视表中应包含哪种数据。为了能够像Excel那样从所有类型的数据创建数据透视表，需要付出更多的努力。几十年来，微软已经与大型程序员团队进行了编程。从这个apache poi很远。

At first this question is not answerable in that general context as it is asked now. Creating pivot tables using apache poi is in beta state until now. So we need not only the high level apache poi API but also the underlying low level objects. And we need exactly to know which kind of data shall be in the pivot table. To be general able creating pivot tables from all kind of data, as Excel can do, there is much more effort necessary. Microsoft has programmed this in decades with big teams of programmers. From this apache poi is far away.

到目前为止，apache poi添加了类型为default的数据透视字段项（< item t = default/> ）如果数据范围中存在行，如果数据库字段用作轴字段。
这是因为他们不想查看数据，因此他们假设的值与数据中的行数不同。

Until now apache poi adds as much pivot field items of type "default" (<item t="default"/>) as rows are present in the data range, if the pivot fields where used as axis fields. This is because they don't want to have a look at the data, and so they are assuming as much different values as rows where in the data.

这很好，因为Excel会在打开时重建其数据透视缓存。但是如果我们想要预选项目，那就不行了。然后我们必须知道哪些项目可以预先选择。

This is fine because Excel will rebuild its pivot cache while opening. But if we want preselect items, then this is not fine. Then we must know what items there are that can be preselected.

所以我们至少需要像预选一样多的项目作为编号项目：< item x =0/>< item x =1/>< item x =2/> ...

So we need at least as much items, as we want preselecting, as numbered items: <item x="0"/><item x="1"/><item x="2"/>...

我们需要构建一个缓存定义，其中包含这些项的共享元素。

And we need to build a cache definition which has shared elements for those items.

示例：

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

import java.util.Random;
import java.io.*;

class PivotTableTest4 {

 private static void setCellData(Sheet sheet) {
  Row row = sheet.createRow(0);
  Cell cell = row.createCell(0);
  cell.setCellValue("Name");
  cell = row.createCell(1);
  cell.setCellValue("Value1");
  cell = row.createCell(2);
  cell.setCellValue("Value2");
  cell = row.createCell(3);
  cell.setCellValue("City");

  for (int r = 1; r < 15; r++) {
   row = sheet.createRow(r);
   cell = row.createCell(0);
   cell.setCellValue("Name " + ((r-1) % 4 + 1));
   cell = row.createCell(1);
   cell.setCellValue(r * new java.util.Random().nextDouble());
   cell = row.createCell(2);
   cell.setCellValue(r * new java.util.Random().nextDouble());
   cell = row.createCell(3);
   cell.setCellValue("City " + ((r-1) % 3 + 1));  
  }
 }

 public static void main(String[] args) {
  try {
   XSSFWorkbook wb = new XSSFWorkbook();
   XSSFSheet sheet = wb.createSheet();

   //Create some data to build the pivot table on
   setCellData(sheet);

   XSSFPivotTable pivotTable = sheet.createPivotTable(
    new AreaReference(new CellReference("A1"), new CellReference("D15")), new CellReference("H5"));
   //Configure the pivot table
   //Use first column as row label
   pivotTable.addRowLabel(0);
   //Sum up the second column
   pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 1);
   //Avarage the third column
   pivotTable.addColumnLabel(DataConsolidateFunction.AVERAGE, 2);
   //Add fourth column as page filter
   pivotTable.addReportFilter(3);
/*   
   Apache poi adds 15 pivot field items of type "default" (<item t="default"/>) here. 
   This is because there are 15 rows (A1:D15) and, because they don't have a look at the data, 
   they are assuming max 15 different values. This is fine because Excel will rebuild its pivot cache while opening. 

   But if we want preselect items, then this is not fine. Then we must know what items there are that can be preselected.

   So we need at least as much items as we want preselecting as numbered items: <item x="0"/><item x="1"/><item x="2"/>... 

   And we must build a cache definition which has shared elements for those items.
*/
   for (int i = 0; i < 3; i++) {
    //take the first 3 items as numbered items: <item x="0"/><item x="1"/><item x="2"/>
    pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(3).getItems().getItemArray(i).unsetT();
    pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(3).getItems().getItemArray(i).setX((long)i);
    //build a cache definition which has shared elements for those items 
    //<sharedItems><s v="City 1"/><s v="City 2"/><s v="City 3"/></sharedItems>
    pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(3).getSharedItems().addNewS().setV("City " + (i+1));
   }

   //Now we can predefinite a page filter. Second item, which is "City 2", in this case.
   pivotTable.getCTPivotTableDefinition().getPageFields().getPageFieldArray(0).setItem(1);

   FileOutputStream fileOut = new FileOutputStream("PivotTableTest4.xlsx");
   wb.write(fileOut);
   fileOut.close();
   wb.close();
  } catch (FileNotFoundException e) {
    e.printStackTrace();
  } catch (IOException e) {
    e.printStackTrace();
  }
 }
}

这需要完整的jar所有模式， ooxml-schemas-1.3.jar ，如常见问题。

This needs the full jar of all of the schemas, ooxml-schemas-1.3.jar, as mentioned in the FAQ.

这篇关于如何在Apache POI数据透视表报表过滤器中设置默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！