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...
No, it is not working for me.
For example, from debugger the cell has a formula like this:
=IF(ISERROR(VLOOKUP(B3,'[1]Special Bids'!$A:$R,16,FALSE)),0,VLOOKUP(B3,'[1]Special Bids'!$A:$R,16,FALSE))
and the cell.Value return 0 while the Excel shows value of 123.
I tried the ApplyFormula() but it did not help:
cell.ApplyFormula(cell.Formula.ToString());
So the WorksheetCell is returning the formula correctly, but not the value.
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).
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.
Excel file i am working has linkage broken but excel still displays value. Is they any way can i retrieve display value of the cell rather than formula? BTW, i am using v10.1 dll's. Any help is greatly appreciated. I am validating an existing process so fixing formula is not an option.
Currently, there is no way to directly get the cached calculated value. Can you post the file with the problem and let me know which cell you are trying to access? I will see if there is a way to get the value.