Conditional Formatting in WPF Spreadsheet (SfSpreadsheet)

23 Jul 20266 minutes to read

This section explains how to apply conditional formatting rules programmatically at run time in SfSpreadsheet.

In SfSpreadsheet, to apply a conditional format to a cell or range of cells, add an IConditionalFormat to that range by using the AddCondition method.

var worksheet = spreadsheet.Workbook.Worksheets[0];
IConditionalFormats condition = worksheet.Range["A1"].ConditionalFormats;
IConditionalFormat condition1 = condition.AddCondition();

Highlight Cell Rules

Based on Cell Value

To format cells based on a cell value, set the format type to CellValue and configure options such as the formula, operator, and background color. Then, invalidate the cells to refresh the view.

var worksheet = spreadsheet.Workbook.Worksheets[0];
IConditionalFormats condition = worksheet.Range["A1:A100"].ConditionalFormats;
IConditionalFormat condition1 = condition.AddCondition();
condition1.FormatType = ExcelCFType.CellValue;
condition1.Operator = ExcelComparisonOperator.Greater;
condition1.FirstFormula = "10";
condition1.BackColor = ExcelKnownColors.Light_orange;
spreadsheet.ActiveGrid.InvalidateCell(GridRangeInfo.Col(1));

Based on Formula or Cell References

To format cells based on a formula or cell reference, set the format type to Formula and configure options such as the formula and background color. Then, invalidate the cells to refresh the view.

var worksheet = spreadsheet.Workbook.Worksheets[0];
IConditionalFormats condition = worksheet.Range["A1:A100"].ConditionalFormats;
IConditionalFormat condition1 = condition.AddCondition();
condition1.FormatType = ExcelCFType.Formula;
condition1.FirstFormula = "=(B1+B2)>50";
condition1.BackColor = ExcelKnownColors.Brown;
spreadsheet.ActiveGrid.InvalidateCell(GridRangeInfo.Col(1));

Based on Specific Text

To format cells based on a specified text, set the format type to SpecificText and configure options such as the text, operator, and background color. Then, invalidate the cells to refresh the view.

var worksheet = spreadsheet.Workbook.Worksheets[0];
IConditionalFormats condition = worksheet.Range["A1:A100"].ConditionalFormats;
IConditionalFormat condition1 = condition.AddCondition();
condition1.FormatType = ExcelCFType.SpecificText;
condition1.Text = "SYNC";
condition1.Operator = ExcelComparisonOperator.ContainsText;
condition1.BackColor = ExcelKnownColors.Light_orange;
spreadsheet.ActiveGrid.InvalidateCell(GridRangeInfo.Col(1));

Based on Time Period

To format cells based on a time period, set the format type to TimePeriod and configure the time period type and background color. The Operator property is not required for TimePeriod. Then, invalidate the cells to refresh the view.

var worksheet = spreadsheet.Workbook.Worksheets[0];
IConditionalFormats condition = worksheet.Range["A1:A100"].ConditionalFormats;
IConditionalFormat condition1 = condition.AddCondition();
condition1.FormatType = ExcelCFType.TimePeriod;
condition1.TimePeriodType = CFTimePeriods.Today;
condition1.BackColor = ExcelKnownColors.Light_orange;
spreadsheet.ActiveGrid.InvalidateCell(GridRangeInfo.Col(1));

Sample output

WPF Spreadsheet Conditional Formatting with Time Period

Data Bars

To apply a conditional format based on data bars, set the format type to DataBar and configure the associated properties such as the bar color, MinPoint, and MaxPoint. Then, invalidate the cells to update the view.

var worksheet = spreadsheet.Workbook.Worksheets[0];
var conditionalFormats = worksheet.Range["B1:B100"].ConditionalFormats;
var conditionalFormat = conditionalFormats.AddCondition();
conditionalFormat.FormatType = ExcelCFType.DataBar;
conditionalFormat.DataBar.BarColor = Color.FromArgb(255, 214, 0, 123);
conditionalFormat.DataBar.MinPoint.Type = ConditionValueType.LowestValue;
conditionalFormat.DataBar.MaxPoint.Type = ConditionValueType.HighestValue;
spreadsheet.ActiveGrid.InvalidateCell(GridRangeInfo.Col(2));

Sample output

WPF Spreadsheet Conditional Formatting with Data Bars

Color Scales

To apply a conditional format based on color scales, set the format type to ColorScale and configure the associated properties such as the condition count and color criteria. Then, invalidate the cells to update the view.

var worksheet = spreadsheet.Workbook.Worksheets[0];
var conditionalFormats = worksheet.Range["C2:C100"].ConditionalFormats;
var conditionalFormat = conditionalFormats.AddCondition();
conditionalFormat.FormatType = ExcelCFType.ColorScale;
conditionalFormat.ColorScale.SetConditionCount(2);
conditionalFormat.ColorScale.Criteria[0].FormatColorRGB = Color.FromArgb(255, 99, 190, 123);
conditionalFormat.ColorScale.Criteria[1].FormatColorRGB = Color.FromArgb(255, 90, 138, 198);
spreadsheet.ActiveGrid.InvalidateCell(GridRangeInfo.Col(3));

Sample output

WPF Spreadsheet Conditional Formatting with Color Scales

Icon Sets

To apply a conditional format for icon sets, set the format type to IconSet and configure the associated properties such as the icon type and criteria. Then, invalidate the cells to update the view.

var worksheet = spreadsheet.Workbook.Worksheets[0];
var conditionalFormats = worksheet.Range["D2:D100"].ConditionalFormats;
var conditionalFormat = conditionalFormats.AddCondition();
conditionalFormat.FormatType = ExcelCFType.IconSet;
conditionalFormat.IconSet.IconSet = ExcelIconSetType.ThreeSymbols;
spreadsheet.ActiveGrid.InvalidateCell(GridRangeInfo.Col(4));

Sample output

WPF Spreadsheet Conditional Formatting with Icon Sets

See also: