How can I interogate/read the resulting value of a cell which contains a formula?
When I look at WorkBook.Worksheets[0].Rows[15].Cells[1].Value I see the formula. What I want to see is the resulting value.
I think because the .XLS file stores the formula, not the calculated value [1], you would need to parse the formula to calculate a resulting value. The Infragistics.Excel class library API wraps the file for I/O purposes, but it doesn't tabulate the spreadsheet as a spreadsheet application would.
[1] - This may not apply to worksheet "snapshots" served by Excel Server 2007 where I believe formula results may be cached, but these are a different animal.
Would it not be logical to be able to toggle between ACTUAL values and formula using a method, ie have it able to automatically parse formula? The current implementation is so frustrating, I just want to import Excel sheet content (that has loads of formulae across a number of sheets with in a workbook) and upload the results into a database. The formula I do not care about, just the values. All I get (if it works) is a worksheet with some values and a load of formulae - which is as useful as a chocolate teapot.
Instead I have to get the user to copy and paste etc etc (ie by not using the infragistics excel framework) and it is a very poor end user experience (their words not mine, they are doing this for hundreds of files per day!).
This feels a bit half baked in it's current implementation and not very useful! Sorry, but if there is an efficient way of importing a spreadsheet's values, it is not very clear to me...