Use the Excel Export API to Format Top or Bottom Ranked Values
- 3 minutes to read
The following example demonstrates how to apply a “Top/Bottom N” conditional formatting rule.
- Create a new XlConditionalFormatting instance that contains formatting rules and settings.
- Specify the cell range to which the formatting is going to be applied. To do that, add the target range to the ranges collection, accessible through the XlConditionalFormatting.Ranges property.
- Create a new XlCondFmtRuleTop10 object that represents the new formatting rule. By default, the created formatting rule will identify top N values. To create a rule to identify bottom N values, set the object’s XlCondFmtRuleTop10.Bottom property to true.
- The default rank value used for the created formatting rule is 10. To change it, specify the XlCondFmtRuleTop10.Rank property.
Specify the formatting parameters to the cells conforming to the condition.
- To use one of the built-in cell styles, set the XlCondFmtRuleWithFormatting.Formatting property to the corresponding XlCellFormatting enumeration value.
- To apply a custom background color, use the XlFormatting.Fill property or use one of the static XlFill methods as a XlCondFmtRuleWithFormatting.Formatting property value. The XlFormatting.Font property allows you to set the desired font parameters (color, size, etc.).
- Add the newly created rule to the corresponding collection contained in the XlConditionalFormatting object by calling the Add method.
- To activate the created conditional formatting rule, add the object created in step 1 to the worksheet collection of conditional formatting rules. The collection can be accessed through the IXlSheet.ConditionalFormattings property.
// Create an instance of the XlConditionalFormatting class.
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify the cell range
// to which the conditional formatting rules should be applied (A1:D10).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 3, 9));
// Create the rule to identify bottom 10 values in the cell range.
XlCondFmtRuleTop10 rule = new XlCondFmtRuleTop10();
rule.Bottom = true;
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Bad;
formatting.Rules.Add(rule);
// Create the rule to identify top 10 values in the cell range.
rule = new XlCondFmtRuleTop10();
// Specify formatting settings to be applied to cells if the condition is true.
rule.Formatting = XlCellFormatting.Good;
formatting.Rules.Add(rule);
// Add the specified format options
// to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
The image below illustrates the result. The cells with the top 10 values are formatted using the “Good” cell style; top 10 bottom values are formatted using the “Bad” cell style.
