DataSorter.sort results in unusual pattern

I’m trying to sort a range that is 88 rows by 7 columns. I’m sorting alphabetically by column# 7. This is the code that I have:

p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco} p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco; color: #4e9072} span.s1 {color: #7e504f} span.s2 {color: #0326cc} span.s3 {color: #000000} span.Apple-tab-span {white-space:pre}

DataSorter sorter = results.getDataSorter();

sorter.setOrder1(SortOrder.DESCENDING);

sorter.setKey1(6);

sorter.sort(results.getWorksheets().get(4).getCells(),1,14,88,20);


The results I get do not have any consistent pattern. It's not alphabetical and only a subset of the range is actually different then before sorting. What am I missing?

Hi,


Thanks for your posting and using Aspose.Cells.

Please see this thread, it should help you deal with your sorting problem. It has sample code [in C# and Java], sample excel file and screenshot that explains how to make use of sort feature of Aspose.Cells. Let us know your feedback.



Thanks Shakeel. I’m able to use to sorting feature with test data and it works as expected. However, it still doesn’t work with the data that I am trying to sort. Is there a limitation with sorting data that has formulas in it?


I’ve attached the excel sheet I’m trying to sort. I’m sorting the range O2:U89 in the Analysis sheet (sheet#5) by column U alphabetically.

Hi,


Thanks for using Aspose.Cells.

Please try the following code and read its comments. I have attached the output excel file for your reference. As you can see in the output excel file, that column U contents are in ascending order. If you find any problem in output excel file, please feel free to let us know, we will look into it further.

Java
//Load your workbook
Workbook wb = new Workbook(dirPath + “results.xlsx”);

//Perform calculation of formulas by Aspose.Cells.
//It is recommended but if you things are not working, then it is necessary
wb.calculateFormula();

//Access your worksheet
Worksheet ws = wb.getWorksheets().get(“Analysis”);

//Create your cell area
CellArea ca = CellArea.createCellArea(“O2”, “U89”);

//Create your sorter
DataSorter sorter = wb.getDataSorter();

//Find the index, since we want to sort by column U, so we should know
//the index for sorter
int idx = CellsHelper.columnNameToIndex(“U”);

//Add key in sorter, it will sort in Ascending order
sorter.addKey(idx, SortOrder.ASCENDING);

//Perform sort
sorter.sort(ws.getCells(), ca);

//Save the workbook in xlsx format
wb.save(dirPath + “output.xlsx”);

C#
//Load your workbook
Workbook wb = new Workbook(“results.xlsx”);

//Perform calculation of formulas by Aspose.Cells.
//It is recommended but if you things are not working, then it is necessary
wb.CalculateFormula();

//Access your worksheet
Worksheet ws = wb.Worksheets[“Analysis”];

//Create your cell area
CellArea ca = CellArea.CreateCellArea(“O2”, “U89”);

//Create your sorter
DataSorter sorter = wb.DataSorter;

//Find the index, since we want to sort by column U, so we should know
//the index for sorter
int idx = CellsHelper.ColumnNameToIndex(“U”);

//Add key in sorter, it will sort in Ascending order
sorter.AddKey(idx, SortOrder.Ascending);

//Perform sort
sorter.Sort(ws.Cells, ca);

//Save the workbook in xlsx format
wb.Save(“output.xlsx”);

Thanks a lot Shakeel! That worked perfectly. It turns out I did have to use the command: wb.calculateFormula();


I appreciate your quick and helpful responses!
Hi,

Thanks for your feedback and using Aspose.Cells.

It is good to know that your issue is resolved now. Let us know if you encounter any other issue, we will be glad to look into it and help you further.

Please also see the following article that explains the purpose and usage of Workbook.calculateFormula() method.