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

Question

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

pivotTable.addReportFilter(13);

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

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 中所述.