How to: Format Cells Using Icon Sets
- 4 minutes to read
This example demonstrates how to apply an icon set conditional formatting rule.
- First of all, specify threshold values separating data categories, each of which will be represented by its own icon from the icon set. To do this, use the ConditionalFormattingCollection.CreateIconSetValue method that creates an instance of the ConditionalFormattingIconSetValue object. This object inherits from the ConditionalFormattingValue base interface and provides access to threshold values and their types. The type of the threshold value is determined by one of the ConditionalFormattingValueType enumeration values and can be a number, percent, formula, or percentile. The third parameter in the ConditionalFormattingCollection.CreateIconSetValue method defines a relational operator specified by one of the ConditionalFormattingValueOperator enumeration values.
To apply a conditional formatting rule represented by the IconSetConditionalFormatting object, access the collection of conditional formats from the Worksheet.ConditionalFormattings property and call the ConditionalFormattingCollection.AddIconSetConditionalFormatting method. Pass the following parameters:
- A CellRange object that defines a range of cells to which the rule is applied.
- An icon set to be displayed, determined by one of the IconSetType enumeration values.
- An array of threshold values specified by the ConditionalFormattingIconSetValue object.
- Specify whether to show or hide the values of cells to which the conditional formatting rule is applied using the IconSetConditionalFormatting.ShowValue property.
To specify the custom icon style for the created rule, do the following:
- Set the IconSetConditionalFormatting.IsCustom property to true to indicate that the custom icon style is used.
- Initialize an instance of the ConditionalFormattingCustomIcon structure using the default constructor. Use the ConditionalFormattingCustomIcon.IconSet and ConditionalFormattingCustomIcon.IconIndex properties of the ConditionalFormattingCustomIcon object to specify the icon set from which you wish to get the icon and the index of the desired icon in the set, respectively.
- Set the custom icon via the IconSetConditionalFormatting.SetCustomIcon method with the following parameters: the position in the initial icon set at which the custom icon will be added, and the custom icon specified by the ConditionalFormattingCustomIcon object.
To remove the IconSetConditionalFormatting object, use the ConditionalFormattingCollection.Remove, ConditionalFormattingCollection.RemoveAt or ConditionalFormattingCollection.Clear methods.
ConditionalFormattingCollection conditionalFormattings = worksheet.ConditionalFormattings;
// Set the first threshold to the lowest value in the range of cells using the MIN() formula.
ConditionalFormattingIconSetValue minPoint = conditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.Formula, "=MIN($E$2:$E$15)", ConditionalFormattingValueOperator.GreaterOrEqual);
// Set the second threshold to 0.
ConditionalFormattingIconSetValue midPoint = conditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.Number, "0", ConditionalFormattingValueOperator.GreaterOrEqual);
// Set the third threshold to 0.01.
ConditionalFormattingIconSetValue maxPoint = conditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.Number, "0.01", ConditionalFormattingValueOperator.GreaterOrEqual);
// Create the rule to apply a specific icon from the three arrow icon set to each cell in the range E2:E15 based on its value.
IconSetConditionalFormatting cfRule = conditionalFormattings.AddIconSetConditionalFormatting(worksheet.Range["$E$2:$E$15"], IconSetType.Arrows3, new ConditionalFormattingIconSetValue[] { minPoint, midPoint, maxPoint });
// Specify the custom icon to be displayed if the second condition is true.
// To do this, set the IconSetConditionalFormatting.IsCustom property to true, which is false by default.
cfRule.IsCustom = true;
// Initialize the ConditionalFormattingCustomIcon object.
ConditionalFormattingCustomIcon cfCustomIcon = new ConditionalFormattingCustomIcon();
// Specify the icon set where you wish to get the icon.
cfCustomIcon.IconSet = IconSetType.TrafficLights13;
// Specify the index of the desired icon in the set.
cfCustomIcon.IconIndex = 1;
// Add the custom icon at the specified position in the initial icon set.
cfRule.SetCustomIcon(1, cfCustomIcon);
// Hide values of cells to which the rule is applied.
cfRule.ShowValue = false;
The image below shows the result (the workbook is opened in Microsoft® Excel®). The icons applied allow you to identify upward and downward cost trends.
