How do you sort Pivot Table Data using the Apache POI library in Java

Question

Is it possible to sort XSSFPivotTable data using Apache POI in Java? Currently I am able to create a pivot table, assign a labels, and add aggregate functions but no luck with the data sorting. Currently my Java code is as follows:

public XSSFSheet createPivotTable (XSSFSheet datasht, XSSFSheet destinationsheet) {
    XSSFPivotTable pivotTable = destinationsheet.createPivotTable(new AreaReference("$A:$AV"), new CellReference("A1"), datasht);
    pivotTable.addRowLabel(0);
    pivotTable.addColumnLabel(DataConsolidateFunction.MIN, 25);
    pivotTable.addColumnLabel(DataConsolidateFunction.MIN, 24);
    pivotTable.addColumnLabel(DataConsolidateFunction.MIN, 46);
    pivotTable.addColumnLabel(DataConsolidateFunction.MIN, 47);
    /*
    Insert code to sort pivot table data in ascending order here
    */
    return destinationsheet;
}

Does anyone out there know how to access the sorting functionality of the XSSFPivotTable or know if this is even possible?

Another option I have looked at is to have a template excel file with a pre-existing pivot table configured how I want (including sorting) which is linked to a named range in excel. Then simply update the named range in my template file with new data every time my Java code is executed.

However the excel pivot table is a strange beast indeed and when you save your "template" excel document as a new file, the pivot table area reference always updates to be absolute, i.e. =OLDFILENAME.xlsx!pivotdatarange as opposed to =NAMEFILENAME.xlsx!pivotdatarange.

So my question for this option would be is it possible to modify the area reference of an existing pivot table in excel?

Cheers,

Josh

Answer 1

It is possible to sort the data in the pivot table, though you have to use the CT-classes and probably need to include another dependency.

To sort by the first column in ascending order:

int indexOfSortColumn = 0;
pivotTable.getCTPivotTableDefinition()
    .getPivotFields()
    .getPivotFieldArray(indexOfSortColumn)
    .setSortType(STFieldSortType.ASCENDING);

indexOfSortColumn is the index in the input area.

If your compiler can't find STFieldSortType, you'll need to replace your dependency on org.apache.poi:poi-ooxml-schemas with a dependency on org.apache.poi:ooxml-schemas. ooxml-schemas and poi-ooxml-schemas both contain the XML model for OOXML documents, but the latter is slimmed down.