We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Formula fails to calculate

We are struggling with a particular formula which does not calculate when CalculateFormula is run on the workbook. This formula updates correctly in excel (when F9 is invoked); but remains errored (#NA) when we attempt to do this in Aspose.



The logic in our spreadsheet is more complex, but we have been able to reduce the problem to the one found in the attachment (see “Before”).



You will notice that the “Default” cell has a value (3); but when the spreadsheet starts out the value is #NA; the 3 gets filled in dynamically later; and then the workbook is recalculated.



The problem formula is the one under the label “FailsToUpdate”. You will notice that it refers to a different range which also returns #NA; and remains #NA throughout. The formula contains an “IF” which is meant to return FALSE in this case (ISNUMBER) because of the #NA; and then return the ‘else’ value (default).



This works fine in Excel (F9, or when we use current process that uses interop to run the workbook in Excel); but not when run under Aspose.



I gave included a separate simpler formula (“Does Update”) which is in fact updated when run under Aspose.



I have attached a “Before” and “After” spreadsheet; to show what happens before / after the following code is run.



You will see that if you press F9 in either, the cell is calculated correctly.



Code:



var workbook = new Workbook(testFile);

workbook.CalculateFormula(true);

workbook.Save(testOut);



We have tried running CalculateFormula several times (with different params; true/false for instance) and not found a way to get it to calculate.



Any help would be appreciated. Thanks!

Hi,


Thanks for providing us template file and sample code.

Please try our latest version/fix: Aspose.Cells for .NET (Latest Version)
I have tested your scenario/ case using your template file and the following sample code it works fine with it:
e.g
Sample code:

var workbook = new Workbook(“e:\test2\Before.xlsx”);
workbook.CalculateFormula();
Console.WriteLine(workbook.Worksheets[0].Cells[“G4”].StringValue); //3 - Ok
workbook.Save(“e:\test2\out1.xlsx”);


Let us know if you still have any issue.

Thank you.

The new version works like a charm; even on the more complex formula in our actual workbook.

Thanks so much for the speedy response.

Hi,

Thanks for your feedback and using Aspose.Cells.

It is good to know that your issue is resolved with the latest version. Let us know if you encounter any other issue, we will be glad to look into it and help you further.