I have an excel spreadsheet that I am creating from scratch using the Infragistics.Excel library version 16.1.20161.2044. After I create the spreadsheet, the user can then recall values from the spreadsheet using the following code:
' get the workbook Dim QuoteWB As Workbook = Workbook.Load("My_Workbook.xlsx") ' get the desired worksheet Dim CensusWS As Worksheet = QuoteWB.Worksheets("Census") ' the formula for cell D4 looks something like this: ' =('Worksheet1'!C3+'Worksheet1'!D3)*12 Dim AnnualSalary As String = CensusWS.GetCell("D4").Value
The first worksheet in my workbook consists of raw values (no formulas). I am able to get values from this worksheet without any problems. All subsequent worksheets have formulas that are referencing the original worksheet. I am only able to get the value from a cell that does not contain a formula. Any cell that has plain text will work just fine, but as soon as I try to get the value from a cell that has a formula, I get the following error message:
System.InvalidCastException: Conversion from type 'ErrorValue' to type 'String' is not valid.
My goal is to get the value from a cell that contains a formula. I have implemented some complex formulas in the workbook and I need a way to retrieve the results.
Hello Jack,
Thank you for using our community!
I have investigated you case and you could get both the value and the formula. My suggestion is to get the target cell by using the following code:
Dim cellvalueRef As WorksheetCell = ws.Rows(4).Cells(49)
“cellvalueRef” is a cell which contains a formula. I am attaching a screenshot with the properties of the worksheetCell. You could get the value of “cellvalueRef” by using its value property.
You could test my suggestion on your side and if it’s not working for you, please send me an isolated code sample in order to be on the same page regarding this issue.
Let me know if I may be of further assistance.
Regards,
Aneta Gicheva,
Infragistics
Thank you for the quick response. I am still having the same problem using the code sample that you provided. Keep in mind that I am opening an existing spreadsheet that has been created at some point in the past and is saved somewhere on our server. I am not trying to get values at the time that the spreadsheet is being built. I have tried both of the following lines:
Dim EmployeeGrossTaxSavings As String = ProposalWS.Rows(10).Cells(2).Value Dim EmployeeGrossTaxSavings As String = ProposalWS.GetCell("C9").Value
Dim EmployeeGrossTaxSavings As String = ProposalWS.Rows(10).Cells(2).Value
Dim EmployeeGrossTaxSavings As String = ProposalWS.GetCell("C9").Value
They both give the following error message:
However, using either of the following lines, I am able to get a value of "#NAME?" from the cell:
Dim EmployeeGrossTaxSavings As String = ProposalWS.Rows(10).Cells(2).Value.ToString() Dim EmployeeGrossTaxSavings As String = ProposalWS.GetCell("C9").Value.ToString()
Dim EmployeeGrossTaxSavings As String = ProposalWS.Rows(10).Cells(2).Value.ToString()
Dim EmployeeGrossTaxSavings As String = ProposalWS.GetCell("C9").Value.ToString()
It's as though my code cannot access the value of the cell because maybe the spreadsheet is in "Protected Mode"? Normally, when I download an Excel spreadsheet from the internet, no formulas are evaluated until I click the button that says "Enable Editing". Before I click that button, all the cells that have formulas simply say "#NAME?". Then, as soon as I click the "Enable Editing" button, the values are shown normally.
Does this have anything to do with the behavior I'm seeing from the Infragistics library?