Circular references - donkma93/EPPlus GitHub Wiki

Behaviour

By default EPPlus will throw an exception when it detects a circular reference. This feature can be disabled by supplying an instance of ExcelCalculationOption to the Calculate method. If you set the property AllowCircularReferences to true, the formula engine will instead treat cells with circular references as empty values.

EPPlus 5 has improved handling of circular references during calculation compared to EPPlus 4. Functions that should not be affected by circular references such as ROW, ROWS, COLUMN and COLUMN now works as expected and will not throw an exception when they refer to their own cell. If ExcelCalculationOption.AllowCircularReferences is set to true a cell containing a circular reference will be handled as a cell containing no value, which is more like Excel handles it after the initial warning you will get.

Configuration

You can configure the behaviour of the formula engine regarding circular references via your applications configuration settings. This way you don't have to set the ExcelCalculationOption property each time you call Calculate().

.NET Core (appsettings.json)

{
  "EPPlus": {
    "ExcelPackage": {
      "LicenseContext": "Commercial",
      "AllowCircularReferences" :  "true"
    }
  }
}

.NET Framework (app.config/web.config)

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="EPPlus:ExcelPackage.LicenseContext" value="Commercial" />
    <add key="EPPlus:ExcelPackage.AllowCircularReferences" value="false" />
  </appSettings>
</configuration>