XlDataValidation Class
Represents the data validation rule defined for a current worksheet.
Namespace: DevExpress.Export.Xl
Assembly: DevExpress.Printing.v19.2.Core.dll
Declaration
Remarks
Excel Export Library allows you to create the following types of validation rules (specified by the XlDataValidation.Type property):
Whole Number - restrict the cell to accept only whole numbers.
Decimal - restrict the cell to accept only decimal numbers.
List - pick data from the drop-down list.
Date - restrict the cell to accept only dates.
Time - restrict the cell to accept only time.
Text Length - restrict the length of the text.
Custom – for custom formula.
Specify the XlDataValidation.Operator property to use relational operator in the validation rule. The XlDataValidation.Criteria1 and XlDataValidation.Criteria2 properties allow you to set the criterion values.
For the XlDataValidationType.List validation rule, use the XlDataValidation.ListValues property to define the list of allowed values, and the XlDataValidation.ListRange property to define a list of cell ranges whose values are used as the validation criteria.
Note
The maximum length of a list used to specify the XlDataValidationType.List validation type or a custom validation criteria is 255 characters. When a list or criteria exceeds this value, an InvalidOperationException is thrown.
The XlDataValidation.Ranges property provides access to the collection of cell ranges to which the validation rule is applied.
You can create a message that explains what data can be entered in a cell. Set the XlDataValidation.ShowInputMessage property to true to display the message. The XlDataValidation.InputPrompt and XlDataValidation.PromptTitle properties allow you to provide a message text and title.
Set the XlDataValidation.ShowErrorMessage property to true to display a message that appears when the end-user types incorrect data. You can change the message style (XlDataValidation.ErrorStyle), caption (XlDataValidation.ErrorTitle) and text (XlDataValidation.ErrorMessage).
The code snippet below illustrates how to create and apply different validation criteria to worksheet cells.
Example
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.ListRange = XlCellRange.FromLTRB(6, 1, 6, 4).AsAbsolute();
// Add the specified rule to the worksheet collection of data validation rules.
sheet.DataValidations.Add(validation);