Problem with using CalculateFormula() method

[peterrow]

Hi,

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:

=IF('Quarter 1'!$Y$6<=0,"Check","OK")

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.

Regards,

Peter Row

[Amjad_Sahi]

Hi,

Thanks for considering Aspose.

I tried your attached excel file and find no problem same to you I think, Following is my testing code for your scenario which works fine, I update the cell value, calculate the formulas and get the results, then save the excel file, re-open it, update again the cell's value, calculate the formulas and get the calculation results:

Workbook workbook = new Workbook();
workbook.Open("d:\\test\\_formulaCodeUpdateTest.xls");
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells["A1"].PutValue(1);
worksheet.Cells["A3"].Formula = "=IF($A$1=1,Sheet2!B1,IF($A$1=2, Sheet3!B1))";
workbook.CalculateFormula();
int first = worksheet.Cells["A2"].IntValue;
int second = worksheet.Cells["A3"].IntValue;
MessageBox.Show("A2: " + first.ToString());//Output.......10 OK!
MessageBox.Show("A3: " + second.ToString());//Output....10 OK!
workbook.Save("d:\\test\\outbk.xls");
workbook.Open("d:\\test\\outbk.xls");
worksheet = workbook.Worksheets[0];
worksheet.Cells["A1"].PutValue(2);
workbook.CalculateFormula();
first = worksheet.Cells["A2"].IntValue;
second = worksheet.Cells["A3"].IntValue;
MessageBox.Show("A2: " + first.ToString());//Output.....20 OK!
MessageBox.Show("A3: " + second.ToString());//Output...20 OK!

We insist you to attach your original template file here and paste your sample code (which will explain how you are doing) to reproduce the problem. So that we may check and figure out the issue (if exists) soon.

Thank you.

[peterrow]

Hi,
Perhaps my example was bad; the example was simple and did work. Please find attached the real workbook I am trying to make work.

Here is the C# code I am using as a test of this attached file:
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(@“C:\PetersData\Documents_Smith_Peaceful 10728 Peaceful Sleep Rest Home PIWorkbook.xls”);
wb.Worksheets[“SPLS PI Data”].Move(0);
wb.Worksheets[“SPLS PI Data”].Cells[“D17”].PutValue((int)2);
wb.Save(@“C:\PetersData\Documents_Smith_Peaceful 10728 Peaceful Sleep Rest Home PIWorkbook.xls”);

wb.Open(@“C:\PetersData\Documents_Smith_Peaceful 10728 Peaceful Sleep Rest Home PIWorkbook.xls”);
wb.CalculateFormula();
object obj = wb.Worksheets[“SPLS PI Data”].Cells[“D25”].DoubleValue;
MessageBox.Show(wb.Worksheets[“SPLS PI Data”].Cells[“D17”].IntValue.ToString() + " " + obj.ToString());

Before the change the value of D25 is 52.75, after the change and the CalculateFormula() method call it should change to 55.75, however instead it throws the exception I mentioned in my original post.

Sorry for the double post.

Regards,
Peter Row

[Amjad_Sahi]

Hi Peter Row,

Thanks for the template file,

After some initial testation, I can reproduce the problem you have mentioned.

We will figure out the issue soon.

Thank you.

[Laurence]

Please try this attached version.

[peterrow]

Hi,

Thanks for the fast response.

The version you attached did indeed work, .

I noticed that the properties for the DLL in VS 2005 report that it was built for v1.0 of the .NET framework. We would need the .NET 2.0 build.

So aside from the .NET 2.0 build the next question is when can we buy Aspose.Cells with this fix included in it?

Regards

[Laurence]

Hi Peter,

Yes, it's built on .Net1.0 but it works fine with .Net2.0. Actually only in 64bit machine, a build on 2.0 is a must. If you do need a .Net2.0 build, I will make it tomorrow.

If you buy Aspose.Cells, you can get free updates and fixes in the next whole year. So sure this fix will be included.

[peterrow]

Hi,

Yes we will definitely need a .NET 2.0 build.

About the purchase, we need the fix to be included with the version we buy because we are about to finish coding on our application so we need to use this fix straight away. I know that when we buy it we get a free year of updates but we need to use the version with this fix in straight away and hence the version we buy must have this fix in.

Regards,

Peter

[Laurence]

Hi Peter,

For .NET2.0 build, I will post it here tomorrow.

If you buy the license, this fix will be included.

[Laurence]

This attached dll is built on .NET 2.0.