Version

Conditional Formatting in Infragistics Excel Engine

Purpose

This topic explains how to configure and set conditional formatting on a Worksheet using the Infragistics Excel Engine.

Required Background

The following table lists the concepts and topics required as a prerequisite to understanding this topic:

Topic Purpose

About Infragistics Excel Engine

In this section you will find information that will help you to better understand the object model and the functionalities of the Infragistics Excel Engine.

Conditional Formatting Configuration Summary

You can configure the conditional formatting of a particular Worksheet by using the many "Add" methods exposed on the ConditionalFormats collection of that worksheet. The first parameter of these "Add" methods is the string region of the Worksheet that you would like to apply the conditional format to.

Many of the conditional formats that you can add to your Worksheet have a CellFormat property that determines the way that the WorksheetCell elements should look when the condition in that conditional format holds true. For example, you can use the properties attached to this CellFormat property such as Fill and Font to determine the background and font settings of your cells, respectively.

When a conditional format is created and a CellFormat applied, there is a subset of properties that are currently supported by the Worksheet Cell. The properties that are currently honored off of the CellFormat are Fill, Border properties, FormatString, and some Font properties such as Strikethrough, Underline, Italic, Bold, and Color. Many of these can be seen from the code snippet below.

There are a few conditional formats that do not have a CellFormat property, as their visualization on the WorksheetCell behaves differently. These conditional formats are the DataBarConditionalFormat, ColorScaleConditionalFormat, and IconSetConditionalFormat.

When loading a pre-existing Workbook from Excel, the formats will be preserved when that Workbook is loaded. The same is true for when you save the Workbook out to an Excel file.

Conditional Formatting Configuration Summary Chart

The following table briefly explains the different conditional formats supported by the Worksheet element and maps them to the methods off of that element’s ConditionalFormats collection used to add them:

Conditional Format Object Method Details

AverageConditionalFormat

AddAverageCondition

Exposes properties which control the visual attributes of a WorksheetCell based on whether a cell’s value is above or below the average or standard deviation for the associated range.

BlanksConditionalFormat

AddBlanksCondition

Exposes properties which control the visual attributes of a WorksheetCell based on whether the cell’s value is not set.

ColorScaleConditionalFormat

AddColorScaleCondition

Exposes properties which control the coloring of a WorksheetCell based on the cell’s value as relative to minimum, midpoint, and maximum threshold values.

DataBarConditionalFormat

AddDataBarCondition

Exposes properties which display data bars in a WorksheetCell based on the cell’s value as relative to the associated range of values.

DateTimeConditionalFormat

AddDateTimeCondition

Exposes properties which control the visual attributes of a WorksheetCell based on whether a cell’s DateTime value falls within a given range of time.

DuplicateConditionalFormat

AddDuplicateCondition

Exposes properties which control the visual attributes of a WorksheetCell based on whether a cell’s value is unique or duplicated across the associated range.

ErrorsConditionalFormat

AddErrorsCondition

Exposes properties which control the visual attributes of a WorksheetCell based on whether the cell’s value is valid.

FormulaConditionalFormat

AddFormulaCondition

Exposes properties which control the visual attributes of a WorksheetCell based on whether the cell’s value meets the criteria defined by a Formula.

IconSetConditionalFormat

AddIconSetCondition

Exposes properties which display icons in a WorksheetCell based on the cell’s value as relative to threshold values.

NoBlanksConditionalFormat

AddNoBlanksCondition

Exposes properties which control the visual attributes of a WorksheetCell based on whether the cell’s value is set.

NoErrorsConditionalFormat

AddNoErrorsCondition

Exposes properties which control the visual attributes of a WorksheetCell based on whether the cell’s value is valid.

OperatorConditionalFormat

AddOperatorCondition

Exposes properties which control the visual attributes of a WorksheetCell based on whether the cell’s value meets the criteria defined by a logical operator.

RankConditionalFormat

AddRankCondition

Exposes properties which control the visual attributes of a WorksheetCell based on whether a cell’s value is within the top of bottom rank of values across the associated range.

TextOperatorConditionalFormat

AddTextCondition

