Search code examples
javaexcelapache-poixssf

Java Poi XSSF - create pivot table with multiple expandable columns

I have about 1000 rows with multiple columns and I want to create a pivot table out of them.

My actual result is: enter image description here

But I want to get a pivot table which looks like this: enter image description here

My current code:

// ... 
// fill sheet with data 
// ...

// create Pivot table
int firstRow = sheet.getFirstRowNum() + firstDataRow;
int lastRow = sheet.getLastRowNum();
int firstCol = sheet.getRow(0).getFirstCellNum();
int lastCol = sheet.getRow(firstRow).getLastCellNum();

CellReference topLeft = new CellReference(firstRow, firstCol);
CellReference botRight = new CellReference(lastRow, lastCol-1);

XSSFPivotTable pivotTable = pivotSheet.createPivotTable(
        new AreaReference(topLeft, botRight), new CellReference(firstRow, lastCol), sheet);

pivotTable.addRowLabel(lastCol - 1); // month
pivotTable.addRowLabel(6); // car
pivotTable.addRowLabel(lastCol - 2); // state
pivotTable.addColumnLabel(DataConsolidateFunction.COUNT, 0, 
        resourceBundle.getString("Count"));

I even do not know how to name the different results. I think I need to go deeper into the poi subject matter to generate a more complex pivot table but I do not know how.

Could anybody help me with this issue?

Solution

  • Found the solution:

    CTPivotFields pFields = pivotTable.getCTPivotTableDefinition().getPivotFields();
            pFields.getPivotFieldArray(lastCol - 1).setOutline(false);
            pFields.getPivotFieldArray(6).setOutline(false);
            pFields.getPivotFieldArray(lastCol - 2).setOutline(false);