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.