Data Validation Examples

Data Validation Types

There are 8 types of data validation

1. Any
2. Whole
3. Decimal
4. List
5. Date
6. Time
7. TextLength
8. Custom

Any Validation

Lacks any type of formula but allows for adding tooltips to cells (As do all other . Can be used like this for example:

using (ExcelPackage package = new ExcelPackage())
{
   var ws = package.Workbook.Worksheets.Add("test");

   var anyValidation = ws.DataValidations.AddAnyValidation("A1");
   anyValidation.ShowInputMessage = true;
   anyValidation.PromptTitle = "A Popup";
   anyValidation.Prompt = "Put info about what's in this field or fields here without restricting it.";

   package.SaveAs("C:/temp/AnyExample.xlsx");
}

Whole Validation

Validation of whole(int) values as defined by Operator and Formula and in some cases Formula2

using (ExcelPackage package = new ExcelPackage())
{
   var ws = package.Workbook.Worksheets.Add("test");

   var intValidation = ws.DataValidations.AddIntegerValidation("A1");
   intValidation.Operator = ExcelDataValidationOperator.lessThanOrEqual;
   intValidation.Formula.Value = 5;

   intValidation.ShowErrorMessage = true;
   intValidation.ErrorTitle = "Input invalid in Data Validation";
   intValidation.Error = "Value must be 5 or less!";

   package.SaveAs("C:/temp/WholeExampleSingle.xlsx");
}

With Operators Between or NotBetween you have to specify a second value via formula2

using (ExcelPackage package = new ExcelPackage())
{
   var ws = package.Workbook.Worksheets.Add("test");

   var intValidation = ws.DataValidations.AddIntegerValidation("A1");
   intValidation.Operator = ExcelDataValidationOperator.ExcelDataValidationOperator.between;
   intValidation.Formula.Value = 5;
   intValidation.Formula2.Value = 10;

   intValidation.ShowErrorMessage = true;
   intValidation.ErrorTitle = "Input invalid in Data Validation";
   intValidation.Error = "Value must between 5 and 10!";

   package.SaveAs("C:/temp/WholeExampleBetween.xlsx");
}

Decimal Validation

Same as Whole but with decimal values e.g "1.5" note that data type is double. To add one same as above except:

ws.DataValidations.AddDecimalValidation("A1")

List Validation

Defines a list of values with optional dropdown.

The following code adds a dropdown list of valid options based on an Excel range.

private static void AddListValidationFormula(ExcelPackage package)
{
    var sheet = package.Workbook.Worksheets.Add("list formula");
    sheet.Cells["B1"].Style.Font.Bold = true;
    sheet.Cells["B1"].Value = "Source values";
    sheet.Cells["B2"].Value = 1;
    sheet.Cells["B3"].Value = 2;
    sheet.Cells["B4"].Value = 3;
            
    // add a validation and set values
    var validation = sheet.DataValidations.AddListValidation("A1");
    // Alternatively:
    // var validation = sheet.Cells["A1"].DataValidation.AddListDataValidation();
    validation.ShowErrorMessage = true;
    validation.ErrorStyle = ExcelDataValidationWarningStyle.warning;
    validation.ErrorTitle = "An invalid value was entered";
    validation.Error = "Select a value from the list";
    //Keep in mind that this is a formula and as such you usually want to use absolute values so it does not change the range
    validation.Formula.ExcelFormula = "$B$2:$B$4";
            
}

The following code adds a dropdown list of valid options based on a set of values. Note that this option is only for a small amount of values, since Excel has a built in limitation on the length of the field containing the values. If you want to create a data validation with a larger number of options, reference a formula instead as demonstrated above.

private static void AddListValidationValues(ExcelPackage package)
{
    var sheet = package.Workbook.Worksheets.Add("list values");

    // add a validation and set values
    var validation = sheet.DataValidations.AddListValidation("A1");
    validation.ShowErrorMessage = true;
    validation.ErrorStyle = ExcelDataValidationWarningStyle.warning;
    validation.ErrorTitle = "An invalid value was entered";
    validation.Error = "Select a value from the list";
    for (var i = 1; i <= 5; i++)
    {
        validation.Formula.Values.Add(i.ToString());
    }
    Console.WriteLine("Added sheet for list validation with values");

}

Date Validation

Used to limit dates. Datatype is DateTime.

Following example shows how to add a dateTime with greaterThan operator. DateTime like other validations can also have Between and NotBetween operators which requires two values.

using (ExcelPackage package = new ExcelPackage())
{
   var ws = package.Workbook.Worksheets.Add("test");

   var dateValidation = ws.DataValidations.AddDateTimeValidation("B1");
   dateValidation.Operator = ExcelDataValidationOperator.greaterThan;
   DateTime date = new DateTime(1997, 05, 01);
   dateValidation.Formula.Value = date;

   dateValidation.ShowErrorMessage = true;
   dateValidation.ErrorTitle = "Input invalid in Data Validation";
   dateValidation.Error = $"Date must be after {date}";

   package.SaveAs("C:/temp/DateTimeExample.xlsx");
}

Time Validation

Used to add time restrictions. The example below shows how to add a validation that ensures entered time must be later than 14:15. Note that the value of Formula can have properties changed directly here unlike most other validations.

using (ExcelPackage package = new ExcelPackage())
{
  var ws = package.Workbook.Worksheets.Add("test");

  var timeValidation = ws.DataValidations.AddTimeValidation("C1");
  timeValidation.Operator = ExcelDataValidationOperator.greaterThan;
  timeValidation.Formula.Value.Hour = 14;
  timeValidation.Formula.Value.Minute = 15;

  timeValidation.ShowErrorMessage = true;
  timeValidation.ErrorTitle = "Input invalid in Data Validation";
  timeValidation.Error = $"Time must be after {timeValidation.Formula.Value}";

  package.SaveAs("C:/temp/TimeExample.xlsx");
}

Text Length Validation

Same as Whole Validation but excel will count characters of the text instead with the provided values.

Created with ws.DataValidations.AddTextLengthValidation("A1)

Custom Validation

Allows for custom defined formulas for data validation.

The following example shows an example of adding a custom validation to cells A1 to A5 that only accepts numbers

using (ExcelPackage package = new ExcelPackage())
{
   var ws = package.Workbook.Worksheets.Add("test");
   var customValidation = ws.DataValidations.AddCustomValidation("A1:A5");

   customValidation.Formula.ExcelFormula = "ISNUMBER(A1)";

   customValidation.ShowErrorMessage = true;
   customValidation.ErrorTitle = "Input invalid in Data Validation";
   customValidation.Error = $"Value in cell must be number!!";

   package.SaveAs("C:/temp/CustomExample.xlsx");
}

See also

• Data validations