Class DataValidation

Namespace

SheetStream

Assembly

SheetStream.dll

A data validation rule over one range: a drop-down list, number or date bounds, text length, or a custom formula. Create through AddDataValidation(string), then pick exactly one rule method and optionally add a prompt and error message.

public sealed class DataValidation

Inheritance

object

DataValidation

Inherited Members

object.Equals(object)

object.Equals(object, object)

object.GetHashCode()

object.GetType()

object.ReferenceEquals(object, object)

object.ToString()

Examples

sheet.AddDataValidation("C2:C500").List("Open", "Closed", "On hold").WithError("Invalid status", "Pick a value from the list.");
sheet.AddDataValidation("D2:D500").WholeNumber(ValidationOperator.Between, 1, 100);

Properties

AllowBlank

Allow the cell to be left empty. Defaults to true, matching Excel.

public bool AllowBlank { get; set; }

Property Value

bool

Error

Text of the error alert.

public string? Error { get; set; }

Property Value

string

ErrorStyle

Severity of the error alert.

public ValidationErrorStyle ErrorStyle { get; set; }

Property Value

ValidationErrorStyle

ErrorTitle

Title of the error alert.

public string? ErrorTitle { get; set; }

Property Value

string

HideDropDown

Hide the in-cell drop-down arrow of a list validation.

public bool HideDropDown { get; set; }

Property Value

bool

Prompt

Text of the input prompt shown when the cell is selected.

public string? Prompt { get; set; }

Property Value

string

PromptTitle

Title of the input prompt shown when the cell is selected.

public string? PromptTitle { get; set; }

Property Value

string

Methods

Custom(string)

Accepts a value only when the formula is TRUE, written relative to the range's top-left cell, e.g. "ISNUMBER(B2)" for a range starting at B2.

public DataValidation Custom(string formula)

Parameters

formula string

Returns

DataValidation

Date(ValidationOperator, DateTime, DateTime?)

Restricts the cells to dates.

public DataValidation Date(ValidationOperator op, DateTime value1, DateTime? value2 = null)

Parameters

op ValidationOperator

value1 DateTime

value2 DateTime?

Returns

DataValidation

Decimal(ValidationOperator, double, double?)

Restricts the cells to decimal numbers.

public DataValidation Decimal(ValidationOperator op, double value1, double? value2 = null)

Parameters

op ValidationOperator

value1 double

value2 double?

Returns

DataValidation

List(params string[])

Restricts the cells to a fixed drop-down list. Excel limits an inline list to 255 characters including separators; for longer lists put the values on a sheet and use ListFromRange(string).

public DataValidation List(params string[] values)

Parameters

values string[]

Returns

DataValidation

ListFromRange(string)

Restricts the cells to the values found in a range, e.g. "'Lists'!A1:A20" or "H2:H30" on the same sheet. A defined name such as "=StatusList" is passed through as written.

public DataValidation ListFromRange(string rangeOrName)

Parameters

rangeOrName string

Returns

DataValidation

TextLength(ValidationOperator, int, int?)

Restricts the number of characters typed into the cells.

public DataValidation TextLength(ValidationOperator op, int value1, int? value2 = null)

Parameters

op ValidationOperator

value1 int

value2 int?

Returns

DataValidation

WholeNumber(ValidationOperator, long, long?)

Restricts the cells to whole numbers.

public DataValidation WholeNumber(ValidationOperator op, long value1, long? value2 = null)

Parameters

op ValidationOperator

value1 long

value2 long?

Returns

DataValidation

WithError(string, string, ValidationErrorStyle)

Sets the error alert shown when a value fails.

public DataValidation WithError(string title, string message, ValidationErrorStyle style = ValidationErrorStyle.Stop)

Parameters

title string

message string

style ValidationErrorStyle

Returns

DataValidation

WithPrompt(string, string)

Sets the input prompt shown when a cell of the range is selected.

public DataValidation WithPrompt(string title, string message)

Parameters

title string

message string

Returns

DataValidation