Insert column label into pivot table using Apache POI?

Question

I created a pivot table using Apache POI 3.11. like this:

FileInputStream file = new FileInputStream(new File(path+fname)); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); //area of pivot data AreaReference a=new AreaReference("A1:J4"); CellReference b=new CellReference("N5"); XSSFPivotTable pivotTable = sheet.createPivotTable(a,b); //insert row pivotTable.addRowLabel(3); pivotTable.addRowLabel(6); //insert column pivotTable.addColumnLabel(DataConsolidateFunction.COUNT, 5); //export FileOutputStream output_file = new FileOutputStream(new File(path+"POI_XLS_Pivot_Example.xlsx")); workbook.write(output_file);//write excel document to output stream output_file.close(); //close the file 

After I generated the report, it correctly displays the line. But it does not show the column label:

I want to display the column label in my pivot table as follows:

_{(source: pivot-table.com )}

Does anyone know a solution to this problem?

Thank you

Answer 1

The following method (a slightly modified version of XSSFPivotTable.addRowLabel) adds a “normal” column label:

 public static void addColLabel(XSSFPivotTable pivotTable, int columnIndex) { AreaReference pivotArea = new AreaReference(pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition() .getCacheSource().getWorksheetSource().getRef()); int lastRowIndex = pivotArea.getLastCell().getRow() - pivotArea.getFirstCell().getRow(); int lastColIndex = pivotArea.getLastCell().getCol() - pivotArea.getFirstCell().getCol(); if (columnIndex > lastColIndex) { throw new IndexOutOfBoundsException(); } CTPivotFields pivotFields = pivotTable.getCTPivotTableDefinition().getPivotFields(); CTPivotField pivotField = CTPivotField.Factory.newInstance(); CTItems items = pivotField.addNewItems(); pivotField.setAxis(STAxis.AXIS_COL); pivotField.setShowAll(false); for (int i = 0; i <= lastRowIndex; i++) { items.addNewItem().setT(STItemType.DEFAULT); } items.setCount(items.sizeOfItemArray()); pivotFields.setPivotFieldArray(columnIndex, pivotField); CTColFields rowFields; if (pivotTable.getCTPivotTableDefinition().getColFields() != null) { rowFields = pivotTable.getCTPivotTableDefinition().getColFields(); } else { rowFields = pivotTable.getCTPivotTableDefinition().addNewColFields(); } rowFields.addNewField().setX(columnIndex); rowFields.setCount(rowFields.sizeOfFieldArray()); } 

Answer 2

Since version 3.12 is a POI , it works like a charm (also with its own column labels):

 //insert column pivotTable.addColumnLabel(DataConsolidateFunction.COUNT, 2, "Central"); pivotTable.addColumnLabel(DataConsolidateFunction.COUNT, 3, "East"); pivotTable.addColumnLabel(DataConsolidateFunction.COUNT, 4, "West"); pivotTable.addColumnLabel(DataConsolidateFunction.COUNT, 5, "Grand Total");