Apache poi not allowing blank values for pivot creation

Question

I am trying to create a pivot table using apache poi for normal values it is working fine but if there is null or blank values xlsx file gets repaired and pivot gets removed on opening it.

Here is my code:

static void addRowLabel(XSSFPivotTable pivotTable, XSSFSheet dataSheet, AreaReference areaReference, int column) { 

      DataFormatter formatter = new DataFormatter(java.util.Locale.US);
      //apache poi creates as much fields for each as rows are in the pivot table data range
      pivotTable.addRowLabel(column);

      java.util.TreeSet<String> uniqueItems = new java.util.TreeSet<String>();
      for (int r = areaReference.getFirstCell().getRow()+1; r < areaReference.getLastCell().getRow()+1; r++) {
          if (dataSheet.getRow(r).getCell(column) != null && dataSheet.getRow(r).getCell(column).getCellType() != CellType.BLANK) {
              uniqueItems.add(formatter.formatCellValue(dataSheet.getRow(r).getCell(column)));
          } else {

              uniqueItems.add("");
          }
      }

      CTPivotField ctPivotField = pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(column);
      int i = 0;
      for (String item : uniqueItems) {
       //take the items as numbered items
       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(column).getSharedItems().addNewS().setV(item);
       i++;
      }

      //set pivot field settings
      ctPivotField.setOutline(false); // no outline format
      ctPivotField.setDefaultSubtotal(false); // no subtotals for this field

      if (ctPivotField.getDefaultSubtotal()) i++; 
      for (int k = ctPivotField.getItems().getItemList().size()-1; k >= i; k--) {
       ctPivotField.getItems().removeItem(k);
      }
      ctPivotField.getItems().setCount(i);

     }

This method is used for adding rows and below code is to start execution:

 public static void secondway( ) throws IOException {
         try (XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream("/opt/source.xlsx")); 
                   FileOutputStream fileout = new FileOutputStream("/opt/ExcelResult.xlsx") ) {

               XSSFSheet dataSheet = workbook.getSheetAt(0);
               XSSFSheet pivotSheet = workbook.createSheet("Pivot");
               
               int firstRow = dataSheet.getFirstRowNum();
               int lastRow = dataSheet.getLastRowNum();
               int firstCol = dataSheet.getRow(0).getFirstCellNum();
               int lastCol = dataSheet.getRow(0).getLastCellNum();
               CellReference topLeft = new CellReference(firstRow, firstCol);
               CellReference botRight = new CellReference(lastRow, lastCol - 1);

               AreaReference areaReference = new AreaReference(topLeft,botRight, SpreadsheetVersion.EXCEL2007);

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

               addRowLabel(pivotTable, dataSheet, areaReference, 0);
               addRowLabel(pivotTable, dataSheet, areaReference, 2);
               
               pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 1, "test");

               workbook.write(fileout);
         
         
         }
     }

I am not sure what is wrong am I doing or how to support blank values. Please help.

Input that I am using:

While opening shows below error and removes piivot:

[![enter image description here][3]][3]

Answer 1

The aim of the addRowLabel method, which seems to be from my answer java: How to create a pivot with apache poi?, is to correct apache poi, which creates as much items for each pivot field as rows are in the pivot table data range. But it should be as much items as unique items are in pivot field data column.

To get the unique items per column a java.util.TreeSet is used as this cannot contain duplicate elements.

But Excel pivot table takes the values case insensitive. So 11 KD and 11 kd are the same value for Excel pivot tables. Thats why String.CASE_INSENSITIVE_ORDER needs to be used as Comparator while creating the java.util.TreeSet.

Do changing:

...
java.util.TreeSet<String> uniqueItems = new java.util.TreeSet<String>();
...

into

...
java.util.TreeSet<String> uniqueItems = new java.util.TreeSet<String>(String.CASE_INSENSITIVE_ORDER);
...

and it should work.