We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on : Nov 16th 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

How do I recalculate all formulas in a workbook?

Thread ID:

Created:

Updated:

Platform:

Replies:

117677 Nov 26,2014 04:58 PM UTC Dec 5,2014 12:07 PM UTC WinForms 9
loading
Tags: XlsIO
Riccardo De Agostini
Asked On November 26, 2014 04:58 PM UTC

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 [Syncfusion]
Replied On November 27, 2014 12:32 PM UTC

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
Replied On November 27, 2014 01:35 PM UTC

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 [Syncfusion]
Replied On November 28, 2014 12:49 PM UTC

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
Replied On November 28, 2014 02:26 PM UTC

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 [Syncfusion]
Replied On December 1, 2014 12:47 PM UTC

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
Replied On December 3, 2014 11:15 AM UTC

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 [Syncfusion]
Replied On December 4, 2014 01:05 PM UTC

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
Replied On December 4, 2014 04:25 PM UTC

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 [Syncfusion]
Replied On December 5, 2014 12:07 PM UTC

Hi Riccardo,

 

Thanks for the update.

 

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

 

Regards,

Sudha Ramachandran


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;