-
Notifications
You must be signed in to change notification settings - Fork 275
External links
EPPlus from version 5.7 supports external references via the Workbook.ExternalLinks
collection.
This includes support for using the external workbooks in the formula calculation. By default EPPlus will use the external workbook cache saved with the workbook, but you can also load the external workbook, and use the values directly from another package.
Version 5.7 supports:
- Formula calculation via the external workbook cache or via loading and calculating the external package directly.
- Updating the external workbook cache (xlsx, xlsm and xlst).
- Adding references to external workbooks.
- Breaking links to external workbooks.
- Retaining OLE and DDE links (read only).
For external links to other workbooks Excel normally uses an index between brackets to reference a item in the external references collection. In a formula this can look something like this:
ws.Cells["A1:C3"].Formula = "[1]SimpleWorksheet!A1";
In this example, Cell A1 in worksheet SimpleWorksheet is referenced for the external reference with index 1.
The external references can be found in the package.Workbook.ExternalLinks
collection. Index 1 references the first item in the collection (Index 0 references the current workbook). If you have multiple external links in your workbook you prefix the address with the index of that external link.
var externalWorkbook=p.Workbook.ExternalLinks[0].As.ExternalWorkbook;
var address = $"'[{externalWorkbook.Index}]Sheet 1'!F38"`
//The first external reference in the collection will have `Index` 1, setting the `address` to '[1]Sheet 1'!F38
Note that the Index should be included in the single quotes if the worksheet name contains spaces or other characters that need to be wrapped.
References to external workbooks can have a cache that is used if when the external workbook is not available or loaded. The external cache contains cell values from the last time the external workbook was updated and information about defined names defined. The cache is stored within the workbook.
The cache is used to calculate formulas without having the original workbook available. Be aware that as the cache only contains the cell values, so not all formulas will evaluate to the same value. An example where it can differ is when using a formula, like SUBTOTAL
that don't sum hidden rows or formulas that is dependent on on cell formats.
EPPlus will by default calculate any external formula using the internal cache. Optionally you can load the external workbook and use it in the calculation.
//In this case, EPPlus uses the package internal saved cache for the external workbook to calculate the formulas referencing this workbook.
ws.ClearFormulaValues();
ws.Calculate();
To calculate using the actual workbook instead, you can load the external workbook. If the external workbook needs calculation, please make sure to call calculate on each external workbook before calculation the referencing workbook. Note that EPPlus don't follow formula dependency chains over packages, so if you have circular references between packages EPPlus will currently not handle that.
//To avoid this behavior you can load the external workbook before doing the calculate.
//This is only an issue in special cases where the function needs information not available in the cache, as for example hidden cells and numeric formats.
var externalWorkbook = p.Workbook.ExternalLinks[0].As.ExternalWorkbook;
externalWorkbook.Load();
ws.ClearFormulaValues();
ws.Calculate();
If you change values in an external workbook and you want to reflect the changes to the cache, you use UpdateCaches
method.
//Update the cache for all external workbooks in the collection
package.Workbook.ExternalLinks.UpdateCaches();
//If you only want to update a single external workbook.
var externalWorkbook = p.Workbook.ExternalLinks[0].As.ExternalWorkbook;
externalWorkbook.UpdateCache();
You can also add a reference to an external workbook to your package.
//Add a reference to an external file.
var externalLinkFile = new FileInfo("ExternalyLinkedData.xlsx");
var externalWorkbook = p.Workbook.ExternalLinks.AddExternalWorkbook(externalLinkFile);
This adds a reference to the workbook so it can be accessed in formulas. Use the Index
of the externalWorkbook
to access it in formulas.
EPPlus will break any formula links to an external workbook when it's removed.
To remove an external workbook use the Remove
or RemoveAt
methods of the ExternalLinks
collection.
//Removes the external reference at position 0 in the collection and break any links in formulas or defined names.
p.Workbook.ExternalLinks.RemoveAt(0);
- Formula calculation - Formula dependency chains is currently limited within a package. This means that circular references between different packages will not be handled.
- External references to pivot table sources is currently not supported.
- DDE and OLE links are only retained and can not be added or changed.
See Sample 1.9-C# or Sample 1.9-VB
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