Skip to content

Data Validation and ExcelFormula

When a data validation is loaded from a file, EPPlus normalizes its formulas for ease of use. If the content of ExcelFormula is a valid literal value, such as 1, it is exposed through Formula.Value instead. This gives you a typed value to work with rather than a formula string.

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

Using package As New ExcelPackage("c:\temp\myWorkbook.xlsx")
    Dim sheet = package.Workbook.Worksheets.Add("Example")
    Dim validation = sheet.DataValidations.AddIntegerValidation("A1")

    validation.Operator = ExcelDataValidationOperator.lessThan
    validation.Formula.ExcelFormula = "1"
    SaveAndCleanup(package)
End Using

When the workbook is loaded again, the validation looks 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;
}

Using package As New ExcelPackage("c:\temp\myWorkbook.xlsx")
    Dim workSheet = package.Workbook.Worksheets(0)
    Dim validation = workSheet.DataValidations(0)

    Dim excelFormula = validation.Formula.ExcelFormula
    Dim value = validation.Formula.Value
End Using

Since "1" is a valid value, EPPlus reads it as a value when the file is loaded, so you get:

  • excelFormula = null
  • value = 1

For consistent results between the code that creates the validation and the code that reads it, we recommend assigning literal values to Formula.Value, and using ExcelFormula for cell addresses and other formulas.

See also