Pivot unclear grouping / remove result rows

Question

I am having issues to understand the pivot table in POI I could generate a compact pivot, but I have still issues to identify how to remove the result rows (red marked)

how are these rows named? So maybe I could have found them by searching for them.

the code which is creating the table:

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

public class CreatePivotTableComplex {

 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[]{"Name", "Country", "Count", "Value", "Date"},
    new Object[]{"A", "C1", 2d, 123.56, "2023-05-22"},
    new Object[]{"B", "C1", 4d, 34.56, "2023-05-23"},
    new Object[]{"A", "C2", 1d, 56.78, "2023-05-24"},
    new Object[]{"B", "C2", 7d, 23.45, "2023-05-25"},
    new Object[]{"A", "C1", 3d, 234.56, "2023-05-26"},
    new Object[]{"A", "C1", 2d, 132.56, "2023-05-27"},
    new Object[]{"B", "C1", 5d, 78.90, "2023-05-28"}
   };
   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 Double) {
            cell.setCellValue((Double) rowData[c]);
         }
      }
   }

   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.addRowLabel(1);
   pivotTable.addRowLabel(4);
   pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 2, "Sum of count");
   pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 3, "Sum of value");

   pivotTable.getCTPivotTableDefinition().getPivotTableStyleInfo().setName("PivotStyleDark7");

   pivotTable.getCTPivotTableDefinition().setCompact(false);
   pivotTable.getCTPivotTableDefinition().setCompactData(false);
   pivotTable.getCTPivotTableDefinition().setGridDropZones(false);

   pivotTable.getCTPivotTableDefinition().setRowGrandTotals(false);
   
    pivotTable.getCTPivotTableDefinition().setColGrandTotals(true);
    pivotTable.getCTPivotTableDefinition().setRowGrandTotals(true);
    pivotTable.getCTPivotTableDefinition().setEnableDrill(false);

   // pivotTable.getCTPivotTableDefinition().setMultipleFieldFilters(false);
   // pivotTable.getCTPivotTableDefinition().setItemPrintTitles(true);
   for (org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotField pivotField : pivotTable.getCTPivotTableDefinition().getPivotFields()
         .getPivotFieldList()) {
      pivotField.setCompact(false);
      pivotField.setOutline(false);
   }

   workbook.write(fileout);

  }

 }

Answer 1

Those totals are properties of the pivot table fields, not of the whole table. Only grand totals are properties of the whole table.

But much pivot field properties can only be set if a proper pivot table definition and pivot table cache definition is present. Neither the one nor the other can Apache POI create so far.

See How to set default value in Apache POI pivot table report filter, Apache POI XSSFPivotTable setDefaultSubtotal and java: How to create a pivot with apache poi?.

Following uses my addRowLabel method to extend XSSFPivotTable.addRowLabel to provide semi-proper pivot table definition and pivot table cache definition per field.

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 CreatePivotTableClassicLayoutNoFieldSubTotals {
    
 static void addRowLabel(XSSFPivotTable pivotTable, Sheet dataSheet, AreaReference areaReference, int column, 
  boolean defaultSubtotals) { 

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

  //determine unique labels in column
  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(column)));
  }
  //System.out.println(uniqueItems);

  //build pivot table and cache
  org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotField ctPivotField 
   = pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(column);
  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(column)
    .getSharedItems().addNewS().setV(item);
   i++;
  }

  //set pivot field settings
  ctPivotField.setDefaultSubtotal(defaultSubtotals); // no subtotals for this field

  //remove further items
  if (ctPivotField.getDefaultSubtotal()) i++; //let one default item be if there shall be subtotals
  for (int k = ctPivotField.getItems().getItemList().size()-1; k >= i; k--) {
   ctPivotField.getItems().removeItem(k);
  }
  ctPivotField.getItems().setCount(i);

 }    

 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[]{"Name", "Country", "Count", "Value", "Date"},
    new Object[]{"A", "C1", 2d, 123.56, "2023-05-22"},
    new Object[]{"B", "C1", 4d, 34.56, "2023-05-23"},
    new Object[]{"A", "C2", 1d, 56.78, "2023-05-24"},
    new Object[]{"B", "C2", 7d, 23.45, "2023-05-25"},
    new Object[]{"A", "C1", 3d, 234.56, "2023-05-26"},
    new Object[]{"A", "C1", 2d, 132.56, "2023-05-27"},
    new Object[]{"B", "C1", 5d, 78.90, "2023-05-28"}
   };
   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 Double) {
      cell.setCellValue((Double) rowData[c]);
     }
    }
   }

   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.addRowLabel(1);
   // pivotTable.addRowLabel(4);
   addRowLabel(pivotTable, dataSheet, areaReference, 0, false);
   addRowLabel(pivotTable, dataSheet, areaReference, 1, false);
   addRowLabel(pivotTable, dataSheet, areaReference, 4, false);   
   
   pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 2, "Sum of count");
   pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 3, "Sum of value");

   pivotTable.getCTPivotTableDefinition().getPivotTableStyleInfo().setName("PivotStyleDark7");

   pivotTable.getCTPivotTableDefinition().setCompact(false);
   pivotTable.getCTPivotTableDefinition().setCompactData(false);
   pivotTable.getCTPivotTableDefinition().setGridDropZones(true);

   // pivotTable.getCTPivotTableDefinition().setMultipleFieldFilters(false);
   // pivotTable.getCTPivotTableDefinition().setItemPrintTitles(true);
   for (org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotField pivotField : pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldList()) {
    pivotField.setCompact(false);  
    pivotField.setOutline(false);  
   }

   workbook.write(fileout);

  }
 }
}