Excel Conditional Formatting

The IronXL library supports Conditional Formatting of cells and ranges. Conditional Formatting allows changing the style of the cell, such as the background color or the style of text, based on logical/programmatic rules.

Use CreateConditionalFormattingRule(string formula) to create a conditional formatting rule based on a Boolean formula. When the formula result is true, the cell is highlighted. Please note that the formula to evaluate MUST be a Boolean function.

The CreateConditionalFormattingRule method with three parameters can only take ComparisonOperator.Between and ComparisonOperator.NotBetween as the first parameter.

Use conditional formatting to highlight cells and ranges with specified color and format combinations that are dependent on the cell's true/false value determined by set rules. This allows the ability to analyze data, detect issues, and identify patterns and trends.

Here's an example of applying conditional formatting using IronXL in C#:

using IronXL;
using IronXL.Formatting; // Required for conditional formatting

class ExcelConditionalFormattingExample
{
    static void Main()
    {
        // Load an existing workbook
        WorkBook workbook = WorkBook.Load("example.xlsx");

        // Select the first worksheet in the workbook
        WorkSheet sheet = workbook.WorkSheets.First();

        // Define a conditional formatting rule using a Boolean formula
        sheet.CreateConditionalFormattingRule("A1 > 10")
             .Style.BackgroundColor = "#FF0000"; // Set the background color to red if the condition is true

        // Alternatively, use the CreateConditionalFormattingRule with three parameters
        sheet.CreateConditionalFormattingRule(
            ComparisonOperator.Between, 
            "10", 
            "20",
            "B1"
        ).Style.Font.SetColor("#00FF00"); // Set the font color to green if the value is between 10 and 20

        // Save changes to the workbook
        workbook.SaveAs("formatted_example.xlsx");
    }
}

using IronXL;
using IronXL.Formatting; // Required for conditional formatting

class ExcelConditionalFormattingExample
{
    static void Main()
    {
        // Load an existing workbook
        WorkBook workbook = WorkBook.Load("example.xlsx");

        // Select the first worksheet in the workbook
        WorkSheet sheet = workbook.WorkSheets.First();

        // Define a conditional formatting rule using a Boolean formula
        sheet.CreateConditionalFormattingRule("A1 > 10")
             .Style.BackgroundColor = "#FF0000"; // Set the background color to red if the condition is true

        // Alternatively, use the CreateConditionalFormattingRule with three parameters
        sheet.CreateConditionalFormattingRule(
            ComparisonOperator.Between, 
            "10", 
            "20",
            "B1"
        ).Style.Font.SetColor("#00FF00"); // Set the font color to green if the value is between 10 and 20

        // Save changes to the workbook
        workbook.SaveAs("formatted_example.xlsx");
    }
}

Imports IronXL
Imports IronXL.Formatting ' Required for conditional formatting

Friend Class ExcelConditionalFormattingExample
	Shared Sub Main()
		' Load an existing workbook
		Dim workbook As WorkBook = WorkBook.Load("example.xlsx")

		' Select the first worksheet in the workbook
		Dim sheet As WorkSheet = workbook.WorkSheets.First()

		' Define a conditional formatting rule using a Boolean formula
		sheet.CreateConditionalFormattingRule("A1 > 10").Style.BackgroundColor = "#FF0000" ' Set the background color to red if the condition is true

		' Alternatively, use the CreateConditionalFormattingRule with three parameters
		sheet.CreateConditionalFormattingRule(ComparisonOperator.Between, "10", "20", "B1").Style.Font.SetColor("#00FF00") ' Set the font color to green if the value is between 10 and 20

		' Save changes to the workbook
		workbook.SaveAs("formatted_example.xlsx")
	End Sub
End Class

Comments:

• We start by including the necessary namespaces from IronXL to handle Excel functionalities and conditional formatting.
• A workbook is loaded from an existing Excel file, example.xlsx.
• We select the first worksheet within the loaded workbook to apply the formatting.
• A conditional formatting rule is created to change the background color of a cell to red if the value in "A1" is greater than 10.
• Another conditional formatting rule is created to change the font color to green if the value in "B1" is between 10 and 20.
• Finally, the workbook is saved as formatted_example.xlsx with the new conditional formatting rules applied.