officefileapi-devexpress-dot-spreadsheet-1a4703b3.md
Represents an icon set conditional formatting rule.
Namespace : DevExpress.Spreadsheet
Assembly : DevExpress.Spreadsheet.v25.2.Core.dll
NuGet Package : DevExpress.Spreadsheet.Core
public interface IconSetConditionalFormatting :
ConditionalFormatting
Public Interface IconSetConditionalFormatting
Inherits ConditionalFormatting
The following members return IconSetConditionalFormatting objects:
The conditional formatting rule specified by the IconSetConditionalFormatting object adds a specific icon to each cell based on its value. The Worksheet.ConditionalFormattings property returns the ConditionalFormattingCollection collection that stores all conditional formatting rules specified on a worksheet. Use the methods of the ConditionalFormattingCollection object to apply (the ConditionalFormattingCollection.AddIconSetConditionalFormatting method) or remove (the ConditionalFormattingCollection.Remove method) the conditional format.
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:
Specify whether to show or hide 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:
To remove the IconSetConditionalFormatting object, use the ConditionalFormattingCollection.Remove, ConditionalFormattingCollection.RemoveAt or ConditionalFormattingCollection.Clear methods.
Dim conditionalFormattings As ConditionalFormattingCollection = worksheet.ConditionalFormattings
' Set the first threshold to the lowest value in the range of cells using the MIN() formula.
Dim minPoint As ConditionalFormattingIconSetValue = conditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.Formula, "=MIN($E$2:$E$15)", ConditionalFormattingValueOperator.GreaterOrEqual)
' Set the second threshold to 0.
Dim midPoint As ConditionalFormattingIconSetValue = conditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.Number, "0", ConditionalFormattingValueOperator.GreaterOrEqual)
' Set the third threshold to 0.01.
Dim maxPoint As ConditionalFormattingIconSetValue = 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.
Dim cfRule As IconSetConditionalFormatting = 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.
Dim cfCustomIcon As 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
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;
See Also