Circular References Formula Not Calculating

[Nagendra Gupta]

Hey Team,

I am using XlsIO for calculating excel formula but unable to find exact solution

Update Cell B1 Value with 21000 but after updating new values are not calculated

function one(){

ExcelEngine excelEngine = new ExcelEngine();

                    IApplication app = excelEngine.Excel;

                    workbook = app.Workbooks.Open(filePath, ExcelOpenType.Automatic);

                    //first worksheet object of worksheets collection

                    worksheet = workbook.Worksheets[excelSheetName];

                    if (worksheet.IsNull())

                    {

                        Utils.ShowDevError("Sheet Name : [ " + excelSheetName + " ] not found excel file");

                    }

                    var maxSize = 10000000;

                    var thread = new Thread(GetCalculatedValue, maxSize);

                    thread.Start();

                    thread.Join();

}

function GetCalculatedValue(){

workbook.CalculationOptions.IsIterationEnabled = true;

            //Number of times to recalculate

            workbook.CalculationOptions.MaximumIteration = 10000;

            //Number of acceptable changes

            workbook.CalculationOptions.MaximumChange = 10000;

            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;

            worksheet[1, 2].Value = "21000";

            //worksheet.Calculate();

            workbook.CalculationOptions.RecalcOnSave = false;

            workbook.Save();

            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)

                {

                }

            }

            excelTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ComputedFormulaValues); //excel.ConvertExcelToDataTableWithoutHeader(filePath,excelSheetName);

            workbook.Close();

}

Attachment: CTCStructure_47449021.zip

[Ramya Sivakumar]

Hi Nagendra, 

 

Greeting from Syncfusion. 

We can reproduce the reported improper calculated values issue at our end and forwarded it to the concerned team. We will share the validation details on March 3, 2022. 

Regards, 

Ramya. 

[Nagendra Gupta]

Hi  Ramya,

Thanks for the update, hope we get the solution on given timeline

Regards,

Nagendra

[Ramya Sivakumar]

Hi Nagendra, 

  

We appreciate your patience. 

  

The concerned team has confirmed the issue as a defect, and we will share the feedback link and the timeline to provide the fix tomorrow [ March 4, 2022]. 

  

Regards, 

Ramya. 

[Ramya Sivakumar]

Hi Nagendra, 

We appreciate your patience. 

We have confirmed the issue as Circular reference is not returned properly after updating cell value programmatically and logged a defect report. We will include the fix for this issue in our upcoming weekly NuGet release scheduled for March 29, 2022.  

You can track the status of the defect report through the following feedback link. 

Track Status: https://www.syncfusion.com/feedback/33175/circular-reference-is-not-returned-properly-after-updating-cell-value 

We will let you know once the new package version is available on nuget.org. 

Regards, 

Ramya. 

[Ramya Sivakumar]

Hi Nagendra, 

 

We appreciate your patience. 

 

We have returned the Calculated value as zero for the circular reference cell. Hence the calculated value was incorrect for all the cells. In MS-Excel while enabling the iterative calculation and providing iteration count, Microsoft Excel calculates the circular reference formula based on the number of iterations. Currently, we do not have support to enable iterative calculation in XlsIO like MS Excel.  

 

We have logged a feature report as Support to enable iterative calculation option in XlsIO, but do not have any immediate plans to implement this feature in near future. You can track the status of the feature report through the following feedback link. 

Track Status: https://www.syncfusion.com/feedback/32834/support-to-enable-iterative-calculation-option-in-xlsio 

Regards, 

Ramya.