How to generate aggregations like sum, average at row labels instead of column labels in excel pivot table using apache poi?

Question

I am trying to produce an excel pivot table using apache-poi library.

Base data

What I want

When i try to add multiple aggregations, excel was grouping it into values(you can see the values attribute in right side of 2nd image in row labels) and placing values in columns. I want to generate an excel with values in the row labels by default.

But when i add multiple aggregations like sum and average(as shown in the below image), the values are shown in column labels. values can be dragged to row labels in excel but i need the values in rows by default.

But I am able to generate this in java

Code for these aggregations

XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet sheet = wb.createSheet();

            //Create some data to build the pivot table on
            setCellData(sheet);
            XSSFPivotTable pivotTable = null;
            try {
                pivotTable = sheet.createPivotTable(new AreaReference("A1:I8", SpreadsheetVersion.EXCEL2007), new CellReference("M10"));
            } catch (Exception ex) {
                System.out.println("In sheet: " + ex);
            }
            //Configure the pivot table
            //Use first column as row label
            pivotTable.addRowLabel(0);
            pivotTable.addRowLabel(1);

            pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(2).setAxis(STAxis.AXIS_COL);
            pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(2).addNewItems();
            pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(2).getItems().addNewItem()
                    .setT(STItemType.DEFAULT);
            pivotTable.getCTPivotTableDefinition().addNewColFields().addNewField().setX(1);
           //this.addCalculatedColumnToPivotTable(pivotTable, "field1", "average of count ab", "'count'");
            //Sum up the second column
            pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 3);
            //Set the third column as filter
            pivotTable.addColumnLabel(DataConsolidateFunction.AVERAGE, 3);

But there is no function in pivot table to add these aggregations in row labels in apache-poi library.

Answer 1

Using the latest apache poi version 4.1.0 it is no more necessary to use the low level underlying beans for adding a column label since there is XSSFPivotTable.addColLabel now.

But there is not any addRowLabel(DataConsolidateFunction function, int columnIndex) until now. The addColumnLabel(DataConsolidateFunction function, int columnIndex) adds data on columns and data fields as col fields. So we need changing that if the need is having data on rows and data fields as row fields.

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

import java.util.GregorianCalendar;

class CreatePivotTable {

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

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

   DataFormat format = workbook.createDataFormat();
   CellStyle dateStyle = workbook.createCellStyle();
   dateStyle.setDataFormat(format.getFormat("M\\/d\\/yy"));

   Sheet sheet = workbook.createSheet();

   String[] headers = new String[]{"Column1", "Column2", "Date", "Count"};
   Row row = sheet.createRow(0);
   Cell cell;
   for (int c = 0; c < headers.length; c++) {
    cell = row.createCell(c); cell.setCellValue(headers[c]);
   }

   Object[][] data = new Object[][]{
    new Object[]{"A", "B", new GregorianCalendar(2019, 0, 1), 2d},
    new Object[]{"A", "B", new GregorianCalendar(2019, 0, 1), 4d},
    new Object[]{"A", "B", new GregorianCalendar(2019, 0, 2), 1d},
    new Object[]{"A", "B", new GregorianCalendar(2019, 0, 2), 7d},
    new Object[]{"A", "C", new GregorianCalendar(2019, 0, 1), 5d},
    new Object[]{"A", "C", new GregorianCalendar(2019, 0, 1), 5d},
    new Object[]{"A", "C", new GregorianCalendar(2019, 0, 2), 2d},
    new Object[]{"A", "C", new GregorianCalendar(2019, 0, 2), 8d}
   };
   for (int r = 0; r < data.length; r++) {
    row = sheet.createRow(r+1);
    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 GregorianCalendar) {
      cell.setCellValue((GregorianCalendar)rowData[c]);
      cell.setCellStyle(dateStyle);
     } else if (rowData[c] instanceof Double) {
      cell.setCellValue((Double)rowData[c]);
     }
    }
   }

   XSSFPivotTable pivotTable = ((XSSFSheet)sheet).createPivotTable(new AreaReference("A1:D9", SpreadsheetVersion.EXCEL2007), new CellReference("M10"));

   pivotTable.addRowLabel(0);
   pivotTable.addRowLabel(1);

   pivotTable.addColLabel(2);

   // the default sets data on columns and data fields as col fields
   pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 3);
   pivotTable.addColumnLabel(DataConsolidateFunction.AVERAGE, 3);

   // now changing from the default
   // set dataOnRows
   pivotTable.getCTPivotTableDefinition().setDataOnRows(true);

   // add a new row field for data fields 
   pivotTable.getCTPivotTableDefinition().getRowFields().addNewField().setX(-2);
   pivotTable.getCTPivotTableDefinition().getRowFields().setCount(3);

   // remove data fields from col fields
   pivotTable.getCTPivotTableDefinition().getColFields().removeField(1);
   pivotTable.getCTPivotTableDefinition().getColFields().setCount(1);

   workbook.write(fileout);

  }

 }
}

The x attribute in a field element in either rowFields or colFields normally specifies the index to a pivotField item value. But dataFields might not be directly related to pivotFields.

If there is only one dataField then there is only one possibility for showing that. So no field element is needed for that.

But if there are multiple dataFields, then there is a additional field named Values in pivot table's GUI view. In the pivot tables XML that additional field is indexed using -2.

So dataOnRows in pivotTableDefinition determines whether dataFields are shown on rows (dataOnRows = "true") or on columns (default). And the x attribute -2 in a field element in either rowFields or colFields specifies where the additional Values field is arranged in the list of fields.