Search code examples
javaexcelapache-poi

Create filter on Integer column (with default values selected) while creating pivot table using apache poi

I am trying to create filter on third field which is not included in Rows/Columns and that field is Integer. For String I am able to create it and it's working fine but for Integer it's not working.

Below is the sample code which referred from one of the post to create a sample for Integer values:

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 PivotIntegerFilter {

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

    try (Workbook workbook = new XSSFWorkbook();
         FileOutputStream fileout = new FileOutputStream("D:\\user\\temp\\pivottable.xlsx")) {

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

        setCellData(dataSheet, workbook);

        AreaReference areaReference = new AreaReference("A1:E5", SpreadsheetVersion.EXCEL2007);

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

        pivotTable.addReportFilter(1);

        pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 2);
 
        DataFormat dataformat = workbook.createDataFormat();
        short numFmtId = dataformat.getFormat("0");
        pivotTable.getCTPivotTableDefinition().getDataFields().getDataFieldArray(1).setNumFmtId(numFmtId);

        java.util.TreeSet<Integer> uniqueItems = new java.util.TreeSet<Integer>();
        for (int r = areaReference.getFirstCell().getRow() + 1; r < areaReference.getLastCell().getRow() + 1; r++) {
            uniqueItems.add((int) dataSheet.getRow(r).getCell(1).getNumericCellValue());
        }
        int i = 0;

        short numFmtId1 = dataformat.getFormat("0");
        pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(1).setNumFmtId(numFmtId1);
        for (Integer item : uniqueItems) {
            pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(1).getItems().getItemArray(i).unsetT();

            pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(1).getItems().getItemArray(i).setX((long) i);

            int y = item.intValue();
            //CTNumber c = pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(1).getSharedItems().addNewN();
            //c.set(XmlObject.Factory.newValue(Integer.toString((int) y)));
            pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(1).getSharedItems().addNewS().setV(Integer.toString(y));
            if (!"3".equals(item.toString())) {
                pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(1).getItems().getItemArray(i).setH(true);
            }
            // pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(1).setNumFmtId(numFmtId1);
            i++;
        }

        pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(1).setMultipleItemSelectionAllowed(true);
        workbook.write(fileout);
    }
}

static void setCellData(Sheet sheet, Workbook workbook) {
    XSSFRow row;
    XSSFCell cell;
    Object[][] data = new Object[][]{
            new Object[]{"PARTY", "SNO", "VOTES", "Total Count", "Total Absent"},
            new Object[]{"REPUBLICAN", 1, 10d, "?", "?"},
            new Object[]{"DEMOCRAT", 3, 5d, "?", "?"},
            new Object[]{"AMERICAN INDEP", 3, 10d, "?", "?"},
            new Object[]{"DECLINED", 2, 10d, "?", "?"}
    };
    for (int r = 0; r < data.length; r++) {
        row = (XSSFRow) sheet.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 Double) {
                cell.setCellValue((Double) rowData[c]);
            } else if (rowData[c] instanceof Integer) {
                cell.setCellValue((int) rowData[c]);
                DataFormat format = workbook.createDataFormat();
                CellStyle integerCellStyle = workbook.createCellStyle();
                integerCellStyle.setDataFormat(format.getFormat("0"));
                cell.setCellStyle(integerCellStyle);
            }
        }
        }
    }
}

I am trying to create filter on SNO field which is not included in rows, columns or values. If someone can help would be really helpful.

Solution

  • The example seems to be adopted from How to set default value for row labels in pivot table using poi. There my answer explains the basics.

    The difference is that the example there uses text items to filter. Your example needs numeric items to filter.

    For text items sharedItems in pivotCacheDefinition - cacheFields - cacheField are s-items.

    For numeric items sharedItems in pivotCacheDefinition - cacheFields - cacheField are n-items. But while sharedItems having s-items don't need furthrt settings, sharedItems having n-items need further settings about the number type. For example:

    <sharedItems containsSemiMixedTypes="false" containsString="false" containsNumber="true" containsInteger="true">
 <n v="1"/>
 <n v="2"/>
 <n v="3"/>
</sharedItems>

    Code differences:

    For text items:

    for (String item : uniqueItems) {
 ...
 pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(0)
  .getSharedItems().addNewS().setV(item);
 ...
}

    For numeric items:

    for (Integer item : uniqueItems) {
 ...
 pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(1).getSharedItems()
  .setContainsSemiMixedTypes(false);
 pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(1).getSharedItems()
  .setContainsString(false);
 pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(1).getSharedItems()
  .setContainsNumber(true);
 pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(1).getSharedItems()
  .setContainsInteger(true);
 pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(1).getSharedItems()
  .addNewN().setV(item);
 ...
}

    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 PivotIntegerFilter {

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

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

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

   setCellData(dataSheet, workbook);

   AreaReference areaReference = new AreaReference("A1:E5", SpreadsheetVersion.EXCEL2007);

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

   pivotTable.addReportFilter(1);

   pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 2);

   java.util.TreeSet<Integer> uniqueItems = new java.util.TreeSet<Integer>();
   for (int r = areaReference.getFirstCell().getRow() + 1; r < areaReference.getLastCell().getRow() + 1; r++) {
    uniqueItems.add((int) dataSheet.getRow(r).getCell(1).getNumericCellValue());
   }
    
   int i = 0;
   for (Integer item : uniqueItems) {
    pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(1).getItems().getItemArray(i).unsetT();
    pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(1).getItems().getItemArray(i).setX((long) i);

    pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(1).getSharedItems()
     .setContainsSemiMixedTypes(false);
    pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(1).getSharedItems()
     .setContainsString(false);
    pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(1).getSharedItems()
     .setContainsNumber(true);
    pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(1).getSharedItems()
     .setContainsInteger(true);
    pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(1).getSharedItems()
     .addNewN().setV(item);
            
    if (!(item == 3)) {
     pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(1).getItems().getItemArray(i).setH(true);
    }
        
     i++;
   }

   pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(1).setMultipleItemSelectionAllowed(true);
    
   workbook.write(fileout);
  }
 }

 static void setCellData(Sheet sheet, Workbook workbook) {
  Row row;
  Cell cell;
  Object[][] data = new Object[][]{
   new Object[]{"PARTY", "SNO", "VOTES", "Total Count", "Total Absent"},
   new Object[]{"REPUBLICAN", 1, 10d, "?", "?"},
   new Object[]{"DEMOCRAT", 3, 5d, "?", "?"},
   new Object[]{"AMERICAN INDEP", 3, 10d, "?", "?"},
   new Object[]{"DECLINED", 2, 10d, "?", "?"}
  };
  for (int r = 0; r < data.length; r++) {
   row = sheet.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 Double) {
     cell.setCellValue((Double) rowData[c]);
    } else if (rowData[c] instanceof Integer) {
     cell.setCellValue((Integer) rowData[c]);
     DataFormat format = workbook.createDataFormat();
     CellStyle integerCellStyle = workbook.createCellStyle();
     integerCellStyle.setDataFormat(format.getFormat("0"));
     cell.setCellStyle(integerCellStyle);
    }
   }
  }
 }
}