Formula Calculation

EPPlus supports formula calculation. 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 workb+ook
   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.
• Table formulas - don't use the syntax used in the Excel user interface. See this wiki page.

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();
}

The #NAME will occur if your formula contains an unsupported function. It can also occur due to missing formula prefixes.

Excel compatibility

The Formula calculation capabilities of EPPlus is a subset of Excel's - here are some examples that are currently not supported yet by EPPlus:

• Array Formulas (applying operators and conditions on combinations of ranges and so called spill over)
• The Intersect operator (space between addresses)
• Addresses in A1-format with more than two parts (> 1 colon)

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 a separate repository).

Floating point precision

Internally Excel stores numbers in the IEEE 754 binary 64-bit floating point format. In EPPlus we use the .NET double struct, which is same size and conforms to the same standard. Since some decimal numbers can't be exactly represented with a float number, these might look like a small error when you see the result of a calculation.

Here is a simple example where two doubles are added that you can try yourself in Visual Studios Immediate Window:

1.1d + 2.2d
3.3000000000000003

As you can see the result is not 3.3 as one would expect.

So when you see these small deviations from what Excel shows you, it is because EPPlus returns the "raw" result from the calculation based on mathematical operations on floating point numbers, whilst Excel rounds it to 15 significant figures. From version 5.5 we have added a parameter that rounds the .NET double type to 15 significant figures in the rounding functions (this is where the results can differ the most as a small deviation can cause the rounding to go in the "wrong" direction). The code below illustrates how this can generate different results.

using(var package = new ExcelPackage())
{
    var sheet = package.Workbook.Worksheets.Add("test");
    sheet.Cells["A1"].Value = 120253.8749999999d;
    sheet.Cells["A2"].Formula = "ROUND(A1,2)";
    sheet.Calculate(opt => opt.PrecisionAndRoundingStrategy = PrecisionAndRoundingStrategy.Excel);
    Assert.AreEqual(120253.88, sheet.Cells["A2"].Value);
    sheet.Calculate(opt => opt.PrecisionAndRoundingStrategy = PrecisionAndRoundingStrategy.DotNet);
    Assert.AreEqual(120253.87, sheet.Cells["A2"].Value);
}

The default value in EPPlus is PrecisionAndRoundingStrategy.DotNet, so if you want to use the Excel value you need to set it explicitly. You can also configure this behaviour via your application configuration file:

.NET Core/5+ - appsettings.json

{
  "EPPlus": {
    "ExcelPackage": {
      "LicenseContext": "Commercial",
      "PrecisionAndRoundingStrategy" :  "Excel"
    }
  }
}

.NET Framework (app.config/web.config)

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="EPPlus:ExcelPackage.LicenseContext" value="Commercial" />
    <add key="EPPlus:ExcelPackage.PrecisionAndRoundingStrategy " value="DotNet" />
  </appSettings>
</configuration>

Here is a good article on this topic.