officefileapi-devexpress-dot-spreadsheet-dot-datavalidationcollection-dot-add-x28-cellrange-datavalidationtype-datavalidationoperator-valueobject-x29.md
Creates a new data validation entry and adds it to the document’s collection.
Namespace : DevExpress.Spreadsheet
Assembly : DevExpress.Spreadsheet.v25.2.Core.dll
NuGet Package : DevExpress.Spreadsheet.Core
DataValidation Add(
CellRange range,
DataValidationType validationType,
DataValidationOperator validationOperator,
ValueObject criteria
)
Function Add(
range As CellRange,
validationType As DataValidationType,
validationOperator As DataValidationOperator,
criteria As ValueObject
) As DataValidation
| Name | Type | Description |
|---|---|---|
| range | CellRange |
A CellRange object that is the worksheet range to which the data validation rule is applied.
| | validationType | DataValidationType |
A DataValidationType enumeration member that specifies the validation type.
| | validationOperator | DataValidationOperator |
A DataValidationOperator enumeration member that specifies the operator used in a criteria expression.
| | criteria | ValueObject |
A ValueObject that is the value used in the criterion.
|
| Type | Description |
|---|---|
| DataValidation |
A DataValidation object that specifies a validation rule for a worksheet range.
|
This method overload is used for validation types DataValidationType.Date, DataValidationType.Decimal, DataValidationType.TextLength, DataValidationType.Time and DataValidationType.WholeNumber.
Note
An individual worksheet cell can have only one data validation rule associated with it. If subsequent Add method calls affect the same cell, only the data validation rule created in the last call is applied.
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.
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]);
}
workbook.LoadDocument("Documents\DataValidation.xlsx")
Dim worksheet As Worksheet = workbook.Worksheets(0)
worksheet("C1").SetValue(Date.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.
Dim validation As DataValidation = 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, Date.Now)
' Highlight data validation ranges.
worksheet("H4:H9").FillColor = Color.LightGray
Dim MyColorScheme() As Integer = { &HFFC4C4, &HFFD9D9, &HFFF6F6, &HFFECEC, &HE9D3D3, &HFFDFC4, &HFFDAE9}
For i As Integer = 0 To worksheet.DataValidations.Count - 1
worksheet.DataValidations(i).Range.FillColor = Color.FromArgb(MyColorScheme(i))
Next i
The following code snippets (auto-collected from DevExpress Examples) contain references to the Add(CellRange, DataValidationType, DataValidationOperator, ValueObject) method.
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.
// Restrict data entry to 3 symbols.
worksheet.DataValidations.Add(worksheet["D4:D11"], DataValidationType.TextLength, DataValidationOperator.Equal, 3);
// Restrict data entry to 3 symbols.
worksheet.DataValidations.Add(worksheet["D4:D11"], DataValidationType.TextLength, DataValidationOperator.Equal, 3);
// Restrict data entry to 3 symbols.
worksheet.DataValidations.Add(worksheet["D4:D11"], DataValidationType.TextLength, DataValidationOperator.Equal, 3);
' Restrict data entry to 3 symbols.
worksheet.DataValidations.Add(worksheet("D4:D11"), DevExpress.Spreadsheet.DataValidationType.TextLength, DevExpress.Spreadsheet.DataValidationOperator.Equal, 3)
' Restrict data entry to values in a drop-down list specified in code.
' Restrict data entry to 3 symbols.
worksheet.DataValidations.Add(worksheet("D4:D11"), DataValidationType.TextLength, DataValidationOperator.Equal, 3)
See Also
DataValidationCollection Interface