Upgrading 8.3.2 -> 8.5.2/8.6.1: calculation unexpectedly produces #NAME?

Aspose.Cells Product Family
1

I have encountered a calculation problem in a very large Excel spreadsheet after upgrading Cells For Java from 8.3.2.

Below I have shown some code which creates and evaluates a workbook with a few formulas. When I run this code using version 8.3.2, I get the expected result of 1.0 in cell B4. If I run the same code in versions 8.5.2 or 8.6.1, the result is #NAME? instead. If I save the workbook and load it into Excel, I obtain the expected result.

Almost any change to this simple workbook will produce the correct result, including simply swapping the worksheets when defining the formulas. However, the problem is not so easily fixed in the original spreadsheet.

Here is the code:

Workbook workbook = new Workbook();
WorksheetCollection worksheets = workbook.getWorksheets();
Worksheet sheet1 = worksheets.get("Sheet1");
Worksheet sheet2 = worksheets.add("Sheet2");

// If we swap the sheets, then the bug does not occur.

// Worksheet t = sheet1; sheet1 = sheet2; sheet2 = t;


sheet1.getCells().get(“A1”).setFormula("=Sheet2!$B$4");


Cells sheet2Cells = sheet2.getCells();

sheet2Cells.createRange("$1:$1").setName(“FirstRow”);

sheet2Cells.createRange("$2:$2").setName(“SecondRow”);

sheet2Cells.get(“A1”).setFormula(“2”);

sheet2Cells.get(“A2”).setFormula(“1”);

sheet2Cells.get(“A3”).setFormula("=FirstRow-SecondRow");

sheet2Cells.get(“B2”).setFormula("=A3");

sheet2Cells.get(“B4”).setFormula("=SecondRow");


workbook.calculateFormula();


System.out.format(“B4: expect 1.0, got %s%n”, sheet2Cells.get(“B4”).getValue());


workbook.save(“test.xlsx”);

Any advice welcomed.

2

Hi,

Thanks for your posting and using Aspose.Cells.

We were able to see this regression by executing your sample code with the older and latest version.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSJAVA-41577 - Formula calculation unexpectedly produces #NAME?

Here is the console output of the code for a reference.

Console Output:
Aspose.Cells for Java v8.1.0.3
B4: expect 1.0, got 1.0
---------------------------------------
Aspose.Cells for Java v8.6.1.6
B4: expect 1.0, got #NAME?
3
Hi again,

This is to update you that we have fixed the problem logged earlier as CELLSJAVA-41577. We will shortly provide the fix here after ensuring the quality and incorporating other enhancements.

4

Hi,

Thanks for using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for Java v8.6.2.1 and let us know your feedback.

5

I can confirm that the bug is fixed in version 8.6.2.1, for the simple test program listed above and also for the full production spreadsheet in which the bug was originally found.

Thank you for your quick response to this issue.

6

Hi,

Thanks for sharing a good news with us 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.

7

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

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