Interface ISheetConditionalFormatting

The Conditional Formatting facet of WorkSheet

Namespace: IronXL.Formatting

Assembly: IronXL.dll

Syntax

public interface ISheetConditionalFormatting

ISheetConditionalFormatting manages the conditional formatting rules attached to a worksheet. It creates rules, adds them to cell regions, reads them back, and removes them, so all of a sheet's "color this cell when its value crosses a threshold" logic is reached through one object. A developer building a dashboard or a flagged report works through it to make the spreadsheet react to its own data.

A worksheet exposes it as WorkSheet.ConditionalFormatting, a get-only property, so there is no construction step. The usual sequence is two stages: build a rule, then apply it to one or more cell regions. CreateConditionalFormattingRule produces a ConditionalFormattingRule, taking either a single formula, a ComparisonOperator with one formula, or a ComparisonOperator with two formulas for between-style conditions. The returned rule carries its own font, fill, and border formatting before it is applied.

Applying and managing rules covers the rest of the surface. The AddConditionalFormatting overloads attach a rule, a pair of rules, or a List of rules to a region given as a single range string or an IEnumerable of ranges, and each returns the ConditionalFormatting that was added. The overloads that take two rules let a sheet stack a pair of conditions on the same region in one call. For inspection and cleanup, ConditionalFormattingsCount reports how many are present, GetConditionalFormattingAt fetches one by index, and RemoveConditionalFormatting deletes one by index. Create and configure the rule first, then add it to the regions it should govern, and use the count and index members afterward to audit or revise what a sheet already carries.

using IronXL;
using IronXL.Formatting;

WorkSheet sheet = WorkBook.Load("report.xlsx").DefaultWorkSheet;
ISheetConditionalFormatting formatting = sheet.ConditionalFormatting;
ConditionalFormattingRule rule =
    formatting.CreateConditionalFormattingRule(ComparisonOperator.GreaterThan, "100");
rule.FontFormatting.IsBold = true;
formatting.AddConditionalFormatting("A1:A20", rule);

The conditional formatting how-to walks through creating and applying rules, the conditional formatting example shows a worked rule end to end, and the background pattern color how-to covers the fills a rule can apply.

Properties

ConditionalFormattingsCount

Get the number of conditional formats in this sheet

Declaration

int ConditionalFormattingsCount { get; }

Property Value

Type	Description
System.Int32	The conditional formattings count.

Methods

AddConditionalFormatting(ConditionalFormatting)

Adds a copy of a ConditionalFormatting object to the sheet

Declaration

ConditionalFormatting AddConditionalFormatting(ConditionalFormatting cf)

Parameters

Type	Name	Description
ConditionalFormatting	cf	The Conditional Formatting to clone.

Returns

Type	Description
ConditionalFormatting	The new Conditional Formatting object.

Remarks

This method could be used to copy ConditionalFormatting object from one sheet to another. For example: ConditionalFormatting cf = sheet.GetConditionalFormattingAt(index); newSheet.AddConditionalFormatting(cf);

AddConditionalFormatting(IEnumerable<String>, ConditionalFormattingRule)

Add a new Conditional Formatting to the sheet.

Declaration

ConditionalFormatting AddConditionalFormatting(IEnumerable<string> regions, ConditionalFormattingRule rule)

Parameters

Type	Name	Description
System.Collections.Generic.IEnumerable<System.String>	regions	List of rectangular ranges of cells (e.g. "A1:C10") to apply conditional formatting rules
ConditionalFormattingRule	rule	The rule to apply

Returns

Type	Description
ConditionalFormatting	The newly created Conditional Formatting object

AddConditionalFormatting(IEnumerable<String>, ConditionalFormattingRule, ConditionalFormattingRule)

Add a new Conditional Formatting consisting of two rules.

Declaration

ConditionalFormatting AddConditionalFormatting(IEnumerable<string> regions, ConditionalFormattingRule rule1, ConditionalFormattingRule rule2)

Parameters

Type	Name	Description
System.Collections.Generic.IEnumerable<System.String>	regions	List of rectangular ranges of cells (e.g. "A1:C10") to apply conditional formatting rules
ConditionalFormattingRule	rule1	The first rule
ConditionalFormattingRule	rule2	The second rule

Returns

Type	Description
ConditionalFormatting	The newly created Conditional Formatting object

AddConditionalFormatting(IEnumerable<String>, List<ConditionalFormattingRule>)

Add a new Conditional Formatting Set to the sheet.

Declaration

ConditionalFormatting AddConditionalFormatting(IEnumerable<string> regions, List<ConditionalFormattingRule> cfRules)

Parameters

