Xlsio Formula Update -> Stackoverflow Exception

[Alek]

Howdy

I am attempting to update all formulas in a workbook that may have cross-worksheet formulas. My standard method has been working fine until a client had a document with lots of formulas that referred to the cell above it.

My project has to do the following:

1. Load a client's excel file
2. Update certain cell values (if they have formulas) which are the parameters of our SCADA scripts
3. Execute the SCADA scripts which will output tables or single values to ranges within the workbook
4. Update all formulas in entire workbook. These formulas usually refer to data retrieved from the SCADA scripts. This step is important and needs to complete 100% otherwise bad values will be included in the report.
5. Save

Step 4 is where a StackOverflow exception is occurring, and it cannot be caught within a try block.

A client is having an issue with one excel workbook that contains lots of formulas causing the application to throw an unhandled stack overflow exception. I've been able to reproduce it and create a simplified .sln with the xlsx embedded.

I've noticed I can adjust sheet.CalcEngine.MaximumRecursiveCalls to handle it, however I feel that the exception shouldn't be occurring.

Also, is there a better way I can be sure that all formulas in the workbook are updated?

Currently I call EnableSheetCalculations() and loop through every cell calling range.CalculatedValue, or sheet.CalcEngine.PullUpdatedValue( range.AddressLocal ). 

Attachment: XlsioCalculateStackoverflow_d6a43601.zip

[Abirami Varadharajan]

Hi Alek, 

Thank you for contacting Syncfusion support. 

We are able to reproduce the issue. However, it can be avoided by calculating the values in the threading  and  specify the maximum stack size (Default stack size is 1 Mb. Throws exception if exceeds). Please refer below code to achieve the same. 

 

        static void Main(string[] args) 

        { 

            ExcelEngine excelEngine = new ExcelEngine(); 

            var excelApplication = excelEngine.Excel; 

 

 

 

            using (var embedStream = EmbeddedFiles.OpenFile(EmbeddedFiles.ExcelDocumentName)) 

                xlsxWorkbook = excelApplication.Workbooks.Open(embedStream, ExcelOpenType.Automatic); 

 

            worksheet = xlsxWorkbook.Worksheets.First(); 

 

            var maxSize = 10000000; 

            var thread = new Thread(GetCalculatedValue, maxSize); 

            thread.Start(); 

            thread.Join(); 

 

        } 

        private static void GetCalculatedValue() 

        { 

            worksheet = xlsxWorkbook.Worksheets[0]; 

            worksheet.EnableSheetCalculations(); 

            worksheet.CalcEngine.UseFormulaValues = true; 

            worksheet.CalcEngine.AllowShortCircuitIFs = true; 

            worksheet.CalcEngine.MaximumRecursiveCalls = 10000; 

            worksheet.CalcEngine.IterationMaxCount = 10000; 

            worksheet.CalcEngine.ThrowCircularException = true; 

            CalcEngine.MaxStackDepth = 10000; 

         

            foreach (IRange cell in worksheet.UsedRange.Cells.Where(r => r.HasFormula)) 

            { 

                try 

                { 

                    // Trigger formula to update  

                    string dummy = worksheet.CalcEngine.PullUpdatedValue(cell.AddressLocal); 

                } 

                catch (Exception e) 

                { 

                    Debug.WriteLine("Failed to update cell with formula: " + cell.Formula + " at " + cell.AddressLocal); 

                } 

            } 

 

        } 

We have also modified your sample according to it and the sample can be downloaded from following link. 

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

Regards, 

Abirami.