ConditionalFormatRuleCollection Class

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

Represents a collection of a conditional formatting rules.

Rules are applied in the order they appear in the collection (first rule has highest priority and last rule has lowest priority).

Newly added rules are inserted at the beginning of the collection so they are first applied.

public sealed class ConditionalFormatRuleCollection : IEnumerable<ConditionalFormatRule>, IEnumerable
Public NotInheritable Class ConditionalFormatRuleCollection
    Implements IEnumerable(Of ConditionalFormatRule), IEnumerable
Inheritance:
System.Object
ConditionalFormatRuleCollection
Implements
System.Collections.Generic.IEnumerable<ConditionalFormatRule>
System.Collections.IEnumerable

Properties

Count

Gets the number of ConditionalFormatRules contained in the ConditionalFormatRuleCollection.

public int Count { get; }
Public ReadOnly Property Count As Integer
Property Value
System.Int32

Item[Int32]

Gets the ConditionalFormatRule at the specified index.

public ConditionalFormatRule this[int index] { get; }
Public ReadOnly Property Item(index As Integer) As ConditionalFormatRule
Parameters
index
System.Int32

The zero-based index of the ConditionalFormatRule to get.

Property Value
ConditionalFormatRule

The ConditionalFormatRule at the specified index.

Exceptions
System.ArgumentOutOfRangeException

index is less than 0 or is equal to or greater than Count.

Methods

Add2ColorScale(String)

Adds a '2-Color Scale' conditional formatting rule applied to a specified sequence of references to cell ranges.

public ColorScaleCondition Add2ColorScale(string appliesTo)
Public Function Add2ColorScale(appliesTo As String) As ColorScaleCondition
Parameters
appliesTo
System.String

The sequence of references to cell ranges over which the newly added conditional formatting rule is applied.

Returns
ColorScaleCondition

A ColorScaleCondition set to a newly created conditional formatting rule which can be used to further customize the formatting condition.

Remarks

Newly added rule is inserted at the beginning of the collection so it has the highest priority and is first applied.

Newly added rule has the same default settings as in MS Excel.

Exceptions
System.ArgumentException

Parameter appliesTo is null or System.String.Empty.

Add3ColorScale(String)

Adds a '3-Color Scale' conditional formatting rule applied to a specified sequence of references to cell ranges.

public ColorScaleCondition Add3ColorScale(string appliesTo)
Public Function Add3ColorScale(appliesTo As String) As ColorScaleCondition
Parameters
appliesTo
System.String

The sequence of references to cell ranges over which the newly added conditional formatting rule is applied.

Returns
ColorScaleCondition

A ColorScaleCondition set to a newly created conditional formatting rule which can be used to further customize the formatting condition.

Remarks

Newly added rule is inserted at the beginning of the collection so it has the highest priority and is first applied.

Newly added rule has the same default settings as in MS Excel.

Exceptions
System.ArgumentException

Parameter appliesTo is null or System.String.Empty.

AddAboveOrBelowAverage(String, Boolean)

Adds an 'Above or Below Average' conditional formatting rule applied to a specified sequence of references to cell ranges.

public AboveOrBelowAverageCondition AddAboveOrBelowAverage(string appliesTo, bool below)
Public Function AddAboveOrBelowAverage(appliesTo As String, below As Boolean) As AboveOrBelowAverageCondition
Parameters
appliesTo
System.String

The sequence of references to cell ranges over which the newly added conditional formatting rule is applied.

below
System.Boolean

if set to true format cells with values below average; otherwise, format cells with values above average.

Returns
AboveOrBelowAverageCondition

A AboveOrBelowAverageCondition set to a newly created conditional formatting rule which can be used to further customize the formatting condition.

Remarks

Newly added rule is inserted at the beginning of the collection so it has the highest priority and is first applied.

Exceptions
System.ArgumentException

Parameter appliesTo is null or System.String.Empty.

AddContain(String, ContainConditionType)

Adds a 'Cells with Blanks' or 'Cells with No Blanks' or 'Cells with Errors' or 'Cells with No Errors' conditional formatting rule applied to a specified sequence of references to cell ranges.

public ContainCondition AddContain(string appliesTo, ContainConditionType containConditionType)
Public Function AddContain(appliesTo As String, containConditionType As ContainConditionType) As ContainCondition
Parameters
appliesTo
System.String

