Worksheet.DataValidations Property

Provides access to a collection of DataValidation objects for the worksheet.

Namespace: DevExpress.Spreadsheet

Assembly: DevExpress.Spreadsheet.v24.2.Core.dll

NuGet Package: DevExpress.Spreadsheet.Core

Declaration

C#

DataValidationCollection DataValidations { get; }

VB.NET

ReadOnly Property DataValidations As DataValidationCollection

Property Value

Type	Description
DataValidationCollection	A DataValidationCollection collection of DataValidation objects.

Example

View Example

DataValidationActions.cs

workbook.LoadDocument("Documents\\DataValidation.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

// Add data validations.
worksheet.DataValidations.Add(worksheet["D4:D11"], DataValidationType.TextLength, DataValidationOperator.Equal, 3);
worksheet.DataValidations.Add(worksheet["E4:E11"], DataValidationType.List, ValueObject.FromRange(worksheet["H4:H9"].GetRangeWithAbsoluteReference()));

// Get data validation entry associated with a particular cell.
worksheet.DataValidations.GetDataValidation(worksheet.Cells["E4"]).Criteria = ValueObject.FromRange(worksheet["H4:H5"]);

// Get data validation entries for the specified range.
var myValidation = worksheet.DataValidations.GetDataValidations(worksheet["D4:E11"])
    .Where(d => d.ValidationType == DataValidationType.TextLength).SingleOrDefault();
if (myValidation != null) myValidation.Criteria = 4;

// Get data validation entries that meet certain criteria.
foreach (var d in worksheet.DataValidations.GetDataValidations(DataValidationType.TextLength, DataValidationOperator.Equal, 4, ValueObject.Empty))
{
    // Change criteria operator.
    // Range D4:D11 should contain text with more than 4 characters.
    d.Operator = DataValidationOperator.GreaterThan;
}              

// Highlight data validation ranges.
int[] MyColorScheme = new int[] { 0xFFC4C4, 0xFFD9D9, 0xFFF6F6, 0xFFECEC, 0xE9D3D3 };
for (int i = 0; i < worksheet.DataValidations.Count; i++)
{
    worksheet.DataValidations[i].Range.FillColor = Color.FromArgb(MyColorScheme[i]);
}

DataValidationActions.vb

workbook.LoadDocument("Documents\DataValidation.xlsx")
Dim worksheet As Worksheet = workbook.Worksheets(0)

' Add data validations.
worksheet.DataValidations.Add(worksheet("D4:D11"), DataValidationType.TextLength, DataValidationOperator.Equal, 3)
worksheet.DataValidations.Add(worksheet("E4:E11"), DataValidationType.List, ValueObject.FromRange(worksheet("H4:H9").GetRangeWithAbsoluteReference()))

' Get data validation entry associated with a particular cell.
worksheet.DataValidations.GetDataValidation(worksheet.Cells("E4")).Criteria = ValueObject.FromRange(worksheet("H4:H5"))

' Get data validation entries for the specified range.
Dim myValidation = worksheet.DataValidations.GetDataValidations(worksheet("D4:E11")).Where(Function(d) d.ValidationType = DataValidationType.TextLength).SingleOrDefault()
If myValidation IsNot Nothing Then
    myValidation.Criteria = 4
End If

' Get data validation entries that meet certain criteria.
For Each d In worksheet.DataValidations.GetDataValidations(DataValidationType.TextLength, DataValidationOperator.Equal, 4, ValueObject.Empty)
    ' Change criteria operator.
    ' Range D4:D11 should contain text with more than 4 characters.
    d.Operator = DataValidationOperator.GreaterThan
Next d

' Highlight data validation ranges.
Dim MyColorScheme() As Integer = { &HFFC4C4, &HFFD9D9, &HFFF6F6, &HFFECEC, &HE9D3D3 }
For i As Integer = 0 To worksheet.DataValidations.Count - 1
    worksheet.DataValidations(i).Range.FillColor = Color.FromArgb(MyColorScheme(i))
Next i