how do I read a cell value produced by a formula?

Jan 20, 2011 at 10:55 AM

I've a cell with a formula like "LEFT(H3,(SEARCH(" ",H3,1)))" how do I programmatically read the value of that cell?

If I do u.Cell(12).Value I get the formula instead

is that even possible?

thanks

Coordinator
Jan 20, 2011 at 3:20 PM

Right now it's not possible.

The good news is that I have a very good idea on how to implement such a feature.

The bad news is that it's a major endeavor so you probably won't see it for a while. Once I fix some things here and there and polish the API a little bit my next stop is charts.

Jan 20, 2011 at 3:22 PM

I'll be waiting for this!

In the meanwhile I'll move the formula as a function in my code

 

thanks

May 13, 2011 at 2:03 PM

Is this an update you've started or is it still on the "todo" list?

You mentioned you have a good idea on how to implement, anything you'd care to share? Maybe one of us who have found and really like this package could find some time to implement using your idea as a guideline and if successful send you the updated code.

 

Coordinator
May 13, 2011 at 4:36 PM

It's still on the to-do list as I'm still working on charts. Charts have so many options it's almost demoralizing to look at the road ahead. I'm just concentrating on the next step I have to take to move forward with its implementation. I'm aiming to for you to be able to make a chart in one shot like this:

ws.RangeUsed().CreateChart(XLChartType.Pie3D, startPositionRow, startPositionColumn, endPositionRow, endPositionColumn);

Anyway back to your question. To parse the formulas I was thinking of rewriting the following expression evaluator to make it work with my workbooks and worksheets: http://www.codeproject.com/KB/vb/FormulaEngine.aspx

I'd be very happy if anyone wants to take a crack at it...

May 16, 2011 at 11:00 AM

There is a list of creatures in Excel. On a sheet of cells is computed by the formula. Howcan I get these values​​? If I look XML - I can see that these values ​​are present in it at par with the formula. For example in a cell goes calculation = 2 +1

<c r="A13"> <f> 2 +1 </ f> <v> 3 </ v> </ c>

As we can see Excel, save the last settlement and the value of - 3 as well as the value ofthat without the formula:

<c r="A10" s="6"> <v> 123 </ v> </ c>

Coordinator
May 16, 2011 at 5:57 PM

Excel caches the formula result for performance reasons but there is no guarantee that the cache is going to be there, nor that the cache number is correct (Excel only updates the cache when the formulas are calculated, which can be turned off). That and the fact ClosedXML doesn't store this cache because it doesn't calculate formulas so the cache is lost as soon as you save it with ClosedXML. Furthermore if a cell changes its value (with ClosedXML) and it's referenced in a formula, the cache will automatically be invalid.

It's just too inconsistent for me to recommend using it.

May 17, 2011 at 6:06 AM

Okay, I understand the problems that may arise in this case. But in a project in which I want to use this library - I think that this can be ignored. Is it possible to add a property "CachedValue" for this?

Coordinator
May 17, 2011 at 3:58 PM

I just checked in a new version with your name on it...

May 17, 2011 at 4:48 PM

I hadn't seen the FormulaEngine code project. Promising. Now if I can find a little extra time to pull it down and review. Thanks for the information.

May 18, 2011 at 7:33 AM
Edited May 18, 2011 at 7:37 AM

Yes, thank you - this is exactly what I need.

May 24, 2011 at 5:00 PM

Manuel,

 

Has this been addressed yet?  I'm a little unclear from the thread if the "version with your name on it" was the fix.  How do you get the value of a cell that has =Sum(A7:A1000)?  Thanks

Coordinator
May 24, 2011 at 5:23 PM

Added cell.ValueCached property that holds Excel's cache of formula evaluations. This doesn't resolve the formulas. It only gives you the resolved value cached by Excel. It's already checked in on change set 66709 and it will appear on the next release.

May 24, 2011 at 5:44 PM
Edited May 24, 2011 at 5:50 PM

When will the next release be available? If I download change set 66709 is that the cumulative .dll, or do I just replace the changed files and re-build?  thanks

Coordinator
May 24, 2011 at 6:07 PM

I'll release it sometime today. If you need that functionality *right now* you can download the code on 66709 and compile it as it will contain all files.

May 24, 2011 at 6:50 PM

thanks for the quick response.  I can wait until tomorrow.

Jun 27, 2011 at 10:36 PM
mdeleon wrote:

It's just too inconsistent for me to recommend using it.

Hi,

     This is a wonderful tool and I wish I found it before I spent the better part of a week delving into both Interop and Open XML to automate some Excel work.  I'm in the same boat as some of the other posters in that I came to ClosedXML (on a recommendation) in hopes of finding a way to use an Excel spreadsheet as a makeshift calculation engine.  Unfortunately, I'm running into the same problem as everyone else in that when I try to get the value of a cell (which contains a formula), it comes up as the formula and not the calculated value.

     My question, as it pertains to the portion of your statement that I've quoted above, is:  You do not recommend using ClosedXML for the purpose I've stated above for now OR do you see this as an insurmountable hurdle which will not be able to be overcome in the foreseeable future?  Obviously, your answer would be very important for a number of us.  If it's just around the corner and a matter of days then I think we all look forward to this milestone.  If it's just not technically feasible, then perhaps it's best we looked elsewhere.  Personally, I'm not looking forward to my two choices if this is not able to be overcome: model all the formulas on a spreadsheet of which there are hundreds or install Office on a production server which I'm sure my boss would not appreciate even suggesting.  Since this thread was last commented upon over a month ago I'm of the impression that this is just not able to be done any time soon.

     Again, this is a great tool and I see great potential.  Since the true strength of Excel is its spreadsheet and mathematical modelling, I'm really looking forward to seeing what other great things come of ClosedXML.

 

Coordinator
Jun 27, 2011 at 10:59 PM

From my research it is feasible to resolve formulas but I really can't say when I'll begin working on it or how much time it will take once I do. As much as I'd like to say we'll have it soon, it's not going to appear any time soon. If you need to resolve formulas then your only options are commercial components or Excel Interop.

Sorry,

Jun 27, 2011 at 11:12 PM

Hi,

     Thank you for the quick reply.  I was afraid that would be the answer.  Since my options come down to a great deal of tedious calculations or Excel Interop, I think I'll go with the latter even though it a last resort.  I can only imagine how difficult this would be for you to implement.  ClosedXML is a wonderful tool and maybe when I revisit it down the road it'll be even more robust and promising than it already is.  Good luck to you.  Bye!