Free Support Forum - aspose.com

Calculation error when using calculation chain and defined name

Hello,

We found that in some cases, Workbook.CalculateFormula() does not produce the correct value when define name evaluates to a value (not a range) and calculation chain is in use. I have created an example attached to repeat the problem.

Test.zip (8.8 KB)

In the attached Excel Test.xlsx, cell A2 shows the value of the name “Returns”, while cell A3 contains the formula of “Returns”. This means A2 = A3 because A3’s formula is exactly the same as “Returns” formula.

However, as the code below demonstrates, A2 is not the same as A3 when calculation chain is used. I’ve tested this on Aspose.Cells (.NET Framework 4.7) version 18.4.0.0. Can you please have a look?

var workbook = new Workbook(@"C:\Temp\Test.xlsx");
workbook.Settings.CreateCalcChain = true;

workbook.CalculateFormula();
var cells1 = workbook.Worksheets["Sheet1"].Cells;
var cellsf = workbook.Worksheets["Factors"].Cells;

Console.WriteLine(">> Before Factor Change <<");

Console.WriteLine("A2 Returns (name reference): {0}", cells1["A2"].Value);
Console.WriteLine("B2 Returns (direct formula): {0}", cells1["B2"].Value);

// Change factor.
cellsf["C2"].PutValue(100.0);
workbook.CalculateFormula();

Console.WriteLine(">> After Factor Change <<");

Console.WriteLine("A2 Returns (name reference): {0}", cells1["A2"].Value);
Console.WriteLine("B2 Returns (direct formula): {0}", cells1["B2"].Value);

// Remove calculation chain.
workbook.Settings.CreateCalcChain = false;
workbook.CalculateFormula();

Console.WriteLine(">> After Factor Change No Calculation Chain <<");

Console.WriteLine("A2 Returns (name reference): {0}", cells1["A2"].Value);
Console.WriteLine("B2 Returns (direct formula): {0}", cells1["B2"].Value);

The console output from running the above code is:

>> Before Factor Change <<
A2 Returns (name reference): 0.0897984431137725
B2 Returns (direct formula): 0.0897984431137725
>> After Factor Change <<
A2 Returns (name reference): 0.0897984431137725
B2 Returns (direct formula): 0.0789287221095335
>> After Factor Change No Calculation Chain <<
A2 Returns (name reference): 0.0789287221095335
B2 Returns (direct formula): 0.0789287221095335

After factor change and calculation chain is still true, A2 and B2 are not the same, which shouldn’t be the case. Because when calculation chain is turned off, they are the same.

Thanks for your help.

@llawryy

Thanks for using Aspose APIs.

We were able to observe this issue as per your code, sample Excel file and issue description. We have logged the issue in our database for investigation and for a fix. Once, the issue is resolved or we have some other news for you, we will update you asap.

This issue has been logged as

  • CELLSNET-46096 - Calculation Error when using Calculation Chain and Defined Name

@llawryy

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

The issues you have found earlier (filed as CELLSNET-46096) have been fixed in this update. Please also check the document/article for your reference: https://docs.aspose.com/display/cellsnet/Installation

The issues you have found earlier (filed as ) have been fixed in this update. This message was posted using BugNotificationTool from Downloads module by MuzammilKhan