Apache Poi-使用值过滤器而不是使用Java的标签过滤器过滤数据透视表 [英] Apache Poi- Filter a Pivot Table Using the Value Filters instead of the Label Filters using Java

问题描述

可能与之相关的人

我目前坚持尝试使用excel将过滤器添加到Java中的数据透视表中，该过滤器按列的总和而不是标签进行过滤.但是，当我尝试按值对其进行过滤时，我却得到了一个透视表，根本没有进行任何过滤.这就是当前表的样子，即使我尝试执行值过滤时也是如此:

I am currently stuck on trying to add a filter to a pivot table in java using excel which filters by the sum of the columns instead of by the labels. However, when I try and filter it by value, I instead get a pivot table with no filtering done at all. This is what the current table looks like, even when I try and do value filtering:

这是我的代码(此示例实际上是可复制的):

And this is my code (this example is actually reproducible):

package com.tutorialspoint.spring;
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 org.openxmlformats.schemas.spreadsheetml.x2006.main.CTAutoFilter;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCustomFilter;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCustomFilters;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDataField;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDataFields;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTFilter;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTFilterColumn;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTFilters;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotField;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STFilterOperator;

import java.util.GregorianCalendar;

class CreatePivotTableFilter {

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

  try (Workbook workbook = new XSSFWorkbook(); 
       FileOutputStream fileout = new FileOutputStream("MyExcelV2.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", "IntVal", "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]);
     }
     else if (rowData[c] instanceof Integer)
         cell.setCellValue((Integer) 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);
   pivotTable.getCTPivotTableDefinition().setOutline(true);
   pivotTable.getCTPivotTableDefinition().setOutlineData(false);
   for (CTPivotField pf: pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldList())
   {
    System.out.println("FOO");

    pf.setOutline(true);
    pf.setDefaultSubtotal(true);
   }
   org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotFilters filters =
    org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotFilters.Factory.newInstance();
    org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotFilter field = filters.addNewFilter();
   field.setId(0);
   field.setFld(1);
   field.setType(org.openxmlformats.schemas.spreadsheetml.x2006.main.STPivotFilterType.VALUE_LESS_THAN_OR_EQUAL);
   field.setStringValue1("3");
   CTFilterColumn myCol = field.addNewAutoFilter().addNewFilterColumn();
   CTCustomFilters myFilter2= myCol.addNewCustomFilters();
   CTCustomFilter custFilt = myFilter2.addNewCustomFilter();
   custFilt.setOperator(STFilterOperator.LESS_THAN_OR_EQUAL);
   custFilt.setVal("3");
     field.getAutoFilter().setRef("A1");
   field.getAutoFilter().getFilterColumnArray(0).setColId(0);
   System.out.println(sheet.getLastRowNum());
   System.out.println(pivotTable.getColLabelColumns());
   System.out.println(pivotTable.getCTPivotTableDefinition().getDataFields().toString());
   pivotTable.getCTPivotTableDefinition().setFilters(filters);
   workbook.write(fileout);
  }

 }
}

由于此项目是使用Spring引导通过Java完成的，因此，如果尚未完成，则需要将这些依赖项添加到pom.xml文件中:

Because this project is done with Java using Spring boot, these dependencies need to be added to the pom.xml file, if not done already:

        <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/ooxml-schemas -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>ooxml-schemas</artifactId>
    <version>1.4</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>

我知道代码真的很接近访问值过滤器，我想我忘了访问它们需要做些什么.请帮帮我！

I know the code is really close to accessing the value filters, I think I forgot what I need to do to access them. Please help me!

推荐答案

主要问题是您的代码为 CTPivotFilter 设置了 StringValue1 .但是应该设置 IMeasureFld .

The main problem is that your code sets StringValue1 for CTPivotFilter. But it should set IMeasureFld.

内部测量字段 IMeasureFld 设置过滤器应在其上进行过滤的数据字段.在这种情况下， 0 是具有数据合并功能的第一个数据字段->.和. IMeasureFld 1 是具有数据合并功能的第二个数据字段->平均.

The internal measure field IMeasureFld sets the data field on which the filter shall filter. In this case 0 is the first data filed having a data consolidate function -> Sum. IMeasureFld 1 would be the second data filed having a data consolidate function -> Average.

以下代码提供了一个最小的工作示例.该代码在不自我描述的地方加上注释.

Following code provides a minimal working example. The code is commented where it is not self describing.

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 org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCustomFilter;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCustomFilters;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDataField;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTFilter;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTFilterColumn;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STFilterOperator;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotFilters;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotFilter;

import java.util.GregorianCalendar;

class CreatePivotTableFilter {

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

  try (Workbook workbook = new XSSFWorkbook(); 
       FileOutputStream fileout = new FileOutputStream("./MyExcelV2.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", "IntVal", "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]);
     }
     else if (rowData[c] instanceof Integer)
         cell.setCellValue((Integer) 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 filters
   CTPivotFilters filters = CTPivotFilters.Factory.newInstance();

   //set custom value filter
   int filtersCount = 0; // to count filters
   CTPivotFilter filter = filters.addNewFilter();
   filter.setId(0); // filter needs Id
   filter.setFld(1); // filter on column B level
   filter.setType(org.openxmlformats.schemas.spreadsheetml.x2006.main.STPivotFilterType.VALUE_LESS_THAN_OR_EQUAL);
   filter.setIMeasureFld(0); //internal measure field is 0 (first data field) = Sum; 1 would be Average
   CTFilterColumn filterColumn = filter.addNewAutoFilter().addNewFilterColumn();
   filterColumn.setColId(0); // filterColumn need colId
   CTCustomFilters customFilters= filterColumn.addNewCustomFilters();
   CTCustomFilter customFilter = customFilters.addNewCustomFilter();
   customFilter.setOperator(STFilterOperator.LESS_THAN_OR_EQUAL);
   customFilter.setVal("3");

   filtersCount++;
   filters.setCount(filtersCount); // set filters count

   pivotTable.getCTPivotTableDefinition().setFilters(filters);

   workbook.write(fileout);
  }

 }
}

它产生:

这篇关于Apache Poi-使用值过滤器而不是使用Java的标签过滤器过滤数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！