IconSetConditionalFormatting Interface

Represents an icon set conditional formatting rule.

Namespace: DevExpress.Spreadsheet

Assembly: DevExpress.Spreadsheet.v24.1.Core.dll

NuGet Package: DevExpress.Spreadsheet.Core

Declaration

C#

public interface IconSetConditionalFormatting :
    ConditionalFormatting

VB.NET

Public Interface IconSetConditionalFormatting
    Inherits ConditionalFormatting

Related API Members

The following members return IconSetConditionalFormatting objects:

• ConditionalFormattingCollection.AddIconSetConditionalFormatting(CellRange, IconSetType, ConditionalFormattingIconSetValue[])

Remarks

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.

Example

This example demonstrates how to apply an icon set conditional formatting rule.

1. 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.

2. 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.
3. 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:

1. Set the IconSetConditionalFormatting.IsCustom property to true to indicate that the custom icon style is used.
2. 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.
3. 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.

View Example

ConditionalFormatting.vb

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

ConditionalFormatting.cs

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;