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);
}
}
}
因为这个项目是用Java用Spring boot做的,所以这些依赖需要添加到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屋!