Error in CalculateFormula with version 17.1.0

Aspose.Cells Product Family
1

Good evening, we’ve encountered a problem with a file excel generated and saved using Aspose.Cells .NET


In that report some formulas are not calculated correctly: if we get the file saved by Aspose we see some values but if we recalculate formulas in Excel these values change.

It’s a very strange error because the errors appears in a cell with a formula that is the same that there is in the upper row or in under row.

The resolution is very urgent because the software is deployed in production environment and the customer has a very short deadline.

Can you provide us a fix?

To see the errors, you must open the file in Excel with CalculateMode=Manual and update the cells where there is the error.

I attach you the file where we see the errors with highlighted in red the cells with the problem (sheet C.SCR_Def_List!AH16, sheet SCR_Def_Calculation!D18 and E18)


2

Hi,

Thanks for the template file and details.

After an initial test, I observed the issue as you mentioned by using the following sample code with your template file. I found an issue with Workbook.CalculateFormula() method, it does not calculate the formulas correctly:
e.g
Sample code:

Workbook workbook = new Workbook(“e:\test2\Rep+EsempioErroreAspose.xlsm”);
workbook.CalculateFormula();
Console.WriteLine(workbook.Worksheets[“C.SCR_Def_List”].Cells[“AH16”].StringValue);
Console.WriteLine(workbook.Worksheets[“SCR_Def_Calculation”].Cells[“D18”].StringValue);//it should be 16
Console.WriteLine(workbook.Worksheets[“SCR_Def_Calculation”].Cells[“E18”].StringValue);//it should be 104

Output:
0.0100%
2
2

I have logged a ticket with an id “CELLSNET-45087” for your issue. We will look into it soon.

Once we have an update on it, we will let you know here.

Thank you.

3

Ok we wait for a response. It’s very important for our project to solve this problem. We notice that in a older version 8.2.3 the errors not happened. We’ve changed versione for another bug with formula IRR that we find fixed in version 16.11 and recent.


Please help us.
4

Hi,

We have evaluated your issue further. Well, you need to specify PrecisionStrategy for calculating formulas via Aspose.Cells APIs. I have tested it and it works fine, see the updated sample code for your reference:
e.g
Sample code:

Workbook workbook = new Workbook("e:\test2\Rep+EsempioErroreAspose.xlsm");
CalculationOptions co = new CalculationOptions();
co.PrecisionStrategy = CalculationPrecisionStrategy.Round;
workbook.CalculateFormula(co);
Console.WriteLine(workbook.Worksheets["C.SCR_Def_List"].Cells["AH16"].StringValue);
Console.WriteLine(workbook.Worksheets["SCR_Def_Calculation"].Cells["D18"].StringValue);//16 - Ok
Console.WriteLine(workbook.Worksheets["SCR_Def_Calculation"].Cells["E18"].StringValue);//104 - Ok

Let us know if you still have any issue.

Thank you.

5

HI, thank you for the response. We try your suggestion but before we need to know what this change can produce in formula calculation because our software produce a great number of excel files and a change can produce a lot of regression. Can you explain this change what means in evaluating formula? There is the same option in Excel?


Thank you and regards
6

Hi,


For your question about the precision option and regression possibility, kindly see below posts for your reference:

Thank you.
7

Hi, thank you for the clarifications.


We tested excel file with your suggestion to change the CalculationPrecisionStrategy and works fine.

The only thing opened now is that reading the other thread I understand that sometimes it’s needed to use a CalculationPrecisionStrategy and sometime another one to calculate correctly. Our tool generate a very large number of reports using a single process so we use the same strategy for all reports. There is a way to choose the correct strategy without a calculation errors happens? Is there a strategy safer than others?

Thanks in advance
8

Hi,


Thanks for your posting and using Aspose.Cells.

We have logged your comment in our database against this issue i.e. CELLSNET-45087. We will look into it and help you if possible. Once, there is some news for you, we will update you asap.

9

Hi,


Thanks for using Aspose.Cells.

We are still working on this issue. For this case, we are making further investigation to check whether we can make it work without changing the precision strategy. Once, there is any other news for you, we will update you asap.
10

Hi,

Thanks for using Aspose.Cells.

For your issue, please try our latest fix given below. Now, you do not need to use the PrecisionStrategy option.

Aspose.Cells for .NET v17.1.8 (.NET 2.0) compiled in .NET Framework 2.0.
Aspose.Cells for .NET v17.1.8 (.NET 4.0) compiled in .NET Framework 4.0.

Let us know your feedback.