Data Validation
Data validation is used to control the type of data or the values that users enter into a cell. Compared to Protection, data validation does not restrict the user input but ensures that the entered data complies with certain rules. For example, the user may be restricted to enter data only within a certain range of dates, whole numbers, decimal numbers, or from a list of predefined values.
Data Validation Rules
The data validation rules help you restrict the user input. Additionally, you can set a message which will be shown when the cell containing the rule is selected once the current workbook is shown in a spreadsheet UI control.
You have the ability to specify how the user will be notified if the rule is not satisfied. There are three types of notifications:
-
Error: If the user enters invalid data, a message box gives him the following choice:
- Retry: Try again.
- Cancel the change.
-
Cancel: If the user enters invalid data, a message box gives him the following choice:
- Yes: Override the warning and apply the change.
- No: Try again.
- Cancel: Revert the change.
-
Information: If the user enters invalid data, a message box gives him the following choice:
- OK: Override the warning and apply the change.
- Cancel: Revert the change.
Data Validation Rule Types
There are the following data validation rule types:
- Any value
- Whole number
- Decimal
- List
- Date
- Time
- Text length
- Custom
To be able to create data validation rules you have to pass parameters to the rule. This is done using data validation rule contexts. There are several context types with different parameters which are used for the different types of data validation rules.
AnyValueDataValidationRuleContext
The AnyValueDataValidationRuleContext class exposes the following properties:
- ShowInputMessag: Specifies whether to show input message or not.
- InputMessageTitle: The title of the input message.
- InputMessageContent: The content of the input message.
- ShowErrorMessage: Specifies whether to show error message or not.
- ErrorStyle: Specifies the style of the error message. The possible choices are Error, Warning and Information.
SingleArgumentdataValidationRuleContext
SingleArgumentdataValidationRuleContext exposes the properties which the AnyValueDataValidationRuleContext class has, but extends them with the following properties:
- IgnoreBlank: Specifies if the validation will ignore blank values and this way consider them as valid values.
- Argument1: Тhe argument needed for the validation.
- CellIndex: The cell index of the cell based on which the validation rule is created.
- Worksheet: The worksheet in which the data validation rule is created.
NumberDataValidationRuleContext
NumberDataValidationRuleContext exposes the properties which the SingleArgumentDataValidationRuleContext class has, but extends them with the following properties:
- ComparisonOperator: The comparison operator used by the data validation rule.
- Argument2: The second argument needed by the data validation rule.
ListDataValidationRuleContext
ListDataValidationRuleContext exposes the properties which the SingleArgumentDataValidationRuleContext class has, but extends them with the following properties:
- InCellDropdown: Specifies if a drop-down containing the list values of the data validation rule should be shown.
Any Value Rule
The any value data validation rule is the default rule. It is applied to all cells and it does not perform any data validation. You can simply specify an input message. The error message is never shown because in this rule all values are considered as valid input.
Example 1 shows hot to create any value validation rule and set it to a cell.
Example 1: Apply any value rule
AnyValueDataValidationRuleContext context = new AnyValueDataValidationRuleContext();
context.InputMessageContent = "Sample input message content";
context.InputMessageTitle = "Sample input message title";
AnyValueDataValidationRule rule = new AnyValueDataValidationRule(context);
worksheet.Cells[0, 0].SetDataValidationRule(rule);
Figure 1: Any value rule
Whole Number Rule
The whole number data validation rule allows you to restrict the user input to whole numbers in a certain range. The range is specified using two arguments and a comparison operator. In some cases, for example when restricting user input in the range between 0 and 100, both arguments are used. In other cases like restricting the input to numbers that are greater than 100, only one of the arguments is used. Additionally, there is an option to ignore the blank values which is turned on by default.
The code snippet in Example 2 shows how to create a whole number data validation rule that restricts the user input using two arguments to the range between 0 and 100 and considers blank values as invalid.
Example 2: Apply whole number rule with two arguments
CellIndex dataValidationRuleCellIndex = new CellIndex(0, 0);
NumberDataValidationRuleContext context = new NumberDataValidationRuleContext(worksheet, dataValidationRuleCellIndex);
context.InputMessageTitle = "Restricted input";
context.InputMessageContent = "The input is restricted to whole number values between 0 and 100";
context.ErrorStyle = ErrorStyle.Stop;
context.ErrorAlertTitle = "Wrong value";
context.ErrorAlertContent = "The entered value is not valid. Allowed values are in the range between 0 and 100!";
context.IgnoreBlank = false;
context.ComparisonOperator = ComparisonOperator.Between;
context.Argument1 = "0";
context.Argument2 = "100";
WholeNumberDataValidationRule rule = new WholeNumberDataValidationRule(context);
worksheet.Cells[dataValidationRuleCellIndex].SetDataValidationRule(rule);
Figure 2: Whole number rule
The result from entering value “test” in the cell containing the data validation rule is shown on Figure 3.
Figure 3: Whole number rule invalid result
The code snippet in Example 3 shows how to create a whole number data validation rule that restricts the user input with one argument to numbers that are greater than 100.
Example 3: Apply whole number rule with one argument
CellIndex dataValidationRuleCellIndex = new CellIndex(0, 0);
NumberDataValidationRuleContext context = new NumberDataValidationRuleContext(worksheet, dataValidationRuleCellIndex);
context.InputMessageTitle = "Restricted input";
context.InputMessageContent = "The input is restricted to whole number values greater than 100";
context.ErrorStyle = ErrorStyle.Stop;
context.ErrorAlertTitle = "Wrong value";
context.ErrorAlertContent = "The entered value is not valid. Allowed values are all whole number values greater than 100!";
context.ComparisonOperator = ComparisonOperator.GreaterThan;
context.Argument1 = "100";
WholeNumberDataValidationRule rule = new WholeNumberDataValidationRule(context);
worksheet.Cells[dataValidationRuleCellIndex].SetDataValidationRule(rule);
Example 4: Apply whole number rule with formula
// The value of A1
worksheet.Cells[0, 0].SetValue(60);
// The value of B1
worksheet.Cells[0, 1].SetValue(40);
CellIndex dataValidationRuleCellIndex = new CellIndex(1, 0);
NumberDataValidationRuleContext context = new NumberDataValidationRuleContext(worksheet, dataValidationRuleCellIndex);
context.InputMessageTitle = "Restricted input";
context.InputMessageContent = "The input is restricted to whole number values greater than 100";
context.ErrorStyle = ErrorStyle.Warning;
context.ErrorAlertTitle = "Wrong value";
context.ErrorAlertContent = "The entered value is not valid. Allowed values are all whole number values greater than 100!";
context.ComparisonOperator = ComparisonOperator.LessThan;
context.Argument1 = "=Sum(A1+B1)";
WholeNumberDataValidationRule rule = new WholeNumberDataValidationRule(context);
worksheet.Cells[dataValidationRuleCellIndex].SetDataValidationRule(rule);
Figure 4: Whole number rule based on formula
Note that the cell index that is passed to the constructor of the NumberDataValidationRuleContext is the cell for which the rule is created. Consider the case in which an area from A2 to C5 is selected using the UI and the active cell is A2. You create the same rule as in Example 4 but apply it not just for cell A2, but for the cell range A2:C5. The formula “=SUM(A1+B1)” contains two relative cell references – A1 and B1. If you select the cell C5 and open the data validation dialog you will see that the formula that specifies the minimum value is “=SUM(C4+D4)” instead of “=SUM(A1+B1)”. The relative references in the formula are translated relatively to the cell passed in the constructor of the data validation rule context.
Decimal Rule
The decimal data validation rule allows you to restrict the user input to decimal numbers in a certain range which is specified using two arguments and a comparison operator. The difference from the whole number Rule is that the decimal rule allows whole and decimal numbers to be entered in the cells.
Example 5 demonstrates how to create a decimal data validation rule that restricts the user input to be outside the range between 0 and 100.
Example 5: Apply decimal rule
CellIndex dataValidationRuleCellIndex = new CellIndex(0, 0);
NumberDataValidationRuleContext context = new NumberDataValidationRuleContext(worksheet, dataValidationRuleCellIndex);
context.InputMessageTitle = "Restricted input";
context.InputMessageContent = "The input is restricted to decimal number values out of the range between 0 and 100";
context.ErrorStyle = ErrorStyle.Stop;
context.ErrorAlertTitle = "Wrong value";
context.ErrorAlertContent = "The entered value is not valid. Allowed values are all decimal number values out of the range between 0 and 100!";
context.ComparisonOperator = ComparisonOperator.Between;
context.Argument1 = "0";
context.Argument2 = "100";
DecimalDataValidationRule rule = new DecimalDataValidationRule(context);
worksheet.Cells[dataValidationRuleCellIndex].SetDataValidationRule(rule);
List Rule
The list data validation rule allows you to restrict the user input to a predefined set of values. Using the InCellDropdown property you can specify if a drop-down list containing the values will be shown next to the cell.
Example 6 shows the creation of a list data validation rule that restricts the user input to a day of the week.
Example 6: Apply list rule
CellIndex dataValidationRuleCellIndex = new CellIndex(0, 0);
ListDataValidationRuleContext context = new ListDataValidationRuleContext(worksheet, dataValidationRuleCellIndex);
context.InputMessageTitle = "Restricted input";
context.InputMessageContent = "The input is restricted to the week days.";
context.ErrorStyle = ErrorStyle.Stop;
context.ErrorAlertTitle = "Wrong value";
context.ErrorAlertContent = "The entered value is not valid. Allowed values are the week days!";
context.InCellDropdown = true;
context.Argument1 = "Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday";
ListDataValidationRule rule = new ListDataValidationRule(context);
worksheet.Cells[dataValidationRuleCellIndex].SetDataValidationRule(rule);
Figure 5: List rule
Date Rule
The date rule allows you to restrict the user input to a certain range of dates which is specified using two arguments and a comparison operator.
Example 7 shows how to restrict the user input to the dates in the range between 12 February 2013 and 22 May 2017.
Example 7: Apply date rule
CellIndex dataValidationRuleCellIndex = new CellIndex(0, 0);
NumberDataValidationRuleContext context = new NumberDataValidationRuleContext(worksheet, dataValidationRuleCellIndex);
context.InputMessageTitle = "Restricted input";
context.InputMessageContent = "The input is restricted to dates in the range from 12 February 2013 to 22 May 2017.";
context.ErrorStyle = ErrorStyle.Stop;
context.ErrorAlertTitle = "Wrong value";
context.ErrorAlertContent = "The entered value is not valid. Allowed values are all dates between 12 February 2013 and 22 May 2017";
context.ComparisonOperator = ComparisonOperator.Between;
context.Argument1 = new DateTime(2013, 2, 12).ToShortDateString();
context.Argument2 = new DateTime(2017, 5, 22).ToShortDateString();
DateDataValidationRule rule = new DateDataValidationRule(context);
worksheet.Cells[dataValidationRuleCellIndex].SetDataValidationRule(rule);
Time Rule
The time rule allows you to restrict the user input to a certain range of times which is specified using two arguments and a comparison operator.
The code snippet in Example 8 shows how to restrict the user input to the range between 10:25 AM and 3:45 PM:
Example 8: Apply time rule
CellIndex dataValidationRuleCellIndex = new CellIndex(0, 0);
NumberDataValidationRuleContext context = new NumberDataValidationRuleContext(worksheet, dataValidationRuleCellIndex);
context.InputMessageTitle = "Restricted input";
context.InputMessageContent = "The input is restricted to times in the range from 10:25 AM to 3:45 PM.";
context.ErrorStyle = ErrorStyle.Stop;
context.ErrorAlertTitle = "Wrong value";
context.ErrorAlertContent = "The entered value is not valid. Allowed values are all times between 10:25 AM to 3:45 PM.";
context.ComparisonOperator = ComparisonOperator.Between;
context.Argument1 = new DateTime(2015, 2, 2, 10, 25, 0).ToShortTimeString();
context.Argument2 = new DateTime(2015, 2, 2, 15, 45, 0).ToShortTimeString();
TimeDataValidationRule rule = new TimeDataValidationRule(context);
worksheet.Cells[dataValidationRuleCellIndex].SetDataValidationRule(rule);
Text Length Rule
The text length rule allows you to restrict the user input to text with length in a certain range specified using two arguments and a comparison operator.
Example 9 shows how to restrict the user input to text with a length between 5 and 10 symbols.
Example 9: Apply text length rule
CellIndex dataValidationRuleCellIndex = new CellIndex(0, 0);
NumberDataValidationRuleContext context = new NumberDataValidationRuleContext(worksheet, dataValidationRuleCellIndex);
context.InputMessageTitle = "Restricted input";
context.InputMessageContent = "The input is restricted to text with length between 5 and 10 symbols";
context.ErrorStyle = ErrorStyle.Stop;
context.ErrorAlertTitle = "Wrong value";
context.ErrorAlertContent = "The entered value is not valid. It is allowed to enter only text with length between 5 and 10 symbols.";
context.ComparisonOperator = ComparisonOperator.Between;
context.Argument1 = "5";
context.Argument2 = "10";
TextLengthDataValidationRule rule = new TextLengthDataValidationRule(context);
worksheet.Cells[dataValidationRuleCellIndex].SetDataValidationRule(rule);
Custom Rule
The custom rule allows you to restrict the user input with a custom condition specified with a formula which results in a Boolean value.
The code snippet in Example 10 shows how to restrict the user input to values that are greater or equal to the sum of the values in the cells A1 and B1.
Example 10: Apply custom rule
CellIndex dataValidationRuleCellIndex = new CellIndex(1, 0);
SingleArgumentDataValidationRuleContext context = new SingleArgumentDataValidationRuleContext(worksheet, dataValidationRuleCellIndex);
context.InputMessageTitle = "Restricted input";
context.InputMessageContent = "The input is restricted to values that are greater or equal to the sum of the values in the cells A1 and B1.";
context.ErrorStyle = ErrorStyle.Stop;
context.ErrorAlertTitle = "Wrong value";
context.ErrorAlertContent = "The entered value is not valid. It is allowed to enter only values that are greater or equal to the sum of the values in the cells A1 and B1";
context.Argument1 = "=A2=Sum(A1, B1)";
CustomDataValidationRule rule = new CustomDataValidationRule(context);
worksheet.Cells[dataValidationRuleCellIndex].SetDataValidationRule(rule);
Evaluate Rules
In order to check if the cell value satisfies a rule, you have to evaluate the rule using the desired cell value. Each data validation rule implements the IDataValidationRule interface which exposes the Evaluate() method. The method receives as parameters the worksheet in which the value is located, the cell index in which the value will be placed or in which the value is contained, and the value itself.
Example 11 demonstrates how to evaluate a rule using the Evaluate() method.
Example 11: Evaluate rule
double value = 125;
ICellValue cellValue = value.ToCellValue();
if (rule.Evaluate(worksheet, 0, 0, cellValue))
{
// the rule is satisfied, the value 125 is valid
}
else
{
// the rule is not satisfied
}