Class ExcelConditionalFormattingCollection

Collection of ExcelConditionalFormattingRule. This class is providing the API for EPPlus conditional formatting.

Inheritance

System.Object

XmlHelper

ExcelConditionalFormattingCollection

Implements

System.Collections.Generic.IEnumerable<IExcelConditionalFormattingRule>

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

Assembly: EPPlus.dll

Syntax

public class ExcelConditionalFormattingCollection : XmlHelper, IEnumerable<IExcelConditionalFormattingRule>, IEnumerable

Remarks

The public methods of this class (Add[...]ConditionalFormatting) will create a ConditionalFormatting/CfRule entry in the worksheet. When this Conditional Formatting has been created changes to the properties will affect the workbook immediately.

Each type of Conditional Formatting Rule has diferente set of properties.

// Add a Three Color Scale conditional formatting
var cf = worksheet.ConditionalFormatting.AddThreeColorScale(new ExcelAddress("A1:C10"));
// Set the conditional formatting properties
cf.LowValue.Type = ExcelConditionalFormattingValueObjectType.Min;
cf.LowValue.Color = Color.White;
cf.MiddleValue.Type = ExcelConditionalFormattingValueObjectType.Percent;
cf.MiddleValue.Value = 50;
cf.MiddleValue.Color = Color.Blue;
cf.HighValue.Type = ExcelConditionalFormattingValueObjectType.Max;
cf.HighValue.Color = Color.Black;

Properties

Count

Number of validations

Declaration

public int Count { get; }

Property Value

Type	Description
System.Int32

Item[Int32]

Index operator, returns by 0-based index

Declaration

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

Parameters

Type	Name	Description
System.Int32	index

Property Value

Type	Description
IExcelConditionalFormattingRule

Methods

AddAboveAverage(ExcelAddress)

Add AboveAverage Rule

Declaration