The sequence of references to cell ranges over which the newly added conditional formatting rule is applied.

containConditionType
ContainConditionType

The contain condition type (Blanks or NoBlanks or Errors or NoErrors).

Returns
ContainCondition

A ContainCondition set to a newly created conditional formatting rule which can be used to customize the formatting.

Remarks

Newly added rule is inserted at the beginning of the collection so it has the highest priority and is first applied.

Exceptions
System.ArgumentException

Parameter appliesTo is null or System.String.Empty.

System.ArgumentException

Parameter containConditionType is not Blanks or NoBlanks or Errors or NoErrors.

AddContainDate(String, ContainDateOperator)

Adds a 'Cells with Dates Occurring' conditional formatting rule applied to a specified sequence of references to cell ranges.

public ContainDateCondition AddContainDate(string appliesTo, ContainDateOperator operator)
Public Function AddContainDate(appliesTo As String, operator As ContainDateOperator) As ContainDateCondition
Parameters
appliesTo
System.String

The sequence of references to cell ranges over which the newly added conditional formatting rule is applied.

operator
ContainDateOperator

The date operator.

Returns
ContainDateCondition

A ContainDateCondition set to a newly created conditional formatting rule which can be used to customize the formatting.

Remarks

Newly added rule is inserted at the beginning of the collection so it has the highest priority and is first applied.

Exceptions
System.ArgumentException

Parameter appliesTo is null or System.String.Empty.

AddContainText(String, ContainTextOperator, String)

Adds a 'Cells with Specific Text' conditional formatting rule applied to a specified sequence of references to cell ranges.

public ContainTextCondition AddContainText(string appliesTo, ContainTextOperator operator, string value)
Public Function AddContainText(appliesTo As String, operator As ContainTextOperator, value As String) As ContainTextCondition
Parameters
appliesTo
System.String

The sequence of references to cell ranges over which the newly added conditional formatting rule is applied.

operator
ContainTextOperator

The textual operator.

value
System.String

The value (constant string or formula).

Returns
ContainTextCondition

A ContainTextCondition set to a newly created conditional formatting rule which can be used to customize the formatting.

Remarks

Newly added rule is inserted at the beginning of the collection so it has the highest priority and is first applied.

Value can be either constant or formula.

If value is constant, then it cannot be null.

If value is formula, it must start with '='.

Exceptions
System.ArgumentException

Parameter appliesTo is null or System.String.Empty.

System.ArgumentNullException

Parameter value is null.

AddContainValue(String, ContainValueOperator, Object)

Adds a 'Cells with Cell Value' conditional formatting rule applied to a specified sequence of references to cell ranges.

public ContainValueCondition AddContainValue(string appliesTo, ContainValueOperator operator, object value)
Public Function AddContainValue(appliesTo As String, operator As ContainValueOperator, value As Object) As ContainValueCondition
Parameters
appliesTo
System.String

The sequence of references to cell ranges over which the newly added conditional formatting rule is applied.

operator
ContainValueOperator

The operator (all except Between and NotBetween).

value
System.Object

The value (constant or formula).

Returns
ContainValueCondition

A ContainValueCondition set to a newly created conditional formatting rule which can be used to customize the formatting.

Remarks

Newly added rule is inserted at the beginning of the collection so it has the highest priority and is first applied.

Value can be either constant or formula.

If value is constant, then it cannot be null and value's type must be supported in Value (types supported in SupportsType(Type)).

If value is formula, then value type must be System.String and it must start with '='.

Exceptions
System.ArgumentException

Parameter appliesTo is null or System.String.Empty.

System.ArgumentException

Parameter operator is Between or NotBetween. Use other method overload for these operators.

System.ArgumentNullException

Parameter value is null.

System.ArgumentException

Parameter value type is not supported.

AddContainValue(String, ContainValueOperator, Object, Object)

Adds a 'Cells with Cell Value' conditional formatting rule applied to a specified sequence of references to cell ranges.

public ContainValueCondition AddContainValue(string appliesTo, ContainValueOperator operator, object value, object value2)
Public Function AddContainValue(appliesTo As String, operator As ContainValueOperator, value As Object, value2 As Object) As ContainValueCondition
Parameters
appliesTo
System.String

