Formula Calculation

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

using(var package = new ExcelPackage(new FileInfo(@"c:\temp\tmp.xlsx")))
{
   // calculate all formulas in the workbook
   package.Workbook.Calculate();
   // calculate one worksheet
   package.Workbook.Worksheets["my sheet"].Calculate();
  // calculate a range
  package.Workbook.Worksheets["my sheet"].Cells["A1"].Calculate();
}

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.
• The .Calculate() methods are available on Workbook, Worksheet and Range level only after a using OfficeOpenXml;. Alternatively, you might call method OfficeOpenXml.CalculationExtension.Calculate()

Trouble shooting/Logging

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 Calculate() 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.
         package.Workbook.FormulaParserManager.AttachLogger(logfile);
         // Calculate - can also be executed on sheet- or range level.
         package.Workbook.Calculate();
         // The following method removes any logger attached to the workbook.
         package.Workbook.FormulaParserManager.DetachLogger();
}

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. Set the property AllowCircularReferences to true.

Known issues

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 - Supported functions. You can also add your own implementation of functions in runtime, see the Samples project (available in the downloads section).