Hello
I have an Excel file which I load into an Infragistics.Excel.Workbook object. In this Excel file, I have a cell where I put a value, and another one which calculates something from the first one. In my code, I want to retrieve the resulting value from this second cell, but all I can get is the formula itself.How do I tell the Infragistics.Excel engine to give me the value, not the formula?ThanksGuy
The ability to solve formulas is not currently supported in the Excel assembly. I would recommend submitting a feature request for this to the support group: http://devcenter.infragistics.com/Protected/RequestFeature.aspx. I have seen other people request this and the number of requests for a certain feature does play a part in what gets implemented in future releases.
Wow... I don't see how the Infragistics.Excel engine can be useful without the ability to get calculated values out of the worksheet!!!!
What do you use it for?!?
Isn't there a way I could do it? I find this hard to believe...
So there are two things mentioned here, both of which are not currently supported. The first is using values of cells from external workbooks. The other is getting the cached cell value when referenced external cells are not available. You can submit feature requests for these abilities.
Hi,
I'm using .NET Advantage 2011.1 and when I try to get the value from a cell with a formula inside, it takes about 4-5 sec to get it done.
The code I'm using is the following:
myForumla = Formula.Parse("='myWorksheet1'!R1C1", CellReferenceMode.R1C1);myForumla.ApplyTo(workSheet2.Rows[rowIndex].Cells[columnIndex]);string val = workSheet2.Rows[rowIndex].Cells[columnIndex].Value.ToString(); //<- This line takes 4-5 sec
Since I must run that code several times, do you have any suggestion to speed up the execution? Now it's unacceptable for me.
Thanks
Stefano
The first time a calculated value is requested, all formulas in the Workbook must be calculated. If you have many formulas in your workbook, this might explain the lag the first time you ask for the value. However, subsequent requests should return the value instantly. The only reason why you would see the lag on each request is if you have many formulas in the workbook with volatile values. For example, NOW() is a volatile function because it returns a different value every time it is evaluated. So the value for a formula using that function, as well as any formula which directly or indirectly references that formula, must always be recalculated.
Hi Mike,
I tried to insert wokrbook.Recalculate() before looping throught the cell values. It takes 5 sec to calculate all formulas and then I'm able to get the cell values instantly.
If I remove Recalculate(), the lag problem returns. Any idea?
Well that seems to support my theory about what is causing the lag. Recalculate will explicitly force the calculation of all formulas whereas getting the first calculated value just does it lazily.
It makes sense that the lag would return when the Recalculate call is removed. But I am curious: is there one lag and then everything is fine after that, or are you experiencing multiple lags? If it is only one, then there is really nothing you can do other than submit a performance bug so that we can look into possibly reducing the lag. And the only thing you could do in code in that case is choose whether you want to control when the lag occurs (with a Recalculate call) or you want to let it happen when it needs to happen. But if there are multiple lags, there may be a bug.
In that case, I think I will need to see the source code related to populating the worksheet to be of any help. It is something you can post on this thread? If not, I will have a Developer Support engineer contact you to create a private case.
It shouldn't be my case, beacuse if it was, it would be irrelevant to put Recalculate() or not before looping my cell values. In my case, instead, if I execute Recalculate(), all formulas are calculated just once and all is running well, if I comment Recalculate(), it seems to calculate all formulas every time I ask for a cell value.
When you add in new formulas in your sample, are many of the other existing formulas dependant on that new formula (directly or indirectly). In other words, would the added formula require all other formulas to be recalculated?
I'm trying to reproduce the problem in a sample project but it seems to work fine. The first time I get the cell value there is a lag, then all is running smoothly.I'm trying to figure out why in my real project I have that issue and perhaps I'll ask for further support.Thanks
Would it be possible to attach a small sample reproducing this?