Use the Excel Export API to Format Cells Using Icon Sets
- 6 minutes to read
The following example describes how to apply an icon set 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 new XlCondFmtRuleIconSet object that represents the new formatting rule.
Specify the icon set that will be used in the formatting rule. You can use one of built-in icon sets as is, or create a custom icon set that contains images from multiple built-in collections.
- To use one of the built-in icon sets, set the object’s XlCondFmtRuleIconSet.IconSetType property to the corresponding XlCondFmtIconSetType enumeration value.
- To create a custom icon set, create custom icons first. To do that, add a new XlCondFmtCustomIcon object that stores an icon from the built-in set (the required icon set and icon index must be passed to the XlCondFmtCustomIcon object’s constructor method). Then, add your custom icons to the corresponding collection, accessible through the XlCondFmtRuleIconSet.CustomIcons property. Note that you have to add at least three icons. Otherwise, an exception will be thrown.
- 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 rule should be applied (A1:A11).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 0, 10));
// Create the rule to apply a specific icon
// from the "3 Arrows" icon set to each cell in the range based on its value.
XlCondFmtRuleIconSet rule = new XlCondFmtRuleIconSet();
rule.IconSetType = XlCondFmtIconSetType.Arrows3;
// Set the rule priority.
rule.Priority = 1;
formatting.Rules.Add(rule);
// Add the specified format options
// to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
// Create an instance of the XlConditionalFormatting class.
formatting = new XlConditionalFormatting();
// Specify the cell range
// to which the conditional formatting rule should be applied (B1:B11).
formatting.Ranges.Add(XlCellRange.FromLTRB(1, 0, 1, 10));
// Create the rule to apply a specific icon
// from the "3 Flags" icon set to each cell in the range based on its value.
rule = new XlCondFmtRuleIconSet();
rule.IconSetType = XlCondFmtIconSetType.Flags3;
// Set the rule priority.
rule.Priority = 2;
formatting.Rules.Add(rule);
// Add the specified format options
// to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
// Create an instance of the XlConditionalFormatting class.
formatting = new XlConditionalFormatting();
// Specify the cell range
// to which the conditional formatting rule should be applied (C1:C11).
formatting.Ranges.Add(XlCellRange.FromLTRB(2, 0, 2, 10));
// Create the rule to apply a specific icon
// from the "5 Ratings" icon set to each cell in the range based on its value.
rule = new XlCondFmtRuleIconSet();
rule.IconSetType = XlCondFmtIconSetType.Rating5;
// Hide values of cells to which the rule is applied.
rule.ShowValues = false;
// Set the rule priority.
rule.Priority = 3;
formatting.Rules.Add(rule);
// Add the specified format options
// to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
// Create an instance of the XlConditionalFormatting class.
formatting = new XlConditionalFormatting();
// Specify the cell range
// to which the conditional formatting rule should be applied (D1:D11).
formatting.Ranges.Add(XlCellRange.FromLTRB(3, 0, 3, 10));
// Create the rule to apply a specific icon
// from the "4 Traffic Lights" icon set to each cell in the range based on its value.
rule = new XlCondFmtRuleIconSet();
rule.IconSetType = XlCondFmtIconSetType.TrafficLights4;
// Reverse the icon order.
rule.Reverse = true;
// Set the rule priority.
rule.Priority = 4;
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 of the code execution.
