Class ExcelDataValidationCollection

Collection of ExcelDataValidation. This class is providing the API for EPPlus data validation.

The public methods of this class (Add[...]Validation) will create a datavalidation entry in the worksheet. When this validation has been created changes to the properties will affect the workbook immediately.

Each type of validation has either a formula or a typed value/values, except for custom validation which has a formula only. 

// Add a date time validation
var validation = worksheet.DataValidation.AddDateTimeValidation("A1");
// set validation properties
validation.ShowErrorMessage = true;
validation.ErrorTitle = "An invalid date was entered";
validation.Error = "The date must be between 2011-01-31 and 2011-12-31";
validation.Prompt = "Enter date here";
validation.Formula.Value = DateTime.Parse("2011-01-01");
validation.Formula2.Value = DateTime.Parse("2011-12-31");
validation.Operator = ExcelDataValidationOperator.between;
Inheritance
System.Object
ExcelDataValidationCollection
Implements
System.Collections.Generic.IEnumerable<IExcelDataValidation>
System.Collections.IEnumerable
Inherited Members
System.Object.Equals(System.Object)
System.Object.Equals(System.Object, System.Object)
System.Object.GetHashCode()
System.Object.GetType()
System.Object.MemberwiseClone()
System.Object.ReferenceEquals(System.Object, System.Object)
System.Object.ToString()
Namespace: OfficeOpenXml.DataValidation
Assembly: EPPlus.dll
Syntax
public class ExcelDataValidationCollection : IEnumerable<IExcelDataValidation>, IEnumerable

Properties

Count

Number of validations

Declaration
public int Count { get; }
Property Value
Type Description
System.Int32

InternalValidationEnabled

Epplus validates that all data validations are consistend and valid when they are added and when a workbook is saved. Since this takes some resources, it can be disabled for improve performance.

Declaration
public bool InternalValidationEnabled { get; set; }
Property Value
Type Description
System.Boolean

Item[Int32]

Index operator, returns by 0-based index

Declaration
public ExcelDataValidation this[int index] { get; set; }
Parameters
Type Name Description
System.Int32 index
Property Value
Type Description
ExcelDataValidation

Item[String]

Index operator, returns a data validation which address partly or exactly matches the searched address.

Declaration
public IExcelDataValidation this[string address] { get; }
Parameters
Type Name Description
System.String address

A cell address or range
Property Value
Type Description
IExcelDataValidation

A ExcelDataValidation or null if no match

Methods

AddAnyValidation(String)

Adds a ExcelDataValidationAny to the worksheet.

Declaration
public IExcelDataValidationAny AddAnyValidation(string address)
Parameters
Type Name Description
System.String address

The range/address to validate
Returns
Type Description
IExcelDataValidationAny

AddCustomValidation(String)

Adds a ExcelDataValidationCustom to the worksheet.

Declaration
public IExcelDataValidationCustom AddCustomValidation(string address)
Parameters
Type Name Description
System.String address

The range/address to validate
Returns
Type Description
IExcelDataValidationCustom

AddDateTimeValidation(String)

Adds an IExcelDataValidationDateTime to the worksheet.

Declaration
public IExcelDataValidationDateTime AddDateTimeValidation(string address)
Parameters
Type Name Description
System.String address

The range/address to validate
Returns
Type Description
IExcelDataValidationDateTime

AddDecimalValidation(String)

Addes an IExcelDataValidationDecimal to the worksheet. The only accepted values are decimal values.

Declaration
public IExcelDataValidationDecimal AddDecimalValidation(string address)
Parameters
Type Name Description
System.String address

The range/address to validate
Returns
Type Description
IExcelDataValidationDecimal

AddIntegerValidation(String)

Adds an IExcelDataValidationInt to the worksheet. Whole means that the only accepted values are integer values.

Declaration
public IExcelDataValidationInt AddIntegerValidation(string address)
Parameters
Type Name Description
System.String address

the range/address to validate
Returns
Type Description
IExcelDataValidationInt

AddListValidation(String)

Adds an IExcelDataValidationList to the worksheet. The accepted values are defined in a list.

Declaration
public IExcelDataValidationList AddListValidation(string address)
Parameters
Type Name Description
System.String address

The range/address to validate
Returns
Type Description
IExcelDataValidationList

AddTextLengthValidation(String)

Adds an IExcelDataValidationInt regarding text length to the worksheet.

Declaration
public IExcelDataValidationInt AddTextLengthValidation(string address)
Parameters
Type Name Description
System.String address

The range/address to validate
Returns
Type Description
IExcelDataValidationInt

AddTimeValidation(String)

Adds an IExcelDataValidationDateTime to the worksheet.

Declaration
public IExcelDataValidationTime AddTimeValidation(string address)
Parameters
Type Name Description
System.String address

The range/address to validate
Returns
Type Description
IExcelDataValidationTime

Clear()

Removes all validations from the collection.

Declaration
public void Clear()

Find(Predicate<ExcelDataValidation>)

Returns the first matching validation.

Declaration
public ExcelDataValidation Find(Predicate<ExcelDataValidation> match)
Parameters
Type Name Description
System.Predicate<ExcelDataValidation> match
Returns
Type Description
ExcelDataValidation

FindAll(Predicate<ExcelDataValidation>)

Returns all validations that matches the supplied predicate match.

Declaration
public IEnumerable<ExcelDataValidation> FindAll(Predicate<ExcelDataValidation> match)
Parameters
Type Name Description
System.Predicate<ExcelDataValidation> match

predicate to filter out matching validations
Returns
Type Description
System.Collections.Generic.IEnumerable<ExcelDataValidation>

ReadDataValidations(XmlReader)

Read data validation from xml via xr reader

Declaration
public void ReadDataValidations(XmlReader xr)
Parameters
Type Name Description
System.Xml.XmlReader xr

Remove(IExcelDataValidation)

Removes an ExcelDataValidation from the collection.

Declaration
public bool Remove(IExcelDataValidation item)
Parameters
Type Name Description
IExcelDataValidation item

The item to remove
Returns
Type Description
System.Boolean

True if remove succeeds, otherwise false
Exceptions
Type Condition
System.ArgumentNullException

if item is null

RemoveAll(Predicate<ExcelDataValidation>)

Removes the validations that matches the predicate

Declaration
public void RemoveAll(Predicate<ExcelDataValidation> match)
Parameters
Type Name Description
System.Predicate<ExcelDataValidation> match

Explicit Interface Implementations

IEnumerable<IExcelDataValidation>.GetEnumerator()

Declaration
IEnumerator<IExcelDataValidation> IEnumerable<IExcelDataValidation>.GetEnumerator()
Returns
Type Description
System.Collections.Generic.IEnumerator<IExcelDataValidation>

IEnumerable.GetEnumerator()

Declaration
IEnumerator IEnumerable.GetEnumerator()
Returns
Type Description
System.Collections.IEnumerator

Implements

System.Collections.Generic.IEnumerable<T>
System.Collections.IEnumerable