In the following discussion I have simplified everything to the things I think are relevant so please ask for extra info if I've not provided enough detail.
In our .NET 2.0 WinForms app (but also for an ASP.Net app as well) I need to import data from a workbook.
- The workbook as several sheets in it 4 of which related to a quarter of a years data.
- One of the sheets does validation, and depending on the contents from cells in other sheets either causes the value of the cell to be either "OK" or "CHECK".
- The final sheet in the workbook is a summary and has a whole bunch of values that I want to import, it is ordered nicely so I can quite easily iterate over it.
- On the same summary sheet there is a cell that says which quarters data is being summerised. The formulas in the cells I want to import check this value and then pull data from the appropriate quarter sheet.
When the user imports the workbook with either app they have to specify which quarter (1-4) they want to import. What we then do is check if the quarter the user specified is the same as the one in the workbook if it is not then I update the cell value, save, reopen and then call CalculateFormula() - and that's where the trouble starts. There is an error, if I call it and ignore errors then when I start reading the import data the values I get back are 0, even though I know through checking with Excel that data should change from say 52 to 55.
The error I get when calling CalculateFormula() is the following:
Specified cast is not valid.\nError in calculating cell D49 in Worksheet Checks
Now if I go to the worksheet called "Checks" and look at the formula in D49 it is:
The value of cell Y6 on the sheet "Quarter 1" is 13 at this point and so I can't for the life of me see why this error is occuring.
If I open the workbook in Excel and look on the summary page following our code update Excel re-calculates the formulas appropriately so I'm not sure what to check next. Has anybody had this problem before? This is by first time using Apose.Cells, previously we were using an OleDbConnection but that simply doesn't do the recalculate at all.
I created a mega simple example where by on sheet 1 when you enter 1 in A1 it puts sheet 2 B1 into sheet 1 A2, if you put 2 into A1 then it gets the value from sheet 2 B1. Updating this with a simple C# 2.0 test app works fine - I can read the newly recalculated field in code without having to open it in Excel. See the attached file for this test example.
Any help with working past this error would be much appreciated.