Excel Workbook not automatically calculating when opening on client side

Aspose.Cells Product Family
1

We are switching from version 18.6 to 19.10 and we noticed that we have to invoke Workbook.CalculateFormula() method in order for the formulas to calculate values once we’ve placed the raw data (the data we look up against) in the new version.

In the previous version, we would have the Display sheet contain formulas, we would then do a data dump in the “raw data” sheet and when the excel would open on client side, it would calculate the formulas and display values.

In the newer version (19.10), we do the same thing, but if we spit out the excel without using calculate formula, it does not show any values. If we then go into the cell (Key: F2) and press enter, the formula gets calculated.

We are aware that we can just call workbook.CalculateFormula() at the end, but this would mean the the formulas are getting calculated on the server side, which takes memory. Wondering if there is a way to get back to old functionality where formula is calculated on the client side on open of workbook.

Currently using Excel 2016 and .Net Environment

Code:
string dataDir = @“C:/Projects/Test/Aspose19.10Demo/”;
var wb = new Workbook(dataDir + “AsposeTestBook.xlsx”);
var RawSheet = wb.Worksheets[“RawData”];
RawSheet.Cells[0, 0].PutValue(1);
RawSheet.Cells[1, 0].PutValue(2);
RawSheet.Cells[2, 0].PutValue(3);
RawSheet.Cells[3, 0].PutValue(4);
RawSheet.Cells[4, 0].PutValue(5);

        RawSheet.Cells[0, 1].PutValue("Test Data 1");
        RawSheet.Cells[1, 1].PutValue("Test Data 2");
        RawSheet.Cells[2, 1].PutValue("Test Data 3");
        RawSheet.Cells[3, 1].PutValue("Test Data 4");
        RawSheet.Cells[4, 1].PutValue("Test Data 5");

       // wb.CalculateFormula();
        wb.Save(dataDir + "OutputExcelFromVersion19.10_2.xlsx");

AsposeTestBook.zip (6.6 KB)

2

@SPDora,
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-47006 – XLSX workbook not automatically calculating when opened in Excel 2016

3

@ahsaniqbalsidiqui ,
Thank you for your reply.

Can you please let me know what are workaround except using Workbook.CalculateFormula()? As CalculateFormula() has heavy memory footprint, we would like to use anything else except this.

Another question, when we can expect fix for this? Do you guys release a patch or a new version release? Any approx date would work for me.

Thanks

4

@SPDora,
We are afraid that no workaround is available yet to resolve this issue without using CalculateFormula. However I am logging your comments along with the ticket for future reference. We will write back here once any feedback is available in this regard.

5

@SPDora,
In the template file, the CalculationId is greater than the one of excel 2016. This id denotes that only those excels whose versions are greater than it will do the re-calculation for formulas in the template file on loading. Maybe the file was created by higer version of ms excel, or it was created by some old versions of Aspose.Cells. You may change it by code before saving:

wb.Settings.CalculationId = “0”; //you may set this value corresponding to the desired version of ms excel, or just set as “0” to ensure it can be re-calculated by all versions of ms excel.

6

@ahsaniqbalsidiqui
When we run the project, we see that the calculation ID of the template is “162913”. What does this signify? This excel template was created by using Excel 2016, so not sure how the ID is different? What is the ID of Excel 2016?

This was working until Aspose version 19.1, but does not work in the current version. Additionally, do you have more documentation on CalculationID. We found the below link, but wondering if there’s more information so we can get a better understanding.

Thanks for the help.

7

@SPDora,
We are gathering information in this regard. In the meantime could you please share if setting 0 solves the issue or not at your end?

8

@ahsaniqbalsidiqui ,
Yes, it does solves the issue. But we don’t have any knowledge on it. Can put this piece of code in our system before gathering some information. Or may be we can wait for the next fix release.

9

@SPDora,
Thanks for the feedback. We will share our feedback soon.

10

@SPDora,

For older versions of Aspose.Cells, we reset the WorkbookSettings.ReCalculateOnOpen property as true by default. It was not consistent with MS Excel’s behavior 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 be calculated automatically by MS Excel when the file being opened, user may set this property as true. Setting the calculation id as “0” also can get the same effect, but we think it may be not be normal way. Instead, specifying the WorkbookSettings.ReCalculateOnOpen property as true should be the preferred solution.