Aspose 17.5.0 Formula copying issue after sorting in xls

Aspose.Cells Product Family
1

Hi,


We are having issues with Aspose version 17.5.0 while sorting the data in excel (.xls). After sort formulas copying are being shuffled. However, in excel(.xlsx) after sorting formulas copying is working fine. Tested in 17.3.0 version and it is working fine for .xls.

I have attached the after sorting .xls and .xlsx file.

Light green colour : Columns on which sorting has been applied
Orange colour: Columns having formula

Expected Result: Formula columns as in .xlsx file

Code:

class Program
{
static void Main(string[] args)
{
var wb = new Workbook(“AsposeSortFormulaTest_After.xls”);
wb.CalculateFormula();

wb.DataSorter.Order1 = SortOrder.Ascending;
wb.DataSorter.Key1 = wb.Worksheets[0].Cells[“D2”].Column;

wb.DataSorter.Order2 = SortOrder.Ascending;
wb.DataSorter.Key2 = wb.Worksheets[0].Cells[“E2”].Column;

wb.DataSorter.Order3 = SortOrder.Ascending;
wb.DataSorter.Key3 = wb.Worksheets[0].Cells[“C2”].Column;

CellArea ca = new CellArea();
ca.StartRow = 1;
ca.StartColumn = 0;
ca.EndRow = 10;
ca.EndColumn = 9;

wb.DataSorter.Sort(wb.Worksheets[0].Cells, ca);
wb.Save(“AsposeSortFormulaTest_After.xls”);
}
}

is there any fix for this issue that we can apply?

Thanks
Lavi

2

Hi,


Thanks for providing us sample files and sample code.

I have tested your scenario/case a bit using both Aspose.Cells for .NET v17.5.x and v17.3.0, both produce same output. Also, we need your expected XLS file which should be created manually by applying data sorting (Data|Sort menu option) upon your data (in the sheet) in MS Excel, so we could evaluate your issue precisely. By the way, I tried to manually apply data sorting in MS Excel as per your sample code, the output is same as per output file (generated by Aspose.Cells APIs).

Thank you.
3

Hi Amjad,


Thanks for your quick response.

I have attached the unsorted and expected XLS(manually applied sorting as asked), which is supposed to be sorted according to column D,E,C.

Main issue: In column G for cell(2,7) formula is =F2/D27(unsorted.xls) but when applying sorting using Aspose v17.5.x, it is becoming =F3/D37, also cell(8,7) and cell(9,7) is having same formula =F9/D97(see the attchement AsposeSortFormulaTest_After.xls).

Result Expected: Expected XLS (Formula column G,I,J) i.e=F2/D27, and so on


Thanks
Lavi

4

Hi,


Thanks for the sample files and further details.

I am able to reproduce the issue as you mentioned by using your following sample code with your template file “UnsortedFile.xls”. I found that data sorting is not working fine on data set (having formulas) in the worksheet. I have logged a ticket with an id “CELLSNET-45421” for your issue. We will look into it soon.

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

Thank you.
5
Hi,

Thanks for using Aspose.Cells.

This is to inform you that we have fixed your issue CELLSNET-45421 now. We will soon provide the fix after performing QA and including other enhancements and fixes.
6

Thanks a lot for fixing this issue.

7
Hi,

Thanks for using Aspose.Cells.

Please download and try the following latest fix

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

and let us know your feedback.

8

Thanks a lot for the quick fix. Fix is working fine now

9

Hi,


Good to know that your issue is sorted out by the new fix/version. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.
10

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

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