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

[Enes Karagic]

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]

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]

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]

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]

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]

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]

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]

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:

http://www.syncfusion.com/support/directtrac/incidents/116676

Please let us know if you need any clarification.

Thanks,

Sathish

[Ujjwal Sapkota]

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

[Abirami Varadharajan]

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. 

 

Sample Link: http://www.syncfusion.com/downloads/support/directtrac/general/ze/XlsIOSample1647569912.zip  

 

Regards, 

Abirami. 

[Dylan Wegner]

Hello, for me when the excel sheet opens, but it doesn't do the calculation because all the numbers are text. How do I get around this? I am using the Xamarin.Forms platform not the ASP.NET. 

Thank you,

Dylan

[Dilli Babu Nandha Gopal]

Hi Dylan,  

Thank you for contacting Syncfusion support.  

As per Microsoft Excel behavior, when the cell type is a text then the calculation will not give expected result as number type for same number values. In order to calculate the formula, the cell type needs to be number.   

Please let us know if you have any other queries. 

Regards, 

Dilli babu