Unable to sort by column of apache POI pivot table

Viewed 49

As shown here, I am generating the pivot table using the apache POI. When the excel sheet is generated with pivot table, I want it to sort the column which is showing the months data in chronological order by month (ascending). Below is the sample code used to generate the pivot table.

XSSFSheet dataSheet
        = workbook.createSheet("DataSheet");
workbook = dataWriteService.writeToSheet(workbook, dataSheet, dataResponseList); //data is written in the sheet
XSSFSheet sheet = workbook.createSheet("PIVOTSHEET");

AreaReference ar = new AreaReference(topLeft, botRight, SpreadsheetVersion.EXCEL2007);
CellReference cr1 = new CellReference("A1");
XSSFPivotTable pivotTable1 = sheet.createPivotTable(ar, cr1, dataSheet);
pivotTable1.addRowLabel(5);
pivotTable1.addRowLabel(12);
pivotTable1.addColLabel(19);
final File xlsFile = new File("dataWithPivot.xlsm");
FileOutputStream fileOutputStream = new FileOutputStream(xlsFile);
workbook.write(fileOutputStream);
    

Below is the sample code I have been trying to sort pivot table by column and not worked.

int indexOfSortColumn = 1;
pivotTable.getCTPivotTableDefinition()
        .getPivotFields()
        .getPivotFieldArray(indexOfSortColumn)
        .setSortType(STFieldSortType.ASCENDING);
//below is the second tried approach and didnot work
CTPivotField fld = pivotTable1.getCTPivotTableDefinition().getPivotFields().getPivotFieldList().get(1);
fld.setSortType(STFieldSortType.ASCENDING);

build.gradle

implementation group: 'org.apache.poi', name: 'poi-ooxml-full', version: '5.2.2'
implementation group: 'org.apache.poi', name: 'ooxml-schemas', version: '1.1'
0 Answers
Related