Class DataValidationsCollection

Class for managing the collection of DataValidation elements.

Inheritance

System.Object

DataValidationsCollection

Implements

System.Collections.Generic.IEnumerable<DataValidation>

System.Collections.IEnumerable

Namespace: IronXL.DataValidations

Assembly: IronXL.dll

Syntax

public class DataValidationsCollection : Object

When a worksheet needs input rules, the validations for a sheet are reached through DataValidationsCollection. It holds every DataValidation on the sheet and exposes the typed factory methods that create them, so adding, finding, and removing validation rules all happen through one object. It is the entry point behind a search like "C# Excel data validation".

A developer obtains the collection from the DataValidations property of a WorkSheet. From there the factory methods both build a rule and attach it to a range, returning the DataValidation for further configuration. The collection is enumerable, so a loop over it visits each rule, and Count and Item[Int32] give size and indexed access.

The members group by job. The creation bucket builds rules: AddIntegerRule, AddDecimalRule, AddDateRule, and AddTextLengthRule each take a range, an IronXL.DataValidations.ComparisonOperator such as Between, and bounds, while AddStringListRule and AddFormulaListRule build the drop-down list rules. Each factory has overloads accepting a Cell, a Range, a RangeAddress, or a string range. The lookup bucket finds rules: GetRuleForCell returns the rule covering a single cell, GetRulesForRange returns those across a range, and RangeAlreadyHasDataValidation tests whether a range is already constrained. The removal bucket clears rules: Remove takes a DataValidation back out, RemoveAt removes by index, RemoveRuleFromCell clears a single cell, and RemoveAllRulesFromRange clears a span. The iteration bucket exposes Count, the Item[Int32] indexer, and GetEnumerator for foreach.

using IronXL;
using IronXL.DataValidations;

WorkSheet sheet = WorkBook.Create().DefaultWorkSheet;
DataValidationsCollection validations = sheet.DataValidations;
validations.AddIntegerRule("A1:A10", ComparisonOperator.Between, 1, 100);
validations.AddStringListRule("B1:B10", new[] { "Yes", "No" });

The data validation example shows rule-driven cells in action, the select range how-to covers the ranges rules target, and the set cell data format how-to covers related cell-formatting controls.

Properties

Count

Gets the number of data validation rules in the collection.

Declaration

public int Count { get; }

Property Value

Type	Description
System.Int32

Item[Int32]

Gets the data validation rule at the specified index.

Declaration

public DataValidation this[int index] { get; }

Parameters

Type	Name	Description
System.Int32	index

Property Value

Type	Description
DataValidation

Methods

AddDateRule(Cell, ComparisonOperator, DateTime, Nullable<DateTime>)

Adds a new data validation rule with a ConstraintType of Date to the worksheet in the specified range.

Declaration

public DataValidation AddDateRule(Cell constrainedCell, ComparisonOperator comparisonOperator, DateTime minValue, Nullable<DateTime> maxValue)

Parameters

Type	Name	Description
Cell	constrainedCell	The cell to which the data validation rule will be applied.
ComparisonOperator	comparisonOperator	The comparison operator to use when validating data.
System.DateTime	minValue	The minimum value for the data validation rule.
System.Nullable<System.DateTime>	maxValue	The maximum value for the data validation rule.

Returns

Type	Description
DataValidation	The created DataValidation object.

Exceptions

Type	Condition
System.ArgumentException
System.ArgumentNullException
System.InvalidOperationException

AddDateRule(Range, ComparisonOperator, DateTime, Nullable<DateTime>)

Adds a new data validation rule with a ConstraintType of Date to the worksheet in the specified range.

Declaration

public DataValidation AddDateRule(Range constrainedRange, ComparisonOperator comparisonOperator, DateTime minValue, Nullable<DateTime> maxValue)

Parameters

Type	Name	Description
Range	constrainedRange	The range to which the data validation rule will be applied.
ComparisonOperator	comparisonOperator	The comparison operator to use when validating data.
System.DateTime	minValue	The minimum value for the data validation rule.
System.Nullable<System.DateTime>	maxValue	The maximum value for the data validation rule.

Returns

