Skip to main content
Row

DataValidation Interface

Provides access to a data validation rule defined for a worksheet range.

Namespace: DevExpress.Spreadsheet

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

NuGet Package: DevExpress.Spreadsheet.Core

Declaration

public interface DataValidation

Remarks

Objects of the DataValidation class are contained in the DataValidationCollection collection, accessible using the Worksheet.DataValidations property. To create a new data validation item which combines the validation rule and a worksheet range, use the DataValidationCollection.Add method.

Example

Note

The maximum length of a list used to specify the DataValidationType.List validation type or a custom validation criteria is 255 characters. When a list or criteria exceeds this value, an InvalidOperationException is thrown.

View Example

workbook.LoadDocument("Documents\\DataValidation.xlsx");
Worksheet worksheet = workbook.Worksheets[0];
worksheet["C1"].SetValue(DateTime.Now);
worksheet["C1"].NumberFormat = "mmm/d/yyyy h:mm";

// Restrict data entry to a whole number from 10 to 20.
worksheet.DataValidations.Add(worksheet["B1"], DataValidationType.WholeNumber, DataValidationOperator.Between, 10, 20);

// Restrict data entry to a number within limits.
DataValidation validation = worksheet.DataValidations.Add(worksheet["F4:F11"], DataValidationType.Decimal, DataValidationOperator.Between, 10, 40);

// Restrict data entry using criteria calculated by a worksheet formula.
worksheet.DataValidations.Add(worksheet["B4:B11"], DataValidationType.Custom, "=AND(ISNUMBER(B4),LEN(B4)=5)");

// Restrict data entry to 3 symbols.
worksheet.DataValidations.Add(worksheet["D4:D11"], DataValidationType.TextLength, DataValidationOperator.Equal, 3);

// Restrict data entry to values in a drop-down list specified in code. 
// Note that the list in code should always use comma to separate entries, 
// but the list in UI is displayed using culture-specific list separator.
worksheet.DataValidations.Add(worksheet["A4:A11"], DataValidationType.List, "PASS, FAIL");

// Restrict data entry to values in a drop-down list obtained from a worksheet.
worksheet.DataValidations.Add(worksheet["E4:E11"], DataValidationType.List, ValueObject.FromRange(worksheet["H4:H9"].GetRangeWithAbsoluteReference()));

// Restrict data entry to a time before the specified time.
worksheet.DataValidations.Add(worksheet["C1"], DataValidationType.Time, DataValidationOperator.LessThanOrEqual, DateTime.Now);

// Highlight data validation ranges.
worksheet["H4:H9"].FillColor = Color.LightGray;
int[] MyColorScheme = new int[] { 0xFFC4C4, 0xFFD9D9, 0xFFF6F6, 0xFFECEC, 0xE9D3D3, 0xFFDFC4, 0xFFDAE9};
for (int i = 0; i < worksheet.DataValidations.Count; i++){
    worksheet.DataValidations[i].Range.FillColor = Color.FromArgb(MyColorScheme[i]);
}
See Also