Excel Conditional Formatting

VB C#

using IronXL; using IronXL.Formatting.Enums; using IronXL.Styles; WorkBook workBook = WorkBook.Load("sample.xlsx"); WorkSheet workSheet = workBook.DefaultWorkSheet; // Create conditional formatting rule var rule = workSheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.LessThan, "8"); // Set style options rule.FontFormatting.IsBold = true; rule.FontFormatting.FontColor = "#123456"; rule.BorderFormatting.RightBorderColor = "#ffffff"; rule.BorderFormatting.RightBorderType = BorderType.Thick; rule.PatternFormatting.BackgroundColor = "#54bdd9"; rule.PatternFormatting.FillPattern = FillPattern.Diamonds; // Apply formatting on specified region workSheet.ConditionalFormatting.AddConditionalFormatting("A3:A8", rule); // Create conditional formatting rule var rule1 = workSheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.Between, "7", "10"); // Set style options rule1.FontFormatting.IsItalic = true; rule1.FontFormatting.UnderlineType = FontUnderlineType.Single; // Apply formatting on specified region workSheet.ConditionalFormatting.AddConditionalFormatting("A3:A9", rule1); workBook.SaveAs("applyConditionalFormatting.xlsx");

Imports IronXL Imports IronXL.Formatting.Enums Imports IronXL.Styles Private workBook As WorkBook = WorkBook.Load("sample.xlsx") Private workSheet As WorkSheet = workBook.DefaultWorkSheet ' Create conditional formatting rule Private rule = workSheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.LessThan, "8") ' Set style options rule.FontFormatting.IsBold = True rule.FontFormatting.FontColor = "#123456" rule.BorderFormatting.RightBorderColor = "#ffffff" rule.BorderFormatting.RightBorderType = BorderType.Thick rule.PatternFormatting.BackgroundColor = "#54bdd9" rule.PatternFormatting.FillPattern = FillPattern.Diamonds ' Apply formatting on specified region workSheet.ConditionalFormatting.AddConditionalFormatting("A3:A8", rule) ' Create conditional formatting rule Dim rule1 = workSheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.Between, "7", "10") ' Set style options rule1.FontFormatting.IsItalic = True rule1.FontFormatting.UnderlineType = FontUnderlineType.Single ' Apply formatting on specified region workSheet.ConditionalFormatting.AddConditionalFormatting("A3:A9", rule1) workBook.SaveAs("applyConditionalFormatting.xlsx")

Install-Package IronXL.Excel

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.

How to Use Excel Conditional Formatting in C#

Set up an Excel library to use Excel Conditional Formatting.
Open the current Excel file.
Select a default Worksheet.
Save the `Workbook` to an Excel file.

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

$vbLabelText $csharpLabel

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.