Skip to main content
Row

DevExpress v24.2 Update — Your Feedback Matters

Our What's New in v24.2 webpage includes product-specific surveys. Your response to our survey questions will help us measure product satisfaction for features released in this major update and help us refine our plans for our next major release.

Take the survey Not interested

DataValidation Interface

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

Namespace: DevExpress.Spreadsheet

Assembly: DevExpress.Spreadsheet.v24.2.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