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