Insert Column Label into pivot table by using Apache POI?

Question

I've 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 shows the row correctly. But it doesn't show a column label:

I want to display the column label in my pivot table like this:

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

Does anyone know the solution for this problem?

Thanks.

At this moment I've exactly the same problem! Well, since it's a beta version, it's probably not possible right now... It's a pity, that there is no method addColumnLabel(int) which would add a column label without a data consolidation function... I searched for some sample code and found this: code.google.com/p/web-design-r/source/browse/trunk/zpoiex-r/src/… The createPivotTable method looks interesting where they handle some CTPivotFields. Don't know, if there is a way through these fields?! — bobbel
Did you get this to work? the Create addColumnLabel as shown in below answers still does not work..thanks — labheshr

Jens Gabe Jens Gabe · Accepted Answer · 2015-11-13T21:04:34

The following method (a slightly modified version of XSSFPivotTable.addRowLabel) adds a "normal" pivot 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());
}

Insert Column Label into pivot table by using Apache POI?

2 Answers