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 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,
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
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.
By the way, is there any setting that will cause the WorksheetCell to return "cached value"? I noticed that if I deleted the referenced file (referenced in the formula) Excel still shows values (from cache I think). It would be nice if we can get the cached values like what Excel is doing.
The formula is
=IF(ISERROR(VLOOKUP(B4,'C:\IBM\2011\Q3''11\FCST\[IBM Q3''11 WW38.xlsm]Special Bids'!$A:$R,16,FALSE)),0,VLOOKUP(B4,'C:\IBM\2011\Q3''11\FCST\[IBM Q3''11 WW38.xlsm]Special Bids'!$A:$R,16,FALSE))
not the one posted above (it was copied from the debugger and seemed did not handle quotes correctly).