Spread for WPF allows you to apply conditional formatting rules to worksheet cells. This feature enhances data visualization and helps you analyze worksheets. To apply conditional formatting rules to a worksheet, you can use the properties and methods of the IFormatConditions interface.
Here are some basic conditions regarding the rules for the conditional formatting:
There are multiple types of conditional rules. The following sections outline these rules along with their explanations.
Color scales rule is useful to represent values in a range of cells as high, medium, or low using different shades. This rule uses a sliding color scale and gives you the option to use two colors (2-scale rule) or three colors (3-scale rule) in the scale. For example, consider a green, yellow, and red color scale where you can specify higher values with green color, middle values with yellow color, and lower values with red color.
To apply a color scale rule, you can use the AddColorScale method of the IFormatConditions interface. When you define a color scale rule, you can specify the format type using the Numeric field of the CfValueType enumeration and the color of the format using the IsKnownColor method of the Color structure along with the KnownColor enumeration.
The following code sample creates a color scale rule.
Data bars rule displays a bar in a cell based on the cell value. The length of the data bar matches the size of the data that is related to other data in the worksheet. The larger the value in the cell, the longer the bar.
To apply a data bar rule, you can use the AddDatabar method of the IFormatConditions interface. When defining a data bar rule, you can specify the color of the data bar using the FromArgb method of the Color structure and the color type using the DatabarNegativeColorType enumeration. To set the axis position, you can use the AxisPosition property of the IDatabar interface.
The following code sample creates a data bar rule.
Highlighting rules can be applied in a worksheet to emphasize relevant information. You can highlight data by either applying a predefined style from the following list of data highlighting rules or creating a custom highlight style as per your specific preferences.
You can highlight data which is:
To apply highlighting rules in a worksheet, you can set any one of the above condition by using the AddUniqueValues method of the IFormatConditions interface. Next, you need to enter a value or formula against which each cell is compared and then the formatting is applied based on the cell data that satisfies the specified criteria.
The following code sample creates a highlighting cell rule.
Icon set rule displays custom icons when the value of a cell is greater than, equal to, or less than the specified value.
To apply icon set rule in a worksheet, you can use the AddIconSetCondition method of the IFormatConditions interface. You can specify the icon set type by using the IconSetType enumeration and then use the methods and properties of the IIconCriteria interface to specify the criteria for applying the icon set rule.
The following code sample creates an icon set rule.
Top/Bottom rules apply formatting to cells whose values are within the top or bottom 10 percent. The Top10 rule uses the TopBottom enumeration to specify the top or bottom values. You can also change the percentage using the Rank property of the ITop10 interface. Additionally, the average rule checks for values that are either above the average value or below the average value. This rule applies formatting to the greater or lesser average value of the entire range.
To apply top, bottom or average rule, you can use the AddTop10 method and the AddAboveAverage method of the IFormatConditions interface.
The following code sample creates a rule to identify the top 50 percent of the values.
