Interface IConditionalFormats

Represents a collection of conditional formats.

Inherited Members

IOptimizedUpdate.BeginUpdate()

IOptimizedUpdate.EndUpdate()

IParentApplication.Application

IParentApplication.Parent

Namespace: Syncfusion.XlsIO

Assembly: Syncfusion.XlsIO.NET.dll

Syntax

public interface IConditionalFormats : IEnumerable, IParentApplication, IOptimizedUpdate

Properties

Count

Gets the number of conditional formats in the collection. Read-only.

Declaration

int Count { get; }

Property Value

Type
System.Int32

Examples

Count returns the number of conditions applied. Here for example, we check Count value before and after applying three conditions.

        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;

            format2.FirstFormula = "30";
            format2.SecondFormula = "70";
            format2.Operator = ExcelComparisonOperator.Between;

            //Set color.
            format2.BackColor = ExcelKnownColors.Blue;

            format3.FirstFormula = "30";
            format3.SecondFormula = "50";
            format3.Operator = ExcelComparisonOperator.Between;

            //Set color.
            format3.BackColor = ExcelKnownColors.Green;

            //Check Count.
            Console.WriteLine(formats.Count);

            //Save and Dispose.
            workbook.SaveAs("ConditionalFormats.xlsx");
            workbook.Close();
        }

//Output will be
//3

Item[Int32]

Gets a conditional format of the specified index from the collection. Read-only.

Declaration

IConditionalFormat this[int index] { get; }

Parameters

Type	Name	Description
System.Int32	index

Property Value

Type
IConditionalFormat

Examples

The following code illustrates how to access the first condition from IConditionalFormats.

        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;

            //Add condition.
            IConditionalFormat format = formats.AddCondition();

            //Set Condition. (Access the condition using index).
            formats[0].FirstFormula = "60";
            formats[0].SecondFormula = "20";
            formats[0].Operator = ExcelComparisonOperator.Between;

            //Set color.
            formats[0].BackColor = ExcelKnownColors.Red;

            //Save and Dispose.
            workbook.SaveAs("ConditionalFormats.xlsx");
            workbook.Close();
        }

Methods

AddCondition()

Adds a condition format to the collection.

Declaration

IConditionalFormat AddCondition()

Returns

Type	Description
IConditionalFormat	Returns the newly added IConditionalFormat instance.

Examples

To add a condition to the conditional formats we use AddCondition() method. It adds and returns a new conditional format object, that we can use to set and get conditional format criteria and 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["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;

            format2.FirstFormula = "30";
            format2.SecondFormula = "70";
            format2.Operator = ExcelComparisonOperator.Between;

            //Set color.
            format2.BackColor = ExcelKnownColors.Blue;

            format3.FirstFormula = "30";
            format3.SecondFormula = "50";
            format3.Operator = ExcelComparisonOperator.Between;

            //Set color.
            format3.BackColor = ExcelKnownColors.Green;

            //Check Count.
            Console.WriteLine(formats.Count);

            //Save and Dispose.
            workbook.SaveAs("ConditionalFormats.xlsx");
            workbook.Close();
        }

Remove()

Removes all the conditional formats in the collection.

Declaration

void Remove()

Remarks

To remove a particular condition we can use RemoveAt(Int32) by specifying the index of the conditional format in the collection.

Examples

The following code illustrates how to set the remove all the conditions applied from the conditional formats collection.

        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;

            //Add conditions.
            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 Condition.
            format2.FirstFormula = "30";
            format2.SecondFormula = "70";
            format2.Operator = ExcelComparisonOperator.Between;

            //Set color.
            format2.BackColor = ExcelKnownColors.Blue;

            //Set Condition.
            format3.FirstFormula = "30";
            format3.SecondFormula = "50";
            format3.Operator = ExcelComparisonOperator.Between;

            //Set color.
            format3.BackColor = ExcelKnownColors.Green;

            //Remove conditions.
            formats.Remove();

            //Save and Dispose.
            workbook.SaveAs("ConditionalFormats.xlsx");
            workbook.Close();
        }

RemoveAt(Int32)

Removes the conditional format at the specified index from the collection.

Declaration

void RemoveAt(int index)

Parameters

Type	Name	Description
System.Int32	index	Index of the conditional format to be removed.

Examples

The following code illustrates how to remove a particular condition from the conditional formats collection.

        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;

            //Add conditions.
            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 Condition.
            format2.FirstFormula = "30";
            format2.SecondFormula = "70";
            format2.Operator = ExcelComparisonOperator.Between;

            //Set color.
            format2.BackColor = ExcelKnownColors.Blue;

            //Set Condition.
            format3.FirstFormula = "30";
            format3.SecondFormula = "50";
            format3.Operator = ExcelComparisonOperator.Between;

            //Set color.
            format3.BackColor = ExcelKnownColors.Green;

            //Remove condition.
            formats.RemoveAt(2);

            //Save and Dispose.
            workbook.SaveAs("ConditionalFormats.xlsx");
            workbook.Close();
        }