-
Notifications
You must be signed in to change notification settings - Fork 279
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.
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 are some more examples of various types of Conditional Formatting.
Here are some examples and info only applicable from epplus 7.0 forward:
- Custom Iconsets Example
- Advanced Color Options Example
- Priority Example
- Order In the worksheet on Load
- Specific-Text-Formulas
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...
}
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 |
More extensive examples are available in our sample projects, see Sample 3.2-C# and Sample 3.2-VB
EPPlus Software AB - https://epplussoftware.com
- What is new in EPPlus 5+
- Breaking Changes in EPPlus 5
- Breaking Changes in EPPlus 6
- Breaking Changes in EPPlus 7
- Addressing a worksheet
- Dimension/Used range
- Copying ranges/sheets
- Insert/Delete
- Filling ranges
- Sorting ranges
- Taking and skipping columns/rows
- Data validation
- Comments
- Freeze and Split Panes
- Header and Footer
- Autofit columns
- Grouping and Ungrouping Rows and Columns
- Formatting and styling
- Conditional formatting
- Using Themes
- Working with custom named table- or slicer- styles