如何在Apache POI数据透视表报告筛选器中设置默认值

Question

我有一个包含数据的工作表，我想创建一个带有报告筛选器的数据透视表。我希望将默认值设置为报告筛选器。

pivotTable.addReportFilter(13);

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

- Coding Ninja

Answer 1

起初，这个问题在当前的一般上下文中是无法回答的。使用apache poi创建数据透视表仍处于测试阶段。因此，我们不仅需要高级别的apache poi API，还需要底层的低级对象。我们需要确切地知道数据透视表中应该包含哪种类型的数据。要创建能够从所有类型的数据中创建数据透视表，就像Excel可以做到的那样，需要付出更多的努力。微软花费了几十年时间，由大量程序员编写程序来完成这项工作。相比之下，apache poi还差得远呢。

到目前为止，如果使用数据透视表字段作为轴字段，则apache poi将添加与数据范围中存在的行数相同的“默认”类型的数据透视表字段项(<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中所述。

- Axel Richter