Data Validation Properties
On this page we bring up a few aspects of adding DataValidations that might be good to know or might seem unexpected at first and explain the function of the base properties.
Screenshots in this article are from version 2301 build 16.0 of Excel in Office 365 and were taken in 2023.
Each Validation created with Epplus has a unique ID number attached to it.
The cell or range the DataValidation has been applied to.
An enum defining what type the current validation is.
ValidationType represents this field in Excel:
Enum to define type of error to show if input is invalid.
Represents this menu in Excel
Each data validation contains a few boolean properties:
ShowErrorMessageShowPromptAllowblankIsStale
Notably the data validation will not stop faulty inputs if ShowErrorMessage is false. It is false by default so in most cases you'll want to set it to true even if you don't specify a unique error message.
ShowErrorMessage represents this checkbox in Excel:
ShowPrompt defines wheter the tooltip prompt when the cell is selected.
it represents this checkbox in Excel:
AllowBlank lets you not fill in formulas when true even if the validation will not work with empty fields. Can be used to avoid compilation errors when performing certain unit tests where only some properties of the validation is relevant for example.
It represents this checkbox in Excel:
NB! This property is deprecated from EPPlus 6.2 (which has a new implementation of Data Validations).
IsStale indicates wheter this validation instance is Stale
In addition ExcelDataValidationList contains a boolean for showing the dropdown menu or not called HideDropDown.
PromptTitle, Prompt, ErrorTitle and Error are all string values for tooltips or error messages in Excel respectively.
PromptTitle and Prompt represent this field in Excel:
While ErrorTitle and Error represent this:
Data validations read from a workbook are always returned as ExcelDataValidation instances. Use the As property to cast a data validation to its specialized type.
Every data validation (Except the AnyDatavalidation type) has one or two Formula properties Each formula has either .Value, .ExcelFormula or both.
Value is a typed value which is different for each type of validation for ExcelDataValidationInteger its an int for ExcelDataValidationDateTime it's a DateTime and so on.
ExcelFormula however is always a string and is meant to represent choosing cell ranges (especially with the List data validation) or to simply input an excelFormula like ISNUMBER(A1) for a custom formula for example
Formula and Formula2 represent the following fields in Excel:
Enum that defines the basis of comparison for the validation between, greaterThan, LessThan etc.
Represented by this field in Excel:
