XlDataValidation Class

Represents the data validation rule defined for a current worksheet.

Namespace: DevExpress.Export.Xl

Assembly: DevExpress.Printing.v18.1.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)

Related GitHub Examples

The following code snippets (auto-collected from DevExpress Examples) contain references to the XlDataValidation class.

Note

The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.

C#

excel-export-api-examples-t253492/CS/XLExportExamples/SpreadsheetActions/PageViewAndLayoutActions.cs#L711

// Restrict data entry in the cell range E2:E5 to values in a drop-down list obtained from the cells G2:G5.
XlDataValidation validation = new XlDataValidation();
validation.Ranges.Add(XlCellRange.FromLTRB(4, 1, 4, 4));

how-to-use-the-excel-export-api-to-generate-a-list-of-employees-t251723/CS/XLExportExample/Form1.cs#L306

// Restrict data entry in the "Employee ID" column using criteria calculated by a worksheet formula (Employee ID must be a 5-digit number).
XlDataValidation validation = new XlDataValidation();
validation.Ranges.Add(XlCellRange.FromLTRB(0, firstDataRowIndex, 0, sheet.CurrentRowIndex - 1));

VB.NET

excel-export-api-examples-t253492/VB/XLExportExamples/SpreadsheetActions/PageViewAndLayoutActions.vb#L675

' Restrict data entry in the cell range E2:E5 to values in a drop-down list obtained from the cells G2:G5.
Dim validation As New XlDataValidation()
validation.Ranges.Add(XlCellRange.FromLTRB(4, 1, 4, 4))

how-to-use-the-excel-export-api-to-generate-a-list-of-employees-t251723/VB/XLExportExample/Form1.vb#L322

' Restrict data entry in the "Employee ID" column using criteria calculated by a worksheet formula (Employee ID must be a 5-digit number).
Dim validation As New XlDataValidation()
validation.Ranges.Add(XlCellRange.FromLTRB(0, firstDataRowIndex, 0, sheet.CurrentRowIndex - 1))

Inheritance

Object

XlDataValidation