Data Validation in the Excel Export Library
- 4 minutes to read
The example below demonstrates how to create and apply data validation rules.
- Create a new XlDataValidation object.
- Specify the cell range to which the validation rule is going to be applied. Add the target range to the object’s ranges collection, accessible by the XlDataValidation.Ranges property.
- Set the XlDataValidation.Type property to the corresponding XlDataValidationType enumeration value to specify what data type is valid in the current rule.
- Use the XlDataValidation.Operator property to specify the operator for the validation rule.
Specify the XlDataValidation.Criteria1 and the XlDataValidation.Criteria2 property (if the validation rule requires two values) properties to provide the criterion values.
If you create the XlDataValidationType.List validation rule, use the XlDataValidation.ListValues or XlDataValidation.ListRange properties to define a list of allowed values.
- Set the XlDataValidation.ShowErrorMessage to true to enable the error message. The XlDataValidation.ErrorMessage, XlDataValidation.ErrorTitle and XlDataValidation.ErrorStyle properties allows you to specify the error message’s text, title and style.
- You can also specify the input message that is shown when the target cell is selected. Set the XlDataValidation.ShowInputMessage to true and specify the message’s text (XlDataValidation.InputPrompt) and title (XlDataValidation.PromptTitle).
- Add the created rule to the worksheet’s validation rules collection. Access the collection by the IXlSheet.DataValidations property.
// 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.ListRange = XlCellRange.FromLTRB(6, 1, 6, 4).AsAbsolute();
// Add the specified rule to the worksheet collection of data validation rules.
sheet.DataValidations.Add(validation);