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(ExcelPackagepackage=newExcelPackage()){varws=package.Workbook.Worksheets.Add("test");varanyValidation=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(ExcelPackagepackage=newExcelPackage()){varws=package.Workbook.Worksheets.Add("test");varintValidation=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(ExcelPackagepackage=newExcelPackage()){varws=package.Workbook.Worksheets.Add("test");varintValidation=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.

privatestaticvoidAddListValidationFormula(ExcelPackagepackage){varsheet=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 valuesvarvalidation=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 rangevalidation.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.

privatestaticvoidAddListValidationValues(ExcelPackagepackage){varsheet=package.Workbook.Worksheets.Add("list values");// add a validation and set valuesvarvalidation=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(vari=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(ExcelPackagepackage=newExcelPackage()){varws=package.Workbook.Worksheets.Add("test");vardateValidation=ws.DataValidations.AddDateTimeValidation("B1");dateValidation.Operator=ExcelDataValidationOperator.greaterThan;DateTimedate=newDateTime(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(ExcelPackagepackage=newExcelPackage()){varws=package.Workbook.Worksheets.Add("test");vartimeValidation=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(ExcelPackagepackage=newExcelPackage()){varws=package.Workbook.Worksheets.Add("test");varcustomValidation=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