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.
I really need some information about this. Can anyone please offer any advice? I have been waiting for weeks.
Can you please give me an update on this?
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.
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.
Regards,
Aneta Gicheva,
Infragistics
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.