public IExcelConditionalFormattingAverageGroup AddAboveAverage(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingAverageGroup

AddAboveOrEqualAverage(ExcelAddress)

Add AboveOrEqualAverage Rule

Declaration

public IExcelConditionalFormattingAverageGroup AddAboveOrEqualAverage(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingAverageGroup

AddAboveStdDev(ExcelAddress)

Add AboveStdDev Rule

Declaration

public IExcelConditionalFormattingStdDevGroup AddAboveStdDev(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingStdDevGroup

AddBeginsWith(ExcelAddress)

Add BeginsWith Rule

Declaration

public IExcelConditionalFormattingBeginsWith AddBeginsWith(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingBeginsWith

AddBelowAverage(ExcelAddress)

Add BelowAverage Rule

Declaration

public IExcelConditionalFormattingAverageGroup AddBelowAverage(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingAverageGroup

AddBelowOrEqualAverage(ExcelAddress)

Add BelowOrEqualAverage Rule

Declaration

public IExcelConditionalFormattingAverageGroup AddBelowOrEqualAverage(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingAverageGroup

AddBelowStdDev(ExcelAddress)

Add BelowStdDev Rule

Declaration

public IExcelConditionalFormattingStdDevGroup AddBelowStdDev(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingStdDevGroup

AddBetween(ExcelAddress)

Add Between Rule

Declaration

public IExcelConditionalFormattingBetween AddBetween(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingBetween

AddBottom(ExcelAddress)

Add Bottom Rule

Declaration

public IExcelConditionalFormattingTopBottomGroup AddBottom(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingTopBottomGroup

AddBottomPercent(ExcelAddress)

Add BottomPercent Rule

Declaration

public IExcelConditionalFormattingTopBottomGroup AddBottomPercent(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingTopBottomGroup

AddContainsBlanks(ExcelAddress)

Add ContainsBlanks Rule

Declaration

public IExcelConditionalFormattingContainsBlanks AddContainsBlanks(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingContainsBlanks

AddContainsErrors(ExcelAddress)

Add ContainsErrors Rule

Declaration

public IExcelConditionalFormattingContainsErrors AddContainsErrors(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingContainsErrors

AddContainsText(ExcelAddress)

Add ContainsText Rule

Declaration

public IExcelConditionalFormattingContainsText AddContainsText(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingContainsText

AddDatabar(ExcelAddress, Color)

Adds a databar rule

Declaration

public IExcelConditionalFormattingDataBarGroup AddDatabar(ExcelAddress Address, Color color)

Parameters

Type	Name	Description
ExcelAddress	Address
System.Drawing.Color	color

Returns

Type	Description
IExcelConditionalFormattingDataBarGroup

AddDuplicateValues(ExcelAddress)

Add DuplicateValues Rule

Declaration

public IExcelConditionalFormattingDuplicateValues AddDuplicateValues(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingDuplicateValues

AddEndsWith(ExcelAddress)

Add EndsWith Rule

Declaration

public IExcelConditionalFormattingEndsWith AddEndsWith(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingEndsWith

AddEqual(ExcelAddress)

Add Equal Rule

Declaration

public IExcelConditionalFormattingEqual AddEqual(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingEqual

AddExpression(ExcelAddress)

Add Expression Rule

Declaration

public IExcelConditionalFormattingExpression AddExpression(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingExpression

AddFiveIconSet(ExcelAddress, eExcelconditionalFormatting5IconsSetType)

Adds a FiveIconSet rule

Declaration

public IExcelConditionalFormattingFiveIconSet AddFiveIconSet(ExcelAddress Address, eExcelconditionalFormatting5IconsSetType IconSet)

Parameters

Type	Name	Description
ExcelAddress	Address
eExcelconditionalFormatting5IconsSetType	IconSet

Returns

Type	Description
IExcelConditionalFormattingFiveIconSet

AddFourIconSet(ExcelAddress, eExcelconditionalFormatting4IconsSetType)

Adds a FourIconSet rule

Declaration

public IExcelConditionalFormattingFourIconSet<eExcelconditionalFormatting4IconsSetType> AddFourIconSet(ExcelAddress Address, eExcelconditionalFormatting4IconsSetType IconSet)

Parameters

Type	Name	Description
ExcelAddress	Address
eExcelconditionalFormatting4IconsSetType	IconSet

Returns

Type	Description
IExcelConditionalFormattingFourIconSet<eExcelconditionalFormatting4IconsSetType>

AddGreaterThan(ExcelAddress)

Add GreaterThan Rule

Declaration

public IExcelConditionalFormattingGreaterThan AddGreaterThan(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingGreaterThan

AddGreaterThanOrEqual(ExcelAddress)

Add GreaterThanOrEqual Rule

Declaration

public IExcelConditionalFormattingGreaterThanOrEqual AddGreaterThanOrEqual(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingGreaterThanOrEqual

AddLast7Days(ExcelAddress)

Add Last7Days Rule

Declaration

public IExcelConditionalFormattingTimePeriodGroup AddLast7Days(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingTimePeriodGroup

AddLastMonth(ExcelAddress)

Add LastMonth Rule

Declaration

public IExcelConditionalFormattingTimePeriodGroup AddLastMonth(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingTimePeriodGroup

AddLastWeek(ExcelAddress)

Add LastWeek Rule

Declaration

public IExcelConditionalFormattingTimePeriodGroup AddLastWeek(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingTimePeriodGroup

AddLessThan(ExcelAddress)

Add LessThan Rule

Declaration

public IExcelConditionalFormattingLessThan AddLessThan(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingLessThan

AddLessThanOrEqual(ExcelAddress)

Add LessThanOrEqual Rule

Declaration

public IExcelConditionalFormattingLessThanOrEqual AddLessThanOrEqual(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingLessThanOrEqual

AddNextMonth(ExcelAddress)

Add NextMonth Rule

Declaration

public IExcelConditionalFormattingTimePeriodGroup AddNextMonth(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingTimePeriodGroup

AddNextWeek(ExcelAddress)

Add NextWeek Rule

Declaration

public IExcelConditionalFormattingTimePeriodGroup AddNextWeek(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingTimePeriodGroup

AddNotBetween(ExcelAddress)

Add NotBetween Rule

Declaration

public IExcelConditionalFormattingNotBetween AddNotBetween(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingNotBetween

AddNotContainsBlanks(ExcelAddress)

Add NotContainsBlanks Rule

Declaration

public IExcelConditionalFormattingNotContainsBlanks AddNotContainsBlanks(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingNotContainsBlanks

AddNotContainsErrors(ExcelAddress)

Add NotContainsErrors Rule

Declaration

public IExcelConditionalFormattingNotContainsErrors AddNotContainsErrors(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingNotContainsErrors

AddNotContainsText(ExcelAddress)

Add NotContainsText Rule

Declaration

public IExcelConditionalFormattingNotContainsText AddNotContainsText(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingNotContainsText

AddNotEqual(ExcelAddress)

Add NotEqual Rule

Declaration

public IExcelConditionalFormattingNotEqual AddNotEqual(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingNotEqual

AddThisMonth(ExcelAddress)

Add ThisMonth Rule

Declaration

public IExcelConditionalFormattingTimePeriodGroup AddThisMonth(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingTimePeriodGroup

AddThisWeek(ExcelAddress)

Add ThisWeek Rule

Declaration

public IExcelConditionalFormattingTimePeriodGroup AddThisWeek(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingTimePeriodGroup

AddThreeColorScale(ExcelAddress)

Add ThreeColorScale Rule

Declaration

public IExcelConditionalFormattingThreeColorScale AddThreeColorScale(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingThreeColorScale

AddThreeIconSet(ExcelAddress, eExcelconditionalFormatting3IconsSetType)

Add ThreeIconSet Rule

Declaration

public IExcelConditionalFormattingThreeIconSet<eExcelconditionalFormatting3IconsSetType> AddThreeIconSet(ExcelAddress Address, eExcelconditionalFormatting3IconsSetType IconSet)

Parameters

Type	Name	Description
ExcelAddress	Address	The address
eExcelconditionalFormatting3IconsSetType	IconSet	Type of iconset

Returns

Type	Description
IExcelConditionalFormattingThreeIconSet<eExcelconditionalFormatting3IconsSetType>

AddToday(ExcelAddress)

Add Today Rule

Declaration

public IExcelConditionalFormattingTimePeriodGroup AddToday(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingTimePeriodGroup

AddTomorrow(ExcelAddress)

Add Tomorrow Rule

Declaration

public IExcelConditionalFormattingTimePeriodGroup AddTomorrow(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingTimePeriodGroup

AddTop(ExcelAddress)

Add Top Rule

Declaration

public IExcelConditionalFormattingTopBottomGroup AddTop(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingTopBottomGroup

AddTopPercent(ExcelAddress)

Add TopPercent Rule

Declaration

public IExcelConditionalFormattingTopBottomGroup AddTopPercent(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingTopBottomGroup

AddTwoColorScale(ExcelAddress)

Add TwoColorScale Rule

Declaration

public IExcelConditionalFormattingTwoColorScale AddTwoColorScale(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingTwoColorScale

AddUniqueValues(ExcelAddress)

Add Unique Rule

Declaration

public IExcelConditionalFormattingUniqueValues AddUniqueValues(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingUniqueValues

AddYesterday(ExcelAddress)

Add Yesterday Rule

Declaration

public IExcelConditionalFormattingTimePeriodGroup AddYesterday(ExcelAddress address)

Parameters

Type	Name	Description
ExcelAddress	address

Returns

Type	Description
IExcelConditionalFormattingTimePeriodGroup

Remove(IExcelConditionalFormattingRule)

Remove a Conditional Formatting Rule by its object

Declaration

public void Remove(IExcelConditionalFormattingRule item)

Parameters

Type	Name	Description
IExcelConditionalFormattingRule	item

RemoveAll()

Removes all 'cfRule' from the collection and from the XML. This is the same as removing all the 'conditionalFormatting' nodes.

Declaration

public void RemoveAll()

RemoveAt(Int32)

Remove a Conditional Formatting Rule by its 0-based index

Declaration

public void RemoveAt(int index)

Parameters

Type	Name	Description
System.Int32	index

RemoveByPriority(Int32)

Remove a Conditional Formatting Rule by its priority

Declaration

public void RemoveByPriority(int priority)

Parameters

Type	Name	Description
System.Int32	priority

RulesByPriority(Int32)

Get a rule by its priority

Declaration

public IExcelConditionalFormattingRule RulesByPriority(int priority)

Parameters

Type	Name	Description
System.Int32	priority

Returns

Type	Description
IExcelConditionalFormattingRule

Explicit Interface Implementations

IEnumerable<IExcelConditionalFormattingRule>.GetEnumerator()

Get the 'cfRule' enumerator

Declaration

IEnumerator<IExcelConditionalFormattingRule> IEnumerable<IExcelConditionalFormattingRule>.GetEnumerator()

Returns

Type	Description
System.Collections.Generic.IEnumerator<IExcelConditionalFormattingRule>

IEnumerable.GetEnumerator()

Get the 'cfRule' enumerator

Declaration

IEnumerator IEnumerable.GetEnumerator()

Returns

Type	Description
System.Collections.IEnumerator

Implements

System.Collections.Generic.IEnumerable<T>

System.Collections.IEnumerable