DataValidation Class

Namespace:
GemBox.Spreadsheet
Assembly:
GemBox.Spreadsheet.dll

A single item of data validation defined on ranges of the worksheet.

public sealed class DataValidation
Public NotInheritable Class DataValidation
Inheritance:
System.Object
DataValidation

Constructors

DataValidation()

Initializes a new instance of the DataValidation class.

public DataValidation()
Public Sub New

DataValidation(CellRange[])

Initializes a new instance of the DataValidation class with specified cell ranges on which this data validation will be applied.

public DataValidation(params CellRange[] cellRanges)
Public Sub New(ParamArray cellRanges As CellRange())
Parameters
cellRanges
CellRange[]

The cell ranges on which this data validation will be applied.

DataValidation(ExcelWorksheet, IEnumerable<String>)

Initializes a new instance of the DataValidation class with specified worksheet and cell references on which this data validation will be applied.

public DataValidation(ExcelWorksheet worksheet, IEnumerable<string> cellReferences)
Public Sub New(worksheet As ExcelWorksheet, cellReferences As IEnumerable(Of String))
Parameters
worksheet
ExcelWorksheet

The worksheet that will be used to initialize CellRanges that will be added to the CellRanges collection of this data validation.

cellReferences
System.Collections.Generic.IEnumerable<System.String>

The cell references from which to create CellRanges.

DataValidation(ExcelWorksheet, String[])

Initializes a new instance of the DataValidation class with specified worksheet and cell references on which this data validation will be applied.

public DataValidation(ExcelWorksheet worksheet, params string[] cellReferences)
Public Sub New(worksheet As ExcelWorksheet, ParamArray cellReferences As String())
Parameters
worksheet
ExcelWorksheet

The worksheet that will be used to initialize CellRanges that will be added to the CellRanges collection of this data validation.

cellReferences
System.String[]

The cell references from which to create CellRanges.

DataValidation(IEnumerable<CellRange>)

Initializes a new instance of the DataValidation class with specified cell ranges on which this data validation will be applied.

public DataValidation(IEnumerable<CellRange> cellRanges)
Public Sub New(cellRanges As IEnumerable(Of CellRange))
Parameters
cellRanges
System.Collections.Generic.IEnumerable<CellRange>

The cell ranges on which this data validation will be applied.

Properties

CellRanges

Gets the ranges over which data validation is applied.

public CellRangeCollection CellRanges { get; }
Public ReadOnly Property CellRanges As CellRangeCollection
Property Value
CellRangeCollection

ErrorMessage

Gets or sets the message text of error alert.

public string ErrorMessage { get; set; }
Public Property ErrorMessage As String
Property Value
System.String

The message text of error alert.

ErrorStyle

Gets or sets the style of error alert used for this data validation.

public DataValidationErrorStyle ErrorStyle { get; set; }
Public Property ErrorStyle As DataValidationErrorStyle
Property Value
DataValidationErrorStyle

The style of error alert used for this data validation.

ErrorTitle

Gets or sets the title bar text of error alert.

public string ErrorTitle { get; set; }
Public Property ErrorTitle As String
Property Value
System.String

The title bar text of error alert.

Formula1

Gets or sets the first formula in the DataValidation dropdown.

public object Formula1 { get; set; }
Public Property Formula1 As Object
Property Value
System.Object

The first formula in the DataValidation dropdown.

Remarks

It used as a bounds for Between and NotBetween relational operators, and the only formula used for other relational operators (Equal, NotEqual, LessThan, LessThanOrEqual, GreaterThan, GreaterThanOrEqual), or for Custom or List data validation type. The content can be an instance of any type, but the following rules apply when formula is being written:

