Apache POI : Excel Pivot Table - Row Label

Question

I have a requirement to create a excel sheet in JAVA as below :

I am not able to create a row label with multiple columns side by side ( menus and submenus filter ).

Instead of displaying the submenu in different column, its coming under the menu column.

Below is the piece of code which i have written :

XSSFSheet sheet = my_xlsx_workbook.getSheetAt(0); 
    AreaReference a=new AreaReference("A1:G7");
    CellReference b=new CellReference("I5");
    XSSFPivotTable pivotTable = sheet.createPivotTable(a,b);
    pivotTable.addReportFilter(0);
    pivotTable.addReportFilter(1);
    pivotTable.addRowLabel(2);
    pivotTable.addRowLabel(3);
    pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 4, "Sum");

But its getting wrongly displayed as below :

Can someone please help me ?

under pivot table options, select "Classic Pivot Table layout" — OldUgly
Can you please elaborate bit more please ? I am new to poi API's. — Yathish Manjunath
I was giving you how to change it in Excel. The macro recorder shows you want pivotTable.InGridDropZones = True and pivotTable.RowAxisLayout xlTabularRow — OldUgly
I want to do it from the java code. I cannot change it manually on excel sheet. This sheet is been downloaded by user. — Yathish Manjunath

Axel Richter Axel Richter · Accepted Answer · 2016-04-06T16:05:11

Since the format is XML it is easy to check what is needed. Unpack the Zip xlsx and look in the /xl/pivotTables/pivotTable1.xml.

Then: https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFPivotTable.html#getCTPivotTableDefinition%28%29 and http://grepcode.com/file/repo1.maven.org/maven2/org.apache.poi/ooxml-schemas/1.1/org/openxmlformats/schemas/spreadsheetml/x2006/main/CTPivotTableDefinition.java.

    AreaReference a=new AreaReference(new CellReference("A1"), new CellReference("E7"));
    CellReference b=new CellReference("I5");
    XSSFPivotTable pivotTable = sheet.createPivotTable(a,b);
    pivotTable.addReportFilter(0);
    pivotTable.addReportFilter(1);
    pivotTable.addRowLabel(2);

pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(2).setOutline(false);

    pivotTable.addRowLabel(3);
    pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 4, "Sum");

Apache POI : Excel Pivot Table - Row Label

1 Answers