Type	Description
DataValidation	The created DataValidation object.

Exceptions

Type	Condition
System.ArgumentException
System.ArgumentNullException
System.InvalidOperationException

AddDateRule(RangeAddress, ComparisonOperator, DateTime, Nullable<DateTime>)

Adds a new data validation rule with a ConstraintType of Date to the worksheet in the specified range.

Declaration

public DataValidation AddDateRule(RangeAddress constrainedRangeAddress, ComparisonOperator comparisonOperator, DateTime minValue, Nullable<DateTime> maxValue)

Parameters

Type	Name	Description
RangeAddress	constrainedRangeAddress	The range address to which the data validation rule will be applied.
ComparisonOperator	comparisonOperator	The comparison operator to use when validating data.
System.DateTime	minValue	The minimum value for the data validation rule.
System.Nullable<System.DateTime>	maxValue	The maximum value for the data validation rule.

Returns

Type	Description
DataValidation	The created DataValidation object.

Exceptions

Type	Condition
System.ArgumentException
System.ArgumentNullException
System.InvalidOperationException

AddDateRule(String, ComparisonOperator, DateTime, Nullable<DateTime>)

Adds a new data validation rule with a ConstraintType of Date to the worksheet in the specified range.

Declaration

public DataValidation AddDateRule(string constrainedAddress, ComparisonOperator comparisonOperator, DateTime minValue, Nullable<DateTime> maxValue)

Parameters

Type	Name	Description
System.String	constrainedAddress	The range address to which the data validation rule will be applied.
ComparisonOperator	comparisonOperator	The comparison operator to use when validating data.
System.DateTime	minValue	The minimum value for the data validation rule.
System.Nullable<System.DateTime>	maxValue	The maximum value for the data validation rule.

Returns

Type	Description
DataValidation	The created DataValidation object.

Exceptions

Type	Condition
System.ArgumentException
System.ArgumentNullException
System.InvalidOperationException

AddDecimalRule(Cell, ComparisonOperator, Decimal, Nullable<Decimal>)

Adds a new data validation rule with a ConstraintType of Decimal to the worksheet in the specified range.

Declaration

public DataValidation AddDecimalRule(Cell constrainedCell, ComparisonOperator comparisonOperator, Decimal minValue, Nullable<Decimal> maxValue)

Parameters

Type	Name	Description
Cell	constrainedCell	The cell to which the data validation rule will be applied.
ComparisonOperator	comparisonOperator	The comparison operator to use when validating data.
System.Decimal	minValue	The minimum value for the data validation rule.
System.Nullable<System.Decimal>	maxValue	The maximum value for the data validation rule.

Returns

Type	Description
DataValidation	The created DataValidation object.

Exceptions

Type	Condition
System.ArgumentException
System.ArgumentNullException
System.InvalidOperationException

AddDecimalRule(Range, ComparisonOperator, Decimal, Nullable<Decimal>)

Adds a new data validation rule with a ConstraintType of Decimal to the worksheet in the specified range.

Declaration

public DataValidation AddDecimalRule(Range constrainedRange, ComparisonOperator comparisonOperator, Decimal minValue, Nullable<Decimal> maxValue)

Parameters

Type	Name	Description
Range	constrainedRange	The range to which the data validation rule will be applied.
ComparisonOperator	comparisonOperator	The comparison operator to use when validating data.
System.Decimal	minValue	The minimum value for the data validation rule.
System.Nullable<System.Decimal>	maxValue	The maximum value for the data validation rule.

Returns

Type	Description
DataValidation	The created DataValidation object.

Exceptions

Type	Condition
System.ArgumentException
System.ArgumentNullException
System.InvalidOperationException

AddDecimalRule(RangeAddress, ComparisonOperator, Decimal, Nullable<Decimal>)

Adds a new data validation rule with a ConstraintType of Decimal to the worksheet in the specified range.

Declaration

public DataValidation AddDecimalRule(RangeAddress constrainedRangeAddress, ComparisonOperator comparisonOperator, Decimal minValue, Nullable<Decimal> maxValue)

Parameters

