ConditionalFormatRule Class

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

Represents a conditional formatting rule.

public sealed class ConditionalFormatRule
Public NotInheritable Class ConditionalFormatRule
Inheritance:
System.Object
ConditionalFormatRule

Properties

AppliesTo

Gets or sets the sequence of references to cell ranges over which this conditional formatting rule is applied.

public string AppliesTo { get; set; }
Public Property AppliesTo As String
Property Value
System.String

The sequence of references to cell ranges over which this conditional formatting rule is applied.

Exceptions
System.ArgumentException

Parameter is null or System.String.Empty.

Condition

Gets the condition specified in this conditional formatting rule.

public FormatCondition Condition { get; }
Public ReadOnly Property Condition As FormatCondition
Property Value
FormatCondition

The condition specified in this conditional formatting rule.

StopIfTrue

Gets or sets a value indicating whether a rule with lower priority may be applied over this rule, when this rule evaluates to true.

public bool StopIfTrue { get; set; }
Public Property StopIfTrue As Boolean
Property Value
System.Boolean

true if no rules with lower priority may be applied over this rule, when this rule evaluates to true; otherwise, false.

Methods

Calculate()

Calculates conditional formatting for this rule.

public void Calculate()
Public Sub Calculate
Remarks

This method caches intermediate results for this rule, 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.

Matches(ExcelCell)

Determines whether the given cell matches this rule.

public bool Matches(ExcelCell cell)
Public Function Matches(cell As ExcelCell) As Boolean
Parameters
cell
ExcelCell

The cell to evaluate with this rule. If the given cell is outside of the range of this rule, the method always returns false.

Returns
System.Boolean

True if the given cell matches this rule, false otherwise.

Remarks

If Condition is of ColorScaleCondition, IconSetCondition, or DataBarCondition type, this method returns true for cells with numeric values, false otherwise.

Set2ColorScale()

Sets a '2-Color Scale' formatting condition to this conditional formatting rule.

public ColorScaleCondition Set2ColorScale()
Public Function Set2ColorScale As ColorScaleCondition
Returns
ColorScaleCondition

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

Remarks

Newly set condition has the same default settings as in MS Excel.

Set3ColorScale()

Sets a '3-Color Scale' formatting condition to this conditional formatting rule.

public ColorScaleCondition Set3ColorScale()
Public Function Set3ColorScale As ColorScaleCondition
Returns
ColorScaleCondition

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

Remarks

Newly set condition has the same default settings as in MS Excel.

SetAboveOrBelowAverage(Boolean)

Sets an 'Above or Below Average' formatting condition to this conditional formatting rule.

public AboveOrBelowAverageCondition SetAboveOrBelowAverage(bool below)
Public Function SetAboveOrBelowAverage(below As Boolean) As AboveOrBelowAverageCondition
Parameters
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 this conditional formatting rule which can be used to further customize the formatting condition.

SetContain(ContainConditionType)

Sets a 'Cells with Blanks' or 'Cells with No Blanks' or 'Cells with Errors' or 'Cells with No Errors' formatting condition to this conditional formatting rule.

public ContainCondition SetContain(ContainConditionType containConditionType)
Public Function SetContain(containConditionType As ContainConditionType) As ContainCondition
Parameters
containConditionType
ContainConditionType

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

Returns
ContainCondition

A ContainCondition set to this conditional formatting rule which can be used to customize the formatting.

Exceptions
System.ArgumentException

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

SetContainDate(ContainDateOperator)

Sets a 'Cells with Dates Occurring' formatting condition to this conditional formatting rule.

public ContainDateCondition SetContainDate(ContainDateOperator operator)
Public Function SetContainDate(operator As ContainDateOperator) As ContainDateCondition
Parameters
operator
ContainDateOperator

The date operator.

Returns
ContainDateCondition

A ContainDateCondition set to this conditional formatting rule which can be used to customize the formatting.

SetContainText(ContainTextOperator, String)

