Data Validation and ExcelFormula

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.

See also

• Data validation