Exposes properties which control the visual attributes of a WorksheetCell based on whether a cell’s text value meets the criteria defined by a string and a FormatConditionTextOperator value as placed in the AddTextCondition method’s parameters.

UniqueConditionalFormat

AddUniqueCondition

Exposes properties which control the visual attributes of a WorksheetCell based on whether a cell’s value is unique across the associated range.

Code Example

The following code example demonstrates usage of the conditional format objects mentioned above.

In C#:

Workbook book = new Workbook();
Worksheet sheet = book.Worksheets.Add("Sheet1");

AverageConditionalFormat avgFormat = sheet.ConditionalFormats.AddAverageCondition("A1:A10", FormatConditionAboveBelow.AboveAverage);
avgFormat.CellFormat.Font.ColorInfo = new WorkbookColorInfo(System.Drawing.Color.Red);

BlanksConditionalFormat blankFormat = sheet.ConditionalFormats.AddBlanksCondition("B1:B10");
blankFormat.CellFormat.Fill = CellFill.CreateSolidFill(System.Drawing.Color.Gray);

ColorScaleConditionalFormat colorFormat = sheet.ConditionalFormats.AddColorScaleCondition("C1:C10", ColorScaleType.ThreeColor);
colorFormat.MinimumThreshold.FormatColor = new WorkbookColorInfo(System.Drawing.Color.Green);
colorFormat.MidpointThreshold.FormatColor = new WorkbookColorInfo(System.Drawing.Color.Orange);
colorFormat.MaximumThreshold.FormatColor = new WorkbookColorInfo(System.Drawing.Color.Red);

DataBarConditionalFormat dataBarFormat = sheet.ConditionalFormats.AddDataBarCondition("D1:D10");

DateTimeConditionalFormat dateTimeFormat = sheet.ConditionalFormats.AddDateTimeCondition("E1:E10", FormatConditionTimePeriod.NextWeek);
dateTimeFormat.CellFormat.Font.ColorInfo = new WorkbookColorInfo(System.Drawing.Color.Blue);

DuplicateConditionalFormat duplicateFormat = sheet.ConditionalFormats.AddDuplicateCondition("F1:F10");
duplicateFormat.CellFormat.Font.ColorInfo = new WorkbookColorInfo(System.Drawing.Color.Magenta);

FormulaConditionalFormat formulaFormat = sheet.ConditionalFormats.AddFormulaCondition("H1:H10", "=H1>2");
formulaFormat.CellFormat.Font.ColorInfo = new WorkbookColorInfo(System.Drawing.Color.Blue);

IconSetConditionalFormat iconFormat = sheet.ConditionalFormats.AddIconSetCondition("I1:I10", FormatConditionIconSet.IconSet3Arrows);

NoBlanksConditionalFormat noBlanksFormat = sheet.ConditionalFormats.AddNoBlanksCondition("J1:J10");
noBlanksFormat.CellFormat.Fill = CellFill.CreateSolidFill(System.Drawing.Color.LightGray);

OperatorConditionalFormat operatorFormat = sheet.ConditionalFormats.AddOperatorCondition("L1:L10", FormatConditionOperator.Greater);
operatorFormat.SetOperand1(500);
operatorFormat.CellFormat.Font.ColorInfo = new WorkbookColorInfo(System.Drawing.Color.Red);

RankConditionalFormat rankFormat = sheet.ConditionalFormats.AddRankCondition("M1:M10", FormatConditionTopBottom.Top, 5);
rankFormat.CellFormat.Font.ColorInfo = new WorkbookColorInfo(System.Drawing.Color.Blue);

TextOperatorConditionalFormat textFormat = sheet.ConditionalFormats.AddTextCondition("N1:N10", "A", FormatConditionTextOperator.Contains);
textFormat.CellFormat.Font.ColorInfo = new WorkbookColorInfo(System.Drawing.Color.Magenta);

UniqueConditionalFormat uniqueFormat = sheet.ConditionalFormats.AddUniqueCondition("O1:O10");
uniqueFormat.CellFormat.Font.ColorInfo = new WorkbookColorInfo(System.Drawing.Color.Red);