Type	Name	Description
RangeAddress	constrainedRangeAddress	The range address to which the data validation rule will be applied.
ComparisonOperator	comparisonOperator	The comparison operator to use when validating data.
System.Decimal	minValue	The minimum value for the data validation rule.
System.Nullable<System.Decimal>	maxValue	The maximum value for the data validation rule.

Returns

Type	Description
DataValidation	The created DataValidation object.

Exceptions

Type	Condition
System.ArgumentException
System.ArgumentNullException
System.InvalidOperationException

AddDecimalRule(String, ComparisonOperator, Decimal, Nullable<Decimal>)

Adds a new data validation rule with a ConstraintType of Decimal to the worksheet in the specified range.

Declaration

public DataValidation AddDecimalRule(string constrainedAddress, ComparisonOperator comparisonOperator, Decimal minValue, Nullable<Decimal> maxValue)

Parameters

Type	Name	Description
System.String	constrainedAddress	The range address to which the data validation rule will be applied.
ComparisonOperator	comparisonOperator	The comparison operator to use when validating data.
System.Decimal	minValue	The minimum value for the data validation rule.
System.Nullable<System.Decimal>	maxValue	The maximum value for the data validation rule.

Returns

Type	Description
DataValidation	The created DataValidation object.

Exceptions

Type	Condition
System.ArgumentException
System.ArgumentNullException
System.InvalidOperationException

AddFormulaListRule(Cell, String)

Adds a new data validation rule with a ConstraintType of List to the worksheet in the specified range.

Declaration

public DataValidation AddFormulaListRule(Cell constrainedCell, string formula)

Parameters

Type	Name	Description
Cell	constrainedCell	The cell to which the data validation rule will be applied.
System.String	formula	The formula of a range of cells whose values will be used to populate the drop-down list. Can also be a name of the Named table on this same sheet.

Returns

Type	Description
DataValidation	The created DataValidation object.

Exceptions

Type	Condition
System.ArgumentException
System.ArgumentNullException
System.InvalidOperationException

AddFormulaListRule(Range, String)

Adds a new data validation rule with a ConstraintType of List to the worksheet in the specified range.

Declaration

public DataValidation AddFormulaListRule(Range constrainedRange, string formula)

Parameters

Type	Name	Description
Range	constrainedRange	The range to which the data validation rule will be applied.
System.String	formula	The formula of a range of cells whose values will be used to populate the drop-down list. Can also be a name of the Named table on this same sheet.

Returns

Type	Description
DataValidation	The created DataValidation object.

Exceptions

Type	Condition
System.ArgumentException
System.ArgumentNullException
System.InvalidOperationException

AddFormulaListRule(RangeAddress, String)

Adds a new data validation rule with a ConstraintType of List to the worksheet in the specified range.

Declaration

public DataValidation AddFormulaListRule(RangeAddress constrainedRangeAddress, string formula)

Parameters

Type	Name	Description
RangeAddress	constrainedRangeAddress	The range address to which the data validation rule will be applied.
System.String	formula	The formula of a range of cells whose values will be used to populate the drop-down list. Can also be a name of the Named table on this same sheet.

Returns

Type	Description
DataValidation	The created DataValidation object.

Exceptions

Type	Condition
System.ArgumentNullException
System.InvalidOperationException
System.ArgumentException

AddFormulaListRule(String, String)

Adds a new data validation rule with a ConstraintType of List to the worksheet in the specified range.

Declaration

public DataValidation AddFormulaListRule(string constrainedAddress, string formula)

Parameters

Type	Name	Description
System.String	constrainedAddress	The range address to which the data validation rule will be applied.
System.String	formula	The formula of a range of cells whose values will be used to populate the drop-down list. Can also be a name of the Named table on this same sheet.

Returns

Type	Description
DataValidation	The created DataValidation object.

Exceptions

Type	Condition
System.ArgumentException
System.ArgumentNullException
System.InvalidOperationException

AddIntegerRule(Cell, ComparisonOperator, Int32, Nullable<Int32>)

Adds a new data validation rule with a ConstraintType of WholeNumber to the worksheet in the specified range.

Declaration

public DataValidation AddIntegerRule(Cell constrainedCell, ComparisonOperator comparisonOperator, int minValue, Nullable<int> maxValue)

