Detecting and listing circular references

Aspose.Cells Product Family
1

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

2

@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;
            }
        }
3

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?

4

@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.

5

@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.

6

@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.

7

@johnson.shi
We are getting what we believe is a false negative in the circular reference detection.
Is it possible to share our Excel file via email rather than posting on this forum?

8

@johnson.shi
I managed to remove all the unnecessary content from the file, so I can share it with you here. Please see attachedCirc2_nolinks_noformulas5.zip (553.6 KB)

It is reporting, what we believe to be a false a circular reference detection in cell CashFlow!GH521

The code we use to detect this is as follows:

var wb = new Aspose.Cells.Workbook(fstream);
wb.CalculateFormula(new Aspose.Cells.CalculationOptions() { CalculationMonitor = new CircularReferenceAsposeProcessorCalculationMonitor() });

We are using the latest version of Aspose.Cells 22.10.0

Can you please advise what could be causing this. Thank you.

9

@EMASTER,

Thanks for the template XLSX file.

Please notice, after an initial test, I am able to reproduce the issue as you mentioned by using the following sample code with your template file. I found an incorrect or false circular reference detection in cell CashFlow!GH521 via Aspose.Cells APIs:
e.g.
Sample code:

 Workbook wb = new Workbook("e:\\test2\\Circ2_nolinks_noformulas5.xlsx");
            Worksheet sheet = wb.Worksheets[0];
            Cells cells = sheet.Cells;
            //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();
            try
            {
                wb.CalculateFormula(new CalculationOptions() { CalculationMonitor = new CircularReferenceAsposeProcessorCalculationMonitor() });
            }
            catch (CellsException ce)
            {
                if (ce.Code == ExceptionType.Interrupted)
                {
                    Console.WriteLine(ce.Message);
                }
                else
                {
                    Console.WriteLine("Unexpected exception: " + ce.Message);
                }
            }

............

class CircularReferenceAsposeProcessorCalculationMonitor : 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;
        }
    }

I have logged a ticket with an id “CELLSNET-52158” for your issue. We will look into the details of the issue.

Once we have an update on it, we will let you know.

10

Hi @Amjad_Sahi, is there any update on the ticket?

11

@EMASTER,

This is to inform you that your issue (logged earlier as “CELLSNET-52158”) has been resolved now. The fix will be included in our upcoming release (Aspose.Cells v22.11) which is scheduled in the first half of November, 2022. You will also be notified when the next version is released.

12

The issues you have found earlier (filed as CELLSNET-52158) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi