Conditional formatting

Conditional formatting is a feature that sets various styling on cells/ranges based on their values. It is accessible via the ConditionalFormatting property of the ExcelWorksheet class or via ranges. EPPlus supports 45 different types of conditional formatting and provides a strongly typed interface for each type.

Add conditional formatting

On the ConditionalFormatting property of worksheets/ranges you will find a number of methods starting with "Add" followed by the type of Conditional formatting you want to use.

Here is a simple example where we add a GreaterThan rule, using the ConditionalFormatting.AddGreaterThan() method. Cells with a higher value than 1 will get green background with white font color.

sheet.Cells["A1"].Value = 1;
sheet.Cells["A2"].Value = 2;
sheet.Cells["A3"].Value = 3;

var cf = sheet.ConditionalFormatting.AddGreaterThan(sheet.Cells["A1:A3"]);
// alternatively:
//var cf = sheet.Cells["A1:A3"].ConditionalFormatting.AddGreaterThan();
cf.Formula = "1";
cf.Style.Fill.BackgroundColor.SetColor(Color.Green);
cf.Style.Font.Color.SetColor(Color.White);

Result:

Here is another example where we add a ThreeColorScale rule. Let's start with how the result will look like:

/Images/CondFormatting2a.png

Here is how this rule will look like in Excel:

/Images/CondFormatting3.png

And here is the code to achieve it:

// Add TwoColorScale conditional formatting to visualize temperatures
var cfRule = sheet.ConditionalFormatting.AddThreeColorScale(sheet.Cells["B2:G11"]);
cfRule.LowValue.Color = ColorTranslator.FromHtml("#FF63BE7B");
cfRule.MiddleValue.Color = ColorTranslator.FromHtml("#FFFFEB84");
cfRule.MiddleValue.Type = eExcelConditionalFormattingValueObjectType.Percentile;
cfRule.MiddleValue.Value = 50;
cfRule.HighValue.Color = ColorTranslator.FromHtml("#FFF8696B");

Read/edit conditional formatting

To get a the specific typed conditional formatting rule from the ConditionalFormatting collection use the As property of the base class. You can then change the values of the properties of casted object.

Example:

if(worksheet.ConditionalFormatting[0].Type==eExcelConditionalFormattingRuleType.ThreeIconSet)
{
    var iconRule = worksheet.ConditionalFormatting[0].As.ThreeIconSet; //Type cast the rule as an iconset rule.    
    //Do something with the iconRule...
}
if (worksheet.ConditionalFormatting[1].Type == eExcelConditionalFormattingRuleType.DataBar)
{
    var dataBarRule = worksheet.ConditionalFormatting[1].As.DataBar; //Type cast the rule as an iconset rule.
    //Do something with the databarRule...
}

Remove conditional formatting

Use the following methods which are available on the ConditionalFormatting property on worksheets or ranges.

Method	Description
Remove(IExcelConditionalFormattingRule item)	Removes the item from the collection
RemoveAll	Removes all items from the collection
RemoveAt(int index)	Removes an item by its 0-based index
RemoveByPriority(int priority)	Removes a conditional formatting by its priority

See also

More extensive examples are available in our sample projects, seesample 11-.NET Framework and sample 11-.NET Core