Data Validation and ExcelFormula

This page has moved. The current version of this page is at https://epplussoftware.com/docs/worksheet-and-ranges/data-validation/data-validation-and-excelformula/. The EPPlus documentation is no longer updated in this wiki; please use the EPPlus guides and update your bookmarks.

A potentially unexpected behaviour of DataValidations in EPPlus is that assignment of values in ExcelFormula will be read into Formula.Value if the file is read again.

As an example, observe the following code:

using(var package = new ExcelPackage(@"c:\temp\myWorkbook.xlsx"))
{
  var sheet = package.Workbook.Worksheets.Add("Example");
  var validation = sheet.DataValidations.AddIntegerValidation("A1");

  validation.Operator = ExcelDataValidationOperator.lessThan;
  validation.Formula.ExcelFormula = "1";
  SaveAndCleanup(package);
}

When then loading the package after creation like this:

using(var package = new ExcelPackage(@"c:\temp\myWorkbook.xlsx"))
{
  var workSheet = package.Workbook.Worksheets[0];
  var validation = workSheet.DataValidations[0];

  var excelFormula = validation.Formula.Excelformula;
  var value = validation.Formula.Value;
}

It would be natural to think that in this code:

excelFormula = "1"

and

value = null

However, since "1" is a valid value, EPPlus will read it into the system as a value automatically for ease of use. So when the file is read we will actually get:

excelFormula = null

and

value = 1

To avoid confusion, it is recommended to always assign to Formula.Value when assigning a valid value, and to use ExcelFormula for addresses and potentially other use-cases.

• Data validation