ConditionalFormattingCollection.AddIconSetConditionalFormatting(CellRange, IconSetType, ConditionalFormattingIconSetValue[]) Method
Applies an icon set conditional formatting rule.
Namespace: DevExpress.Spreadsheet
Assembly: DevExpress.Spreadsheet.v24.1.Core.dll
NuGet Package: DevExpress.Spreadsheet.Core
Declaration
IconSetConditionalFormatting AddIconSetConditionalFormatting(
CellRange range,
IconSetType iconSet,
ConditionalFormattingIconSetValue[] points
)Parameters
| Name | Type | Description |
|---|---|---|
| range | CellRange | A CellRange object that specifies a range of cells to which the conditional formatting rule is applied. |
| iconSet | IconSetType | One of the IconSetType enumeration values which determines an icon set to be displayed. |
| points | ConditionalFormattingIconSetValue[] | An array of ConditionalFormattingIconSetValue values that specifies threshold values. |
Returns
| Type | Description |
|---|---|
| IconSetConditionalFormatting | A IconSetConditionalFormatting object that represents the icon set conditional format. |
Example
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.AddIconSetConditionalFormattingmethod. 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;
Related GitHub Examples
The following code snippets (auto-collected from DevExpress Examples) contain references to the AddIconSetConditionalFormatting(CellRange, IconSetType, ConditionalFormattingIconSetValue[]) method.
Note
The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.