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.
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Formulas in Excel spreadsheet do not calculate until "Enable Editing" is clicked.

Thread ID:

Created:

Updated:

Platform:

Replies:

109560 Jun 19,2013 05:36 PM UTC Oct 10,2016 09:28 AM UTC ASP.NET Web Forms (Classic) 9
loading
Tags: XlsIO
Enes Karagic
Asked On June 19, 2013 05:36 PM UTC

Hi,

My spreadsheet contains formulas. When the spreadsheet is opened, the cells that contain a formula display "0" instead of the formula output number. The top of the Excel 2010 window states "Protected View This file originated from an Internet location and might be unsafe. Click for more details. Enable Editing

Once enable editing is clicked the cells that displayed "0" now display the correct formula output numbers.

 

So I have two questions:

1. Is there a way to display formula output numbers without having to click Enable Editing?

2. Is there code I can add that would disable the "Protected View" so that clients could edit spreadsheet without having to click Enable Editing?

 

 

 


Manikandan M [Syncfusion]
Replied On June 24, 2013 06:18 AM UTC

Hi Enes Karagic,

 

Thanks for using Syncfusion products.

 

Query 1:

We can save the file with evaluated value of the formula by Enable sheet calculations. We have shared the code snippet for enable worksheet calculations below. Please make use of this and let us know if this helps you.

Workaround Code snippet:

     workbook = application.Workbooks.Create();

            IWorksheet sheet = workbook.Worksheets[0];

            sheet.EnableSheetCalculations();

            sheet.Range["A2"].Number = 1;

            sheet.Range["A3"].Formula = "=A2";

            workbook.SaveAs("output.xlsx");

 

 

Query 2:

When downloading the file from internet, MS Excel opens in protect mode for security propose. And so we don’t provide any option to disable the protected view in XlsIO.

 

Thanks for your patience. Please let us know if you need any clarifications.

 

Thanks,

Manikandan M.           

 


Enes Karagic
Replied On July 24, 2013 08:04 PM UTC

Thank you for the response, solved my problem for coded reports.  The only time enabling sheet calculations is a problem is when I try to apply it to template reports. Is there something different that I have to do?






Manikandan M [Syncfusion]
Replied On July 31, 2013 11:06 AM UTC

Hi Enes Karagic,

 

Thanks for your patience.

 

The above reported query is not clear enough to understand as there is no more details about your sample scenario. Could you please share us some more details about your query and your sample scenario?

 

Thank you very much for your patience. Please let us know if you need any clarifications.

 

Thanks,

Manikandan M.

 


Sean Monad
Replied On December 8, 2013 03:00 PM UTC

Not sure if this was resolved, i downloaded the trail edition yesterday to check the XlsIO module and see if we can use this for our internal reporting and faced the same problem.

To make the request clear, lets assume we have a excel file with few columns with formula and charts etc and we save this file lets say SavedTemplate.xls (this file is created using simple MS Excel). Now in our code we open the same file using your DLL and fill few columns, the EnableSheetCalculations() wouldnt update the values of existing formula cell until we open the file in edit mode. Please let me know if i am missing something here. This is critical for us since we have a lot of complicated calculation and coding them all on runtime in the excel sheet is not a preferred way. Also keeping template enable us to modify the formula and ensure the code still works.

Also I couldnt find the .net 2.0 assemblies with the installer, have you discontinued the support for the same?

Thanks
SM

Johnson Monohar M [Syncfusion]
Replied On December 12, 2013 07:18 AM UTC

Hi Sean Monad,

The reported issue is logged as a feature request. Kindly follow-up in the incident 116676 for further queries.

Thanks,
Johnson

Josh Rimokh
Replied On April 28, 2014 03:55 PM UTC

Hi,
I'm having the same problem.  I use an xls template and then fill the fields in code.
When I open the excel it's in 'protected view' and the SUM calculated cell shows 0 until we enable editing.
Has this problem been resolved?
I'm unable to find an incident with the number 116676.  I'm assuming I can't see it because I wan't the one to initiate the issue.
Thanks


Sathish Babu R [Syncfusion]
Replied On May 21, 2014 06:04 AM UTC

Hi Josh,

Sorry for the inconvenience. The feature  "update the calculated values in the workbook before “Enable Editing” button" not yet implemented. You can follow up the feature in the incident 116676. Please use the below link to view the incident.

Incident Link:

Please let us know if you need any clarification.

Thanks,
Sathish

Ujjwal Sapkota
Replied On October 7, 2016 04:20 PM UTC

I am having similar issues when using IListObject table with calculated values. Is there something I can get done to resolve this?

Abirami Varadharajan [Syncfusion]
Replied On October 10, 2016 09:28 AM UTC

Hi Ujjwal, 
 
Thank you for contacting Syncfusion support. 
 
Currently XlsIO don’t have support to update the calculated values before Enabling “Enable Editing” button. We have added it to our feature request list and it will be included in any one of our upcoming release. 
However, as a workaround we recommend you to access the CalculatedValue property of cells in the worksheet with formula. This shows evaluated values in the formula cells before clicking Enable Editing button. 
 
Kindly refer below code snippet to achieve this. 
 
Code Example: 
            worksheet.EnableSheetCalculations(); 
            string dummy = null; 
            foreach (IRange range in worksheet.UsedCells) 
            { 
                if (range.HasFormula) 
                    dummy = range.CalculatedValue; 
            } 
 
We have also shared sample for your reference which can be downloaded from following location. 
 
 
Regards, 
Abirami. 


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

;