The sequence of references to cell ranges over which the newly added conditional formatting rule is applied.

operator
ContainValueOperator

The operator (Between or NotBetween).

value
System.Object

The value (constant or formula).

value2
System.Object

The second value (constant or formula).

Returns
ContainValueCondition

A ContainValueCondition set to a newly created conditional formatting rule which can be used to customize the formatting.

Remarks

Newly added rule is inserted at the beginning of the collection so it has the highest priority and is first applied.

Values can be either constant or formula.

If value is constant, then it cannot be null and value's type must be supported in Value (types supported in SupportsType(Type)).

If value is formula, then value type must be System.String and it must start with '='.

Exceptions
System.ArgumentException

Parameter appliesTo is null or System.String.Empty.

System.ArgumentException

Parameter operator is not Between nor NotBetween. Use other method overload for other operators.

System.ArgumentNullException

Parameter value or value2 is null.

System.ArgumentException

Parameter value or value2 type is not supported.

AddDataBar(String)

Adds a 'Data Bar' conditional formatting rule applied to a specified sequence of references to cell ranges.

public DataBarCondition AddDataBar(string appliesTo)
Public Function AddDataBar(appliesTo As String) As DataBarCondition
Parameters
appliesTo
System.String

The sequence of references to cell ranges over which the newly added conditional formatting rule is applied.

Returns
DataBarCondition

A DataBarCondition set to a newly created conditional formatting rule which can be used to further customize the formatting condition.

Remarks

Newly added rule is inserted at the beginning of the collection so it has the highest priority and is first applied.

Newly added rule has the same default settings as in MS Excel.

Exceptions
System.ArgumentException

Parameter appliesTo is null or System.String.Empty.

AddFormula(String, String)

Adds a 'Formula' conditional formatting rule applied to a specified sequence of references to cell ranges.

public FormulaCondition AddFormula(string appliesTo, string formula)
Public Function AddFormula(appliesTo As String, formula As String) As FormulaCondition
Parameters
appliesTo
System.String

The sequence of references to cell ranges over which the newly added conditional formatting rule is applied.

formula
System.String

The formula used to determine which cells to format (only cells for which the formula result evaluates to true are formatted).

Returns
FormulaCondition

A FormulaCondition set to a newly created conditional formatting rule which can be used to customize the formatting.

Remarks

Newly added rule is inserted at the beginning of the collection so it has the highest priority and is first applied.

Exceptions
System.ArgumentException

Parameter appliesTo is null or System.String.Empty.

System.ArgumentException

Parameter formula is null or System.String.Empty.

AddIconSet(String)

Adds an 'Icon Set' conditional formatting rule applied to a specified sequence of references to cell ranges.

public IconSetCondition AddIconSet(string appliesTo)
Public Function AddIconSet(appliesTo As String) As IconSetCondition
Parameters
appliesTo
System.String

The sequence of references to cell ranges over which the newly added conditional formatting rule is applied.

Returns
IconSetCondition

An IconSetCondition set to a newly created conditional formatting rule which can be used to further customize the formatting condition.

Remarks

Newly added rule is inserted at the beginning of the collection so it has the highest priority and is first applied.

Newly added rule has the same default settings as in MS Excel.

Exceptions
System.ArgumentException

Parameter appliesTo is null or System.String.Empty.

AddTopOrBottomRanked(String, Boolean, Int32)

Adds a 'Top or Bottom Ranked' conditional formatting rule applied to a specified sequence of references to cell ranges.

public TopOrBottomRankedCondition AddTopOrBottomRanked(string appliesTo, bool bottom, int rank)
Public Function AddTopOrBottomRanked(appliesTo As String, bottom As Boolean, rank As Integer) As TopOrBottomRankedCondition
Parameters
appliesTo
System.String

The sequence of references to cell ranges over which the newly added conditional formatting rule is applied.

bottom
System.Boolean

if set to true format cells with bottom ranked values; otherwise, format cells with top ranked values.

rank
System.Int32

The number of top or bottom ranked cells which are formatted.

Returns
TopOrBottomRankedCondition

A TopOrBottomRankedCondition set to a newly created conditional formatting rule which can be used to further customize the formatting condition.

Remarks

