How do I recalculate all formulas in a workbook?

[Riccardo De Agostini]

How do I recalculate all formulas in a workbook with XlsIO? I tried enabling calculation on all worksheets, but nothing changed: when I save my workbook and later load it in Excel, cells with formulas have the same values they had before I modified data in referenced cells. Any clues?

Thanks

Riccardo De Agostini

[Sudha Ramachandran]

Hi Riccardo,

 

Thank you for using Syncfusion products.

 

We tried a sample based on your scenario which can be found in the below link.

 

In the sample below:

 

Button1:  File with Manual Calculation mode is opened, manipulated and saved using XlsIO, the formulas in the workbook are re-calculated.

Button2:  File with Manual Calculation mode is opened, manipulated and saved using XlsIO, the formulas in the workbook are not re-calculated.

 

NOTE: While using Button2, keep the input file opened in MS Excel.

 

Sample Link: http://www.syncfusion.com/downloads/support/directtrac/131597/XlsIOSample-1833452405.zip

 

We suspect your approach might be like the one in button 2. If this is the case, please use the input file’s calculation mode as mentioned. If not, provide us a simple issue reproducing sample or screen-shots. This will help us to investigate further on this.

 

Kindly try this and let us know if this helps.

Thanks,

Sudha Ramachandran

[Riccardo De Agostini]

Hi Sudha,

thanks a lot for your answer! My scenario is quite different, as my workbook is definitely not open while I use it; however, I've found out that executing the following code just before saving the workbook solves my problem:

                foreach (var sheetObj in workbook.Worksheets)

                    (sheetObj as IWorksheet).EnableSheetCalculations();

                foreach (var sheetObj in workbook.Worksheets)

                {

                    var sheet = sheetObj as IWorksheet;

                    foreach (var cell in sheet.Cells.Where(c => c.HasFormula))

                    {

                        var frml = cell.Formula;

                        cell.Value = null;

                        cell.Formula = frml;

                    }

                }

(BTW, shame on the forum's text editor for not providing a <code> tag, nor a preview)

In short, this is what I do now:

• open the workbook;
• insert all data. For every row of data I use the InsertRow method to copy a template row, which also contains a formula, then I set the values of the data cells;
• recalculate all formulas using the above code;
• save the workbook.

If i don't use the above code, even if I enable calculations on all worksheets first. the result is a workbook that contains the right formulas (with relative cell references correctly updated by InsertRow) but the wrong values - values that LibreOffice Calc (hey, not everyone has Excel - the file is in .xlsx format anyway) won't even recalculate until I edit each cell containing a formula.

The workbook's calculation method is set to automatic.

I have more useful info to give, but I'll just prepare a sample workbookm and a sample VS2012 solution and attach them to my next reply. In the meantime, my immediate problem is solved. Thanks again!

Riccardo

[Sudha Ramachandran]

Hi Riccardo,

 

It is good that your problem is resolved. It will be grateful for us to further investigate on this if you could share us your sample.

 

Regarding <code> tag or preview: We have forwarded your suggestion to concerned team.

 

Please let us know if you have any queries.

Thanks,

Sudha Ramachandran

[Ma-Estro]

Here is the sample. I remain at disposal for any clarification you may need.

Thanks again, have a great weekend!

Riccardo

Attachment: SalesReport_4acd9e69.zip

[Sudha Ramachandran]

Hi Riccardo,

 

Thank you for updating us.

 

The calculated values in both the files i.e. ‘SalesReport’ mentioned as file without recalculation and ‘SalesReport_Recalculated’ mentioned as file with recalculation, are same on our side. We have provided a video-screen shot representing this in the below link. Kindly open a new Direct-Trac incident for further follow-up, and share us a video screen shot or the output files, representing your scenario where formulas are not recalculated. This will help us to investigate further on this and provide a prompt solution.

 

Video Screenshot Link: http://www.syncfusion.com/downloads/support/directtrac/general/ScreenCapture_12-1-2014_5.44.38_PM.wmv-173427775.zip

 

Please let us know if you have any queries.

Thanks,

Sudha Ramachandran

[Riccardo De Agostini]

Hi Sudha,

I can see the two output workbooks are the same on your side, once you load them in Excel. I think at this point we've found out what's going on: LibreOffice does not seem to recalculate forumlas upon opening a workbook, as Excel does. In the attached screenshot you can see the two output workbooks, opened side by side in LibreOffice 4.3.

I understand that we're dealing with a bug in LibreOffice here; however, I wonder why XlsIO does not update dependent formula values when the value of a referenced cell changes.

Thanks again for your cooperation and best regards

Riccardo

Attachment: screenshot_93278d9f.zip

[Sudha Ramachandran]

Hi Riccardo,

 

Thank you for updating us.

 

XlsIO calculates formulas only when the ‘CalculatedValue’ or other formula values like ‘FormulaNumberValue’ or ‘FormulaStringValue’ of the referred range is accessed, due to performance lag. So, we consider this to be a feature request. Kindly create a Direct-Trac incident to log a feature request for the same.

 

Please let us know if you have any queries.

Regards,

Sudha Ramachandran

[Riccardo De Agostini]

Hi Sudha,

Thanks a lot for clarifying what's needed to update the cell values. There's even no need for a feature request.

Best regards

Riccardo

[Sudha Ramachandran]

Hi Riccardo,

 

Thanks for the update.

 

Please get back to us if you have any other concerns.

 

Regards,

Sudha Ramachandran