How to: Apply Data Validation
- 4 minutes to read
The following example demonstrates how to manage the data validation rules.
- Create a new XlDataValidation object that represents the new validation rule.
- Specify the cell range to which the validation rule is going to be applied. To do that, add the target range to the object’s ranges collection, accessible through the XlDataValidation.Ranges property.
- Specify the type of data that is valid in the current rule by setting the XlDataValidation.Type property to the corresponding XlDataValidationType enumeration value.
- The operator for the validation rule can be specified using the XlDataValidation.Operator property.
- Specify the rule criterion by setting the XlDataValidation.Criteria1 property. If the rule operator requires two criteria, specify the XlDataValidation.Criteria2 property as well.
- To enable showing the error message, set the XlDataValidation.ShowErrorMessage to true. The XlDataValidation.ErrorMessage, XlDataValidation.ErrorTitle and XlDataValidation.ErrorStyle properties allows you to specify the error message text, title and style.
- You can also specify the input message that will be shown when the target cell is selected. To do that, set the XlDataValidation.ShowInputMessage to true and specify the text (XlDataValidation.InputPrompt) and the title (XlDataValidation.PromptTitle) of the message.
- Add the created validation rule to the corresponding worksheet collection. The collection can be accessed through the IXlSheet.DataValidations property.
Note
A complete sample project is available at https://github.com/DevExpress-Examples/xl-export-api-examples-t253492
// Apply data validation to cells.
// Restrict data entry in the range A2:A5 to a 5-digit number.
XlDataValidation validation = new XlDataValidation();
validation.Ranges.Add(XlCellRange.FromLTRB(0, 1, 0, 4));
validation.Type = XlDataValidationType.Custom;
validation.Criteria1 = "=AND(ISNUMBER(A2),LEN(A2)=5)";
// Add the specified rule to the worksheet collection of data validation rules.
sheet.DataValidations.Add(validation);
// Restrict data entry in the cell range C2:C5 to a whole number between 600 and 2000.
validation = new XlDataValidation();
validation.Ranges.Add(XlCellRange.FromLTRB(2, 1, 2, 4));
validation.Type = XlDataValidationType.Whole;
validation.Operator = XlDataValidationOperator.Between;
validation.Criteria1 = 600;
validation.Criteria2 = 2000;
// Display the error message.
validation.ErrorMessage = "The salary amount must be between 600$ and 2000$.";
validation.ErrorTitle = "Warning";
validation.ErrorStyle = XlDataValidationErrorStyle.Warning;
validation.ShowErrorMessage = true;
// Display the input message.
validation.InputPrompt = "Please enter a whole number between 600 and 2000";
validation.PromptTitle = "Salary";
validation.ShowInputMessage = true;
// Add the specified rule to the worksheet collection of data validation rules.
sheet.DataValidations.Add(validation);
// Restrict data entry in the cell range D2:D5 to a decimal number within the specified limits.
// Bonus cannot be greater than 10% of the salary.
validation = new XlDataValidation();
validation.Ranges.Add(XlCellRange.FromLTRB(3, 1, 3, 4));
validation.Type = XlDataValidationType.Decimal;
validation.Operator = XlDataValidationOperator.Between;
validation.Criteria1 = 0;
// Use a formula to specify the validation criterion.
validation.Criteria2 = "=C2*0.1";
// Display the error message.
validation.ErrorMessage = "Bonus cannot be greater than 10% of the salary.";
validation.ErrorTitle = "Information";
validation.ErrorStyle = XlDataValidationErrorStyle.Information;
validation.ShowErrorMessage = true;
// Add the specified rule to the worksheet collection of data validation rules.
sheet.DataValidations.Add(validation);
// Restrict data entry in the cell range E2:E5 to values in a drop-down list obtained from the cells G2:G5.
validation = new XlDataValidation();
validation.Ranges.Add(XlCellRange.FromLTRB(4, 1, 4, 4));
validation.Type = XlDataValidationType.List;
validation.Criteria1 = XlCellRange.FromLTRB(6, 1, 6, 4).AsAbsolute();
// Add the specified rule to the worksheet collection of data validation rules.
sheet.DataValidations.Add(validation);