Formula Calculation (+) inconsistency

Aspose.Cells Product Family
1

Hello,

We’ve encountered an inconsistency and difference on how Aspose and Excel behave when calculating a formula.

Having the following code:

Workbook workbook = new Workbook();

var worksheet = workbook.Worksheets[0];
worksheet.Cells[“A1”].Value = “”;
worksheet.Cells[“A2”].Value = 3;
worksheet.Cells[“A3”].Formula = “=A1+A2”;

workbook.CalculateFormula(true);

Console.WriteLine(“A3 value is: ‘{0}’”, worksheet.Cells[“A3”].Value); // 1st print

workbook.Save(“Excel.xlsx”, SaveFormat.Xlsx);

Workbook workbookAfterSaveAndReload = new Workbook(“Excel.xlsx”);
workbookAfterSaveAndReload.CalculateFormula(true);
Console.WriteLine(“A3 value after saving, reloading and recalculating the Excel is: ‘{0}’”,
workbookAfterSaveAndReload.Worksheets[0].Cells[“A3”].Value); //2nd print

The 1st print displays: A3 value is: '#VALUE!"
And the 2nd print displays: A3 value after saving, reloading and recalculating the Excel is: '3’

This shows some inconsistent behavior between the 2 CalculateFormula operations.

Also, when loading the file into MS Office Excel the A3 Cell displays the numeric value 3, the same as Aspose does when reloading and recalculating the Excel document. This behavior seems to be the correct one, and needed by one of our customers when performing the first CalculateFormula, i.e. without the need of reloading and recalculating the document.

We are using Aspose.Cells version 7.6.1.0.

Any feedback will be highly appreciated.

Thank you in advance,
Mihai Andrei
Senior Software Engineer
IBM Romania

2

Hi Mihai,

Thanks for your posting and using Aspose.Cells.

I was able to notice this inconsistency. Aspose.Cells calculates the =A1+AB as '#VALUE!" when A1 is “” while Excel calculates it to 3.
I have logged this issue in our database for investigation. We will look into it and fix this issue. Once, this issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-42319.

3

Hi,

Thanks for using Aspose.Cells.

We have fixed this issue.

Please download and try the latest version: Aspose.Cells for .NET v7.7.1.2 and let us know your feedback.

4

The issues you have found earlier (filed as CELLSNET-42319) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
5

Hello,

We’ve tested with Aspose.Cells version 7.7.2.0 (which we presume it includes the fix done in 7.7.1.2) and now both of the prints show #VALUE! as being computed, insteaf of 3 as Excel does.

Please investigate again why this happens.

–Mihai

6

Hi Mihai,

Thanks for your feedback and using Aspose.Cells.

We were able to observe this issue. Now the code given by you prints #VALUE! both of times. We have reopened this issue for investigation. We will look into it and fix it. Once, there is some news for you, we will update you asap.

7

Hi Mihai,

Thanks for using Aspose.Cells.

Please check the result file (Excel.xlsx) in MS excel, you will get “#Value!”
too. Aspose.Cells works same as MS Excel.

If you want to get 3 as the result of
the formula, please change your code as

worksheet.Cells[“A1”].Value = null;

8

Hello,

Indeed, I’ve checked the output Excel.xlsx file produced by the original code I’ve posted, and now the file produced by Aspose gives the #VALUE! when loading it in Excel. This is different behavior than the one from the original post.

So you have fixed something - after saving the document and loading it in Excel to not resolve the SUM to 3, as it was… :slight_smile:

Anyway, I have to say the way it behaves now it matches the Excel one, so you can close this ticket.

Thank you,
Mihai Andrei

9

Hi Mihai,

Thank you for your confirmation on this. We have now closed the ticket. Please feel free to write back in case you need further assistance with Aspose.Cells APIs.