If data validation type is WholeNumber or TextLength, and content is integral data type (System.SByte, System.Byte, System.Int16, System.UInt16, System.Int32, System.UInt32, System.Int64, System.UInt64), formula will be saved as integral constant, else if formula is an Excel formula or an Excel reference (System.String that starts with '='), value will be saved as a formula, otherwise, it will be saved as string constant.
If data validation type is Decimal, and content is floating-point data type (System.Single, System.Double, System.Decimal), formula will be saved as floating-point constant, else if formula is an Excel formula or an Excel reference (System.String that starts with '='), value will be saved as a formula, otherwise, it will be saved as string constant.
If data validation type is Date, and content is System.DateTime data type, formula will be saved as date constant, else if formula is an Excel formula or an Excel reference (System.String that starts with '='), value will be saved as a formula, otherwise, it will be saved as string constant.
If data validation type is Time, and content is System.TimeSpan data type, formula will be saved as time constant, else if formula is an Excel formula or an Excel reference (System.String that starts with '='), value will be saved as a formula, otherwise, it will be saved as string constant.
If data validation type is List, and content is any type that implements System.Collections.IEnumerable, formula will be saved as list series (comma separated values), else if formula is an Excel formula or an Excel reference (System.String that starts with '='), value will be saved as a formula, otherwise, it will be saved as string constant.
If data validation type is Custom, and formula is an Excel formula or an Excel reference (System.String that starts with '='), value will be saved as a formula, otherwise, it will be saved as string constant.
If data validation type is None, formula won't be saved.

Formula2

Gets or sets the second formula in the DataValidation dropdown.

public object Formula2 { get; set; }
Public Property Formula2 As Object
Property Value
System.Object

The second formula in the DataValidation dropdown.

Remarks

It used as a bounds for Between and NotBetween relational operators only. For the information about the content, see Formula1 remarks.

IgnoreBlank

Gets or sets a value indicating whether the data validation treats empty or blank entries as valid. true means empty entries are OK and do not violate the validation constraints.

public bool IgnoreBlank { get; set; }
Public Property IgnoreBlank As Boolean
Property Value
System.Boolean

true if empty entries are OK and do not violate the validation constraints; otherwise, false.

InCellDropdown

Gets or sets a value indicating whether to display the dropdown combo box for a List data validation type.

public bool InCellDropdown { get; set; }
Public Property InCellDropdown As Boolean
Property Value
System.Boolean

true to display the dropdown combo box for a List data validation type; otherwise, false.

InputMessage

Gets or sets the message text of input prompt.

public string InputMessage { get; set; }
Public Property InputMessage As String
Property Value
System.String

The message text of input prompt.

InputMessageTitle

Gets or sets the title bar text of input prompt.

public string InputMessageTitle { get; set; }
Public Property InputMessageTitle As String
Property Value
System.String

The title bar text of input prompt.

Operator

Gets or sets the relational operator used with this data validation.

public DataValidationOperator Operator { get; set; }
Public Property Operator As DataValidationOperator
Property Value
DataValidationOperator

The relational operator used with this data validation.

ShowErrorAlert

Gets or sets a value indicating whether to display the error alert message when an invalid value has been entered, according to the criteria specified.

public bool ShowErrorAlert { get; set; }
Public Property ShowErrorAlert As Boolean
Property Value
System.Boolean

true to display the error alert message when an invalid value has been entered; otherwise, false.

ShowInputMessage

Gets or sets a value indicating whether to display the input prompt message.

public bool ShowInputMessage { get; set; }
Public Property ShowInputMessage As Boolean
Property Value
System.Boolean

true to display the input prompt message; otherwise, false.

Type

Gets or sets the type of data validation.

public DataValidationType Type { get; set; }
Public Property Type As DataValidationType
Property Value
DataValidationType

The type of data validation.

Methods

GetListValues(ExcelCell)

Calculates the list of values for this data validation based on the given cell.

public List<string> GetListValues(ExcelCell cell)
Public Function GetListValues(cell As ExcelCell) As List(Of String)
Parameters
cell
ExcelCell

The cell where the list of values should be populated.

Returns
System.Collections.Generic.List<System.String>

The list of values for this data validation if the type is List, otherwise null.

Remarks

The cell is just used as a reference to calculate the list of values, it will not be affected by this method.

Validate(ExcelCell)

Applies this data validation to a specific cell, returning true if its value is to be considered valid.

public bool Validate(ExcelCell cell)
Public Function Validate(cell As ExcelCell) As Boolean
Parameters
cell
ExcelCell

The cell to be validated.

Returns
System.Boolean

true if the cell value is valid, false if it is not.