Get evaluated value when calling CalculateFormula on a single reference cell

Aspose.Cells Product Family
1

Hi,

When I call Worksheet.CalculateFormula, I normally get the evaluated value of the formula I pass in.
Example: If I pass “=A1+A1” to Worksheet.CalculateFormula and the value of A1 is 5, I expect to get 10.

However, I noticed that calling Worksheet.CalculateFormula with a single reference cell returns the referenced cell rather than the value on the cell.
Example: If I pass “=A1” to Worksheet.CalculateFormula and the value of A1 is 5, I get Sheet!A1 instead of 5.

Is there a way for CalculateFormula to always return the evaluated value?

Thanks.

2

@vonH,

It is the expected result for calculating a formula expression directly. For many scenarios of using the calculated result of one formula, the required result is not the direct value(s) but the reference itself. So, for formula like “=A1”, you will get a ReferredArea object which may provide flexible features. If you need to get the ultimate value(s) of this formula, please call GetValue/ GetValues from the returned object.

1 Like
3

Hi,

I noticed that if I have the code below, I get an exception when retrieving the evaluated value for a cell after calling CalculateFormula that references that cell.
…
Worksheet testSheet = workbook.Worksheets[“Test”];
// Put formula on cell A2
testSheet.Cells[“A2”].Formula = “=0+0”;
// Calculate a formula that references cell A2
testSheet.CalculateFormula("=A2", new CalculationOptions());
// Get the value of the cell that was just calculated in the formula. (NOTE: This throws an exception)
double cellValue = testSheet.Cells[“A2”].DoubleValue;

Does this mean that in this case the call to CalculateFormula did not evaluate the referenced cell? If the formula for cell A2 contained other reference cells “A3+A4+A5” instead of “0+0”, does it mean those don’t get computed as well when I call CalculateFormula?

I know that CalculateFormula will return a ReferredArea, and I can call ReferredArea.GetValue(0, 0, true) to force calculation. Isn’t the call to CalculateFormula expected to perform the calculations in this case? Is the call to GetValue required in this scenario?

Thanks.

4

@vonH,

Yes, it is because the calculation of:

testSheet.CalculateFormula("=A2", new CalculationOptions());

returns the reference only and does not require to calculate the reference’s value. If A2 has not been calculated, its value will be null, so you get exception when require it to give a double value.

If your formula is of type “Value”, such as “=0+A2”, then A2 needs to be calculated and after the calculation you can get the expected value for it.

For your situation, for performance consideration, commonly it should be better if you calculate the workbook one time before you calculate other formula expression dynamically. Then you can use:

testSheet.CalculateFormula("=A2", new CalculationOptions(){Recursive = false});

to avoid formulas in the workbook being calculated repeatedly.

1 Like