Data validation

Data validations is accessed via the DataValidation property of the ExcelWorksheet class or via the ExcelRange class. The following types of data validations are supported: Integer ("whole" in Excel), Decimal, List, Date, Time, Any and Custom.

Add Data validations

Here are some examples on how to add data validation to a worksheet. The following code adds a data validation to a range that only allows integer values between 1 and 5.

private static void AddIntegerValidation(ExcelPackage package)
{
    var sheet = package.Workbook.Worksheets.Add("integer");
    // add a validation and set values
    var validation = sheet.DataValidations.AddIntegerValidation("A1:A2");
    // Alternatively:
    //var validation = sheet.Cells["A1:A2"].DataValidation.AddIntegerDataValidation();
    validation.ErrorStyle = ExcelDataValidationWarningStyle.stop;
    validation.PromptTitle = "Enter a integer value here";
    validation.Prompt = "Value should be between 1 and 5";
    validation.ShowInputMessage = true;
    validation.ErrorTitle = "An invalid value was entered";
    validation.Error = "Value must be between 1 and 5";
    validation.ShowErrorMessage = true;
    validation.Operator = ExcelDataValidationOperator.between;
    validation.Formula.Value = 1;
    validation.Formula2.Value = 5;
}

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";
    validation.Formula.ExcelFormula = "B2:B4";
            
}

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

}

Read existing Data validations from a worksheet

foreach (var dataValidation in worksheet.DataValidations)
{
   switch(dataValidation.ValidationType.Type)
   {
       case eDataValidationType.Whole:
          var intValidation = dataValidation.As.IntegerValidation;
          break;
       case eDataValidationType.List:
          var listValidation = dataValidation.As.ListValidation;
          break;
       default:
          break;
   }
}

Search/Modify

The following functions exposed as members of the worksheet.DataValidations property are useful for searching/modifying data validations:

• FindAll(Predicate<IExcelDataValidation> match) - returns all matches
• Find(Predicate<IExcelDataValidation> match) - returns the first match
• Clear() - removes all data validations from the worksheet
• RemoveAll(Predicate<IExcelDataValidation> match) - Removes the validations that matches the predicate

Performance

Due to how Data validations are implemented in Excel/Office Open Xml it is always better to use a shared range rather than adding the validations on each row. For example: if you have 50 000 cells and wants to add a data validation on each one of them try - if feasible - to define a common validation for the cells (i.e. Worksheet.AddListValidation("C1:C50000")) rather than setting them per row.

See also

• Sample 4.1-C# or sample 4.1-VB