IXlSheet.ConditionalFormattings Property

Provides access to the collection of conditional formatting rules in a worksheet.

Namespace: DevExpress.Export.Xl

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

NuGet Package: DevExpress.Printing.Core

Declaration

C#

IList<XlConditionalFormatting> ConditionalFormattings { get; }

VB.NET

ReadOnly Property ConditionalFormattings As IList(Of XlConditionalFormatting)

Property Value

Type	Description
IList<XlConditionalFormatting>	A collection of XlConditionalFormatting objects which specify worksheet ranges and conditional formatting rules applied to them.

Remarks

A conditional format is a format, such as cell background color, border line style or font color, that can be automatically applied to cells whose values meet a certain condition specified by a conditional formatting rule. The ConditionalFormattings property provides access to the collection of all conditional formatting rules specified in a worksheet.

The example below demonstrates how to apply color scale conditional formatting rules to worksheet ranges. Cells affected by these rules are shaded with gradations of two or three colors that correspond to minimum, midpoint, and maximum threshold values.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/excel-export-api-examples

ConditionalFormattingActions.cs

// Create an instance of the XlConditionalFormatting class. 
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify cell ranges to which the conditional formatting rule should be applied (A1:A11 and C1:C11).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 0, 10)); 
formatting.Ranges.Add(XlCellRange.FromLTRB(2, 0, 2, 10));
// Create the default three-color scale rule to differentiate low, medium and high values in cell ranges.
XlCondFmtRuleColorScale rule = new XlCondFmtRuleColorScale();
formatting.Rules.Add(rule);
// Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);

// Create an instance of the XlConditionalFormatting class. 
formatting = new XlConditionalFormatting();
// Specify cell ranges to which the conditional formatting rule should be applied (B1:B11 and D1:D11).
formatting.Ranges.Add(XlCellRange.FromLTRB(1, 0, 1, 10));
formatting.Ranges.Add(XlCellRange.FromLTRB(3, 0, 3, 10));
// Create the two-color scale rule to differentiate low and high values in cell ranges. 
rule = new XlCondFmtRuleColorScale();
rule.ColorScaleType = XlCondFmtColorScaleType.ColorScale2;
// Set a color corresponding to the minimum value in the cell range.
rule.MinColor = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
// Set a color corresponding to the maximum value in the cell range.
rule.MaxColor = XlColor.FromTheme(XlThemeColor.Accent1, 0.5);
formatting.Rules.Add(rule);
// Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);

ConditionalFormattingActions.vb

' Create an instance of the XlConditionalFormatting class. 
Dim formatting As New XlConditionalFormatting()
' Specify cell ranges to which the conditional formatting rule should be applied (A1:A11 and C1:C11).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 0, 10))
formatting.Ranges.Add(XlCellRange.FromLTRB(2, 0, 2, 10))
' Create the default three-color scale rule to differentiate low, medium and high values in cell ranges.
Dim rule As New XlCondFmtRuleColorScale()
formatting.Rules.Add(rule)
' Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting)

' Create an instance of the XlConditionalFormatting class. 
formatting = New XlConditionalFormatting()
' Specify cell ranges to which the conditional formatting rule should be applied (B1:B11 and D1:D11).
formatting.Ranges.Add(XlCellRange.FromLTRB(1, 0, 1, 10))
formatting.Ranges.Add(XlCellRange.FromLTRB(3, 0, 3, 10))
' Create the two-color scale rule to differentiate low and high values in cell ranges. 
rule = New XlCondFmtRuleColorScale()
rule.ColorScaleType = XlCondFmtColorScaleType.ColorScale2
' Set a color corresponding to the minimum value in the cell range.
rule.MinColor = XlColor.FromTheme(XlThemeColor.Light1, 0.0)
' Set a color corresponding to the maximum value in the cell range.
rule.MaxColor = XlColor.FromTheme(XlThemeColor.Accent1, 0.5)
formatting.Rules.Add(rule)
' Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting)

For more examples on how to apply conditional formatting rules of different types to cell ranges in a worksheet, refer to the Conditional Formatting section.