ValueObject Class

An object that can be used to specify a value associated with a custom cell in-place editor or to create a criterion for data validation.

Namespace: DevExpress.Spreadsheet

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

NuGet Package: DevExpress.Spreadsheet.Core

Declaration

C#

public class ValueObject

VB.NET

Public Class ValueObject

Related API Members

The following members return ValueObject objects:

Library	Related API Members
WinForms Controls	SpreadsheetCustomCellEditEventArgs.ValueObject
WPF Controls	SpreadsheetCustomCellEditEventArgs.ValueObject
Office File API	CustomCellInplaceEditor.Value
	DataValidation.Criteria
	DataValidation.Criteria2
	ValueObject.CreateListSource(CellValue[])
	ValueObject.Empty
	ValueObject.FromDateTime(DateTime, Boolean)
	ValueObject.FromFormula(String, Boolean)
	ValueObject.FromRange(CellRange)

Remarks

The ValueObject instance can be created either implicitly or explicitly when calling one of the following methods:

• the DataValidationCollection.Add method which creates a data validation rule;
• the CustomCellInplaceEditorCollection.Add method which creates a custom cell in-place editor of a particular type and assigns it to a specific cell range in a worksheet.

The following example illustrates how the ValueObject objects can be used to specify criteria for various data validation rules.

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

DataValidationActions.cs

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]);
}

DataValidationActions.vb

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

Inheritance

Object

ValueObject