Free Support Forum - aspose.com

Calculation Chain shrunked and Caluclation don't work anymore

Hi,

when I open and save the attached file with latest Aspose Version (20.9.0.0),
output1.zip (219.7 KB)
the calculations inside the xml are shunk to one entry.
This causes that the calculation in older office versions (older than Office 2016) does not work, you have has to press: CTRL,ALT+F9 to start the calculation.

To get the Calculationchain, open the zip file, open the xl folder and then open the CalcChain.xml.

Kind regards,
GUido

@Nachti,

The CalculationId in your template file might not be ok. This id denotes that only those Excel versions which are greater than it will do the re-calculations for formulas in the template file on loading. Maybe the file was created by some higher version of MS Excel, or it is also possible that the input was created by some older versions of Aspose.Cells. You may change it by code before saving:
i.e.,

wb.Settings.CalculationId = “0”; //you may either set this value corresponding to the desired version of MS Excel, or just set it as “0” to ensure it can be re-calculated (in any case) by all versions of MS Excel
wb.Save(…);

Furthermore, for old versions of Aspose.Cells, we did reset the WorkbookSettings.ReCalculateOnOpen property as true by default. But it was not consistent with MS Excel’s behavior completely and gave unexpected results for some users. So we changed the default value of this property and do not change it without user’s explicit invocation. With new versions of Aspose.Cells, to let formulas in generated file to be calculated automatically by MS Excel when the file is being opened in it, user may set this property as true. Setting the calculation id as “0” also can get the same effect, but we think it may not be the normal way to tackle it. Instead by specifying the WorkbookSettings.ReCalculateOnOpen property as true should be the preferred solution.

Thanks for your understanding and let us know if you still have the issue after following the proposed solutions.

Thank you.

Any way to set the calculation Id in Excel directly ?

Kind regards,
Guido

@Nachti,

As we told you in previous post, you may set it using workbook.Settings.CalculationId to set to “0” before saving the excel file in code. But the recommended (second) approach is better.

@Amjad_Sahi,

sorry but where / how can I find WorkbookSettings.ReCalculateOnOpen property ?

Kind regards,
Guido

@Nachti,

See the sample code for your reference:
e.g
Sample code:

Workbook workbook = new Workbook(stringFilePath);
workbook.Settings.ReCalculateOnOpen = true; 
workbook.Save(filePath);

Hope, this helps a bit.