It appears that Aspose.Cells for Java sorting of PivotTable row values is different than Excel’s. Can you confirm that this is true?
I’m creating a PivotTable with sorted Status rows:
pivotTable.addFieldToArea(PivotFieldType.ROW, “StatusText”);
PivotField rowField = pivotTable.getRowFields().get(0);
rowField.setAutoSort(true);
rowField.setAscendSort(true);
I’m trying to get the position of status rows (to use in formulas below the Pivot Table). I can see programmatically by getting cell values that, for example, 2 of the statuses are sorted:
Last BEAR Paid
Last BEAR certified
When I open the Excel file they are sorted (as expected):
Last BEAR certified
Last BEAR Paid
Also, Aspose.Cells sorts “[” below alphabet characters while Excel sorts it above. (Possibly Aspose.Cells sorts all symbols below letters? I haven’t tested it.)
If this is true, is there a way I can manually arrange PivotTable rows using Aspose.Cells?
Thank you.
@wweippertcentraled
It appears that you are experiencing differences in the sorting behavior of PivotTable row values between Aspose.Cells and Excel. This can happen due to the way Aspose.Cells handles sorting, particularly with special characters and symbols, which may be sorted differently than in Excel.
To manually arrange PivotTable rows in Aspose.Cells, you can use the AutoSort and AscendSort properties of the PivotField class. Here’s a code snippet that demonstrates how to set up a PivotTable with custom sorting:
using Aspose.Cells;
using Aspose.Cells.Pivot;
class Program
{
static void Main()
{
// Load the workbook
Workbook workbook = new Workbook("YourFile.xlsx");
Worksheet worksheet = workbook.Worksheets[0];
PivotTable pivotTable = worksheet.PivotTables[0];
// Set up the PivotTable
pivotTable.AddFieldToArea(PivotFieldType.Row, "StatusText");
PivotField rowField = pivotTable.RowFields[0];
// Enable auto sorting
rowField.IsAutoSort = true;
rowField.IsAscendSort = true; // Set to false for descending order
// Refresh and calculate the PivotTable
pivotTable.RefreshData();
pivotTable.CalculateData();
// Save the workbook
workbook.Save("SortedPivotTable.xlsx");
}
}
In this example, the IsAutoSort property is set to true to enable automatic sorting, and IsAscendSort is set to true for ascending order. You can adjust these settings based on your requirements.
If you find that the sorting still does not match Excel’s behavior, you may need to implement a custom sorting logic by manipulating the data before it is added to the PivotTable or by using additional sorting methods provided by Aspose.Cells.
For more detailed guidance on custom sorting in PivotTables, you can refer to the official documentation on this topic here [1].
Sources:
[1]: Custom sorting in Pivot Table - Aspose Documentation
@wweippertcentraled,
Aspose.Cells sorts row values of the pivot table in the same way as MS Excel does. Could you please share a sample standalone Java program/application with template Excel file(s) to demonstrate the issue/difference, we will check your issue soon.
@wweippertcentraled
Create a sample file based on the provided information. First, create a pivot table in Excel. Then, use the Aspose.Cells apis to create the pivot table. Finally, calculate all pivot tables and compare the data. By testing on the latest version v25.2 using the following sample code, we can obtain the same results as Excel. Please check the attachment. result.zip (21.0 KB)
Workbook workbook = new Workbook(filePath + "Test.xlsx");
Worksheet sheet = workbook.Worksheets[0];
Cell cell = sheet.Cells["E11"];
Console.WriteLine("E11: " + cell.StringValue);
cell = sheet.Cells["E12"];
Console.WriteLine("E12: " + cell.StringValue);
int index = sheet.PivotTables.Add("=Sheet1!A1:B5", "E20", "NEWPivot");
PivotTable pivotTable = sheet.PivotTables[index];
pivotTable.PivotTableStyleType = PivotTableStyleType.PivotTableStyleLight16;
pivotTable.AddFieldToArea(PivotFieldType.Row, "StatusText");
pivotTable.AddFieldToArea(PivotFieldType.Data, "Data");
PivotField rowField = pivotTable.RowFields[0];
rowField.IsAutoSort = true;
rowField.IsAscendSort = true;
sheet.RefreshPivotTables();
Console.WriteLine("after refresh and calculate pivottables");
cell = sheet.Cells["E11"];
Console.WriteLine("E11: " + cell.StringValue);
cell = sheet.Cells["E12"];
Console.WriteLine("E12: " + cell.StringValue);
cell = sheet.Cells["E22"];
Console.WriteLine("E22: " + cell.StringValue);
cell = sheet.Cells["E23"];
Console.WriteLine("E23: " + cell.StringValue);
workbook.Save(filePath + "out_net.xlsx");
The output:
E11: Last BEAR certified
E12: Last BEAR Paid
after refresh and calculate pivottables
E11: Last BEAR certified
E12: Last BEAR Paid
E22: Last BEAR certified
E23: Last BEAR Paid
If you still have questions, please provide sample files and runnable test code, and we will check them soon.
Thank you John. I updated to the latest version v25.2. I converted your code to Java and it’s not sorting correctly for me. My output:
E11: Last BEAR certified
E12: Last BEAR Paid
after refresh and calculate pivottables
E11: Last BEAR Paid
E12: Last BEAR certified
E22: Last BEAR Paid
E23: Last BEAR certified
How can I debug this further? Does your sort code use common Java utility functions I can test?
Thank you.
@wweippertcentraled
System.out.println("Last BEAR Paid".compareTo("Last BEAR certified"));
System.out.println("a".compareTo("b"));
System.out.println("Paid".compareTo("certified"));
Workbook workbook = new Workbook(dir + "Test.xlsx");
Worksheet sheet = workbook.getWorksheets().get(0);
int index = sheet.getPivotTables().add("=Sheet1!A1:B5", "E20", "NEWPivot");
PivotTable pivotTable = sheet.getPivotTables().get(index);
pivotTable.setPivotTableStyleType ( PivotTableStyleType.PIVOT_TABLE_STYLE_LIGHT_16);
pivotTable.addFieldToArea(PivotFieldType.ROW, "StatusText");
pivotTable.addFieldToArea(PivotFieldType.DATA, "Data");
PivotField rowField = pivotTable.getRowFields().get(0);
rowField.setAutoSort(true);
rowField.setAscendSort ( true);
sheet.refreshPivotTables();
workbook.save(dir + "dest.html");
workbook.save(dir + "dest.xlsx");
We have reproduced your issue . From a coding perspective, “P” < “c” is also reasonable in java.
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSJAVA-46298
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
Thank you. Also, please be aware that it also needs to sort symbols like “[” like Excel does.
@wweippertcentraled,
Thanks for sharing further details.
We have noted it down. We will also evaluate it when fixing the row sorting issue.
@wweippertcentraled
Could you share a sample file to show this issue ?
Aspose.Cells Java sorts differently than Excel sort order. In the attached file I used Aspose.Cells to create columns A, B, C, and E. I sorted B using DataSorter. I manually sorted column E in Excel.
Java code:
private void testForAsposeSupport(String tempfolder) {
try {
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);
Cells cells = worksheet.getCells();
cells.get(0, 0).setValue("UNSORTED Char");
cells.get(0, 1).setValue("Char sorted by Aspose.Cells");
cells.get(0, 2).setValue("Dec Value");
cells.get(0, 4).setValue("Sort this list in Excel");
int row = 1;
for (int i = 33; i <= 126; i++) {
String c = Character.toString((char)i);
cells.get(row, 0).setValue(c); // "UNSORTED Char"
cells.get(row, 1).setValue(c); // "Char sorted by Aspose.Cells"
cells.get(row, 2).setValue(i); // "Dec Value"
cells.get(row, 4).setValue(c); // "Sort this list in Excel"
row++;
}
// sort column B (and C)
DataSorter sorter = workbook.getDataSorter();
sorter.setOrder1(SortOrder.ASCENDING);
sorter.setKey1(1);
CellArea cellArea = new CellArea();
cellArea.StartRow = 1;
cellArea.EndRow = 94;
cellArea.StartColumn = 1;
cellArea.EndColumn = 2;
sorter.sort(cells, cellArea);
workbook.save(tempfolder + "Test2ForAsposeSupport.xlsx");
} catch (Exception ex) {
ex.printStackTrace();
}
}
Test2ForAsposeSupport.zip (9.3 KB)
@wweippertcentraled
By testing with sample code on the latest version v25.2, we can reproduce the issue. The sorting result is inconsistent with Excel when using DataSorter to sort the data.
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSJAVA-46313
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
@wweippertcentraled
Thank you for the example code to demonstrate the issue. Sorting data is complex especially when considering different locales and special characters.
For those characters in your sample, different locale may sort them differently, and sorting them by java by default gives another different result than ms excel.
To provide better solution for those unknown region and features to us, we provide mechanism for user to custom the sorting provider. There are two methods of GlobalizationSettings used for this purpose:
compare(String v1, String v2, boolean ignoreCase) and getCollationKey(String v, boolean ignoreCase)
User may implement the sorting feature of their own by overriding those methods. For example, you may use icu library to sort data which process globalization features better than java’s default implementation. Here is the code example:
Workbook workbook = ...;
...
final com.ibm.icu.text.Collator ciCaseIgnore = com.ibm.icu.text.Collator.getInstance();
ciCaseIgnore.setStrength(com.ibm.icu.text.Collator.PRIMARY);
final com.ibm.icu.text.Collator ciCaseSensitive = com.ibm.icu.text.Collator.getInstance();
ciCaseSensitive.setStrength(com.ibm.icu.text.Collator.TERTIARY);
workbook.getSettings().setGlobalizationSettings(new GlobalizationSettings()
{
@Override
public Comparable getCollationKey(String s, boolean ignoreCase)
{
return (ignoreCase ? ciCaseIgnore : ciCaseSensitive).getCollationKey(s);
}
@Override
public int compare(String x, String y, boolean ignoreCase)
{
return (ignoreCase ? ciCaseIgnore : ciCaseSensitive).compare(x, y);
}
});
DataSorter sorter = wb.getDataSorter();
...
sorter.sort(...);
However, by our test, even icu cannot give the exactly same result with ms excel. Currently we have no idea how to produce the same result with ms excel. Maybe you can test it at your end to check whether you can get better result because such kind of issue also may be locale dependent. And you also may make some changes for the example code to see whether the result can be improved further.
John.He and johnson.shi,
Thank you. simon.zhao had asked for a sample file. I assumed that DataSorter and PivotTable sorting is using the same logic and it was easier to use DataSorter to test and show with all alphanumeric characters.
Since there is a known set of statuses in the Pivot Table I’ve created, maybe I can put them in the right order myself: In Excel a user can manually drag rows. Can I do that programmatically using Aspose.Cells? Can you link to or create sample code? Thank you.
@wweippertcentraled,
We will thorougly assess your needs/requirements and provide you with detailed information, tailored samples (if possible) and our insights.
@wweippertcentraled
Please refer to the following example code to move pivot items using PivotItem.Move method. The sample code moves the first PivotItem down one position, please refer to the attachment. result.png (40.7 KB)
// Instantiating a Workbook object
Workbook workbook = new Workbook();
// Obtaining the reference of the newly added worksheet
Worksheet sheet = workbook.Worksheets[0];
Cells cells = sheet.Cells;
// Setting the value to the cells
Cell cell = cells["A1"];
cell.PutValue("Sport");
cell = cells["B1"];
cell.PutValue("Quarter");
cell = cells["C1"];
cell.PutValue("Sales");
cell = cells["A2"];
cell.PutValue("Golf");
cell = cells["A3"];
cell.PutValue("Golf");
cell = cells["A4"];
cell.PutValue("Tennis");
cell = cells["A5"];
cell.PutValue("Football");
cell = cells["A6"];
cell.PutValue("Football");
cell = cells["A7"];
cell.PutValue("Tennis");
cell = cells["A8"];
cell.PutValue("Golf");
cell = cells["B2"];
cell.PutValue("Qtr3");
cell = cells["B3"];
cell.PutValue("Qtr4");
cell = cells["B4"];
cell.PutValue("Qtr3");
cell = cells["B5"];
cell.PutValue("Qtr4");
cell = cells["B6"];
cell.PutValue("Qtr3");
cell = cells["B7"];
cell.PutValue("Qtr4");
cell = cells["B8"];
cell.PutValue("Qtr3");
cell = cells["C2"];
cell.PutValue(1500);
cell = cells["C3"];
cell.PutValue(2000);
cell = cells["C4"];
cell.PutValue(600);
cell = cells["C5"];
cell.PutValue(1500);
cell = cells["C6"];
cell.PutValue(4070);
cell = cells["C7"];
cell.PutValue(5000);
cell = cells["C8"];
cell.PutValue(6430);
Aspose.Cells.Pivot.PivotTableCollection pivotTables = sheet.PivotTables;
// Adding a PivotTable to the worksheet
int index = pivotTables.Add("=A1:C8", "E3", "PivotTable2");
// Accessing the instance of the newly added PivotTable
Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];
// Unshowing grand totals for rows.
pivotTable.ShowRowGrandTotals = false;
// Draging the first field to the row area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 0);
// Draging the second field to the column area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 1);
// Draging the third field to the data area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 2);
sheet.RefreshPivotTables();
// Saving the Excel file
workbook.Save(filePath + "out_net.xlsx");
//move pivot item
pivotTable.RowFields[0].PivotItems[0].Move(1, true);
sheet.RefreshPivotTables();
workbook.Save(filePath + "out_move.xlsx");
Hope helps a bit.