Conditional formatting

Conditional formatting is a features 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

Here is a simple example where we add a GreaterThan rule. 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.Color = Color.Green;
cf.Style.Font.Color.Color = Color.White;

Result:

Here is another example from our sample project where we add a TwoColorScale rule.

// -------------------------------------------------------------------
// TwoColorScale Conditional Formatting example
// -------------------------------------------------------------------
ExcelAddress cfAddress1 = new ExcelAddress("A2:A10");
var cfRule1 = worksheet.ConditionalFormatting.AddTwoColorScale(cfAddress1);

// Now, lets change some properties:
cfRule1.LowValue.Type = eExcelConditionalFormattingValueObjectType.Num;
cfRule1.LowValue.Value = 4;
cfRule1.LowValue.Color = ColorTranslator.FromHtml("#FFFFEB84");
cfRule1.HighValue.Type = eExcelConditionalFormattingValueObjectType.Formula;
cfRule1.HighValue.Formula = "IF($G$1=\"A</x:&'cfRule>\",1,5)";
cfRule1.StopIfTrue = true;
cfRule1.Style.Font.Bold = true;

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	Removes an item by its 0-based index
RemoveByPriority	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