-
Notifications
You must be signed in to change notification settings - Fork 270
Circular references
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 and up 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 how Excel handles it after the initial warning you will get.
You can configure the behaviour of the calculation 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, not supported in .NET Core, 6, 7)
<?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>
EPPlus Software AB - https://epplussoftware.com
- Getting Started
- The Sample Project
- EPPlus and Docker
- EPPlus and Blazor
- EPPlus and Linux
- Security
- Configuration
- Releases/versioning
- What is new in EPPlus 5+
- Breaking Changes in EPPlus 5
- Breaking Changes in EPPlus 6
- Breaking Changes in EPPlus 7
- Addressing a worksheet
- Dimension/Used range
- Copying ranges/sheets
- Insert/Delete
- Filling ranges
- Sorting ranges
- Taking and skipping columns/rows
- Data validation
- Comments
- Freeze and Split Panes
- Header and Footer
- Autofit columns
- Grouping and Ungrouping Rows and Columns
- Formatting and styling
- Conditional formatting
- Using Themes
- Working with custom named table- or slicer- styles