Data sorting in Pivot Table using "Sort by Value" in Excel

Aspose.Cells Product Family
1

Hi,

I am looking to implement Data sorting in pivot table as Excel supports it. But i didn’t get proper methods in Aspose Cells for Java. I have tried by using methods of PivotField Object. I have given sample lines of code that i tried and attached the screenshots of Excel on how to do it manually.Could you please suggest anything?

            pivotFields = pivotTable.getDataFields();
            for (int pIndex = 0; pIndex < pivotFields.getCount(); pIndex++) {
                PivotField pivotField = pivotFields.get(pIndex);
                 pivotField.setAutoSortField(-1);
                //                    pivotField.setAscendSort(true);

               // pivotField.setAutoSort(false);
             }

sort_by_value.png (101.5 KB)
sort_by_value_2.png (108.7 KB)

Thanks,
Sukesh

2

@sukesh.kotian.eistec,

Thanks for the screenshots and sample code segment.

Could you try the following sample code if it works for your needs:
e.g
Sample code:

pivotFields = pivotTable.getRowFields();
PivotField pivotField = pivotFields.get(0);
pivotField.setAutoSort(true);
pivotField.setAscendSort(true);//Ascending.
pivotField.setAutoSortField(0);//First data field

If you still find the issue, kindly give us your output Excel file by Aspose.Cells APIs and your expected file in which you have updated the PivotTable as per your needs in MS Excel manually. We will check it soon.

Thank you.

3

Hi,

Thank you for quick response. I have tried the code given by you. But it is not working as expected.
I have attached excel file(output file) and runnable java file with the code segment given by you.
I have attached two more sheets manually in the excel file. These sheets are the actual outputs that i’m expecting through aspose configuration.
In attached Excel file,
First Sheet contains Data.
Second Sheet(PivotTable) contains pivot Table with data sorted by the code segment given by you.
Third Sheet(Sort Asc Top to Bottom) contains Pivot Table with Data Sorted by using Sort options(Smallest to Largest) and Sort direction(Top to Bottom) given by MS-Excel.
Forth Sheet(Sort Asc Left to Right) contains Pivot Table with Data Sorted by using Sort options(Smallest to Largest) and Sort direction(Left to Right) given by MS-Excel.

Sort By Value.zip (8.8 KB)

Thanks,
Sukesh

4

@sukesh.kotian.eistec,

Thanks for the template file and sample code.

I have played upon different combinations to sort out your specific data but I could not implement it. Well, since your requirement is to customize sorting based on some specific data values in the Pivot Table report, so you might not achieve it directly via PivotTable sorting attributes/options. I think, you have to first create your PivotTable via Aspose.Cells APIs, then you got to refresh PivotTable with its data (i.e., use PivoTable.refreshData() and PivotTable.calculateData(), etc.), so Aspose.Cells should render the PivotTable report into the worksheet cells. Now use MS Excel’s data sorting feature (see the document for your reference: Data Sorting|Documentation) to sort your desired area (using top to bottom or left to right options) accordingly via Aspose.Cells APIs. There might be other workaround to move specific PivotItems accordingly. Both techniques are not simple because you first need to find out which area or part of area (row or column) to be sorted out.

I will exercise a few things and need to discuss with the concerned developer.

We will get back to you soon.

Thank you.

5

@sukesh.kotian.eistec,

After further investigation, we found we don’t have relevant APIs to do your desired custom sorting as per template file. The PivotTable in your file not only sorts by DataField, but also sorts by the values in selected column too. We need to add relevant APIs to implement the feature (if possible). I have logged a ticket with an id “CELLSJAVA-42339” for your requested feature.Our concerned developer will look into it soon.

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

Thank you.

6

Hi @Amjad_Sahi

Any update on this issue?

7

@sukesh.kotian,

I am afraid, your issue is not sorted out yet. I have recorded your concerns against your issue into our database. Hopefully we will get back to you soon with latest updates or ETA for your issue.

Once we have any new information, we will share it with you.

8

@sukesh.kotian,

Please try our latest version/fix: Aspose.Cells for Java v18.5.1:

Please try the following sample code for your reference. We have created the expected PivotTables in “source.xlsx” (attached) manually. And then we create the PivotTables via Aspose.Cells APIs, you can discover that they have the same results.
e.g
Sample code:

Workbook workbook = new Workbook(filePath + "source.xlsx"); 
int sheetIndex = workbook.getWorksheets().add(); 
Worksheet tempSheet = workbook.getWorksheets().get(sheetIndex); 
tempSheet.setName("Sort Asc Top to Bottom By API"); 
PivotTableCollection pivotTables = tempSheet.getPivotTables(); 
// Adding a PivotTable to the worksheet 
int index = pivotTables.add("=Data!A1:F30", "B3", "SortAscTopToBottom"); 
// Accessing the instance of the newly added PivotTable 
PivotTable pivotTable = pivotTables.get(index); 
pivotTable.addFieldToArea(PivotFieldType.ROW, 0); //Employee 
pivotTable.addFieldToArea(PivotFieldType.COLUMN, 2); // Product 
pivotTable.addFieldToArea(PivotFieldType.DATA, 5); //Sale 

PivotFieldCollection rowFields = pivotTable.getRowFields(); 
PivotField rowField = rowFields.get(0); 
pivotTable.setRowHeaderCaption(rowField.getName()); 
rowField.setAutoSubtotals(false); 
rowField.setAutoSort(true); 
rowField.setAscendSort(true);//Ascending. 
rowField.setAutoSortField(0);//First data field 

PivotFieldCollection columnFields = pivotTable.getColumnFields(); 
PivotField columnField = columnFields.get(0); 
pivotTable.setColumnHeaderCaption(columnField.getName()); 
columnField.setAutoSubtotals(false); 
columnField.setAutoSort(true); 
columnField.setAscendSort(true);//Ascending. 


sheetIndex = workbook.getWorksheets().add(); 
tempSheet = workbook.getWorksheets().get(sheetIndex); 
tempSheet.setName("Sort Asc Left to Right By API"); 

pivotTables = tempSheet.getPivotTables(); 
// Adding a PivotTable to the worksheet 
index = pivotTables.add("=Data!A1:F30", "B3", "SortAscLeftToRight"); 
// Accessing the instance of the newly added PivotTable 
pivotTable = pivotTables.get(index); 
pivotTable.addFieldToArea(PivotFieldType.ROW, 0); //Employee 
pivotTable.addFieldToArea(PivotFieldType.COLUMN, 2); // Product 
pivotTable.addFieldToArea(PivotFieldType.DATA, 5); //Sale 

columnFields = pivotTable.getColumnFields(); 
columnField = columnFields.get(0); 
pivotTable.setColumnHeaderCaption(columnField.getName()); 
columnField.setAutoSubtotals(false); 
columnField.setAutoSort(true); 
columnField.setAscendSort(true);//Ascending. 
columnField.setAutoSortField(0);//First data field 

rowFields = pivotTable.getRowFields(); 
rowField = rowFields.get(0); 
pivotTable.setRowHeaderCaption(rowField.getName()); 
rowField.setAutoSubtotals(false); 
rowField.setAutoSort(true); 
rowField.setAscendSort(true);//Ascending. 

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

files1.zip (30.4 KB)

Hope, this helps a bit.

9

The issues you have found earlier (filed as CELLSJAVA-42339) have been fixed in Aspose.Cells for Java 18.6. Please also see the document for your reference: Installation|Documentation