Newly added rule is inserted at the beginning of the collection so it has the highest priority and is first applied.

Exceptions
System.ArgumentException

Parameter appliesTo is null or System.String.Empty.

System.ArgumentOutOfRangeException

Parameter rank is less than or equal to zero.

AddUniqueOrDuplicate(String, Boolean)

Adds a 'Unique or Duplicate' conditional formatting rule applied to a specified sequence of references to cell ranges.

public UniqueOrDuplicateCondition AddUniqueOrDuplicate(string appliesTo, bool duplicate)
Public Function AddUniqueOrDuplicate(appliesTo As String, duplicate As Boolean) As UniqueOrDuplicateCondition
Parameters
appliesTo
System.String

The sequence of references to cell ranges over which the newly added conditional formatting rule is applied.

duplicate
System.Boolean

if set to true format cells with duplicate values; otherwise, format cells with unique values.

Returns
UniqueOrDuplicateCondition

A UniqueOrDuplicateCondition set to a newly created conditional formatting rule which can be used to customize the formatting.

Remarks

Newly added rule is inserted at the beginning of the collection so it has the highest priority and is first applied.

Exceptions
System.ArgumentException

Parameter appliesTo is null or System.String.Empty.

Calculate()

Calculates conditional formatting for all rules in this collection.

public void Calculate()
Public Sub Calculate
Remarks

This method caches intermediate results for all rules in this collection, therefore it can improve performance if the Matches(ExcelCell) method is called repeatedly on the same rule. This is useful especially for AboveOrBelowAverageCondition, TopOrBottomRankedCondition, and UniqueOrDuplicateCondition.

Clear()

Removes all elements from the ConditionalFormatRuleCollection.

public void Clear()
Public Sub Clear

GetEnumerator()

Returns an enumerator that iterates through the ConditionalFormatRuleCollection.

public IEnumerator<ConditionalFormatRule> GetEnumerator()
Public Function GetEnumerator As IEnumerator(Of ConditionalFormatRule)
Returns
System.Collections.Generic.IEnumerator<ConditionalFormatRule>

An enumerator that can be used to iterate through the ConditionalFormatRuleCollection.

Move(Int32, Int32)

Moves the ConditionalFormatRule at the specified index to a new location in the ConditionalFormatRuleCollection.

public void Move(int oldIndex, int newIndex)
Public Sub Move(oldIndex As Integer, newIndex As Integer)
Parameters
oldIndex
System.Int32

The zero-based index specifying the location of the ConditionalFormatRule to be moved.

newIndex
System.Int32

The zero-based index specifying the new location of the ConditionalFormatRule.

Exceptions
System.ArgumentOutOfRangeException

oldIndex is less than 0 or is equal to or greater than Count.

System.ArgumentOutOfRangeException

newIndex is less than 0 or is equal to or greater than Count.

Remove(ConditionalFormatRule)

Removes the specified ConditionalFormatRule from the ConditionalFormatRuleCollection.

public bool Remove(ConditionalFormatRule rule)
Public Function Remove(rule As ConditionalFormatRule) As Boolean
Parameters
rule
ConditionalFormatRule
Returns
System.Boolean

true if ConditionalFormatRule is successfully removed; otherwise, false. This method also returns false if ConditionalFormatRule was not found in the ConditionalFormatRuleCollection.

Remove(FormatCondition)

Removes a ConditionalFormatRule associated with the specified FormatCondition from the ConditionalFormatRuleCollection.

public bool Remove(FormatCondition condition)
Public Function Remove(condition As FormatCondition) As Boolean
Parameters
condition
FormatCondition
Returns
System.Boolean

true if ConditionalFormatRule associated with the specified FormatCondition is successfully removed; otherwise, false. This method also returns false if ConditionalFormatRule associated with the specified FormatCondition was not found in the ConditionalFormatRuleCollection.

RemoveAt(Int32)

Removes the ConditionalFormatRule at the specified index of the ConditionalFormatRuleCollection.

public void RemoveAt(int index)
Public Sub RemoveAt(index As Integer)
Parameters
index
System.Int32

The zero-based index of the ConditionalFormatRule to remove.

Exceptions
System.ArgumentOutOfRangeException

index is less than 0 or is equal to or greater than Count.

Implements

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

Examples