I'm still evaluating Aspose.Cells for .NET 4.4.0.30. I've attached a multi-sheet XLS file generated by my application using Aspose. The code simply creates various sheets, stores values and formulas, and saves the file. Note that in most of the cells with formulas Excel 2003 displays #VALUE! instead of the calculated result. If I go to cell E6 on the Inputs worksheet, for example, I see that it shows the formula "=D6". D6 has a number in it. If I type the F2 key and hit enter, without making any edits to the formula, the correct value is displayed. Thus, the formula itself seems to be correct but for some reason the calculation was suppressed.
Adding a CalculateFormula call before saving doesn't fix the problem. What I would like to have happen is for the entire workbook to be recomputed when first loaded into Excel so that the values are numerically what Excel wants them to be.
The underlying application code works fine when run against the Excel COM interface.
Thanks! There's been great progress but there are still issues. I've attached a ZIP with two XLS files, one generated through Aspose and one generated through Excel. As you can see on the Sales tab, the Revenue_Growth_pct (entire table) and Unit_Growth_Pct (total) fail to subtract the 1 in the formula. These rows are correct in the Excel-generated version. When I force Excel to recalculate the cell formula in the Aspose-generated file it returns the correct result.
Note that I am NOT calling CalculateFormula before saving the workbook since I want the calculations to be performed by Excel, not by Aspose. Can you verify that this will be the case?
Laurence, thank you so much, that seems to work well.
Can you tell me whether the calculations are being done in Excel when the file is first loaded or in Aspose before it is saved? We would like to have Excel do it so that the results are consistent as a user changes the spreadsheet.
Well, I thought we were out of the woods, but we tried making a larger workbook and ran into the #VALUE! problem again. I've attached the workbook. If you look at Assets!D10 you'll see that it's a simple formula dependent only on Default!E3, which just has a number in it, yet it comes up in Excel as #VALUE!. If you navigate to the cell and press F2, then Enter, without any editing of the formula, the correct value is computed.
Laurence, we're making real progress. There's still a problem at Default!E22 and adjacent cells down and to the right. Excel has no problem calculating these cells one by one using the F2/Enter trick. In the previous spreadsheet I posted, Excel thought some of these references were circular, which they weren't if you followed the right branches of the IF's, but we changed the formulas so that Excel no longer complains about this.
I'm wondering if you might explain a little about why we're having so many problems with this particular issue. We might be able to work around the problem in our software some way or another if we knew why it was happening.
In these days we are enhancing and optimizing formula parsing and calculation. In previous dev versions, a few formula flags are not set correctly. Please try this attached version. It should solve this problem.
And in your files, we also find some issues:
In "Inputs" worksheet, manually input "=ISBLANK(F33)" formula in G33, you will see it returns FALSE. Please click F33 and press delete key. You will see that G33 will be TRUE. There are blank strings in cell F33 to F36.
Laurence, thanks, with the latest version I don't see any more formula evaluation problems in this spreadsheet and in a larger one I've tried.
Also thanks for your comment about "Inputs". It turns out that it is intentional that the Total column be blank. We will eliminate the column entirely it in a future version of our spreadsheet generator.