Excel Conditional Formatting

Following sample shows how to apply various kinds of Conditional Formatting rules to ranges of a worksheet.

Conditional Formatting resolution is currently not implemented in GemBox.Spreadsheet. Conditional Formatting is loaded from and saved to XLSX file format only.

Screenshot

Conditional Formatting Screenshot

See the full code below, use RUN EXAMPLE to execute.


1using System;
2using System.Collections.Generic;
3using System.Text;
4using GemBox.Spreadsheet;
5using GemBox.Spreadsheet.ConditionalFormatting;
6using GemBox.Spreadsheet.PivotTables;
7
8class Sample
9{
10    [STAThread]
11    static void Main(string[] args)
12    {
13        // If using Professional version, put your serial key below.
14        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
15
16        ExcelFile ef = new ExcelFile();
17        ExcelWorksheet ws = ef.Worksheets.Add("Conditional Formatting");
18
19        int rowCount = 20;
20
21        // Specify sheet formatting.
22        ws.Rows[0].Style.Font.Weight = ExcelFont.BoldWeight;
23        ws.Columns[0].SetWidth(3, LengthUnit.Centimeter);
24        ws.Columns[1].SetWidth(3, LengthUnit.Centimeter);
25        ws.Columns[2].SetWidth(3, LengthUnit.Centimeter);
26        ws.Columns[3].SetWidth(3, LengthUnit.Centimeter);
27        ws.Columns[3].Style.NumberFormat = "[$$-409]#,##0.00";
28        ws.Columns[4].SetWidth(3, LengthUnit.Centimeter);
29        ws.Columns[4].Style.NumberFormat = "yyyy-mm-dd";
30
31        var cells = ws.Cells;
32
33        // Specify header row.
34        cells[0, 0].Value = "Departments";
35        cells[0, 1].Value = "Names";
36        cells[0, 2].Value = "Years of Service";
37        cells[0, 3].Value = "Salaries";
38        cells[0, 4].Value = "Deadlines";
39
40        // Insert random data to sheet.
41        var random = new Random();
42        var departments = new string[] { "Legal", "Marketing", "Finance", "Planning", "Purchasing" };
43        var names = new string[] { "John Doe", "Fred Nurk", "Hans Meier", "Ivan Horvat" };
44        for (int i = 0; i < rowCount; ++i)
45        {
46            cells[i + 1, 0].Value = departments[random.Next(departments.Length)];
47            cells[i + 1, 1].Value = names[random.Next(names.Length)] + ' ' + (i + 1).ToString();
48            cells[i + 1, 2].SetValue(random.Next(1, 31));
49            cells[i + 1, 3].SetValue(random.Next(10, 101) * 100);
50            cells[i + 1, 4].SetValue(DateTime.Now.AddDays(random.Next(-1, 2)));
51        }
52
53        // Apply shading to alternate rows in a worksheet using 'Formula' based conditional formatting.
54        ws.ConditionalFormatting.AddFormula(ws.Cells.Name, "MOD(ROW(),2)=0").
55            Style.FillPattern.PatternBackgroundColor = SpreadsheetColor.FromName(ColorName.Accent1Lighter40Pct);
56        ws.ConditionalFormatting.AddFormula(ws.Cells.Name, "MOD(ROW(),2)=1").
57            Style.FillPattern.PatternBackgroundColor = SpreadsheetColor.FromName(ColorName.Accent5Lighter80Pct);
58
59        // Apply '2-Color Scale' conditional formatting to 'Years of Service' column.
60        ws.ConditionalFormatting.Add2ColorScale("C2:C" + (rowCount + 1));
61
62        // Apply '3-Color Scale' conditional formatting to 'Salaries' column.
63        ws.ConditionalFormatting.Add3ColorScale("D2:D" + (rowCount + 1));
64
65        // Apply 'Data Bar' conditional formatting to 'Salaries' column.
66        ws.ConditionalFormatting.AddDataBar("D2:D" + (rowCount + 1));
67
68        // Apply 'Icon Set' conditional formatting to 'Years of Service' column.
69        ws.ConditionalFormatting.AddIconSet("C2:C" + (rowCount + 1)).IconStyle = SpreadsheetIconStyle.FourTrafficLights;
70
71        // Apply green font color to cells in a 'Years of Service' column which have values between 15 and 20.
72        ws.ConditionalFormatting.AddContainValue("C2:C" + (rowCount + 1), ContainValueOperator.Between, 15, 20).
73            Style.Font.Color = SpreadsheetColor.FromName(ColorName.Green);
74
75        // Apply double red border to cells in a 'Names' column which contain text 'Doe'.
76        ws.ConditionalFormatting.AddContainText("B2:B" + (rowCount + 1), ContainTextOperator.Contains, "Doe").
77            Style.Borders.SetBorders(MultipleBorders.Outside, SpreadsheetColor.FromName(ColorName.Red), LineStyle.Double);
78
79        // Apply red shading to cells in a 'Deadlines' column which are equal to yesterday's date.
80        ws.ConditionalFormatting.AddContainDate("E2:E" + (rowCount + 1), ContainDateOperator.Yesterday).
81            Style.FillPattern.PatternBackgroundColor = SpreadsheetColor.FromName(ColorName.Red);
82
83        // Apply bold font weight to cells in a 'Salaries' column which have top 10 values.
84        ws.ConditionalFormatting.AddTopOrBottomRanked("D2:D" + (rowCount + 1), false, 10).
85            Style.Font.Weight = ExcelFont.BoldWeight;
86
87        // Apply double underline to cells in a 'Years of Service' column which have below average value.
88        ws.ConditionalFormatting.AddAboveOrBelowAverage("C2:C" + (rowCount + 1), true).
89            Style.Font.UnderlineStyle = UnderlineStyle.Double;
90
91        // Apply italic font style to cells in a 'Departments' column which have duplicate values.
92        ws.ConditionalFormatting.AddUniqueOrDuplicate("A2:A" + (rowCount + 1), true).
93            Style.Font.Italic = true;
94
95        ef.Save("Conditional Formatting.xlsx");
96    }
97}
1Imports System
2Imports System.Collections.Generic
3Imports System.Text
4Imports GemBox.Spreadsheet
5Imports GemBox.Spreadsheet.ConditionalFormatting
6Imports GemBox.Spreadsheet.PivotTables
7
8Module Samples
9
10    Sub Main()
11
12        ' If using Professional version, put your serial key below.
13        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
14
15        Dim ef As ExcelFile = New ExcelFile
16        Dim ws As ExcelWorksheet = ef.Worksheets.Add("Conditional Formatting")
17
18        Dim rowCount As Integer = 20
19
20        ' Specify sheet formatting.
21        ws.Rows(0).Style.Font.Weight = ExcelFont.BoldWeight
22        ws.Columns(0).SetWidth(3, LengthUnit.Centimeter)
23        ws.Columns(1).SetWidth(3, LengthUnit.Centimeter)
24        ws.Columns(2).SetWidth(3, LengthUnit.Centimeter)
25        ws.Columns(3).SetWidth(3, LengthUnit.Centimeter)
26        ws.Columns(3).Style.NumberFormat = "[$$-409]#,##0.00"
27        ws.Columns(4).SetWidth(3, LengthUnit.Centimeter)
28        ws.Columns(4).Style.NumberFormat = "yyyy-mm-dd"
29
30        Dim cells = ws.Cells
31
32        ' Specify header row.
33        cells(0, 0).Value = "Departments"
34        cells(0, 1).Value = "Names"
35        cells(0, 2).Value = "Years of Service"
36        cells(0, 3).Value = "Salaries"
37        cells(0, 4).Value = "Deadlines"
38
39        ' Insert random data to sheet.
40        Dim random = New Random()
41        Dim departments = New String() {"Legal", "Marketing", "Finance", "Planning", "Purchasing"}
42        Dim names = New String() {"John Doe", "Fred Nurk", "Hans Meier", "Ivan Horvat"}
43        For i As Integer = 0 To rowCount - 1
44            cells(i + 1, 0).Value = departments(random.Next(departments.Length))
45            cells(i + 1, 1).Value = names(random.Next(names.Length)) + " "c + (i + 1).ToString()
46            cells(i + 1, 2).SetValue(random.Next(1, 31))
47            cells(i + 1, 3).SetValue(random.Next(10, 101) * 100)
48            cells(i + 1, 4).SetValue(DateTime.Now.AddDays(random.Next(-1, 2)))
49        Next
50
51        ' Apply shading to alternate rows in a worksheet using 'Formula' based conditional formatting.
52        ws.ConditionalFormatting.AddFormula(ws.Cells.Name, "MOD(ROW(),2)=0").
53            Style.FillPattern.PatternBackgroundColor = SpreadsheetColor.FromName(ColorName.Accent1Lighter40Pct)
54        ws.ConditionalFormatting.AddFormula(ws.Cells.Name, "MOD(ROW(),2)=1").
55            Style.FillPattern.PatternBackgroundColor = SpreadsheetColor.FromName(ColorName.Accent5Lighter80Pct)
56
57        ' Apply '2-Color Scale' conditional formatting to 'Years of Service' column.
58        ws.ConditionalFormatting.Add2ColorScale("C2:C" & (rowCount + 1))
59
60        ' Apply '3-Color Scale' conditional formatting to 'Salaries' column.
61        ws.ConditionalFormatting.Add3ColorScale("D2:D" & (rowCount + 1))
62
63        ' Apply 'Data Bar' conditional formatting to 'Salaries' column.
64        ws.ConditionalFormatting.AddDataBar("D2:D" & (rowCount + 1))
65
66        ' Apply 'Icon Set' conditional formatting to 'Years of Service' column.
67        ws.ConditionalFormatting.AddIconSet("C2:C" & (rowCount + 1)).IconStyle = SpreadsheetIconStyle.FourTrafficLights
68
69        ' Apply green font color to cells in a 'Years of Service' column which have values between 15 and 20.
70        ws.ConditionalFormatting.AddContainValue("C2:C" & (rowCount + 1), ContainValueOperator.Between, 15, 20).
71            Style.Font.Color = SpreadsheetColor.FromName(ColorName.Green)
72
73        ' Apply double red border to cells in a 'Names' column which contain text 'Doe'.
74        ws.ConditionalFormatting.AddContainText("B2:B" & (rowCount + 1), ContainTextOperator.Contains, "Doe").
75            Style.Borders.SetBorders(MultipleBorders.Outside, SpreadsheetColor.FromName(ColorName.Red), LineStyle.Double)
76
77        ' Apply red shading to cells in a 'Deadlines' column which are equal to yesterday's date.
78        ws.ConditionalFormatting.AddContainDate("E2:E" & (rowCount + 1), ContainDateOperator.Yesterday).
79            Style.FillPattern.PatternBackgroundColor = SpreadsheetColor.FromName(ColorName.Red)
80
81        ' Apply bold font weight to cells in a 'Salaries' column which have top 10 values.
82        ws.ConditionalFormatting.AddTopOrBottomRanked("D2:D" & (rowCount + 1), False, 10).
83            Style.Font.Weight = ExcelFont.BoldWeight
84
85        ' Apply double underline to cells in a 'Years of Service' column which have below average value.
86        ws.ConditionalFormatting.AddAboveOrBelowAverage("C2:C" & (rowCount + 1), True).
87            Style.Font.UnderlineStyle = UnderlineStyle.Double
88
89        ' Apply italic font style to cells in a 'Departments' column which have duplicate values.
90        ws.ConditionalFormatting.AddUniqueOrDuplicate("A2:A" & (rowCount + 1), True).
91            Style.Font.Italic = True
92
93        ef.Save("Conditional Formatting.xlsx")
94
95    End Sub
96
97End Module

Check next sample or find out more about GemBox.Spreadsheet and GemBox Software.