Skip to content

Working with External Workbooks

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 xltx).
  • 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 an 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

Dim externalWorkbook = p.Workbook.ExternalLinks(0).As.ExternalWorkbook
Dim 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.

The External Workbook Cache

References to external workbooks can have a cache that is used 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 doesn'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();

'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 calculating the referencing workbook. Note that EPPlus doesn'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();

'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.
Dim 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();

'Update the cache for all external workbooks in the collection
package.Workbook.ExternalLinks.UpdateCaches()

'If you only want to update a single external workbook.
Dim externalWorkbook = p.Workbook.ExternalLinks(0).As.ExternalWorkbook
externalWorkbook.UpdateCache()

Adding a reference to an external workbook

You can also add a reference to an external workbook to your package. 

//Add a reference to an external file.
var externalLinkFile = new FileInfo("ExternallyLinkedData.xlsx");
var externalWorkbook = p.Workbook.ExternalLinks.AddExternalWorkbook(externalLinkFile);

'Add a reference to an external file.
Dim externalLinkFile = New FileInfo("ExternallyLinkedData.xlsx")
Dim 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);

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

Samples

See Sample 1.9-C# or Sample 1.9-VB