Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Mon Sep 27, 2021 9:00 am

Hello,

Except for the return type: what is the difference between these two code samples?

Code: Select all
CellRange myCell = ...;
string value = myCell.EnvalutedValue;


Code: Select all
CellRange myCell = ...;
object value = myCell.FormulaValue;


The first one "EnvalutedValue" sometimes returns something like "#Recursive reference!" as the calculated value of the formula, whereas the other one "FormulaValue" returns the calculated value without issue.

olefebvre
 
Posts: 13
Joined: Wed May 26, 2021 11:36 am

Mon Sep 27, 2021 10:28 am

Hello,

Thanks for your inquiry!

The difference between the FormulaValue and EnvalutedValue is: the FormulaValue is a static method, it just shows the current value displayed before you read the workbook by our Spire.Xls, while the EnvalutedValue is dynamic, it will show the calculated value that based on the data before you use the method.

For example, when I set the formula in A1 is B1*C1, the value of B1 is 1, the value of C1 is 5, and the current displayed value of A1 is 5.
insheet.png
insheet.png (4.28 KiB) Viewed 2886 times

Then I loaded the workbook and changed the value of B2 to 2 through the code, the result of FormulaValue is still 5, but the value displayed by EnvalutedValue is 10.
difference.png
difference.png (15.99 KiB) Viewed 2886 times


What's more, for the issue of "#Recursive reference!" when use "EnvalutedValue", it most likely depends on the data in your workbook and the operation in your project. To help us reproduce your issue, please provide us with your input file and test code, thanks in advance.

Sincerely,
Marcia
E-iceblue support team
User avatar

Marcia.Zhou
 
Posts: 858
Joined: Wed Nov 04, 2020 2:29 am

Mon Sep 27, 2021 1:42 pm

Ok, I get it now.
Thank you.

About the recursive reference: the issue takes place in a workbook which contains private data unfortunately.

I've managed to isolate the error to an INDEX() method call in Excel linking to a table in another sheet.
However, no issue whatsoever when testing the exact same case with the same values in a blank Excel workbook created by hand.

The original workbook has been converted from XLS to XLSX some time ago, so I guess the error originates from a leftover of the conversion.

Anyway, using the "FormulaValue" is perfectly enough for my use case because I only read the workbook, so that works for me.

Thank you again.

olefebvre
 
Posts: 13
Joined: Wed May 26, 2021 11:36 am

Tue Sep 28, 2021 1:14 am

Hello,

You are welcome, and thanks for your feedback!

Okay, if you have other questions about using Spire.XLS in the future, please feel free to contact us.

Sincerely,
Marcia
E-iceblue support team
User avatar

Marcia.Zhou
 
Posts: 858
Joined: Wed Nov 04, 2020 2:29 am

Return to Spire.XLS