We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Detecting and listing circular references

I have a special use case related to working with complex Excel files, and I was hoping to see if the Aspose Cells product could help with this:

  • We develop a WinForms application that utilises SpreadsheetGear .Net component to load up an xlsx template.
  • Our users can then use the application similar to Excel; they can add their own worksheets to the template, enter their own custom formulas, etc.
  • When writing their own custom formulas, they can inadvertently create circular references, which we recommend for them to avoid.
  • Since SpreadsheetGear doesn’t have its own circular reference detection feature, we had to develop our own that tries to list all the cells in the circular reference. It leverages the installed version of Excel on the users machine for this.
  • This is not a very efficient process, as it takes a very long time. Sometimes with complex models, it cannot even tell us where the circular references are located (similar to Excel, when on some complex files, it gives the user the unhelpful message of “Excel can’t calculate a formula because of a circular reference, but it can’t be shown.” and the Circular Reference drop-down list in the menu is greyed-out and empty.)

Could your Aspose Cells product be used to build a function in our application to detect, and then list the circular references in a file that is faster, more stable and accurate than Excel?

If so:

  • Are there any known limitations on the type of xlsx files that Aspose Cells can process? (e.g. compatibility with formulas, file size, etc)
  • Do you have any code samples for this specific use case?

Thanks

@EMASTER,
We think Aspose.Cells supports those features you required.

For xlsx files, our component has no limitations for processing them. Of course, the larger the file size is, the more resources(memory) will be required for your application.

For detecting circular references, here we provide an example, you may adapt it accordingly for your requirement:

            Workbook wb = new Workbook();
            Worksheet sheet = wb.Worksheets[0];
            Cells cells = sheet.Cells;
            cells[0, 0].Formula = "=B1";
            cells[0, 1].Formula = "=A2";
            cells[1, 0].Formula = "=B2";
            //enable the calculation chain for this workbook
            wb.Settings.FormulaSettings.EnableCalculationChain = true;
            //calculate the workbook, calculation chain will be created during the calculation.
            wb.CalculateFormula();
            //...
            //update or set cell's formula, then check circular reference by calculating it
            cells[1, 1].Formula = "=A1";
            try
            {
                wb.CalculateFormula(new CalculationOptions() { CalculationMonitor = new CM() });
            }
            catch(CellsException ce)
            {
                if(ce.Code == ExceptionType.Interrupted)
                {
                    Console.WriteLine(ce.Message);
                }
                else
                {
                    Console.WriteLine("Unexpected exception: " + ce.Message);
                }
            }

        class CM : AbstractCalculationMonitor
        {
            public override bool OnCircular(IEnumerator circularCellsData)
            {
                StringBuilder sb = new StringBuilder(256);
                sb.Append("Circular references: ");
                circularCellsData.MoveNext();
                CalculationCell cc = (CalculationCell)circularCellsData.Current;
                string cn = CellsHelper.CellIndexToName(cc.CellRow, cc.CellColumn);
                sb.Append(cn);
                while (circularCellsData.MoveNext())
                {
                    cc = (CalculationCell)circularCellsData.Current;
                    sb.Append("->");
                    sb.Append(CellsHelper.CellIndexToName(cc.CellRow, cc.CellColumn));
                }
                sb.Append("->");
                sb.Append(cn);
                Interrupt(sb.ToString());
                return false;
            }
        }

Thank you @johnson.shi

We implemented your example within a proof of concept of ours, and it is working quite well except for one Excel file.

In this Excel file (which I am unable to share unfortunately), we experienced an Object Reference error:
error.png (61.7 KB)
error2.png (27.7 KB)

I know its a bit difficult to see what the issue is without the file, but can you advise what may possibly cause this error and suggest any things we can try?

@EMASTER,
From the stack trace we think the exception was caused by the creation of calculation chain. As you know, formula calculation is very complicated and completely depends on the concrete dataset. We are afraid we cannot know more details about the issue without the template file. For your case, if you calculate the workbook only once, you do not need to enable the calculation chain. So, you may remove the code
wb.Settings.FormulaSettings.EnableCalculationChain = true;
and then try your test again.

If you can provide some template file(maybe you can create a new one based on your original file by removing some sensitive data) to reproduce the issue(according to your requirements in the initial post, we think maybe you still need the calculation chain later in production), we will look into it and try to fix it soon.

@johnson.shi
If I remove the code wb.Settings.FormulaSettings.EnableCalculationChain = true;, will I still be able to check for circular references ? I thought it was required to do so.

@EMASTER,

Formula calculation chain is mainly for helping to improve the performance of calculating workbook repeatedly when you need to change only part of data every time. It is required by some APIs such as Cell.GetDependentsInCalculation()/GetPrecedentsInCalculation() too. But it will not influence the custom calculation monitor during the calculation if you need to calculate your workbook only once.