Apply Conditional Formatting in Excel Files with the Syncfusion .NET Excel Library

Overview

The Syncfusion® .NET Excel Library (XlsIO) enables you to create, read, and edit Excel documents programmatically without Microsoft Excel or interop dependencies. Using this library, you can apply conditional formatting in Excel files using C#.

Watch this video to see how to apply conditional formatting in Excel files using the Syncfusion .NET Excel Library:

Watch the video

Apply conditional formatting in Excel

Follow these steps to apply conditional formatting in Excel files using the Syncfusion .NET Excel Library.

Step 1: Create a new project

Start by creating a new C# Console Application project.

Step 2: Install the NuGet package

Add the Syncfusion.XlsIO.Net.Core package to your project from NuGet.org.

Step 3: Include namespaces

Add the following namespaces to your Program.cs file:

c#
using System.IO;
using Syncfusion.XlsIO;

Step 4: Add code to apply conditional formatting in Excel files

Use the following code in the Program.cs file to create an Excel document and apply conditional formatting in it.

Run

c#
// Initialize the Excel engine and create a workbook.
using (ExcelEngine excelEngine = new ExcelEngine())
{
  application.DefaultVersion = ExcelVersion.Xlsx;
  // Create a workbook.
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

Define conditional formatting rules

c#
// Applying conditional formatting to "A1".
IConditionalFormats condition = worksheet.Range["A1"].ConditionalFormats;
IConditionalFormat condition1 = condition.AddCondition();
// Represents conditional format rule that the value in target range should be between 10 and 20.
condition1.FormatType = ExcelCFType.CellValue;
condition1.Operator = ExcelComparisonOperator.Between;
condition1.FirstFormula = "10";
condition1.SecondFormula = "20";
worksheet.Range["A1"].Text = "Enter a number between 10 and 20";
// Setting back color and font style to be applied for target range.
condition1.BackColor = ExcelKnownColors.Light_orange;
condition1.IsBold = true;
condition1.IsItalic = true;

// Applying conditional formatting to "A3".
condition = worksheet.Range["A3"].ConditionalFormats;
IConditionalFormat condition2 = condition.AddCondition();
// Represents conditional format rule that the cell value should be 1000.
condition2.FormatType = ExcelCFType.CellValue;
condition2.Operator = ExcelComparisonOperator.Equal;
condition2.FirstFormula = "1000";
worksheet.Range["A3"].Text = "Enter the Number as 1000";
// Setting fill pattern and back color to target range.
condition2.FillPattern = ExcelPattern.LightUpwardDiagonal;
condition2.BackColor = ExcelKnownColors.Yellow;

// Applying conditional formatting to "A5".
condition = worksheet.Range["A5"].ConditionalFormats;
IConditionalFormat condition3 = condition.AddCondition();
// Setting conditional format rule that the cell value for target range should be less than or equal to 1000.
condition3.FormatType = ExcelCFType.CellValue;
condition3.Operator = ExcelComparisonOperator.LessOrEqual;
condition3.FirstFormula = "1000";
worksheet.Range["A5"].Text = "Enter a Number which is less than or equal to 1000";
// Setting back color to target range.
condition3.BackColor = ExcelKnownColors.Light_green;

Save

c#
// Saving the workbook.
FileStream outputStream = new FileStream(Path.GetFullPath(@"Output/ConditionalFormat.xlsx"), FileMode.Create, FileAccess.Write);
workbook.SaveAs(outputStream);
// Dispose streams.
outputStream.Dispose();
}