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.

  1. Create a new XlConditionalFormatting instance that contains formatting rules and settings.
  2. 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.
  3. 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.
  4. The default rank value used for the created formatting rule is 10. To change it, specify the XlCondFmtRuleTop10.Rank property.
  5. Specify the formatting parameters to the cells conforming to the condition.

  6. Add the newly created rule to the corresponding collection contained in the XlConditionalFormatting object by calling the Add method.
  7. 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.

XLExport_Examples - ConditionalFormatting_TopBottom