XSSF(Apache POI) - Adding multiple column label from single column value in pivot table

Question

I am currently using the Apache POI 3.12 for adding pivot table. Here my sample.xlsx file :

Now I using the following code for creating the pivot table for the above data.

    File excel = new File("sample.xlsx"); 
    FileInputStream fis = new FileInputStream(excel); 
    XSSFWorkbook wb = new XSSFWorkbook(fis); 
    XSSFSheet sheet = wb.getSheetAt(0); 
    XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A3:C7"), new CellReference("E3"));
    pivotTable.addRowLabel(0);
    pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 1);
    pivotTable.addDataColumn(1, true);
    pivotTable.addReportFilter(2);
    FileOutputStream fileOut = new FileOutputStream("output.xlsx");
    wb.write(fileOut);
    fileOut.close();
    wb.close();

My output.xlsx file have the following pivot table :

When I am going to edit the pivot table in excel it adding the year column in page fields not column fields. Actually I need the following result :

I can't add multiple column label from single column value. Could you please help me? Thanks in advance

Axel Richter Axel Richter · Accepted Answer · 2016-03-12T07:08:22

The class XSSFPivotTable is in @Beta state. So this is only possible using the underlaying low level objects.

XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream("sample.xlsx")); 
XSSFSheet sheet = wb.getSheetAt(0);

//the following creates a Pivot Table with 3 PivotFields (0 to 2) (3 Columns A3:C7); all dataField="false" at first 
XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference(new CellReference("A3"), new CellReference("C7")), new CellReference("E3"));

//the following makes PivotFields(0) an Axis-Field AXIS_ROW with 5 Items (5 Rows A3:C7). Why one Item for each row? I don't know.
//and it adds a new RowField for this
pivotTable.addRowLabel(0);

//the following makes PivotFields(1) a DataField and creates a DataColumn for this
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 1);
//pivotTable.addDataColumn(2, false); //not neccessary since addColumnLabel already adds a DataColumn

//now PivotFields(2) needs to be an Axis-Field AXIS_COL
pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(2).setAxis(
  org.openxmlformats.schemas.spreadsheetml.x2006.main.STAxis.AXIS_COL);

//PivotFields(2) needs to have at least one Item  
pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(2).addNewItems();
pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(2).getItems().addNewItem().setT(
  org.openxmlformats.schemas.spreadsheetml.x2006.main.STItemType.DEFAULT);

//new ColField needs to be added
pivotTable.getCTPivotTableDefinition().addNewColFields().addNewField().setX(2);

//pivotTable.addReportFilter(2);
FileOutputStream fileOut = new FileOutputStream("output.xlsx");
wb.write(fileOut);
fileOut.close();
wb.close();

The pivotTable.addDataColumn(1, true); is not neccessary since addColumnLabel already adds a DataColumn.

XSSF(Apache POI) - Adding multiple column label from single column value in pivot table

1 Answers