Sorting does not work on data field in pivot table

Aspose.Cells Product Family
1

Hi team,

I am using the licensed version of Aspose-Cells 18.10 with JAVA 1.8 for one of the projects in my organization. One of the requirements I have is to sort data fields in a pivot table.
I went through this thread and it was mentioned that this issue has been fixed in Aspose-Cells 19.11. I switched to 19.11 but the issue is still there.

Please find the sample code and input file attached. I am trying to sort the pivot table based on the 3rd data field named ‘Sum of TOTAL PRICE’ (with index 2) in descending order. I was easily able to achieve this using Excel. I have also attached a copy of the expected output sorted using Excel.Sorting data samples.zip (30.3 KB)

Please provide a fix for this since it has been a blocker for a really long time.

    String pathToInputFile = "Enter file path here";
    String pathToSaveFile = "Enter save location here";
    Workbook workbook = new Workbook(pathToInputFile );
    Worksheet worksheet = workbook.getWorksheets().get( "PivotTable" );
	
    PivotTable tbl = worksheet.getPivotTables().get( 0 );
	tbl.calculateData();
	
	PivotField sortField = tbl.getDataFields().get( 2 );
	sortField.setAutoSort( true );
	sortField.setAscendSort( false );
	sortField.setAutoSortField( -1 );
	
	
	tbl.refreshData();
	workbook.save( pathToSaveFile  );
2

@Abhishek.Majumdar32,

Thanks for the template file, expected file, sample code and details.

After an initial test, I am able to reproduce the issue as you mentioned by using your template file and sample code with latest version/fix (e.g Aspose.Cells for Java v20.1.x). I found data sorting is not working on data field in the pivot table. I even tried using the following lines of code (to try to sort row field with respect to third data field) but to no avail:
e.g
Sample code:

.......
tbl.getRowFields().get(0).setAutoSort(true);
tbl.getRowFields().get(0).setAscendSort(false);

//Setting the auto show using field(data field). 
tbl.getRowFields().get(0).setAutoShowField(2);
....

I have logged a ticket with an id “CELLSJAVA-43101” for your issue. We will look into it soon.

Once we have an update on it, we will let you know.

3

@Abhishek.Majumdar32,

As per the expected file you need to do sorting PivotField named “ITEM” via DataField named “Sum of TOTAL PRICE”. So you can try to use the following code to implement it:

JAVA:

Workbook workbook = new Workbook(filePath + "Unsorted pivot table - input.xlsx");
Worksheet worksheet = workbook.getWorksheets().get( "PivotTable" );

PivotTable tbl = worksheet.getPivotTables().get( 0 );

PivotField sortField = tbl.getRowFields().get(1);
sortField.setAutoSort( true );
sortField.setAscendSort( false );
sortField.setAutoSortField(2);

tbl.refreshData();
tbl.calculateData();

workbook.save(filePath + "out_java.xlsx");

Can you try it?

Let us know your results.