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 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 booleans. Namely
- ShowErrorMessage
- ShowPrompt
- Allowblank
- IsStale (Deprectated as of Epplus 6.2)
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:
As of Epplus 6.2 both of these will be set to true by default.
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:
In addition ExcelDataValidationList contains a boolean for showing the dropdown menu or not called HideDropDown.
IsStale indicates wheter this validation instance is stale, see: https://github.com/EPPlusSoftware/EPPlus/wiki/Data-validation-Exceptions NOTE: IsStale is Deprecated as of 6.2 as a Data Validation can no longer become stale. Instead all updates are done upon saving the ExcelPackage.
PromptTitle, Prompt, ErrorTitle and Error are all string values for tooltip or error message in excel respectively.
PromptTitle and Prompt represent this field in Excel:
While ErrorTitle and Error represent this:
Ease of use property to cast dataValidation to its child-types easily.
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:
