java - 如何使用 Apache POI 使用 getFilter() 方法过滤数据透视表

Question

除了reportFilter之外，是否有任何过滤器可以应用于数据透视表。

             pivotTable.getCTPivotTableDefinition().setFilters(filters);

如何使用上面的setFilters方法

Answer 1

要使用CTPivotTableDefinition.setFilters方法，您需要首先创建一个org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotFilters类型的filters对象.

让我们用一个完整的例子来说明这一点。

import java.io.FileOutputStream;

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

import java.util.GregorianCalendar;

class CreatePivotTableFilter {

 public static void main(String[] args) throws Exception {

  try (Workbook workbook = new XSSFWorkbook(); 
       FileOutputStream fileout = new FileOutputStream("Excel.xlsx") ) {

   DataFormat format = workbook.createDataFormat();
   CellStyle dateStyle = workbook.createCellStyle();
   dateStyle.setDataFormat(format.getFormat("M\\/d\\/yy"));

   Sheet sheet = workbook.createSheet();

   String[] headers = new String[]{"Column1", "Column2", "Date", "Count"};
   Row row = sheet.createRow(0);
   Cell cell;
   for (int c = 0; c < headers.length; c++) {
    cell = row.createCell(c); cell.setCellValue(headers[c]);
   }

   Object[][] data = new Object[][]{
    new Object[]{"A", "B1", new GregorianCalendar(2019, 0, 1), 2d},
    new Object[]{"A", "B2", new GregorianCalendar(2019, 0, 1), 4d},
    new Object[]{"B", "B1", new GregorianCalendar(2019, 0, 2), 1d},
    new Object[]{"B", "B2", new GregorianCalendar(2019, 0, 2), 7d},
    new Object[]{"A", "C1", new GregorianCalendar(2019, 0, 1), 5d},
    new Object[]{"A", "C2", new GregorianCalendar(2019, 0, 1), 5d},
    new Object[]{"B", "C1", new GregorianCalendar(2019, 0, 2), 2d},
    new Object[]{"B", "C2", new GregorianCalendar(2019, 0, 2), 8d}
   };
   for (int r = 0; r < data.length; r++) {
    row = sheet.createRow(r+1);
    Object[] rowData = data[r];
    for (int c = 0; c < rowData.length; c++) {
     cell = row.createCell(c);
     if (rowData[c] instanceof String) {
      cell.setCellValue((String)rowData[c]);
     } else if (rowData[c] instanceof GregorianCalendar) {
      cell.setCellValue((GregorianCalendar)rowData[c]);
      cell.setCellStyle(dateStyle);
     } else if (rowData[c] instanceof Double) {
      cell.setCellValue((Double)rowData[c]);
     }
    }
   }
   
   XSSFPivotTable pivotTable = ((XSSFSheet)sheet).createPivotTable(
    new AreaReference("A1:D9", 
    SpreadsheetVersion.EXCEL2007), 
    new CellReference("F4"));

   pivotTable.addRowLabel(0);
   pivotTable.addRowLabel(1);

   pivotTable.addColLabel(2);

   pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 3);
   pivotTable.addColumnLabel(DataConsolidateFunction.AVERAGE, 3);

   //create CTPivotFilters having filter for field 1 caption begins with "B"
   org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotFilters filters =
    org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotFilters.Factory.newInstance();
   org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotFilter filter = filters.addNewFilter();
   filter.setId(0);
   filter.setFld(1);
   filter.setType(org.openxmlformats.schemas.spreadsheetml.x2006.main.STPivotFilterType.CAPTION_BEGINS_WITH);
   filter.setStringValue1("B");
   filter.addNewAutoFilter().addNewFilterColumn().addNewCustomFilters().addNewCustomFilter().setVal("B*");
   filter.getAutoFilter().setRef("A1");
   filter.getAutoFilter().getFilterColumnArray(0).setColId(0);

   //set filters to pivot table definition
   pivotTable.getCTPivotTableDefinition().setFilters(filters);

   workbook.write(fileout);

  }

 }
}

不幸的是，没有任何关于 ooxml 模式(apache poi 的低级基本对象)的公开文档。所以我们需要下载ooxml-schemas的源码然后通过 javadoc 形成这些内容以获得描述类和方法的 API 文档。

<小时/>

请注意，始终存在 CTPivotFilter 类型和值以及自定义过滤器值甚至运算符(如果需要)的组合。例如，要创建字段 1 标题的过滤器不等于“B2”的 CTPivotFilters，需要:

...
   //create CTPivotFilters having filter for field 1 caption not equal "B2"
   org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotFilters filters =
    org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotFilters.Factory.newInstance();
   org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotFilter filter = filters.addNewFilter();
   filter.setId(0);
   filter.setFld(1);
   filter.setType(org.openxmlformats.schemas.spreadsheetml.x2006.main.STPivotFilterType.CAPTION_NOT_EQUAL);
   filter.setStringValue1("B2");
   filter.addNewAutoFilter().addNewFilterColumn().addNewCustomFilters().addNewCustomFilter().setVal("B2");
   filter.getAutoFilter().getFilterColumnArray(0).getCustomFilters().getCustomFilterArray(0).setOperator(
    org.openxmlformats.schemas.spreadsheetml.x2006.main.STFilterOperator.NOT_EQUAL
   );
   filter.getAutoFilter().setRef("A1");
   filter.getAutoFilter().getFilterColumnArray(0).setColId(0);
...