Sets a 'Cells with Specific Text' formatting condition to this conditional formatting rule.

public ContainTextCondition SetContainText(ContainTextOperator operator, string value)
Public Function SetContainText(operator As ContainTextOperator, value As String) As ContainTextCondition
Parameters
operator
ContainTextOperator

The textual operator.

value
System.String

The value (constant string or formula).

Returns
ContainTextCondition

A ContainTextCondition set to this conditional formatting rule which can be used to customize the formatting.

Remarks

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.ArgumentNullException

Parameter value is null.

SetContainValue(ContainValueOperator, Object)

Sets a 'Cells with Cell Value' formatting condition to this conditional formatting rule.

public ContainValueCondition SetContainValue(ContainValueOperator operator, object value)
Public Function SetContainValue(operator As ContainValueOperator, value As Object) As ContainValueCondition
Parameters
operator
ContainValueOperator

The operator (all except Between and NotBetween).

value
System.Object

The value (constant or formula).

Returns
ContainValueCondition

A ContainValueCondition set to this conditional formatting rule which can be used to customize the formatting.

Remarks

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 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.

SetContainValue(ContainValueOperator, Object, Object)

Sets a 'Cells with Cell Value' formatting condition to this conditional formatting rule.

public ContainValueCondition SetContainValue(ContainValueOperator operator, object value, object value2)
Public Function SetContainValue(operator As ContainValueOperator, value As Object, value2 As Object) As ContainValueCondition
Parameters
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 this conditional formatting rule which can be used to customize the formatting.

Remarks

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 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.

SetDataBar()

Sets a 'Data Bar' formatting condition to this conditional formatting rule.

public DataBarCondition SetDataBar()
Public Function SetDataBar As DataBarCondition
Returns
DataBarCondition

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

Remarks

Newly set condition has the same default settings as in MS Excel.

SetFormula(String)

Sets a 'Formula' formatting condition to this conditional formatting rule.

public FormulaCondition SetFormula(string formula)
Public Function SetFormula(formula As String) As FormulaCondition
Parameters
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 this conditional formatting rule which can be used to customize the formatting.

Exceptions
System.ArgumentException

Parameter formula is null or System.String.Empty.

SetIconSet()

Sets an 'Icon Set' formatting condition to this conditional formatting rule.

public IconSetCondition SetIconSet()
Public Function SetIconSet As IconSetCondition
Returns
IconSetCondition

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

Remarks

Newly set condition has the same default settings as in MS Excel.

SetTopOrBottomRanked(Boolean, Int32)

Sets a 'Top or Bottom Ranked' formatting condition to this conditional formatting rule.

public TopOrBottomRankedCondition SetTopOrBottomRanked(bool bottom, int rank)
Public Function SetTopOrBottomRanked(bottom As Boolean, rank As Integer) As TopOrBottomRankedCondition
Parameters
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 this conditional formatting rule which can be used to further customize the formatting condition.

Exceptions
System.ArgumentOutOfRangeException

Parameter rank is less than or equal to zero.

SetUniqueOrDuplicate(Boolean)

Sets a 'Unique or Duplicate' formatting condition to this conditional formatting rule.

public UniqueOrDuplicateCondition SetUniqueOrDuplicate(bool duplicate)
Public Function SetUniqueOrDuplicate(duplicate As Boolean) As UniqueOrDuplicateCondition
Parameters
duplicate
System.Boolean

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

Returns
UniqueOrDuplicateCondition

A UniqueOrDuplicateCondition set to this conditional formatting rule which can be used to customize the formatting.

ToString()

Returns a System.String that represents this ConditionalFormatRule instance.

public override string ToString()
Public Overrides Function ToString As String
Returns
System.String

A System.String that represents this ConditionalFormatRule instance.

Overrides
System.Object.ToString()
Remarks

This method should be used primarily for debugging purposes and should be considered volatile (format of its return value might change in future versions).

Examples