In Visual Basic:

Dim book As New Workbook()
Dim sheet As Worksheet = book.Worksheets.Add("Sheet1")

Dim avgFormat As AverageConditionalFormat = sheet.ConditionalFormats.AddAverageCondition("A1:A10", FormatConditionAboveBelow.AboveAverage)
avgFormat.CellFormat.Font.ColorInfo = New WorkbookColorInfo(System.Drawing.Color.Red)

Dim blankFormat As BlanksConditionalFormat = sheet.ConditionalFormats.AddBlanksCondition("B1:B10")
blankFormat.CellFormat.Fill = CellFill.CreateSolidFill(System.Drawing.Color.Gray)

Dim colorFormat As ColorScaleConditionalFormat = sheet.ConditionalFormats.AddColorScaleCondition("C1:C10", ColorScaleType.ThreeColor)
colorFormat.MinimumThreshold.FormatColor = New WorkbookColorInfo(System.Drawing.Color.Green)
colorFormat.MidpointThreshold.FormatColor = New WorkbookColorInfo(System.Drawing.Color.Orange)
colorFormat.MaximumThreshold.FormatColor = New WorkbookColorInfo(System.Drawing.Color.Red)

Dim dataBarFormat As DataBarConditionalFormat = sheet.ConditionalFormats.AddDataBarCondition("D1:D10")

Dim dateTimeFormat As DateTimeConditionalFormat = sheet.ConditionalFormats.AddDateTimeCondition("E1:E10", FormatConditionTimePeriod.NextWeek)
dateTimeFormat.CellFormat.Font.ColorInfo = New WorkbookColorInfo(System.Drawing.Color.Blue)

Dim duplicateFormat As DuplicateConditionalFormat = sheet.ConditionalFormats.AddDuplicateCondition("F1:F10")
duplicateFormat.CellFormat.Font.ColorInfo = New WorkbookColorInfo(System.Drawing.Color.Magenta)

Dim formulaFormat As FormulaConditionalFormat = sheet.ConditionalFormats.AddFormulaCondition("H1:H10", "=H1>2")
formulaFormat.CellFormat.Font.ColorInfo = New WorkbookColorInfo(System.Drawing.Color.Blue)

Dim iconFormat As IconSetConditionalFormat = sheet.ConditionalFormats.AddIconSetCondition("I1:I10", FormatConditionIconSet.IconSet3Arrows)

Dim noBlanksFormat As NoBlanksConditionalFormat = sheet.ConditionalFormats.AddNoBlanksCondition("J1:J10")
noBlanksFormat.CellFormat.Fill = CellFill.CreateSolidFill(System.Drawing.Color.LightGray)

Dim operatorFormat As OperatorConditionalFormat = sheet.ConditionalFormats.AddOperatorCondition("L1:L10", FormatConditionOperator.Greater)
operatorFormat.SetOperand1(500)
operatorFormat.CellFormat.Font.ColorInfo = New WorkbookColorInfo(System.Drawing.Color.Red)

Dim rankFormat As RankConditionalFormat = sheet.ConditionalFormats.AddRankCondition("M1:M10", FormatConditionTopBottom.Top, 5)
rankFormat.CellFormat.Font.ColorInfo = New WorkbookColorInfo(System.Drawing.Color.Blue)

Dim textFormat As TextOperatorConditionalFormat = sheet.ConditionalFormats.AddTextCondition("N1:N10", "A", FormatConditionTextOperator.Contains)
textFormat.CellFormat.Font.ColorInfo = New WorkbookColorInfo(System.Drawing.Color.Magenta)

Dim uniqueFormat As UniqueConditionalFormat = sheet.ConditionalFormats.AddUniqueCondition("O1:O10")
uniqueFormat.CellFormat.Font.ColorInfo = New WorkbookColorInfo(System.Drawing.Color.Red)

The following topics provide additional information related to this topic.

Topic Purpose

This topic explains how to show or hide the gridlines and set their color.

This topic explains how to show or hide the column and row headers.

This topic explains how to prevent the user from editing the cell values in a worksheet.