Parameters

Type	Name	Description
Cell	constrainedCell	The cell to which the data validation rule will be applied.
ComparisonOperator	comparisonOperator	The comparison operator to use when validating data.
System.Int32	minValue	The minimum value for the data validation rule.
System.Nullable<System.Int32>	maxValue	The maximum value for the data validation rule.

Returns

Type	Description
DataValidation	The created DataValidation object.

Exceptions

Type	Condition
System.ArgumentException
System.ArgumentNullException
System.InvalidOperationException

AddIntegerRule(Range, ComparisonOperator, Int32, Nullable<Int32>)

Adds a new data validation rule with a ConstraintType of WholeNumber to the worksheet in the specified range.

Declaration

public DataValidation AddIntegerRule(Range constrainedRange, ComparisonOperator comparisonOperator, int minValue, Nullable<int> maxValue)

Parameters

Type	Name	Description
Range	constrainedRange	The range address to which the data validation rule will be applied.
ComparisonOperator	comparisonOperator	The comparison operator to use when validating data.
System.Int32	minValue	The minimum value for the data validation rule.
System.Nullable<System.Int32>	maxValue	The maximum value for the data validation rule.

Returns

Type	Description
DataValidation	The created DataValidation object.

Exceptions

Type	Condition
System.ArgumentException
System.ArgumentNullException
System.InvalidOperationException

AddIntegerRule(RangeAddress, ComparisonOperator, Int32, Nullable<Int32>)

Adds a new data validation rule with a ConstraintType of WholeNumber to the worksheet in the specified range.

Declaration

public DataValidation AddIntegerRule(RangeAddress constrainedRangeAddress, ComparisonOperator comparisonOperator, int minValue, Nullable<int> maxValue)

Parameters

Type	Name	Description
RangeAddress	constrainedRangeAddress	The range address to which the data validation rule will be applied.
ComparisonOperator	comparisonOperator	The comparison operator to use when validating data.
System.Int32	minValue	The minimum value for the data validation rule.
System.Nullable<System.Int32>	maxValue	The maximum value for the data validation rule.

Returns

Type	Description
DataValidation	The created DataValidation object.

Exceptions

Type	Condition
System.ArgumentException
System.ArgumentNullException
System.InvalidOperationException

AddIntegerRule(String, ComparisonOperator, Int32, Nullable<Int32>)

Adds a new data validation rule with a ConstraintType of WholeNumber to the worksheet in the specified range.

Declaration

public DataValidation AddIntegerRule(string constrainedAddress, ComparisonOperator comparisonOperator, int minValue, Nullable<int> maxValue)

Parameters

Type	Name	Description
System.String	constrainedAddress	The range address to which the data validation rule will be applied.
ComparisonOperator	comparisonOperator	The comparison operator to use when validating data.
System.Int32	minValue	The minimum value for the data validation rule.
System.Nullable<System.Int32>	maxValue	The maximum value for the data validation rule.

Returns

Type	Description
DataValidation	The created DataValidation object.

Exceptions

Type	Condition
System.ArgumentException
System.ArgumentNullException
System.InvalidOperationException

AddStringListRule(Cell, String[])

Adds a new data validation rule with a ConstraintType of List to the worksheet in the specified range.

Declaration

public DataValidation AddStringListRule(Cell constrainedCell, string[] values)

Parameters

Type	Name	Description
Cell	constrainedCell	The cell to which the data validation rule will be applied.
System.String[]	values	The values to populate the drop-down list with.

Returns

Type	Description
DataValidation	The created DataValidation object.

Exceptions

Type	Condition
System.ArgumentException
System.ArgumentNullException
System.InvalidOperationException

AddStringListRule(Range, String[])

Adds a new data validation rule with a ConstraintType of List to the worksheet in the specified range.

Declaration

public DataValidation AddStringListRule(Range constrainedRange, string[] values)

Parameters

Type	Name	Description
Range	constrainedRange	The range to which the data validation rule will be applied.
System.String[]	values	The values to populate the drop-down list with.

Returns

Type	Description
DataValidation	The created DataValidation object.

Exceptions

Type	Condition
System.ArgumentException
System.ArgumentNullException
System.InvalidOperationException

AddStringListRule(RangeAddress, String[])

Adds a new data validation rule with a ConstraintType of List to the worksheet in the specified range.

Declaration

public DataValidation AddStringListRule(RangeAddress constrainedRangeAddress, string[] values)

Parameters

Type	Name	Description
RangeAddress	constrainedRangeAddress	The range address to which the data validation rule will be applied.
System.String[]	values	The values to populate the drop-down list with.

Returns

Type	Description
DataValidation	The created DataValidation object.

Exceptions

Type	Condition
System.ArgumentException
System.ArgumentNullException
System.InvalidOperationException

AddStringListRule(String, String[])

Adds a new data validation rule with a ConstraintType of List to the worksheet in the specified range.

Declaration

public DataValidation AddStringListRule(string constrainedAddress, string[] values)

Parameters

Type	Name	Description
System.String	constrainedAddress	The range address to which the data validation rule will be applied.
System.String[]	values	The values to populate the drop-down list with.

Returns

Type	Description
DataValidation	The created DataValidation object.

Exceptions

Type	Condition
System.ArgumentException
System.ArgumentNullException
System.InvalidOperationException

AddTextLengthRule(Cell, ComparisonOperator, Int32, Nullable<Int32>)

Adds a new data validation rule with a ConstraintType of Date to the worksheet in the specified range.

Declaration

public DataValidation AddTextLengthRule(Cell constrainedCell, ComparisonOperator comparisonOperator, int minLength, Nullable<int> maxLength)

Parameters

Type	Name	Description
Cell	constrainedCell	The cell to which the data validation rule will be applied.
ComparisonOperator	comparisonOperator	The comparison operator to use when validating data.
System.Int32	minLength	The minimum string length for the data validation rule.
System.Nullable<System.Int32>	maxLength	The maximum string length for the data validation rule.

Returns

Type	Description
DataValidation	The created DataValidation object.

Exceptions

Type	Condition
System.ArgumentException
System.ArgumentNullException
System.InvalidOperationException

AddTextLengthRule(Range, ComparisonOperator, Int32, Nullable<Int32>)

Adds a new data validation rule with a ConstraintType of Date to the worksheet in the specified range.

Declaration

public DataValidation AddTextLengthRule(Range constrainedRange, ComparisonOperator comparisonOperator, int minLength, Nullable<int> maxLength)

Parameters

Type	Name	Description
Range	constrainedRange	The range to which the data validation rule will be applied.
ComparisonOperator	comparisonOperator	The comparison operator to use when validating data.
System.Int32	minLength	The minimum string length for the data validation rule.
System.Nullable<System.Int32>	maxLength	The maximum string length for the data validation rule.

Returns

Type	Description
DataValidation	The created DataValidation object.

Exceptions

Type	Condition
System.ArgumentException
System.ArgumentNullException
System.InvalidOperationException

AddTextLengthRule(RangeAddress, ComparisonOperator, Int32, Nullable<Int32>)

Adds a new data validation rule with a ConstraintType of Date to the worksheet in the specified range.

Declaration

public DataValidation AddTextLengthRule(RangeAddress constrainedRangeAddress, ComparisonOperator comparisonOperator, int minLength, Nullable<int> maxLength)

Parameters

Type	Name	Description
RangeAddress	constrainedRangeAddress	The range address to which the data validation rule will be applied.
ComparisonOperator	comparisonOperator	The comparison operator to use when validating data.
System.Int32	minLength	The minimum string length for the data validation rule.
System.Nullable<System.Int32>	maxLength	The maximum string length for the data validation rule.

Returns

Type	Description
DataValidation	The created DataValidation object.

Exceptions

Type	Condition
System.ArgumentException
System.ArgumentNullException
System.InvalidOperationException

AddTextLengthRule(String, ComparisonOperator, Int32, Nullable<Int32>)

Adds a new data validation rule with a ConstraintType of Date to the worksheet in the specified range.

Declaration

public DataValidation AddTextLengthRule(string constrainedAddress, ComparisonOperator comparisonOperator, int minLength, Nullable<int> maxLength)

Parameters

Type	Name	Description
System.String	constrainedAddress	The range address to which the data validation rule will be applied.
ComparisonOperator	comparisonOperator	The comparison operator to use when validating data.
System.Int32	minLength	The minimum string length for the data validation rule.
System.Nullable<System.Int32>	maxLength	The maximum string length for the data validation rule.

Returns

Type	Description
DataValidation	The created DataValidation object.

Exceptions

Type	Condition
System.ArgumentException
System.ArgumentNullException
System.InvalidOperationException

GetEnumerator()

Gets the enumerator.

Declaration

public IEnumerator<DataValidation> GetEnumerator()

Returns

Type	Description
System.Collections.Generic.IEnumerator<DataValidation>

GetRuleForCell(Cell)

Gets the data validation rule that applies to the specified cell.

Declaration

public DataValidation GetRuleForCell(Cell constrainedCell)

Parameters

Type	Name	Description
Cell	constrainedCell	The cell to check.

Returns

Type	Description
DataValidation

Exceptions

Type	Condition
System.ArgumentNullException

GetRulesForRange(Range)

Gets the data validation rules that applies to the specified range.

Declaration

public DataValidationsCollection GetRulesForRange(Range constrainedRange)

Parameters

Type	Name	Description
Range	constrainedRange	The range to check.

Returns

Type	Description
DataValidationsCollection

Exceptions

Type	Condition
System.ArgumentNullException

GetRulesForRange(RangeAddress)

Gets the data validation rules that applies to the specified range.

Declaration

public DataValidationsCollection GetRulesForRange(RangeAddress rangeAddress)

Parameters

Type	Name	Description
RangeAddress	rangeAddress	The range to check.

Returns

Type	Description
DataValidationsCollection

Exceptions

Type	Condition
System.ArgumentNullException

GetRulesForRange(String)

Gets the data validation rules that applies to the specified range.

Declaration

public DataValidationsCollection GetRulesForRange(string constrainedAddress)

Parameters

Type	Name	Description
System.String	constrainedAddress	The address of the range in A1 notation.

Returns

Type	Description
DataValidationsCollection

Exceptions

Type	Condition
System.ArgumentException

RangeAlreadyHasDataValidation(RangeAddress)

Checks if the specified range already has a data validation rule.

Declaration

public bool RangeAlreadyHasDataValidation(RangeAddress rangeAddress)

Parameters

Type	Name	Description
RangeAddress	rangeAddress

Returns

Type	Description
System.Boolean

Remove(DataValidation)

Remove a specified data validation rule from the worksheet.

Declaration

public void Remove(DataValidation dataValidation)

Parameters

Type	Name	Description
DataValidation	dataValidation	The data validation rule to remove.

Exceptions

Type	Condition
System.ArgumentNullException

RemoveAllRulesFromRange(Range)

Remove all data validation rules from the range on this worksheet.

Declaration

public void RemoveAllRulesFromRange(Range constrainedRange)

Parameters

Type	Name	Description
Range	constrainedRange

RemoveAllRulesFromRange(RangeAddress)

Remove all data validation rules from the range on this worksheet.

Declaration

public void RemoveAllRulesFromRange(RangeAddress rangeAddress)

Parameters

Type	Name	Description
RangeAddress	rangeAddress

Exceptions

Type	Condition
System.ArgumentNullException

RemoveAllRulesFromRange(String)

Remove all data validation rules from the range on this worksheet.

Declaration

public void RemoveAllRulesFromRange(string address)

Parameters

Type	Name	Description
System.String	address

RemoveAt(Int32)

Remove a data validation rule at the specified index from the worksheet.

Declaration

public void RemoveAt(int index)

Parameters

Type	Name	Description
System.Int32	index	The index of the data validation rule to remove.

Exceptions

Type	Condition
System.ArgumentOutOfRangeException

RemoveRuleFromCell(Cell)

Remove all data validation rules from the cell on this worksheet.

Declaration

public void RemoveRuleFromCell(Cell constrainedCell)

Parameters

Type	Name	Description
Cell	constrainedCell

Implements

System.Collections.Generic.IEnumerable<>

System.Collections.IEnumerable