This project has moved. For the latest updates, please go here.

Using ValueCached to obtain result of a formula

Apr 26, 2012 at 8:38 AM

I understand from this piece of FAQ that ClosedXML allows me to get the result of a formula by using ValueCached. However, after doing the following:

ws.Cell(row, col).FormulaA1 = formula;
var val = ws.Cell(row, col).ValueCached;

my val is null!
How can I go about getting my calculated value of the formula? Do I need to wait some? Is there any better way other than ValueCached?

Thanks a bunch. :) 

Coordinator
Apr 26, 2012 at 5:41 PM

You read wrong. From the FAQ:

ClosedXML doesn't resolve formulas so the next best thing is to allow you to get the value calculated by Excel (ValueCached). Since the user can turn off automatic calculation of formulas the ValueCached property is on a "use at your own risk" basis. In other words, if you don't know for sure that the formulas have been calculated by Excel then don't use ValueCached. In this case you won't have the formula results.

May 1, 2012 at 4:51 PM

Hey mdeleon, thanks for the reply.

May I know how I can use ValueCached to get the value calculated by Excel?

Coordinator
May 1, 2012 at 5:07 PM

var val = ws.Cell(row, col).ValueCached;

May 1, 2012 at 5:10 PM

Thanks for the prompt reply...

In this case I get a null value. However, when I open it up, my calculated values are displayed automatically.
I have also set workbook.CalculateMode = XLCalculateMode.Auto;.

It seems like automatic calculation is turned on, but I am missing out on something. Am I doing anything wrong here?
Sorry for the trouble, and thanks for the help. 

Coordinator
May 1, 2012 at 5:22 PM

All this is answered in the FAQ paragraph:

ClosedXML doesn't resolve formulas so the next best thing is to allow you to get the value calculated by Excel (ValueCached). Since the user can turn off automatic calculation of formulas the ValueCached property is on a "use at your own risk" basis. In other words, if you don't know for sure that the formulas have been calculated by Excel then don't use ValueCached. In this case you won't have the formula results.

I'll try to paraphrase a little bit...

ClosedXML doesn't calculate the results from the formulas. It can only give you the results which the Microsoft Excel application saves when the user saves a file. When you add a formula using ClosedXML the ValueCached is not set because this is something the Excel application does.

May 1, 2012 at 6:07 PM

On a sidenote, is there any way I can find out the cause for "Excel found unreadable content in 'target.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.".

The following is done:
Removed Records: Formula from /xl/worksheets/sheet.xml part
Removed Records: Formula from /xl/worksheets/sheet.xml partRemoved Records: Formula from /xl/calcChain.xml part (Calculation properties)

and the file opens up perfectly fine, with all the values displayed nicely.
Is using FormulaA1 illegal for setting formulae with named cells in them?

Coordinator
May 1, 2012 at 6:32 PM

The formula may be in an incorrect format. What's the formula you're using?

May 4, 2012 at 5:30 AM
Edited May 4, 2012 at 5:30 AM

Hey, a really big thankyou for the help; I found out that I mistakenly made one of the values into a formula.
Even so, after saving I am unable to get the value from ValueCached. This is a little depressing.

Do you know of any tools that can read the values inside an Excel document? (I don't mind spending a few more seconds letting the app read the file after saving it)

Coordinator
May 4, 2012 at 6:04 AM

Once again, ClosedXML does not resolve/calculate formulas, the Microsoft Excel application does. ValueCached returns the value saved by Excel. ClosedXML does not set the ValueCached property, it can only read it. If the file hasn't been saved by Excel then the value is null. If you create a new file with ClosedXML then ValueCached will be null.

Please read that paragraph slowly three times.

To my knowledge there aren't any free tools that can resolve formulas. I know there are commercial products that do but I don't remember which.

Sep 25, 2014 at 11:21 AM
If the cell HasForluma then i try to get che ValueCached
and others i get the cell value instead.
But then the cell has a reference and the refer value's type is Datetime
then need to try get the time by Datetime.FromOADate.