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

I've the same question, how to automatically sort the data of a pivot table? Do you already have a solution for this? — bobbel

Sjoerd Hemminga Sjoerd Hemminga · Accepted Answer · 2020-10-13T22:53:05

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.

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

1 Answers