EPPlus supports formula calculation from version 4. This means that you can let EPPlus calculate the results of the formulas in a workbook.
This is done by calling the Calculate() method, which is available on Workbook, Worksheet and Range level. When Calculate() is called EPPlus will evaluate the result of the formula and store the result as the Value of the cell - just like Excel do.
using(var package = new ExcelPackage(new FileInfo(@"c:\temp\tmp.xlsx")))
// calculate all formulas in the workbook
// calculate one worksheet
// calculate a range
Some common mistakes
- Don't use localized function names. Only english names (such as SUM, IF, VLOOKUP, etc) are supported.
- Don't use semicolon as a separator between function arguments. Only comma is supported.
- Don't add the leading = sign in your formula. "=SUM(A1:A2)" is wrong, "SUM(A1:A2)" is correct.
If the formula calculation fails the calculated cells might contain excel errors (#VALUE, #NAME, etc) instead of the expected results. You can attach a logger to the formula parser before you call the
method - the logfile might be helpful to analyze the error/errors.
var excelFile = new FileInfo(@"c:\myExcelFile.xlsx");
using (var package = new ExcelPackage(excelFile))
// Output from the logger will be written to the following file
var logfile = new FileInfo(@"c:\logfile.txt");
// Attach the logger before the calculation is performed.
// Calculate - can also be executed on sheet- or range level.
// The following method removes any logger attached to the workbook.
By default EPPlus will throw an exception when it detects a circular reference. This feature can be disabled by supplying an instance of
to the Calculate method. Set the property AllowCircularReferences
The following features of Excel are currently not supported by EPPlus when it comes to Formula caluclation: Array Formulas, the Intersect operator, references to external workbooks.
We are are trying to keep EPPlus compatible with the most common functions in Excel, but it does not support all functions. See this page -
. You can also add your own implementation of functions in runtime, see the Samples project (available in the downloads section).