Circular Reference detection does not consider workbook

[colbybhearn]

Good day, gentlemen.

We have worksheet called LinkTab in workbook wb1 containing formulas that reference cells in a worksheet called LinkTab in workbook wb2. The sheets were named identically like this as part of a convention for humans to readily know the linked cells between the two workbooks.

With the external link intact in wb1, EPPlus has always said there's a circular reference despite Excel saying there are none. I now think Epplus is not considering the containing workbook when looking for circular references while resolving the address. Since wb1's LinkTab sheet contains references to a sheet called "LinkTab" (despite it being a reference to a sheet in the external reference wb2), EPPlus throws CircularReferenceExceptions. I had ExcelCalculationOption.AllowCircularReferences set to false, as I knew the Excel workbooks were free of circular references. For a long time, we just always broke the links in wb1 to get around this whole mystery in production.

EPPlus/src/EPPlus/FormulaParsing/DependencyChain/DependenyChainFactory.cs

Lines 351 to 359 in ca1bd9c

	foreach (var par in stack)
	{
	if ((par.iteratorWs!=null && par.iterator!=null && ExcelCellBase.GetCellId(par.iteratorWs.IndexInList, par.iterator.Row, par.iterator.Column) == id) ||
	ExcelAddressBase.GetCellId(par.wsIndex, par.Row, par.Column) == id) //This is only neccesary for the first cell in the chain.
	{
	if (options.AllowCircularReferences == false)
	{
	throw (new CircularReferenceException(string.Format("Circular Reference in cell {0}!{1}", par.ws.Name, ExcelAddress.GetAddress(f.Row, f.Column))));
	}

Now, this is not terribly new - it has been going since v5 when I started using EPPlus. We realized this piece of the puzzle only recently and I confirmed our idea solution by renaming the sheet in wb2 from LinkTab to LinkTab2 while both workbooks were simultaneously open in Excel. The CircularReferenceExceptions stopped being thrown subsequently, despite the wb1 link being intact and AllowCircularReferences still set to false.

Also, as a side note, the Circular Reference exceptions thrown at the bottom of DependencyChainFactory included the wrong tab name. The cell address relative to a sheet has always turned out to be correct, but the sheet name being reported never lines up. However, I did not verify that it's also wrong outside my false positive circular reference scenario.

I hope this is helpful and clear. I'm happy to provide more detail if not.
-Colby

[JanKallman]

Apologies for the delayed response to this issue. I had totally missed. it.
EPPlus only checks for circular references within the workbook. No checks are made on external references at this time.
I will check the issue wrong worksheet name, to make sure the correct worksheet name is returned in the exception.
Using the new EPPlus 7 preview, just release, might fix this issue, as this part has been rewritten.

[colbybhearn]

No worries on overlooking. Thank you all so much for investigating and doing what you do!