How to implement label filter for pivot tables using apache poi?

Question

I have data stored in a table format on Sheet1 enter image description here. Utilizing this data from Sheet1, I've generated a new pivot table on Sheet2 enter image description here .

I'm looking for solution to apply a label filter on the column labels to exclusively display the 'High complexity' column in the pivot table using apache poi library.

enter image description here

I tried a couple of code samples from stackoverflow, but nothing worked.

Answer 1

Until now apache poi adds as much pivot field items of type "default" (<item t="default"/>) as rows are present in the data range, if the pivot fields where used as axis fields. This is because they don't want to have a look at the data, and so they are assuming as much different values as rows where in the data.

This is fine because Excel will rebuild its pivot cache while opening. But if we want preselect items, then this is not fine. Then we must know what items there are that can be preselected.

So we need at least as much items, as we want preselecting, as numbered items: <item x="0"/><item x="1"/><item x="2"/>...

And we need to build a cache definition which has shared elements for those items.

To do so the need is to determine unique labels in column B (colimn index 1), the Complexity column.

Then build pivot cache using those unique items.

After that we can filter. That is done by set all not selected items to H(idden).

Complete example:

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.*;

class CreatePivotTableItemFilter {

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

  try (Workbook workbook = new XSSFWorkbook(); 
       FileOutputStream fileout = new FileOutputStream("Excel.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[]{"B", "Low"},
    new Object[]{"C", "Moderate"},
    new Object[]{"D", "High"},
    new Object[]{"E", "High"},
    new Object[]{"F", "Low"},
    new Object[]{"G", "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");
   pivotTable.addColLabel(1);
   //Method addColLabel removes the dataField setting. So we need set it new.
   pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(1)
    .setDataField(true);
   
/*   
   Apache poi adds 8 pivot field items of type "default" (<item t="default"/>) for each pivot field. 
   This is because there are 8 rows (A1:B8) and, because they don't have a look at the data, 
   they are assuming max 8 different values. This is fine because Excel will rebuild its pivot cache while opening. 
   But if we want preselect items, then this is not fine. Then we must know what items there are that can be preselected.
   So we need at least as much items as we want preselecting as numbered items: <item x="0"/><item x="1"/><item x="2"/>... 
   And we must build a cache definition which has shared elements for those items.
*/

   //determine unique labels in column B (colimn index 1)
   DataFormatter formatter = new DataFormatter(java.util.Locale.US);
   int colIdx = 1;
   java.util.TreeSet<String> uniqueItems = new java.util.TreeSet<String>(String.CASE_INSENSITIVE_ORDER);
   for (int r = areaReference.getFirstCell().getRow()+1; r < areaReference.getLastCell().getRow()+1; r++) {
    uniqueItems.add(formatter.formatCellValue(dataSheet.getRow(r).getCell(colIdx)));
   }
   //System.out.println(uniqueItems);

   //build pivot cache using uniqueItems
   org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotField ctPivotField 
    = pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(colIdx);
   int i = 0;
   for (String item : uniqueItems) {
    //take the items as numbered items: <item x="0"/><item x="1"/>
    ctPivotField.getItems().getItemArray(i).unsetT();
    ctPivotField.getItems().getItemArray(i).setX((long)i);
    //build a cache definition which has shared elements for those items 
    pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(colIdx)
     .getSharedItems().addNewS().setV(item);
    i++;
   }
  
   String filterText = "High";
   
   //If the need is selecting multiple items, first MultipleItemSelectionAllowed needs to be set.
   ctPivotField.setMultipleItemSelectionAllowed(true);
   //Then set H(idden) true for all items which not shall be selected. Second ("Low") and third ("Moderate") in this case.
   i = 0;
   for (String item : uniqueItems) {
    if (!item.equals(filterText)) {
     ctPivotField.getItems().getItemArray(i).setH(true);
    }
    i++;
   }
   
   workbook.write(fileout);

  }

 }
}