如何在Apache POI数据透视表报表过滤器中设置默认值

Question

我有一个工作表中的数据，我试图创建一个数据透视表与报告过滤器。我想将默认值设置为报告过滤器。

pivotTable.addReportFilter(13);

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

Answer 1

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

到目前为止，apache poi添加的"default"(<item t="default"/>)类型的枢轴字段项与数据范围中存在的行一样多，如果枢轴字段被用作轴字段。这是因为它们不想查看数据，所以它们假设的值与数据中的行一样多。

这很好，因为Excel将在打开时重建其枢轴缓存。但如果我们想要预选项目，那么这就不行。那么我们必须知道有哪些项目是可以预选的。

所以我们需要至少和我们想要预选的项目一样多的项目:<item x="0"/><item x="1"/><item x="2"/>…

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

的例子:

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，如FAQ中提到的。