Conditional formatting | .NET Excel Library

Overview

Conditional formatting helps identify an Excel spreadsheet’s data by highlighting cells based on certain criteria provided by the user. This feature overrides any default cell styles in the worksheet when the cell value matches the criteria.

Learn about Conditional Formatting.

Explore more features of Conditional Formatting.

.NET Conditional formatting.

How to apply conditional formatting in Excel using C#

Install the Syncfusion.XlsIO.Net.Core NuGet package in your .NET application.
Initialize the ExcelEngine and IApplication objects.
Initialize the IWorkbook and load the first worksheet of workbook into IWorksheet.
Initialize conditional formats through IConditionalFormats.
Call the AddCondition() method of IConditionalFormats.
Set the properties FormatType, Operator, FirstFormula and SecondFormula of IConditionalFormat.
Save the workbook object to a MemoryStream object.

Here is an example of how to apply conditional formatting to Excel using C#

c#
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Applying conditional formatting to "A1"
  IConditionalFormats conditionalFormats = worksheet.Range["A1"].ConditionalFormats;
  IConditionalFormat conditionalFormat = conditionalFormats.AddCondition();

  //Represent conditional format rule that the value in the target range should be between 10 and 20
  conditionalFormat.FormatType = ExcelCFType.CellValue;
  conditionalFormat.Operator = ExcelComparisonOperator.Between;
  conditionalFormat.FirstFormula = "10";
  conditionalFormat.SecondFormula = "20";
  worksheet.Range["A1"].Text = "Enter a number between 10 and 20";

  //Setting back color and font style to be applied for the target range
  conditionalFormat.BackColor = ExcelKnownColors.Light_orange;
  conditionalFormat.IsBold = true;
  conditionalFormat.IsItalic = true;

  //Save the document into a stream
  using (MemoryStream outputStream = new MemoryStream()) 
  { 
      workbook.Save(outputStream); 
  }
}

Syncfusion XlsIO also supports advanced conditional formats like data bars, color scales, and icon sets. These types are explained below.

Data bars

In this type, a color band is used inside a cell to show the top-to-bottom values in a specified cell range. Data bars highlight data with a single color, whereas the color scale uses two or three colors. A data bar is a smaller version of a bar chart in which bars are used inside a cell instead of on a separate chart in the Excel worksheet.

.NET Data bars.

Here is an example of how to apply data bar type conditional formatting to Excel range in C# using Syncfusion .NET Excel library.

c#
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  using(FileStream excelStream = new FileStream(“Sample.xlsx”, FileMode.Open, FileAccess.Read))
  {
    IWorkbook workbook = application.Workbooks.Open(excelStream);
    IWorksheet worksheet = workbook.Worksheets[0];

    //Create data bars for the data in a specified range
    IConditionalFormats conditionalFormats = worksheet.Range["C7:C46"].ConditionalFormats;
    IConditionalFormat conditionalFormat = conditionalFormats.AddCondition();
    conditionalFormat.FormatType = ExcelCFType.DataBar;
    IDataBar dataBar = conditionalFormat.DataBar;

    //Set the constraints
    dataBar.MinPoint.Type = ConditionValueType.LowestValue;
    dataBar.MaxPoint.Type = ConditionValueType.HighestValue;

    //Set color for the Bar
    dataBar.BarColor = Color.FromArgb(156, 208, 243);

    //Hide values in the data bar
    dataBar.ShowValue = false;
    dataBar.BarColor = Color.Aqua;

    //Save the document into a stream
    using (MemoryStream outputStream = new MemoryStream()) 
    { 
      workbook.Save(outputStream); 
    } 
  }
}

Color scales

While using color scale formatting, the cell color intensity will increase from bottom to top values in a cell range between specified colors. This helps identify one cell value’s place within a larger set of data.

.NET Color Scales.

Here is an example of how to apply color scales type conditional formatting to Excel range in C# using Syncfusion .NET Excel library.

c#
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  using(FileStream excelStream = new FileStream(“Sample.xlsx”, FileMode.Open, FileAccess.Read))
  {
    IWorkbook workbook = application.Workbooks.Open(excelStream);
    IWorksheet worksheet = workbook.Worksheets[0];

    //Create color scales for data in a specified range
    IConditionalFormats conditionalFormats = worksheet.Range["D7:D46"].ConditionalFormats;
    IConditionalFormat conditionalFormat = conditionalFormats.AddCondition();
    conditionalFormat.FormatType = ExcelCFType.ColorScale;
    IColorScale colorScale = conditionalFormat.ColorScale;

    //Sets 3 - color scale
    colorScale.SetConditionCount(3);
    colorScale.Criteria[0].FormatColorRGB = Color.FromArgb(230, 197, 218);
    colorScale.Criteria[0].Type = ConditionValueType.LowestValue;
    colorScale.Criteria[0].Value = "0";

    colorScale.Criteria[1].FormatColorRGB = Color.FromArgb(244, 210, 178);
    colorScale.Criteria[1].Type = ConditionValueType.Percentile;
    colorScale.Criteria[1].Value = "50";

    colorScale.Criteria[2].FormatColorRGB = Color.FromArgb(245, 247, 171);
    colorScale.Criteria[2].Type = ConditionValueType.HighestValue;
    colorScale.Criteria[2].Value = "0";

    conditionalFormat.FirstFormulaR1C1 = "=R[1]C[0]";
    conditionalFormat.SecondFormulaR1C1 = "=R[1]C[1]";

    //Save the document into a stream
    using (MemoryStream outputStream = new MemoryStream()) 
    { 
      workbook.Save(outputStream); 
    } 
  }
}

Icon sets

Icon sets help group large amounts of data with icons, each with a specific threshold value. Similar to cell-rule formatting, when a specified condition matches, an icon is applied to the cell instead of any other formatting.

.NET Color Scales.

Here is an example of how to apply icon set type conditional formatting to Excel range in C# using Syncfusion .NET Excel library.

c#
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  using(FileStream excelStream = new FileStream(“Sample.xlsx”, FileMode.Open, FileAccess.Read))
  {
    IWorkbook workbook = application.Workbooks.Open(excelStream);
    IWorksheet worksheet = workbook.Worksheets[0];

    //Create icon sets for the data in a specified range
    IConditionalFormats conditionalFormats = worksheet.Range["E7:E46"].ConditionalFormats;
    IConditionalFormat conditionalFormat = conditionalFormats.AddCondition();
    conditionalFormat.FormatType = ExcelCFType.IconSet;
    IIconSet iconSet = conditionalFormat.IconSet;

    //Apply three symbols icon and hide the data in the specified range
    iconSet.IconSet = ExcelIconSetType.ThreeSymbols;
    iconSet.IconCriteria[1].Type = ConditionValueType.Percent;
    iconSet.IconCriteria[1].Value = "50";
    iconSet.IconCriteria[2].Type = ConditionValueType.Percent;
    iconSet.IconCriteria[2].Value = "50";
    iconSet.ShowIconOnly = true;

    //Save the document into a stream
    using (MemoryStream outputStream = new MemoryStream()) 
    { 
      workbook.Save(outputStream); 
    } 
  }
}