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?
Did you try calling Workbook.Unprotect() before saving your template? Unprotect method has three overloads, you could read more about them here and choose the one you need.
http://help.infragistics.com/doc/ASPNET/2016.1/CLR4.0/?page=Infragistics4.WebUI.Documents.Excel.v16.1~Infragistics.Documents.Excel.Workbook~Unprotect.html
You can choose from some other protected properties of the Workbook if the previous suggestion doesn’t work for you.
http://help.infragistics.com/doc/ASPNET/2016.1/CLR4.0/?page=Infragistics4.WebUI.Documents.Excel.v16.1~Infragistics.Documents.Excel.Workbook_members.html
I tested this scenario with the unprotected method and I can’t reproduce the issue. Please test it on your side. If it’s not working for your scenario, please send me runnable code sample in order to test it on my side and to be able to provide a better support.
Sorry for the delay, but I have now created a sample application to demonstrate my problem. See the attachment on this post.
After you run the application, you'll find two files in the "Output" directory. One is an excel spreadsheet titled "Quote.xlsx". The more interesting one is the PDF document titled "Proposal.pdf".
The Proposal document attempts to pull values from the Excel spreadsheet. If you look at the final page of the Proposal document, you'll see the problem I am having. The first column labeled "Employee Name" is getting a value of "#NAME?" instead of getting the actual name of the employee as it should.
If you open the Module1.vb file and go to line 9393, you can see that I have included two different methods of retrieving the employee name from the spreadsheet. The first method produces the "#NAME?" value and the second method works perfectly fine.
Unfortunately, I will not be able to use the second method in my production environment. So I must figure out a way to get the first method to work. Please help me understand why the first method does not work and what I can do to fix it.
Hello Jouin,
Did you try the suggestion from my previous answer? Furthermore, I will ask you to isolate the problem in runnable code sample. I will provide a better support, if I am able to debug the project.
Yes I have tried using the Workbook.Unprotect() method, but I still experienced the same problem.
Can you tell me specifically what you need to debug the project? The code I provided should compile and run successfully.
Can you please give me an update on this?
I really need some information about this. Can anyone please offer any advice? I have been waiting for weeks.