How to add filter on count column in pivot table?

Question

I need to filter pivot table based on count column values. I have tried couple of code samples from google but nothing worked.

enter image description here How to implement this filter using apache poi in java.

Below is the sample working code to generate pivot table on dummy data.

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"));

        }

    }
}

I need to show only rows which has complexity greater than 2. enter image description here

Answer 1

In Apache POI Pivot Tables- How to use the "In-Between" Value Filter in Java I have shown how to set a value filter for pivot table using Apache POI.

Adopted to your example, that would be:

Run your code.

In result Itemfilter.xlsx do what you want using Excel GUI. Save the file.

Unzip Itemfilter.xlsx and have a look into /xl/pivotTables/pivotTable1.xml. There you will find:

<filters count="1">
 <filter fld="0" type="valueGreaterThan" evalOrder="-1" id="1" iMeasureFld="0">
  <autoFilter ref="A1">
   <filterColumn colId="0">
    <customFilters>
     <customFilter operator="greaterThan" val="2"/>
    </customFilters>
   </filterColumn>
  </autoFilter>
 </filter>
</filters>

Now try creating that using following classes:

import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotFilters;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotFilter;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCustomFilters;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCustomFilter;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTFilterColumn;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STFilterOperator;

Code:

...
   //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 !ToDo - handle carefully
   filter.setFld(0); // filter on column level 0 - first row label
   filter.setType(org.openxmlformats.schemas.spreadsheetml.x2006.main.STPivotFilterType.VALUE_GREATER_THAN);
   filter.setIMeasureFld(0); //internal measure field is 0 - first data field
   CTFilterColumn filterColumn = filter.addNewAutoFilter().addNewFilterColumn();
   filterColumn.setColId(0); // filterColumn need colId !ToDo - handle carefully
   CTCustomFilters customFilters= filterColumn.addNewCustomFilters();
   CTCustomFilter customFilter = customFilters.addNewCustomFilter();
   customFilter.setOperator(STFilterOperator.GREATER_THAN);
   customFilter.setVal("2");

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

   pivotTable.getCTPivotTableDefinition().setFilters(filters);              
...