如何使用Apache POI在Java中筛选数据透视表计数列大于2的行？

我需要基于计数列的值对数据透视表进行筛选，曾尝试过谷歌上的若干代码示例但均无效。请问如何使用Apache POI在Java中实现该筛选？

以下是用于基于模拟数据生成数据透视表的可用示例代码：

package com.technia.upgradetool.export;

import java.awt.Desktop;
import java.io.File;
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 com.technia.upgradetool.Analyzer;

public class CreatePivotTableFilterTest {

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

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

            Sheet pivotSheet = workbook.createSheet("Pivot");
            Sheet dataSheet = workbook.createSheet("Data");

            Row row;
            Cell cell;
            Object[][] data = new Object[][] 
                { new Object[] 
                            { "Header", "Complexity" }, new Object[] 
                            { "A", "High" }, new Object[] 
                            { "A", "Low" }, new Object[] 
                            { "C", "Moderate" }, new Object[] 
                            { "D", "High" }, new Object[] 
                            { "A", "High" }, new Object[] 
                            { "B", "Low" }, new Object[] 
                            { "G", "Low" }, new Object[] 
                            { "G", "High" }, new Object[] 
                            { "G", "High" }, new Object[] 
                            { "G", "High" }, new Object[] 
                            { "G", "Low" }, new Object[] 
                            { "H", "Low" }, new Object[] 
                            { "H", "Low" } };
            for (int r = 0; r < data.length; r++) {
                row = dataSheet.createRow(r);
                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 Number) {
                        cell.setCellValue(((Number) rowData[c]).doubleValue());
                    }
                }
            }

            AreaReference areaReference = new AreaReference(new CellReference(0, 0),
                    new CellReference(data.length - 1, data[0].length - 1),
                    SpreadsheetVersion.EXCEL2007);

            XSSFPivotTable pivotTable = ((XSSFSheet) pivotSheet).createPivotTable(areaReference,
                    new CellReference("A4"), dataSheet);

            pivotTable.addRowLabel(0);
            pivotTable.addColumnLabel(DataConsolidateFunction.COUNT, 1, "Count of complexity");
            // Method addColLabel removes the dataField setting. So we need set it new.
            pivotTable.getCTPivotTableDefinition()
                .getPivotFields()
                .getPivotFieldArray(1)
                .setDataField(true);

            workbook.write(fileout);
            fileout.close();

            Desktop.getDesktop().open(new File("ItemFilter.xlsx"));

        }

    }
}

我需要仅显示复杂度计数大于2的行。

由于Apache POI高层API未封装数据透视表的值筛选功能，需直接操作底层OOXML对象实现。以下是修改后的完整代码，关键筛选逻辑已添加注释：

package com.technia.upgradetool.export;

import java.awt.Desktop;
import java.io.File;
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.CTPivotFilter;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotFilters;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotTableDefinition;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STPivotFilterType;

import com.technia.upgradetool.Analyzer;

public class CreatePivotTableFilterTest {

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

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

            Sheet pivotSheet = workbook.createSheet("Pivot");
            Sheet dataSheet = workbook.createSheet("Data");

            Row row;
            Cell cell;
            Object[][] data = new Object[][] 
                { new Object[] 
                            { "Header", "Complexity" }, new Object[] 
                            { "A", "High" }, new Object[] 
                            { "A", "Low" }, new Object[] 
                            { "C", "Moderate" }, new Object[] 
                            { "D", "High" }, new Object[] 
                            { "A", "High" }, new Object[] 
                            { "B", "Low" }, new Object[] 
                            { "G", "Low" }, new Object[] 
                            { "G", "High" }, new Object[] 
                            { "G", "High" }, new Object[] 
                            { "G", "High" }, new Object[] 
                            { "G", "Low" }, new Object[] 
                            { "H", "Low" }, new Object[] 
                            { "H", "Low" } };
            for (int r = 0; r < data.length; r++) {
                row = dataSheet.createRow(r);
                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 Number) {
                        cell.setCellValue(((Number) rowData[c]).doubleValue());
                    }
                }
            }

            AreaReference areaReference = new AreaReference(new CellReference(0, 0),
                    new CellReference(data.length - 1, data[0].length - 1),
                    SpreadsheetVersion.EXCEL2007);

            XSSFPivotTable pivotTable = ((XSSFSheet) pivotSheet).createPivotTable(areaReference,
                    new CellReference("A4"), dataSheet);

            pivotTable.addRowLabel(0);
            pivotTable.addColumnLabel(DataConsolidateFunction.COUNT, 1, "Count of complexity");
            // 修复addColLabel移除dataField的问题
            pivotTable.getCTPivotTableDefinition()
                .getPivotFields()
                .getPivotFieldArray(1)
                .setDataField(true);

            // -------------------------- 新增筛选逻辑 --------------------------
            CTPivotTableDefinition pivotDef = pivotTable.getCTPivotTableDefinition();
            // 定位到行字段（Header列，索引0）
            int rowFieldIndex = 0;
            // 创建筛选集合
            CTPivotFilters pivotFilters = pivotDef.getPivotFields().getPivotFieldArray(rowFieldIndex).addNewFilters();
            // 创建值筛选规则
            CTPivotFilter valueFilter = pivotFilters.addNewFilter();
            // 设置筛选类型为值筛选
            valueFilter.setType(STPivotFilterType.VALUE);
            // 指定筛选基于的数据字段（Count of complexity，索引0）
            valueFilter.setDataField(0);
            // 设置筛选条件：大于2
            valueFilter.addNewValueFilter().addNewCondition().setVal(2.0);
            valueFilter.getValueFilter().getCondition().setOperator(">");
            // 启用自动显示筛选结果
            pivotDef.getPivotFields().getPivotFieldArray(rowFieldIndex).setAutoShow(true);
            // -----------------------------------------------------------------

            workbook.write(fileout);
            fileout.close();

            Desktop.getDesktop().open(new File("ItemFilter.xlsx"));

        }

    }
}

核心逻辑说明 #

1. 操作底层OOXML对象：通过CTPivotTableDefinition直接配置筛选规则，弥补高层API的功能缺失。
2. 筛选目标：针对行字段（Header列）设置值筛选，基于计数列（Count of complexity）的数值判断。
3. 条件配置：明确筛选运算符为>，阈值为2，仅保留计数大于2的行。
4. 启用筛选：设置setAutoShow(true)确保筛选规则生效并自动显示符合条件的结果。

内容的提问来源于stack exchange，提问作者pranavs