Data validation

Data validation is accessed via the DataValidation Property of the ExcelWorksheet class or via the ExcelRange class. Some examples: 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");

}

More samples can be found in sample 12-.NET Franework or sample 12-.NET Core