Data sorting involving a formula in Excel worksheet using C#.NET

Aspose.Cells Product Family
1

Having trouble sorting a range where the sort key is a formula (result).


No error produced, it just doesn’t do the sort. I confirmed the range and sort code works by changing the key to another column; it sorted.

I am creating the formula using .Formula = "=MYFORUMLAHERE"

Do I have to evaluate the formulas or mark them .IsFormula = true first to get the sort to work?

Jeff



2

Hi,


Thanks for providing us some details.

Could you provide us sample code (runnable) and template file to reproduce the issue on our end, we will check it soon.

Thank you.
3
// Workbook object
Workbook wb = new Workbook(FileFormatType.Xlsx);
Worksheet sheet = wb.Worksheets[0];

// Populate test data
Random rnd = new Random();
for (int x = 0; x < 10; x++)
{
for (int y = 0; y < 10; y++)
{
sheet.Cells[x, y].PutValue(rnd.Next(1, 100));
}
string Formula = "=SUM(A" + (x+1).ToString() + ":" + "J" + (x+1).ToString() + ")";
sheet.Cells[x,10].Formula = Formula;
}

//Obtain the DataSorter object in the workbook
DataSorter sorter = wb.DataSorter;

//Set the first order
sorter.Order1 = Aspose.Cells.SortOrder.Descending;
sorter.Key1 = 10;

//Create a cells area (range).
CellArea ca = new CellArea();

ca.StartRow = 0;
ca.StartColumn = 0;
ca.EndRow = 9;
ca.EndColumn = 10;

//Sort data in the specified data range
sorter.Sort(sheet.Cells, ca);

// Save it
wb.Save("C:\\Test.xlsx", SaveFormat.Xlsx);

4

Hi,

Thanks for your posting and using Aspose.Cells.

You need to call Workbook.CalculateFormula() before sorting so that before sorting is applied on your data, all formulas are calculated.

Please see the following sample code that fixes this issue. I have highlighted the changes in red color.

C#


// Workbook object

Workbook wb = new Workbook(FileFormatType.Xlsx);

Worksheet sheet = wb.Worksheets[0];


// Populate test data

Random rnd = new Random();

for (int x = 0; x < 10; x++)

{

for (int y = 0; y < 10; y++)

{

sheet.Cells[x, y].PutValue(rnd.Next(1, 100));

}

string Formula = “=SUM(A” + (x + 1).ToString() + “:” + “J” + (x + 1).ToString() + “)”;

sheet.Cells[x, 10].Formula = Formula;

}


wb.CalculateFormula();


//Obtain the DataSorter object in the workbook

DataSorter sorter = wb.DataSorter;


//Set the first order

sorter.Order1 = Aspose.Cells.SortOrder.Descending;

sorter.Key1 = 10;


//Create a cells area (range).

CellArea ca = new CellArea();


ca.StartRow = 0;

ca.StartColumn = 0;

ca.EndRow = 9;

ca.EndColumn = 10;


//Sort data in the specified data range

sorter.Sort(sheet.Cells, ca);


// Save it

wb.Save(“Test1.xlsx”, SaveFormat.Xlsx);
5

Thank you


6

Hi,


Thanks for your feedback.

Good to know that by calculating the formulas before sorting fixes you issue now. 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.