Datasorter does not update formulas in dependent cells

Aspose.Cells Product Family
1

Hello,

Using the example code and spreadsheet below you can see that the call to DataSorter.Sort does not update dependent cell references. For example:
Cell A2 contains the value 5.
Cell D2 has the formula “=A2”
The sort operation moves cell A2 to A4
Cell D2 remains with the formula “=A2”, however it would have been expected to be updated to "=A4"

        [Test]
        public void foo28()
        {
            var filename = @"SortWithReferences.xlsx";

            var workbook = new Workbook(filename);
            var worksheet = workbook.Worksheets["Sheet1"];

            var range = worksheet.Cells.CreateRange("A2:A4");

            workbook.DataSorter.Order1 = SortOrder.Ascending;
            workbook.DataSorter.Key1 = range.FirstColumn + 0;

            workbook.DataSorter.Sort(worksheet.Cells, new CellArea
            {
                StartRow = range.FirstRow,
                StartColumn = range.FirstColumn,
                EndRow = range.FirstRow + range.RowCount - 1,
                EndColumn = range.FirstColumn + range.ColumnCount - 1
            });

            workbook.Save(@"C:\rwtemp\SortWithReferences_output.xlsx");
        }

SortWithReferences.zip (7.0 KB)

Thank You,
-Andy

2

One addition to this issue. Named ranges are also not moved with the cells when they are sorted. We had developed a workaround to update the named ranges refers to manually using the resulting int[] from the Sort method.

3

@weissa,

Please add a line to your code segment, it will work fine:

var filename = @“SortWithReferences.xlsx”;
var workbook = new Workbook(filename);
var worksheet = workbook.Worksheets[“Sheet1”];

var range = worksheet.Cells.CreateRange(“A2:A4”);

workbook.DataSorter.Order1 = SortOrder.Ascending;
workbook.DataSorter.Key1 = range.FirstColumn + 0;

workbook.DataSorter.Sort(worksheet.Cells, new CellArea
{
StartRow = range.FirstRow,
StartColumn = range.FirstColumn,
EndRow = range.FirstRow + range.RowCount - 1,
EndColumn = range.FirstColumn + range.ColumnCount - 1
});

workbook.CalculateFormula();

workbook.Save(@“C:\rwtemp\SortWithReferences_output.xlsx”);

Let us know if you still find any issue, we will check it soon.

4

@amjad.sahi,
Thank you for the quick response, however the calculation is not the issue. The issue is the formula references are not updated. By way of example, if you insert a row using aspose, any cells that move will have their dependent cell formulas updated to reference the new cell location. Likewise the named ranges tied to those cells would also have their RefersTo updated. When using the DataSorter, these formula references are not updated.
Thank You,
-Andy

5

@weissa,

Please perform the task (as per your sample code) manually in MS Excel using the Data|Sort menu command. You will notice that it works the same way as Aspose.Cells. If you still believe there is an issue and that Aspose.Cells works differently than MS Excel, kindly provide us with more details, sample files (including the expected file created by the data sorting tool of MS Excel), and some screenshots to demonstrate the issue. We will investigate this soon.

6

@amjad.sahi,

You are correct, this is consistent with MS Excel’s support. We’ll need to consider how we want to address this for our specific needs. Thank again for your support.
-Andy

7

@weissa,

You are welcome.

Should you have further queries or issue, feel free to write us back.