Issue with sorting data from left to right

Aspose.Cells Product Family
1

Hi Team,

We want to sort our excel from left to right.

I want to sort from row 1 to row 5000 using sort by row 13.
It is sorting all the rows below row 13 but the rows above row 13 remains unsorted.

CODE below:

DataSorter dataSorter =
OutputexcelWorkbookPreview.DataSorter;

dataSorter.SortLeftToRight = true;

dataSorter.Key1 = 12;

dataSorter.Order1 = Aspose.Cells.SortOrder.Ascending;

dataSorter.Key2 = 11;

dataSorter.Order2 = Aspose.Cells.SortOrder.Ascending;

dataSorter.HasHeaders = false;

dataSorter.CaseSensitive = false;

CellArea ca = new CellArea();

ca.StartRow = 0;

ca.StartColumn = 18;

ca.EndRow = 5000;

ca.EndColumn = 34;

dataSorter.Sort(OutputexcelWorkbookPreview.Worksheets[“Output”].Cells, ca);


OutputexcelWorkbookPreview.Save(Server.MapPath(OutputDownloadpath + “/” + “OutputFile_PreviewForTest1.xlsx”));

Could you help with this request?

Regards,
Sumit Agarwal

2

Hi,


Thanks for your posting and using Aspose.Cells.

We have tested this issue with the following sample code and found it is working fine. Please check the excel file used in this code and output pdfs (in attachments) generated by the code for your reference.

Links:



C#

Workbook wb = new Workbook(“sort.xlsx”);
wb.DataSorter.Order1 = SortOrder.Ascending;
wb.DataSorter.SortLeftToRight = true;

Worksheet ws = wb.Worksheets[0];

CellArea ca = CellArea.CreateCellArea(“A1”, “I17”);

for (int i=0; i<7; i++)
{
wb.DataSorter.Key1 = i;
wb.DataSorter.Sort(ws.Cells, ca);

ws.Cells[“K1”].PutValue(“Sorting by Row:” + (i + 1));

PdfSaveOptions opts = new PdfSaveOptions();
opts.OnePagePerSheet = true;

wb.Save(“SortByRow” + (i + 1) + “.pdf”, opts);
}
3

Hi Shakeel,
Thanks for the quick response, its working for the rows if containing values but its not working correctly if there are formulas in the rows. I am sorting with row 13 and there are formulas in that row and there are formulas in the others rows as well. So while sorting with with another row which has same value pasted as in row 13 its making Row 13 also as value pasted.
Could you please help on this.

regards,
Sumit

4

Hi,

Thanks for your posting and using Aspose.Cells.

Please call Workbook.CalculateFormula() method after lading your workbook. It should fix your issue.

Please see the following sample code for your reference.

C#

Workbook wb = new Workbook(“sort.xlsx”);

//Call this method
wb.CalculateFormula()

wb.DataSorter.Order1 = SortOrder.Ascending;
wb.DataSorter.SortLeftToRight = true;

Worksheet ws = wb.Worksheets[0];

CellArea ca = CellArea.CreateCellArea(“A1”, “I17”);

for (int i=0; i<7; i++)
{
wb.DataSorter.Key1 = i;
wb.DataSorter.Sort(ws.Cells, ca);

ws.Cells[“K1”].PutValue(“Sorting by Row:” + (i + 1));

PdfSaveOptions opts = new PdfSaveOptions();
opts.OnePagePerSheet = true;

wb.Save(“SortByRow” + (i + 1) + “.pdf”, opts);
}
5

Hi Shakeel,

I have tried the same as you suggested but I am still having issues in sorting, I am attaching a console application and excel files, please have a look and suggest. Some time formula in first row from T1 to Ak1 get removed and get value pasted and some time it doesn’t sort properly. Its behaving very awkwardly.



regards,

Sumit

6

Hi,


Thanks for your sample project and considering Aspose.Cells.

We are afraid, we could not observe your issue properly. Please highlight your issue with screenshots and red circles. Let us know the difference between MS-Excel and Aspose.Cells sort results in images. This will help us look into your issue more precisely and quickly. Thanks for your cooperation in this regard and have a good day.

7

Hi,



Please have a look on the attached screen shots one is of sorted by excel and there is formula in the T1 of the output sheet in the workbook and formula retains in all cells from T1 to Ak1 while sorting with excel using row 13 and range will be T1:AK5000, please refer attached sheet “sortingwithexcel.xlsx” .



And another screen shot is of output sheet which is sorted by aspose, where there is no formula in the T1 to Ak1, formula of T1 is visible in the screen shot, please refer the “aftersoring.xlsx”(will be created in the root directory once you run the application) for the same.



Please have a look on the attachments and let me know if you still have any confusion.



regards,

Sumit

8

Hi,


Thanks for your posting and using Aspose.Cells.

We were able to observe this issue as per your description. We have tested this issue with the following sample code and found that after sorting with Aspose.Cells, cells values are correct but cells formulas are lost. For example, cell T1 has a formula which is lost but its sorted value is correct.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-45249 - Aspose.Cells sorts fine but formulas are converted to values

I have attached the source excel file used in this code, output excel file generated by it and screenshot showing the issue for a reference.

Workbook wb = new Workbook(“withoutsorting.xlsx”);

wb.CalculateFormula();

wb.DataSorter.Order1 = SortOrder.Ascending;
wb.DataSorter.SortLeftToRight = true;

Worksheet ws = wb.Worksheets[“Output”];

CellArea ca = CellArea.CreateCellArea(“T1”, “AK5000”);

wb.DataSorter.Key1 = 12;
wb.DataSorter.Sort(ws.Cells, ca);

wb.Save(“sortedByCells.xlsx”);
9

Hi,

Could you please confirm when we can expect this fix ?

regards,
Sumit

10

Hi,


We are pleased to inform you that we have fixed your issue “CELLSNET-45249” now. We will soon (hopefully in 2-5 days or so) provide the fix after performing QA and including other enhancements and fixes.

Thank you.
11

thanks Amjad !!

12
Hi,

Thanks for using Aspose.Cells.

Please download and try the following latest fix

Aspose.Cells for .NET v17.3.5 (.NET 2.0) compiled in .NET Framework 2.0.
Aspose.Cells for .NET v17.3.5 (.NET 4.0) compiled in .NET Framework 4.0.

and let us know your feedback.
13

The issues you have found earlier (filed as CELLSNET-45249) have been fixed in Aspose.Cells for .NET 17.4.0.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.