Class ExcelDataValidationCollection

Namespace

OfficeOpenXml.DataValidation

Assembly

EPPlus.dll

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;

public class ExcelDataValidationCollection : IEnumerable<IExcelDataValidation>, IEnumerable

Inheritance

object

ExcelDataValidationCollection

Implements

IEnumerable<IExcelDataValidation>

IEnumerable

Inherited Members

object.GetType()

object.MemberwiseClone()

object.ToString()

object.Equals(object)

object.Equals(object, object)

object.ReferenceEquals(object, object)

object.GetHashCode()

Properties

Count

Number of validations

public int Count { get; }

Property Value

int

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.

public bool InternalValidationEnabled { get; set; }

Property Value

bool

this[int]

Index operator, returns by 0-based index

public ExcelDataValidation this[int index] { get; set; }

Parameters

index int

Property Value

ExcelDataValidation

this[string]

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

public IExcelDataValidation this[string address] { get; }

Parameters

address string

A cell address or range

Property Value

IExcelDataValidation

A ExcelDataValidation or null if no match

Methods

AddAnyValidation(string)

Adds a ExcelDataValidationAny to the worksheet.

public IExcelDataValidationAny AddAnyValidation(string address)

Parameters

address string

The range/address to validate

Returns

IExcelDataValidationAny

AddCustomValidation(string)

Adds a ExcelDataValidationCustom to the worksheet.

public IExcelDataValidationCustom AddCustomValidation(string address)

Parameters

address string

The range/address to validate

Returns

IExcelDataValidationCustom

AddDateTimeValidation(string)

Adds an IExcelDataValidationDateTime to the worksheet.

public IExcelDataValidationDateTime AddDateTimeValidation(string address)

Parameters

address string

The range/address to validate

Returns

IExcelDataValidationDateTime

AddDecimalValidation(string)

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

public IExcelDataValidationDecimal AddDecimalValidation(string address)

Parameters

address string

The range/address to validate

Returns

IExcelDataValidationDecimal

AddIntegerValidation(string)

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

public IExcelDataValidationInt AddIntegerValidation(string address)

Parameters

address string

the range/address to validate

Returns

IExcelDataValidationInt

AddListValidation(string)

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

public IExcelDataValidationList AddListValidation(string address)

Parameters

address string

The range/address to validate

Returns

IExcelDataValidationList

AddTextLengthValidation(string)

Adds an IExcelDataValidationInt regarding text length to the worksheet.

public IExcelDataValidationInt AddTextLengthValidation(string address)

Parameters

address string

The range/address to validate

Returns

IExcelDataValidationInt

AddTimeValidation(string)

Adds an IExcelDataValidationDateTime to the worksheet.

public IExcelDataValidationTime AddTimeValidation(string address)

Parameters

address string

The range/address to validate

Returns

IExcelDataValidationTime

Clear()

Removes all validations from the collection.

public void Clear()

Find(Predicate<ExcelDataValidation>)

Returns the first matching validation.

public ExcelDataValidation Find(Predicate<ExcelDataValidation> match)

Parameters

match Predicate<ExcelDataValidation>

Returns

ExcelDataValidation

FindAll(Predicate<ExcelDataValidation>)

Returns all validations that matches the supplied predicate match.

public IEnumerable<ExcelDataValidation> FindAll(Predicate<ExcelDataValidation> match)

Parameters

match Predicate<ExcelDataValidation>

predicate to filter out matching validations

Returns

IEnumerable<ExcelDataValidation>

ReadDataValidations(XmlReader)

Read data validation from xml via xr reader

public void ReadDataValidations(XmlReader xr)

Parameters

xr XmlReader

Remove(IExcelDataValidation)

Removes an ExcelDataValidation from the collection.

public bool Remove(IExcelDataValidation item)

Parameters

item IExcelDataValidation

The item to remove

Returns

bool

True if remove succeeds, otherwise false

Exceptions

ArgumentNullException

if item is null

RemoveAll(Predicate<ExcelDataValidation>)

Removes the validations that matches the predicate

public void RemoveAll(Predicate<ExcelDataValidation> match)

Parameters

match Predicate<ExcelDataValidation>