Type	Name	Description
System.Collections.Generic.IEnumerable<System.String>	regions	List of rectangular ranges of cells (e.g. "A1:C10") to apply conditional formatting rules
System.Collections.Generic.List<ConditionalFormattingRule>	cfRules	Set of up to three conditional formatting rules

Returns

Type	Description
ConditionalFormatting	The newly created Conditional Formatting object

AddConditionalFormatting(String, ConditionalFormattingRule)

Add a new Conditional Formatting to the sheet.

Declaration

ConditionalFormatting AddConditionalFormatting(string region, ConditionalFormattingRule rule)

Parameters

Type	Name	Description
System.String	region	Range of cells (e.g. "A1:C10") to apply conditional formatting rules
ConditionalFormattingRule	rule	The rule to apply

Returns

Type	Description
ConditionalFormatting	The newly created Conditional Formatting object

AddConditionalFormatting(String, ConditionalFormattingRule, ConditionalFormattingRule)

Add a new Conditional Formatting consisting of two rules.

Declaration

ConditionalFormatting AddConditionalFormatting(string region, ConditionalFormattingRule rule1, ConditionalFormattingRule rule2)

Parameters

Type	Name	Description
System.String	region	Range of cells (e.g. "A1:C10") to apply conditional formatting rules
ConditionalFormattingRule	rule1	The first rule
ConditionalFormattingRule	rule2	The second rule

Returns

Type	Description
ConditionalFormatting	The newly created Conditional Formatting object

AddConditionalFormatting(String, List<ConditionalFormattingRule>)

Add a new Conditional Formatting Set to the sheet.

Declaration

ConditionalFormatting AddConditionalFormatting(string region, List<ConditionalFormattingRule> cfRules)

Parameters

Type	Name	Description
System.String	region	Range of cells (e.g. "A1:C10") to apply conditional formatting rules
System.Collections.Generic.List<ConditionalFormattingRule>	cfRules	Set of up to three conditional formatting rules

Returns

Type	Description
ConditionalFormatting	The newly created Conditional Formatting object

CreateConditionalFormattingRule(ComparisonOperator, String)

Create a conditional formatting rule that Compares a cell value to a formula calculated result, using an operator

Declaration

ConditionalFormattingRule CreateConditionalFormattingRule(ComparisonOperator comparisonOperation, string formula)

Parameters

Type	Name	Description
ComparisonOperator	comparisonOperation	MUST be a constant value from ComparisonOperator except Between and NotBetween
System.String	formula	The formula to determine if the conditional formatting is applied

Returns

Type	Description
ConditionalFormattingRule	A conditional formatting rule

CreateConditionalFormattingRule(ComparisonOperator, String, String)

A factory method allowing to create a conditional formatting rule with a cell comparison operator.

The Created conditional formatting rule compares a cell value to a formula calculated result, using the specified operator. The type of the Created condition is CellValueIs

Declaration

ConditionalFormattingRule CreateConditionalFormattingRule(ComparisonOperator comparisonOperation, string formula1, string formula2)

Parameters

Type	Name	Description
ComparisonOperator	comparisonOperation	Value from ComparisonOperator enum.
System.String	formula1	Formula for the valued, Compared with the cell
System.String	formula2	The second formula (only used with Between and NotBetween operations.

Returns

Type	Description
ConditionalFormattingRule	New conditional formatting rule.

CreateConditionalFormattingRule(String)

Create a conditional formatting rule based on a Boolean formula. When the formula result is true, the cell is highlighted.

Declaration

ConditionalFormattingRule CreateConditionalFormattingRule(string formula)

Parameters

Type	Name	Description
System.String	formula	The formula to Evaluate. MUST be a Boolean function.

Returns

Type	Description
ConditionalFormattingRule	Conditional formatting rule

GetConditionalFormattingAt(Int32)

Gets Conditional Formatting object at a particular index

Declaration

ConditionalFormatting GetConditionalFormattingAt(int index)

Parameters

Type	Name	Description
System.Int32	index	0-based index of the Conditional Formatting object to fetch

Returns

Type	Description
ConditionalFormatting	Conditional Formatting object or null if not found

Exceptions

Type	Condition
System.ArgumentException	throws ArgumentException if the index is outside of the allowable range (0 ... numberOfFormats-1)

RemoveConditionalFormatting(Int32)

Removes a Conditional Formatting object by index

Declaration

void RemoveConditionalFormatting(int index)

Parameters

Type	Name	Description
System.Int32	index	0-based index of the Conditional Formatting object to remove

Exceptions

Type	Condition
System.ArgumentException	throws ArgumentException if the index is outside of the allowable range (0 ... numberOfFormats-1)