menu

WPF

  • Code Examples
  • Upgrade Guide
  • User Guide
  • Demos
  • Support
  • Forums
  • Download
Interface IConditionalFormat - WPF API Reference | Syncfusion

    Show / Hide Table of Contents

    Interface IConditionalFormat

    Represents a conditional format.

    Inherited Members
    IOptimizedUpdate.BeginUpdate()
    IOptimizedUpdate.EndUpdate()
    IParentApplication.Application
    IParentApplication.Parent
    Namespace: Syncfusion.XlsIO
    Assembly: Syncfusion.XlsIO.Base.dll
    Syntax
    public interface IConditionalFormat : IParentApplication, IOptimizedUpdate
    Remarks

    The formatting properties are applied if the conditional formatting rule evaluates to True.

    Properties

    AboveBelowAverage

    Gets AboveBelowAverage conditional formatting rule. Read-only.

    Declaration
    IAboveBelowAverage AboveBelowAverage { get; }
    Property Value
    Type
    IAboveBelowAverage
    Remarks

    Valid only if FormatType is set to AboveBelowAverage.

    Examples

    AboveBelowAverage conditional formatting rule can be applied by setting AboveBelowAverage to FormatType property. To customize the AboveBelowAverage rule we have to change properties of IAboveBelowAverage interface. Here for example, we set values to IAboveBelowAverage interface properties.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;            
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["A1"].Text = "Above Average";
                worksheet["A2"].Number = 10;
                worksheet["A3"].Number = 20;
                worksheet["A4"].Number = 30;
                worksheet["A5"].Number = 40;
                worksheet["A6"].Number = 50;
                worksheet["A7"].Number = 60;
                worksheet["A8"].Number = 70;
                worksheet["A9"].Number = 80;
                worksheet["A10"].Number = 90;
                worksheet["A11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["A2:A11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set FormatType as AboveBelowAverage.
                format.FormatType = ExcelCFType.AboveBelowAverage;
                IAboveBelowAverage aboveBelowAverage = format.AboveBelowAverage;
    
                //Set values the IAboveBelowAverage interface properties.
                aboveBelowAverage.AverageType = ExcelCFAverageType.AboveStdDev;
                aboveBelowAverage.StdDevValue = 1;
    
                //Set color.
                format.BackColor = ExcelKnownColors.Red;
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    BackColor

    Gets or sets the background color from predefined colors (ExcelKnownColors).

    Declaration
    ExcelKnownColors BackColor { get; set; }
    Property Value
    Type
    ExcelKnownColors
    Examples

    Background color to cells within the conditional formatting range can be set color from ExcelKnownColors enumeration using BackColor property.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Set Color.
                format.BackColor = ExcelKnownColors.Red;
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    BackColorRGB

    Gets or sets the background RGB color.

    Declaration
    Color BackColorRGB { get; set; }
    Property Value
    Type
    System.Drawing.Color
    Remarks

    Similar to BackColorRGB property, we can use BackColor property. In BackColor we can only select a color from ExcelKnownColors enumeration . But, in BackColorRGB we can create RGB color objects by changing RGB values.

    Examples

    BackColorRGB property is used to set RGB color to the cell's background, if the conditions of conditional formats get satisfied.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Set RGB color.
                format.BackColorRGB = System.Drawing.Color.FromArgb(255, 0, 0);
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    BottomBorderColor

    Gets or sets the bottom border color from predefined colors (ExcelKnownColors).

    Declaration
    ExcelKnownColors BottomBorderColor { get; set; }
    Property Value
    Type
    ExcelKnownColors
    Examples

    To set a color from the ExcelKnownColors enumeration we use BottomBorderColor property. RGB color can be applied by using BottomBorderColorRGB property.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Apply Conditional format.
                format.BottomBorderStyle = ExcelLineStyle.Thick;
                format.LeftBorderStyle = ExcelLineStyle.Thick;
                format.TopBorderStyle = ExcelLineStyle.Thick;
                format.RightBorderStyle = ExcelLineStyle.Thick;
    
                //Set Border color.
                format.BottomBorderColor = ExcelKnownColors.Red;    //Set color
                format.LeftBorderColor = ExcelKnownColors.Red;
                format.TopBorderColor = ExcelKnownColors.Red;
                format.RightBorderColor = ExcelKnownColors.Red;
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    BottomBorderColorRGB

    Gets or sets RGB color to bottom border.

    Declaration
    Color BottomBorderColorRGB { get; set; }
    Property Value
    Type
    System.Drawing.Color
    Examples
            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Apply Conditional format.
                format.BottomBorderStyle = ExcelLineStyle.Thick;
                format.LeftBorderStyle = ExcelLineStyle.Thick;
                format.TopBorderStyle = ExcelLineStyle.Thick;
                format.RightBorderStyle = ExcelLineStyle.Thick;
    
                //Set Border color.
                format.BottomBorderColorRGB = System.Drawing.Color.Red;
                format.LeftBorderColorRGB = System.Drawing.Color.Red;
                format.RightBorderColorRGB = System.Drawing.Color.Red;
                format.TopBorderColorRGB = System.Drawing.Color.Red; 
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    BottomBorderStyle

    Gets or sets the bottom border line style.

    Declaration
    ExcelLineStyle BottomBorderStyle { get; set; }
    Property Value
    Type
    ExcelLineStyle
    Examples

    To change the default line style of bottom border we set a line style from ExcelLineStyle enumeration to BottomBorderStyle property. Here for example, we set Thick to BottomBorderStyle property.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Apply Conditional format.
                format.BottomBorderStyle = ExcelLineStyle.Thick;    //Set LineStyle
                format.LeftBorderStyle = ExcelLineStyle.Thick;
                format.TopBorderStyle = ExcelLineStyle.Thick;
                format.RightBorderStyle = ExcelLineStyle.Thick;
    
                //Set Border color.
                format.BottomBorderColor = ExcelKnownColors.Red;
                format.LeftBorderColor = ExcelKnownColors.Red;
                format.TopBorderColor = ExcelKnownColors.Red;
                format.RightBorderColor = ExcelKnownColors.Red;
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    Color

    Gets or sets the pattern foreground color from predefined colors (ExcelKnownColors).

    Declaration
    ExcelKnownColors Color { get; set; }
    Property Value
    Type
    ExcelKnownColors
    Examples

    Color property is used to set the foreground color for FillPattern based on the condition applied. RGB color can be applied by setting RGB color to ColorRGB property. Here for example, we set DarkHorizontal to FillPattern and Red to Color.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Set Pattern.
                format.FillPattern = ExcelPattern.DarkHorizontal;
    
                //Set Color.
                format.Color = ExcelKnownColors.Red;
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    ColorRGB

    Gets or sets the pattern foreground RGB color.

    Declaration
    Color ColorRGB { get; set; }
    Property Value
    Type
    System.Drawing.Color
    Examples

    ColorRGB property is used to set the foreground RGB color for FillPattern based on the condition applied. Color from ExcelKnownColors can be applied by setting color to Color property. Here for example, we set DarkHorizontal to FillPattern and Red color to ColorRGB.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Set Pattern.                
                format.FillPattern = ExcelPattern.DarkHorizontal;
    
                //Set RGB color.
                format.ColorRGB = System.Drawing.Color.FromArgb(255, 0, 0);
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    ColorScale

    Gets color scale conditional formatting rule. Read-only.

    Declaration
    IColorScale ColorScale { get; }
    Property Value
    Type
    IColorScale
    Remarks

    Valid only if FormatType is set to ColorScale.

    Examples

    ColorScale can be set by setting ColorScale to FormatType property. To customize criteria for applying color scale we have to set values for properties in IColorScale interface. Here for example, we set values for Criteria property and use SetConditionCount(Int32) method.

            //Create a worksheet.        
            ExcelEngine excelEngine = new ExcelEngine();
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];
    
            //Load data to Apply Conditional Formatting.
            worksheet["A1"].Text = "ColorScale";
            worksheet["A2"].Number  =  10;
            worksheet["A3"].Number  =  20;
            worksheet["A4"].Number  =  30;
            worksheet["A5"].Number  =  40;
            worksheet["A6"].Number  =  50;
            worksheet["A7"].Number  =  60;
            worksheet["A8"].Number  =  70;
            worksheet["A9"].Number  =  80;
            worksheet["A10"].Number =  90;
            worksheet["A11"].Number =  100;
    
            //Create instance of IConditonalFormat and IConditionalFormats.
            IConditionalFormats formats = worksheet["A2:A11"].ConditionalFormats;
            IConditionalFormat format = formats.AddCondition();
    
            //Set FormatType as ColorScale.
            format.FormatType = ExcelCFType.ColorScale;
            IColorScale colorScale = format.ColorScale;
    
            //Set 3 as count for color scale
            colorScale.SetConditionCount(3);
    
            //Set Criteria for applying ColorScale
            colorScale.Criteria[0].FormatColorRGB = System.Drawing.Color.White;
            colorScale.Criteria[0].Type = ConditionValueType.Percentile;
            colorScale.Criteria[0].Value = "0";
    
            colorScale.Criteria[1].FormatColorRGB = System.Drawing.Color.Red;
            colorScale.Criteria[1].Type = ConditionValueType.Percentile;
            colorScale.Criteria[1].Value = "50";
    
            colorScale.Criteria[2].FormatColorRGB = System.Drawing.Color.Blue;
            colorScale.Criteria[2].Type = ConditionValueType.Percentile;
            colorScale.Criteria[2].Value = "100";
    
            //Save and Dispose.
            workbook.SaveAs("ConditionalFormats.xlsx");
            workbook.Close();
            excelEngine.Dispose();

    DataBar

    Gets data bar conditional formatting rule. Read-only.

    Declaration
    IDataBar DataBar { get; }
    Property Value
    Type
    IDataBar
    Remarks

    Valid only if FormatType is set to DataBar.

    Examples

    DataBar can be set by setting DataBar to FormatType property. To customize the criteria for applying data bars we have to change properties of IDataBar interface. Here for example, we set Red color to BarColor property.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;            
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["A1"].Text = "DataBar";
                worksheet["A2"].Number = 10;
                worksheet["A3"].Number = 20;
                worksheet["A4"].Number = 30;
                worksheet["A5"].Number = 40;
                worksheet["A6"].Number = 50;
                worksheet["A7"].Number = 60;
                worksheet["A8"].Number = 70;
                worksheet["A9"].Number = 80;
                worksheet["A10"].Number = 90;
                worksheet["A11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["A2:A11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set FormatType as DataBar.
                format.FormatType = ExcelCFType.DataBar;
                IDataBar dataBar = format.DataBar;
    
                //Set Bar color for DataBar
                dataBar.BarColor = System.Drawing.Color.Red;
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    FillPattern

    Gets or sets the fill pattern style.

    Declaration
    ExcelPattern FillPattern { get; set; }
    Property Value
    Type
    ExcelPattern
    Examples

    To set pattern for cells within the conditional formatting range FillPattern property can be used. Here for example, we set DarkHorizontal to FillPattern property.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Set Pattern.
                format.FillPattern = ExcelPattern.DarkHorizontal;
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    FirstFormula

    Gets or sets the value or expression associated with the conditional format.

    Declaration
    string FirstFormula { get; set; }
    Property Value
    Type
    System.String
    Remarks

    Relative cell addresses can be used to apply formatting by using FirstFormulaR1C1 and SecondFormulaR1C1. To know more about R1C1 notation refer Using FormulaR1C1 property in Conditional Formats.

    Examples

    FirstFormula property is used to set value that is used for setting conditions. CellValue applies conditional formatting based on the condition from ExcelComparisonOperator enumeration. This requires atleast one value to apply conditional format. Here for example, we set "60" to FirstFormula property.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Apply Conditional format.
                format.BottomBorderStyle = ExcelLineStyle.Thick;
                format.LeftBorderStyle = ExcelLineStyle.Thick;
                format.TopBorderStyle = ExcelLineStyle.Thick;
                format.RightBorderStyle = ExcelLineStyle.Thick;
    
                //Set Border color.
                format.BottomBorderColor = ExcelKnownColors.Red;
                format.LeftBorderColor = ExcelKnownColors.Red;
                format.TopBorderColor = ExcelKnownColors.Red;
                format.RightBorderColor = ExcelKnownColors.Red;
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    FirstFormulaR1C1

    Gets or sets the value or expression associated with the conditional format in R1C1 notation.

    Declaration
    string FirstFormulaR1C1 { get; set; }
    Property Value
    Type
    System.String
    Remarks

    To know more about FirstFormulaR1C1 refer Using FormulaR1C1 property in Conditional Formats.

    Examples

    To apply formatting by comparing the relative cell addresses we can use FirstFormulaR1C1. Here for example, we compare the adjacent cell values and apply formatting. SecondFormulaR1C1 can also used along with FirstFormulaR1C1 to add additional condition.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 1;
                worksheet["B3"].Number = 2;
                worksheet["B4"].Number = 3;
                worksheet["B5"].Number = 4;
                worksheet["B6"].Number = 5;
                worksheet["B7"].Number = 6;
                worksheet["B8"].Number = 7;
                worksheet["B9"].Number = 8;
                worksheet["B10"].Number = 9;
                worksheet["B11"].Number = 10;
    
                //Data to be compared.
                worksheet["C2"].Number = 11;
                worksheet["C3"].Number = 1;
                worksheet["C4"].Number = 3;
                worksheet["C5"].Number = 2;
                worksheet["C6"].Number = 5;
                worksheet["C7"].Number = 3;
                worksheet["C8"].Number = 7;
                worksheet["C9"].Number = 9;
                worksheet["C10"].Number = 19;
                worksheet["C11"].Number = 20;
    
    
                //Create instance of IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
    
                //Add condition.
                IConditionalFormat format = formats.AddCondition();
    
                //Set FormatType.
                format.FormatType = ExcelCFType.Formula;
    
                //Set Formula.
                format.FirstFormulaR1C1 = "=RC>RC[1]";
    
                //Set color.
                format.BackColor = ExcelKnownColors.Red;
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    FontColor

    Gets or sets the font color from predefined colors (ExcelKnownColors).

    Declaration
    ExcelKnownColors FontColor { get; set; }
    Property Value
    Type
    ExcelKnownColors
    Remarks

    Similar to FontColor and FontColorRGB properties, cell colors can be set using BackColor and BackColorRGB properties.

    Examples

    To set font color for the text in the cells within the conditional formatting range FontColor property can be used. Colors can be selected from the ExcelKnownColors enumeration which has all the colors that are included in Excel by default. FontColorRGB property can be used to set colors in RGB representation.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Set Color.
                format.FontColor = ExcelKnownColors.Red;
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    FontColorRGB

    Gets or sets the font color.

    Declaration
    Color FontColorRGB { get; set; }
    Property Value
    Type
    System.Drawing.Color
    Remarks

    Similar to FontColor and FontColorRGB properties, cell colors can be set using BackColor and BackColorRGB properties.

    Examples

    To set font color for the text in the cells within the conditional formatting range FontColorRGB property can be used. Colors can be set by creating Color object by specfifying RGB values or can be selected from predefined RGB color objects. FontColor property can be used to set colors that are available from the ExcelKnownColors enumeration.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Set RGB color.
                format.FontColorRGB = System.Drawing.Color.FromArgb(255, 0, 0);
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    FormatType

    Gets or sets the type of the conditional format.

    Declaration
    ExcelCFType FormatType { get; set; }
    Property Value
    Type
    ExcelCFType
    Remarks

    By default the FormatType is set to CellValue.

    Examples

    Type of conditional formatting to be applied is based on the value set from ExcelCFType enumeration to FormatType property. Here for example, we set TimePeriod to FormatType property.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].DateTime = DateTime.Today;
                worksheet["B3"].DateTime = DateTime.Parse("1/18/2015");
                worksheet["B4"].DateTime = DateTime.Today;
                worksheet["B5"].DateTime = DateTime.Today;
                worksheet["B6"].DateTime = DateTime.Parse("1/18/2016");
                worksheet["B7"].DateTime = DateTime.Today;
                worksheet["B8"].DateTime = DateTime.Today;
                worksheet["B9"].DateTime = DateTime.Parse("1/18/2017");
                worksheet["B10"].DateTime = DateTime.Today;
                worksheet["B11"].DateTime = DateTime.Today;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set FormatType.
                format.FormatType = ExcelCFType.TimePeriod;
    
                //Set TimePeriodType. (Condition)
                format.TimePeriodType = CFTimePeriods.ThisMonth;
    
                //Set NumberFormat.
                format.NumberFormat = "d-mmm";
    
                //Set color.
                format.BackColor = ExcelKnownColors.Red;
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    GradientStyle

    Gets or Sets the gradient style for conditional format.

    Declaration
    ExcelGradientStyle GradientStyle { get; set; }
    Property Value
    Type
    ExcelGradientStyle
    Examples

    GradientStyle property is used to set the gradient style for FillPattern based on the condition applied. Gradient variant can be applied by setting the gradient variant to GradientVariant property. Here for example, we set Gradient to FillPattern and Diagonl_Up to GradientStyle.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "30";
                format.SecondFormula = "70";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Set Pattern.
                format.FillPattern = ExcelPattern.Gradient;
    
                //Set Gradient Style
                format.GradientStyle=ExcelGradientStyle.Diagonal_Up;
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    GradientVariant

    Gets or Sets the gradient variant for conditional format.

    Declaration
    ExcelGradientVariants GradientVariant { get; set; }
    Property Value
    Type
    ExcelGradientVariants
    Examples

    GradientVariant property is used to set the gradient variant for FillPattern based on the condition applied. Gradient style can be applied by setting the gradient style to GradientStyle property. Here for example, we set Gradient to FillPattern and ShadingVariants_2 to GradientVariant.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "30";
                format.SecondFormula = "70";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Set Pattern.
                format.FillPattern = ExcelPattern.Gradient;
    
                //Set Gradient Variant
                format.GradientVariant=ExcelGradientVariants.ShadingVariants_2";
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    IconSet

    Gets icon set conditional formatting rule. Read-only.

    Declaration
    IIconSet IconSet { get; }
    Property Value
    Type
    IIconSet
    Remarks

    Valid only if FormatType is set to IconSet.

    Examples

    IconSet can be set by setting IconSet to FormatType property. To customize the criteria for applying IconSets we have to change properties of IIconSet interface. Here for example, we set values for IconCriteria property.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;            
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["A1"].Text = "IconSets";
                worksheet["A2"].Number = 105;
                worksheet["A3"].Number = 120;
                worksheet["A4"].Number = 300;
                worksheet["A5"].Number = 240;
                worksheet["A6"].Number = 350;
                worksheet["A7"].Number = 460;
                worksheet["A8"].Number = 170;
                worksheet["A9"].Number = 280;
                worksheet["A10"].Number = 190;
                worksheet["A11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["A2:A11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set FormatType as IconSet.
                format.FormatType = ExcelCFType.IconSet;
                IIconSet iconSet = format.IconSet;
    
                //Set conditions for IconCriteria.
                iconSet.IconCriteria[1].Type = ConditionValueType.Percentile;
                iconSet.IconCriteria[1].Value = "20";
                iconSet.IconCriteria[1].Operator = ConditionalFormatOperator.GreaterThan;
    
                iconSet.IconCriteria[2].Type = ConditionValueType.Percentile;
                iconSet.IconCriteria[2].Value = "70";
                iconSet.IconCriteria[2].Operator = ConditionalFormatOperator.GreaterThan;
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    IsBackgroundColorPresent

    True if background color is present. otherwise, False.

    Declaration
    bool IsBackgroundColorPresent { get; set; }
    Property Value
    Type
    System.Boolean
    Examples
            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Check Background color is present or not.
                Console.WriteLine(format.IsBackgroundColorPresent);
    
                //Set Color.
                format.BackColor = ExcelKnownColors.Red;
    
                //Check Background color is present or not.
                Console.WriteLine(format.IsBackgroundColorPresent);
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }
    //Output will be
    //False
    //True

    IsBold

    Gets or sets a boolean value indicating whether the font is bold.

    Declaration
    bool IsBold { get; set; }
    Property Value
    Type
    System.Boolean
    Remarks

    Similar to IsBold property, we can use IsItalic and IsStrikeThrough properties also.

    Examples

    To format data in the cells to Bold within the conditional formatting range IsBold property can be used.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Set Bold.
                format.IsBold = true;
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    IsBorderFormatPresent

    Declaration
    bool IsBorderFormatPresent { get; set; }
    Property Value
    Type
    System.Boolean

    IsBottomBorderModified

    True if bottom border line style is modified. otherwise, False.

    Declaration
    bool IsBottomBorderModified { get; set; }
    Property Value
    Type
    System.Boolean
    Examples

    We use IsBottomBorderModified property to check whether the bottom border is modifed or not. Returns True if the border is modifed else returns False.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Check whether default border is modified or not.
                Console.WriteLine(format.IsBottomBorderModified);
                Console.WriteLine(format.IsLeftBorderModified);
                Console.WriteLine(format.IsRightBorderModified);
                Console.WriteLine(format.IsTopBorderModified);
    
                //Apply Conditional format.
                format.BottomBorderStyle = ExcelLineStyle.Thick;    //Set LineStyle
                format.LeftBorderStyle = ExcelLineStyle.Thick;
                format.TopBorderStyle = ExcelLineStyle.Thick;
                format.RightBorderStyle = ExcelLineStyle.Thick;
    
                //Set Border color.
                format.BottomBorderColor = ExcelKnownColors.Red;    //Set color
                format.LeftBorderColor = ExcelKnownColors.Red;
                format.TopBorderColor = ExcelKnownColors.Red;
                format.RightBorderColor = ExcelKnownColors.Red;
    
                //Check whether default border is modified or not.
                Console.WriteLine(format.IsBottomBorderModified);
                Console.WriteLine(format.IsLeftBorderModified);
                Console.WriteLine(format.IsRightBorderModified);
                Console.WriteLine(format.IsTopBorderModified);
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }
    //Output will be
    //False
    //False
    //False
    //False
    //True
    //True
    //True
    //True

    IsFontColorPresent

    True if font color is present. otherwise, False.

    Declaration
    bool IsFontColorPresent { get; set; }
    Property Value
    Type
    System.Boolean
    Examples
            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Check Font color is present or not.
                Console.WriteLine(format.IsFontColorPresent);
    
                //Set Color.
                format.FontColor = ExcelKnownColors.Red;
    
                //Check Font color is present or not.
                Console.WriteLine(format.IsFontColorPresent);
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }
    //Output will be
    //False
    //True

    IsFontFormatPresent

    True if the conditional format contains font formatting. otherwise, False.

    Declaration
    bool IsFontFormatPresent { get; set; }
    Property Value
    Type
    System.Boolean
    Examples
            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Check font is formatted or not.
                Console.WriteLine(format.IsFontFormatPresent);
    
                //Set Color.
                format.FontColor = ExcelKnownColors.Red;
    
                //Check font is formatted or not.
                Console.WriteLine(format.IsFontFormatPresent);
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }
    //Output will be
    //False
    //True

    IsItalic

    Gets or sets a boolean value indicating whether the font is italic.

    Declaration
    bool IsItalic { get; set; }
    Property Value
    Type
    System.Boolean
    Remarks

    Similar to IsItalic property, we can use IsBold and IsStrikeThrough properties also.

    Examples

    To format the data in the cells to Italic within the conditional formatting range IsItalic property can be used.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Set Color.
                format.IsItalic = true;
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    IsLeftBorderModified

    True if left border line style is modified. otherwise, False.

    Declaration
    bool IsLeftBorderModified { get; set; }
    Property Value
    Type
    System.Boolean
    Examples

    We use IsLeftBorderModified property to check whether the left border is modifed or not. Returns True if the border is modifed else returns False.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Check whether default border is modified or not.
                Console.WriteLine(format.IsBottomBorderModified);
                Console.WriteLine(format.IsLeftBorderModified);
                Console.WriteLine(format.IsRightBorderModified);
                Console.WriteLine(format.IsTopBorderModified);
    
                //Apply Conditional format.
                format.BottomBorderStyle = ExcelLineStyle.Thick;
                format.LeftBorderStyle = ExcelLineStyle.Thick;  //Set LineStyle
                format.TopBorderStyle = ExcelLineStyle.Thick;
                format.RightBorderStyle = ExcelLineStyle.Thick;
    
                //Set Border color.
                format.BottomBorderColor = ExcelKnownColors.Red;
                format.LeftBorderColor = ExcelKnownColors.Red;  //Set color
                format.TopBorderColor = ExcelKnownColors.Red;
                format.RightBorderColor = ExcelKnownColors.Red;
    
                //Check whether default border is modified or not.
                Console.WriteLine(format.IsBottomBorderModified);
                Console.WriteLine(format.IsLeftBorderModified);
                Console.WriteLine(format.IsRightBorderModified);
                Console.WriteLine(format.IsTopBorderModified);
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }
    //Output will be
    //False
    //False
    //False
    //False
    //True
    //True
    //True
    //True

    IsPatternColorPresent

    True if pattern color is present. otherwise, False.

    Declaration
    bool IsPatternColorPresent { get; set; }
    Property Value
    Type
    System.Boolean
    Examples

    We can use IsPatternColorPresent property to check whether pattern color is set or not. Here for example, we check whether pattern color is set or not before setting and after setting DarkHorizontal to FillPattern and Red to Color.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Check whether pattern color is set or not.
                Console.WriteLine(format.IsPatternColorPresent);
    
                //Set Color.
                format.Color = ExcelKnownColors.Red;
                format.FillPattern = ExcelPattern.DarkHorizontal;
    
                //Check whether pattern color is set or not.
                Console.WriteLine(format.IsPatternColorPresent);
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }
    //Output will be
    //False
    //True

    IsPatternFormatPresent

    True if the conditional format contains pattern formatting. otherwise, False.

    Declaration
    bool IsPatternFormatPresent { get; set; }
    Property Value
    Type
    System.Boolean
    Examples

    To check whether the cell's FillPattern is set or not, We can use the IsPatternFormatPresent property. Here for example, We check before and after applying DarkHorizontal to FillPattern.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Check whether pattern format is present.
                Console.WriteLine(format.IsPatternFormatPresent);
    
                //Set Pattern.
                format.FillPattern = ExcelPattern.DarkHorizontal;
    
                //Check whether pattern format is present.
                Console.WriteLine(format.IsPatternFormatPresent);
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }
    //Output will be
    //False
    //True

    IsRightBorderModified

    True if right border line style is modified. otherwise, False.

    Declaration
    bool IsRightBorderModified { get; set; }
    Property Value
    Type
    System.Boolean
    Examples

    We use IsRightBorderModified property to check whether the right border is modifed or not. Returns True if the border is modifed else returns False.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Check whether default border is modified or not.
                Console.WriteLine(format.IsBottomBorderModified);
                Console.WriteLine(format.IsLeftBorderModified);
                Console.WriteLine(format.IsRightBorderModified);
                Console.WriteLine(format.IsTopBorderModified);
    
                //Apply Conditional format.
                format.BottomBorderStyle = ExcelLineStyle.Thick;
                format.LeftBorderStyle = ExcelLineStyle.Thick;
                format.TopBorderStyle = ExcelLineStyle.Thick;
                format.RightBorderStyle = ExcelLineStyle.Thick; //Set LineStyle
    
                //Set Border color.
                format.BottomBorderColor = ExcelKnownColors.Red;
                format.LeftBorderColor = ExcelKnownColors.Red;
                format.TopBorderColor = ExcelKnownColors.Red;
                format.RightBorderColor = ExcelKnownColors.Red; //Set color
    
                //Check whether default border is modified or not.
                Console.WriteLine(format.IsBottomBorderModified);
                Console.WriteLine(format.IsLeftBorderModified);
                Console.WriteLine(format.IsRightBorderModified);
                Console.WriteLine(format.IsTopBorderModified);
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }
    //Output will be
    //False
    //False
    //False
    //False
    //True
    //True
    //True
    //True

    IsStrikeThrough

    Gets or sets a Boolean value indicating whether the font is strike through.

    Declaration
    bool IsStrikeThrough { get; set; }
    Property Value
    Type
    System.Boolean
    Remarks

    Similar to IsStrikeThrough, we can use Underline, IsBold and IsItalic properties to format the values in the cells within the conditional formatting range.

    Examples

    Data in the cells within the conditional formats range can be struck with the help of IsStrikeThrough property. To strike values in the cells, we have to set the IsStrikeThrough property to True.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Set StrikeThrough.
                format.IsStrikeThrough = true;
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    IsSubScript

    Gets or sets a Boolean value indicating whether the font is subscript.

    Declaration
    bool IsSubScript { get; set; }
    Property Value
    Type
    System.Boolean

    IsSuperScript

    Gets or sets a Boolean value indicating whether the font is superscript.

    Declaration
    bool IsSuperScript { get; set; }
    Property Value
    Type
    System.Boolean

    IsTopBorderModified

    True if top border line style is modified. otherwise, False.

    Declaration
    bool IsTopBorderModified { get; set; }
    Property Value
    Type
    System.Boolean
    Examples

    We use IsTopBorderModified property to check whether the top border is modifed or not. Returns True if the border is modifed else returns False.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Check whether default border is modified or not.
                Console.WriteLine(format.IsBottomBorderModified);
                Console.WriteLine(format.IsLeftBorderModified);
                Console.WriteLine(format.IsRightBorderModified);
                Console.WriteLine(format.IsTopBorderModified);
    
                //Apply Conditional format.
                format.BottomBorderStyle = ExcelLineStyle.Thick;
                format.LeftBorderStyle = ExcelLineStyle.Thick;
                format.TopBorderStyle = ExcelLineStyle.Thick;   //Set LineStyle
                format.RightBorderStyle = ExcelLineStyle.Thick;
    
                //Set Border color.
                format.BottomBorderColor = ExcelKnownColors.Red;
                format.LeftBorderColor = ExcelKnownColors.Red;
                format.TopBorderColor = ExcelKnownColors.Red;   //Set color
                format.RightBorderColor = ExcelKnownColors.Red;
    
                //Check whether default border is modified or not.
                Console.WriteLine(format.IsBottomBorderModified);
                Console.WriteLine(format.IsLeftBorderModified);
                Console.WriteLine(format.IsRightBorderModified);
                Console.WriteLine(format.IsTopBorderModified);
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }
    //Output will be
    //False
    //False
    //False
    //False
    //True
    //True
    //True
    //True

    LeftBorderColor

    Gets or sets the left border color from predefined colors (ExcelKnownColors).

    Declaration
    ExcelKnownColors LeftBorderColor { get; set; }
    Property Value
    Type
    ExcelKnownColors
    Examples

    To set left border color for the cell within conditional formatting range from the ExcelKnownColors enumeration we use LeftBorderColor property. RGB color can be applied by using LeftBorderColorRGB property.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Set Border Style.
                format.BottomBorderStyle = ExcelLineStyle.Thick;
                format.LeftBorderStyle = ExcelLineStyle.Thick;
                format.TopBorderStyle = ExcelLineStyle.Thick;
                format.RightBorderStyle = ExcelLineStyle.Thick;
    
                //Set Border color.
                format.BottomBorderColor = ExcelKnownColors.Red;
                format.LeftBorderColor = ExcelKnownColors.Red;  //Set color
                format.TopBorderColor = ExcelKnownColors.Red;
                format.RightBorderColor = ExcelKnownColors.Red;
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    LeftBorderColorRGB

    Gets or sets the left border RGB color.

    Declaration
    Color LeftBorderColorRGB { get; set; }
    Property Value
    Type
    System.Drawing.Color
    Examples

    To set left border color for the cell within conditional formatting range from the RGB colors we use LeftBorderColorRGB property. Colors from ExcelKnownColors enumeration can be applied by using LeftBorderColor property.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Apply Conditional format.
                format.BottomBorderStyle = ExcelLineStyle.Thick;
                format.LeftBorderStyle = ExcelLineStyle.Thick;
                format.TopBorderStyle = ExcelLineStyle.Thick;
                format.RightBorderStyle = ExcelLineStyle.Thick;
    
                //Set Border color.
                format.BottomBorderColorRGB = System.Drawing.Color.Red;
                format.LeftBorderColorRGB = System.Drawing.Color.Red;  //Set RGB color.
                format.RightBorderColorRGB = System.Drawing.Color.Red;
                format.TopBorderColorRGB = System.Drawing.Color.Red; 
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    LeftBorderStyle

    Gets or sets the left border line style.

    Declaration
    ExcelLineStyle LeftBorderStyle { get; set; }
    Property Value
    Type
    ExcelLineStyle
    Examples

    To change the default line style of left border we set a line style from ExcelLineStyle enumeration to LeftBorderStyle property. Here for example, we set Thick to LeftBorderStyle property.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Set Border Style.
                format.BottomBorderStyle = ExcelLineStyle.Thick;
                format.LeftBorderStyle = ExcelLineStyle.Thick;  //Set LineStyle
                format.TopBorderStyle = ExcelLineStyle.Thick;
                format.RightBorderStyle = ExcelLineStyle.Thick;
    
                //Set Border color.
                format.BottomBorderColor = ExcelKnownColors.Red;
                format.LeftBorderColor = ExcelKnownColors.Red;
                format.TopBorderColor = ExcelKnownColors.Red;
                format.RightBorderColor = ExcelKnownColors.Red;
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    NumberFormat

    Gets or sets number format of the conditional format rule.

    Declaration
    string NumberFormat { get; set; }
    Property Value
    Type
    System.String
    Examples

    Number format for the values in the cells within the conditional formatting range can be set by using NumberFormat property. Here for example, we set "d-mmm" to NumberFormat.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].DateTime = DateTime.Today;
                worksheet["B3"].DateTime = DateTime.Parse("1/18/2015");
                worksheet["B4"].DateTime = DateTime.Today;
                worksheet["B5"].DateTime = DateTime.Today;
                worksheet["B6"].DateTime = DateTime.Parse("1/18/2016");
                worksheet["B7"].DateTime = DateTime.Today;
                worksheet["B8"].DateTime = DateTime.Today;
                worksheet["B9"].DateTime = DateTime.Parse("1/18/2017");
                worksheet["B10"].DateTime = DateTime.Today;
                worksheet["B11"].DateTime = DateTime.Today;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set FormatType.
                format.FormatType = ExcelCFType.TimePeriod;
    
                //Set TimePeriodType. (Condition)
                format.TimePeriodType = CFTimePeriods.ThisMonth;
    
                //Set NumberFormat.
                format.NumberFormat = "d-mmm";
    
                //Set color.
                format.BackColor = ExcelKnownColors.Red;
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    Operator

    Gets or sets the comparison operator for the conditional format.

    Declaration
    ExcelComparisonOperator Operator { get; set; }
    Property Value
    Type
    ExcelComparisonOperator
    Examples

    If conditional formats applied based on the CellValue then the Operator should be used to apply conditional formats based on the value present in the cell. We can choose condition that is required from the ExcelComparisonOperator enumeration. Here for example, we apply borders for the cells which have values between "20" and "60". For Operator we set Between to apply conditional formats for the cells with values within the range "20" and "60".

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
    
                //Set Operator.
                format.Operator = ExcelComparisonOperator.Between;
    
                //Apply Conditional format.
                format.BottomBorderStyle = ExcelLineStyle.Thick;
                format.LeftBorderStyle = ExcelLineStyle.Thick;
                format.TopBorderStyle = ExcelLineStyle.Thick;
                format.RightBorderStyle = ExcelLineStyle.Thick;
    
                //Set Border color.
                format.BottomBorderColor = ExcelKnownColors.Red;
                format.LeftBorderColor = ExcelKnownColors.Red;
                format.TopBorderColor = ExcelKnownColors.Red;
                format.RightBorderColor = ExcelKnownColors.Red;
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    RightBorderColor

    Gets or sets the right border color from predefined colors (ExcelKnownColors).

    Declaration
    ExcelKnownColors RightBorderColor { get; set; }
    Property Value
    Type
    ExcelKnownColors
    Examples

    To set a color from the ExcelKnownColors enumeration we use RightBorderColor property. RGB color can be applied by using RightBorderColorRGB property.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Apply Conditional format.
                format.BottomBorderStyle = ExcelLineStyle.Thick;
                format.LeftBorderStyle = ExcelLineStyle.Thick;
                format.TopBorderStyle = ExcelLineStyle.Thick;
                format.RightBorderStyle = ExcelLineStyle.Thick;
    
                //Set Border color.
                format.BottomBorderColor = ExcelKnownColors.Red;
                format.LeftBorderColor = ExcelKnownColors.Red;
                format.TopBorderColor = ExcelKnownColors.Red;
                format.RightBorderColor = ExcelKnownColors.Red; //Set color.
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    RightBorderColorRGB

    Gets or sets the right border color.

    Declaration
    Color RightBorderColorRGB { get; set; }
    Property Value
    Type
    System.Drawing.Color
    Examples
            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Apply Conditional format.
                format.BottomBorderStyle = ExcelLineStyle.Thick;
                format.LeftBorderStyle = ExcelLineStyle.Thick;
                format.TopBorderStyle = ExcelLineStyle.Thick;
                format.RightBorderStyle = ExcelLineStyle.Thick;
    
                //Set Border color.
                format.BottomBorderColorRGB = System.Drawing.Color.Red;
                format.LeftBorderColorRGB = System.Drawing.Color.Red;
                format.RightBorderColorRGB = System.Drawing.Color.Red; //Set RGB color.
                format.TopBorderColorRGB = System.Drawing.Color.Red; 
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    RightBorderStyle

    Gets or sets the right border line style.

    Declaration
    ExcelLineStyle RightBorderStyle { get; set; }
    Property Value
    Type
    ExcelLineStyle
    Examples

    To change the default line style of right border we set a line style from ExcelLineStyle enumeration to RightBorderStyle property. Here for example, we set Thick to RightBorderStyle property.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Apply Conditional format.
                format.BottomBorderStyle = ExcelLineStyle.Thick;
                format.LeftBorderStyle = ExcelLineStyle.Thick;
                format.TopBorderStyle = ExcelLineStyle.Thick;
                format.RightBorderStyle = ExcelLineStyle.Thick; //Set LineStyle.
    
                //Set Border color.
                format.BottomBorderColor = ExcelKnownColors.Red;
                format.LeftBorderColor = ExcelKnownColors.Red;
                format.TopBorderColor = ExcelKnownColors.Red;
                format.RightBorderColor = ExcelKnownColors.Red;
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    SecondFormula

    Gets the value or expression associated with the second part of a conditional format.

    Declaration
    string SecondFormula { get; set; }
    Property Value
    Type
    System.String
    Remarks

    Relative cell addresses can be used to apply formatting by using FirstFormulaR1C1 and SecondFormulaR1C1. To know more about R1C1 notation refer Using FormulaR1C1 property in Conditional Formats.

    Examples

    To set condition like Between two values are needed. So, we set these values to FirstFormula and SecondFormula properties. Here for example, we apply conditional formats to the cells that has values between "20" and "60".

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Apply Conditional format.
                format.BottomBorderStyle = ExcelLineStyle.Thick;
                format.LeftBorderStyle = ExcelLineStyle.Thick;
                format.TopBorderStyle = ExcelLineStyle.Thick;
                format.RightBorderStyle = ExcelLineStyle.Thick;
    
                //Set Border color.
                format.BottomBorderColor = ExcelKnownColors.Red;
                format.LeftBorderColor = ExcelKnownColors.Red;
                format.TopBorderColor = ExcelKnownColors.Red;
                format.RightBorderColor = ExcelKnownColors.Red;
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    SecondFormulaR1C1

    Gets the value or expression associated with the second part of a conditional format in R1C1 notation.

    Declaration
    string SecondFormulaR1C1 { get; set; }
    Property Value
    Type
    System.String
    Remarks

    To know more about SecondFormulaR1C1 refer Using FormulaR1C1 property in Conditional Formats.

    Examples

    To apply formatting by comparing the relative cell addresses we can use SecondFormulaR1C1. Here for example, we compare the adjacent cell values and apply formatting. SecondFormulaR1C1 can also used along with FirstFormulaR1C1 to add additional condition.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 1;
                worksheet["B3"].Number = 2;
                worksheet["B4"].Number = 3;
                worksheet["B5"].Number = 4;
                worksheet["B6"].Number = 5;
                worksheet["B7"].Number = 6;
                worksheet["B8"].Number = 7;
                worksheet["B9"].Number = 8;
                worksheet["B10"].Number = 9;
                worksheet["B11"].Number = 10;
    
                //Data to be compared.
                worksheet["C2"].Number = 11;
                worksheet["C3"].Number = 1;
                worksheet["C4"].Number = 3;
                worksheet["C5"].Number = 2;
                worksheet["C6"].Number = 5;
                worksheet["C7"].Number = 3;
                worksheet["C8"].Number = 7;
                worksheet["C9"].Number = 9;
                worksheet["C10"].Number = 19;
                worksheet["C11"].Number = 20;
    
    
                //Create instance of IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
    
                //Add condition.
                IConditionalFormat format = formats.AddCondition();
    
                //Set FormatType.
                format.FormatType = ExcelCFType.Formula;
    
                //Set Formula.
                format.SecondFormulaR1C1 = "=RC>RC[1]";
    
                //Set color.
                format.BackColor = ExcelKnownColors.Red;
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    StopIfTrue

    Gets or sets a boolean value that determines if additional formatting rules on the cell should be evaluated if the current rule evaluates to True.

    Declaration
    bool StopIfTrue { get; set; }
    Property Value
    Type
    System.Boolean
    Examples

    StopIfTrue is set to False by default. If we set it to True formatting is applied based on the priority. If a higher priority rule for particular cells gets satisfied then the preceding rules are not applied for those cells.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
                IConditionalFormat format2 = formats.AddCondition();
                IConditionalFormat format3 = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Set Color.
                format.BackColor = ExcelKnownColors.Red;
    
                //Set StopIfTrue.
                format.StopIfTrue = true;
    
                //Set Condition.
                format2.FirstFormula = "30";
                format2.SecondFormula = "70";
                format2.Operator = ExcelComparisonOperator.Between;
    
                 //Set Color.            
                format2.BackColor = ExcelKnownColors.Blue;
    
                //Set StopIfTrue. 
                format2.StopIfTrue = true;
    
                //Set Condition.            
                format3.FirstFormula = "30";
                format3.SecondFormula = "50";
                format3.Operator = ExcelComparisonOperator.Between;
    
                 //Set Color.            
                format3.BackColor = ExcelKnownColors.Green;
    
                //Set StopIfTrue.
                format3.StopIfTrue = true;
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    Text

    Gets or sets the text value used in SpecificText conditional formatting rule. The default value is null.

    Declaration
    string Text { get; set; }
    Property Value
    Type
    System.String
    Examples

    To apply conditional formats based on the text present in the cells within conditional formatting range, then we set the text we want to apply formatting to Text and set SpecificText to FormatType. Here for example, we set "Test" to Text and set SpecificText to FormatType.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Text = "Test";
                worksheet["B3"].Text = "Test";
                worksheet["B4"].Text = "Text";
                worksheet["B5"].Text = "Text";
                worksheet["B6"].Text = "Test";
                worksheet["B7"].Text = "Text";
                worksheet["B8"].Text = "Test";
                worksheet["B9"].Text = "Text";
                worksheet["B10"].Text = "Text";
                worksheet["B11"].Text = "Test";
    
                //Create instance of IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
    
                //Add condition.
                IConditionalFormat format = formats.AddCondition();
    
                //Set FormatType.
                format.FormatType = ExcelCFType.SpecificText;
    
                //Set color.
                format.BackColor = ExcelKnownColors.Red;
    
                //Set Text.
                format.Text = "Test";
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
                Console.ReadKey();
            }

    TimePeriodType

    Gets or sets one of the constants of CFTimePeriods enumeration which represents the type of the time period.

    Declaration
    CFTimePeriods TimePeriodType { get; set; }
    Property Value
    Type
    CFTimePeriods
    Remarks

    The applicable time period in a "date occurring�" conditional formatting rule. This can be ignored if FormatType is not equal to TimePeriod.

    Examples

    For TimePeriod conditional formatting type we can set the criteria by setting a value from CFTimePeriods enumeration to TimePeriodType property. Here for example, we set ThisMonth to TimePeriodType property.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].DateTime = DateTime.Today;
                worksheet["B3"].DateTime = DateTime.Parse("1/18/2015");
                worksheet["B4"].DateTime = DateTime.Today;
                worksheet["B5"].DateTime = DateTime.Today;
                worksheet["B6"].DateTime = DateTime.Parse("1/18/2016");
                worksheet["B7"].DateTime = DateTime.Today;
                worksheet["B8"].DateTime = DateTime.Today;
                worksheet["B9"].DateTime = DateTime.Parse("1/18/2017");
                worksheet["B10"].DateTime = DateTime.Today;
                worksheet["B11"].DateTime = DateTime.Today;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set FormatType.
                format.FormatType = ExcelCFType.TimePeriod;
    
                //Set TimePeriodType. (Condition)
                format.TimePeriodType = CFTimePeriods.ThisMonth;
    
                //Set NumberFormat.
                format.NumberFormat = "d-mmm";
    
                //Set color.
                format.BackColor = ExcelKnownColors.Red;
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    TopBorderColor

    Gets or sets the top border color from predefined colors (ExcelKnownColors).

    Declaration
    ExcelKnownColors TopBorderColor { get; set; }
    Property Value
    Type
    ExcelKnownColors
    Examples

    To set a color from the ExcelKnownColors enumeration we use TopBorderColor property. RGB color can be applied by using TopBorderColorRGB property.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Apply Conditional format.
                format.BottomBorderStyle = ExcelLineStyle.Thick;
                format.LeftBorderStyle = ExcelLineStyle.Thick;
                format.TopBorderStyle = ExcelLineStyle.Thick;
                format.RightBorderStyle = ExcelLineStyle.Thick;
    
                //Set Border color.
                format.BottomBorderColor = ExcelKnownColors.Red;
                format.LeftBorderColor = ExcelKnownColors.Red;
                format.TopBorderColor = ExcelKnownColors.Red;   //Set color
                format.RightBorderColor = ExcelKnownColors.Red;
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    TopBorderColorRGB

    Gets or sets RGB color to top border.

    Declaration
    Color TopBorderColorRGB { get; set; }
    Property Value
    Type
    System.Drawing.Color
    Examples

    To set top border color for the cell within conditional formatting range from the RGB colors we use TopBorderColorRGB property. Colors from ExcelKnownColors enumeration can be applied by using TopBorderColor property.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Apply Conditional format.
                format.BottomBorderStyle = ExcelLineStyle.Thick;
                format.LeftBorderStyle = ExcelLineStyle.Thick;
                format.TopBorderStyle = ExcelLineStyle.Thick;
                format.RightBorderStyle = ExcelLineStyle.Thick;
    
                //Set Border color.
                format.BottomBorderColorRGB = System.Drawing.Color.Red;
                format.LeftBorderColorRGB = System.Drawing.Color.Red;
                format.RightBorderColorRGB = System.Drawing.Color.Red;
                format.TopBorderColorRGB = System.Drawing.Color.Red;   //Set color
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    TopBorderStyle

    Gets or sets the top border line style.

    Declaration
    ExcelLineStyle TopBorderStyle { get; set; }
    Property Value
    Type
    ExcelLineStyle
    Examples

    To change the default line style of top border we set a line style from ExcelLineStyle enumeration to TopBorderStyle property. Here for example, we set Thick to TopBorderStyle property.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Apply Conditional format.
                format.BottomBorderStyle = ExcelLineStyle.Thick;
                format.LeftBorderStyle = ExcelLineStyle.Thick;
                format.TopBorderStyle = ExcelLineStyle.Thick;   //Set LineStyle
                format.RightBorderStyle = ExcelLineStyle.Thick;
    
                //Set Border color.
                format.BottomBorderColor = ExcelKnownColors.Red;
                format.LeftBorderColor = ExcelKnownColors.Red;
                format.TopBorderColor = ExcelKnownColors.Red;
                format.RightBorderColor = ExcelKnownColors.Red;
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    TopBottom

    Gets TopBottom conditional formatting rule. Read-only.

    Declaration
    ITopBottom TopBottom { get; }
    Property Value
    Type
    ITopBottom
    Remarks

    Valid only if FormatType is set to TopBottom.

    Examples

    TopBottom conditional formatting rule can be applied by setting TopBottom to FormatType property. To customize the TopBottom rule we have to change properties of ITopBottom interface. Here for example, we set values to ITopBottom interface properties.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;            
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["A1"].Text = "Top10";
                worksheet["A2"].Number = 10;
                worksheet["A3"].Number = 20;
                worksheet["A4"].Number = 30;
                worksheet["A5"].Number = 40;
                worksheet["A6"].Number = 50;
                worksheet["A7"].Number = 60;
                worksheet["A8"].Number = 70;
                worksheet["A9"].Number = 80;
                worksheet["A10"].Number = 90;
                worksheet["A11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["A2:A11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set FormatType as TopBottom.
                format.FormatType = ExcelCFType.TopBottom;
                ITopBottom topBottom = format.TopBottom;
    
                //Set values the ITopBottom interface properties.
                topBottom.Type = ExcelCFTopBottomType.Bottom;
                topBottom.Percent = false;
                topBottom.Rank = 5;
    
                //Set color.
                format.BackColor = ExcelKnownColors.Red;
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }

    Underline

    Gets or sets the underline type for the conditional format.

    Declaration
    ExcelUnderline Underline { get; set; }
    Property Value
    Type
    ExcelUnderline
    Remarks

    Similar to Underline, we can use IsStrikeThrough to strike out values in the cells within conditional formatting range.

    Examples

    Data in the cells can be given underline with the help of conditional formats. To give underline Underline property should be set to any of the value in the ExcelUnderline enumeration. Here for example, we set Double to Underline property.

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                //Create a worksheet.        
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
    
                //Load data to Apply Conditional Formatting.
                worksheet["B1"].Text = "Conditional Formats";
                worksheet["B2"].Number = 10;
                worksheet["B3"].Number = 20;
                worksheet["B4"].Number = 30;
                worksheet["B5"].Number = 40;
                worksheet["B6"].Number = 50;
                worksheet["B7"].Number = 60;
                worksheet["B8"].Number = 70;
                worksheet["B9"].Number = 80;
                worksheet["B10"].Number = 90;
                worksheet["B11"].Number = 100;
    
                //Create instance of IConditonalFormat and IConditionalFormats.
                IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
                IConditionalFormat format = formats.AddCondition();
    
                //Set Condition.
                format.FirstFormula = "60";
                format.SecondFormula = "20";
                format.Operator = ExcelComparisonOperator.Between;
    
                //Set UnderLine.
                format.Underline = ExcelUnderline.Double;
    
                //Save and Dispose.
                workbook.SaveAs("ConditionalFormats.xlsx");
                workbook.Close();
            }
    Back to top Generated by DocFX
    Copyright © 2001 - 2025 Syncfusion Inc. All Rights Reserved