Indirect formulas does not work properly in Excel 2016


#1

Hello,

We are experiencing an issue with indirect formulas, not longer refreshing after file is saved using Aspose.
Take a look in the attached zip. (Indirect Fromula.zip (303.0 KB))

Inside you will see really simle app and the input file (Input.xlsb)
The app takes the input file recalculate the workbook and saves it in a different file.

Workbook workbook = new Workbook(“Input.xlsb”);
workbook.CalculateFormula();
workbook.Save(“Output.xlsb”, SaveFormat.Xlsb);

After that point the Output.xlsb no longer works.

To test this:
Change the value in B5. Column BC - should change the values.

image.png (44.9 KB)

This work properly in the Input.xlsb but note in Output.xlsb

Note: Output.xlsb works properly in Excel 2019, bit not in previous versions of Excel (ex. 2016)
Note 2: Going inside the cell and pressing enter refreshes the formula and starts working after that point.
Note 3: Pressing CTRL+ALT+F9 fixes the formulas and starts working after that point.

Thanks


#2

@rystadenergy,

Thanks for the sample project, sample code and details.

After an initial test, I am able to reproduce the issue as you mentioned by using your sample code with your template file. The problem can be seen in the output file when changing/selecting value in B5 cell, the dependent cells (e.g in BC column) are not re-calculated or changed accordingly in MS Excel manually. I have logged a ticket with an id “CELLSNET-46968” for your issue. We will look into it soon.

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


#3

@rystadenergy,

We evaluated your issue further.

Generally, we mimic the behavior of newer MS Excel versions. For formula calculation, we set the calculation id (different for different Excel versions) as the one of newer Excel version (currently it is Excel 2016) too. With the higher id, the formula will not be automatically calculated by lower versions of MS Excel. We are considering to change this behavior and the default value of this property (WorkbookSettings.CalculationId) because it causes confusion and inconvenience for some users and scenarios.

Currently as an workaround you may change the calculation id to lower manually, see the following sample code:
e.g
Sample code:

Workbook workbook = new Workbook(“Input.xlsb”);
workbook.CalculateFormula();
workbook.Settings.CalculationId = "0";
workbook.Save(“Output.xlsb”, SaveFormat.Xlsb);

Now it should work fine with the re-saved file.

Let us know if you still find any issue.


#4

Hi,

Thanks for the replay.
This solved the problem.

Do you suggest to always keep this setting for calculation ID, or I should remove it as soon as proper fix is released?

Thanks


#5

@rystadenergy,

Good to know that your issue is sorted out by the suggested code segment.

Yes, once we change the behavior and change the default value of this property, you may remove it.


#6

@rystadenergy,

Please try our latest version/fix: Aspose.Cells for .NET v19.10.4 (attached)
Aspose.Cells19.10.4 For .Net2_AuthenticodeSigned.Zip (5.0 MB)
Aspose.Cells19.10.4 For .Net4.0.Zip (5.0 MB)

Your issue should be fixed in it.

Let us know your feedback.


#7

The issues you have found earlier (filed as CELLSNET-46968) have been fixed in Aspose.Cells for .NET v19.11. This message was posted using Bugs notification tool by Amjad_Sahi