XlDataValidation Class

Represents the data validation rule defined for a current worksheet.

Namespace: DevExpress.Export.Xl

Assembly: DevExpress.Printing.v18.2.Core.dll

Declaration

C#

public class XlDataValidation

VB.NET

Public Class XlDataValidation

Example

Note

A complete sample project is available at https://github.com/DevExpress-Examples/xl-export-api-examples-t253492

DataActions.cs

// 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);

DataActions.vb

' Apply data validation to cells.
' Restrict data entry in the range A2:A5 to a 5-digit number.
Dim validation As 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)

Inheritance

Object

XlDataValidation