Interface IIconSet

Represents a set of icons that are used in an icon set conditional formatting rule.

Namespace: Syncfusion.XlsIO

Assembly: Syncfusion.XlsIO.Portable.dll

Syntax

public interface IIconSet

Properties

IconCriteria

Gets IConditionValue collection which represents the set of criteria for icon set conditional formatting rule.

Declaration

IList<IConditionValue> IconCriteria { get; }

Property Value

Type
System.Collections.Generic.IList<IConditionValue>

Remarks

Similar to IconSet conditions DataBars also use IConditionValue objects to set MinPoint and MaxPoint. To know more about advanced conditional formatting types refer this link.

Examples

The IConditionValue objects in the IconCriteria property list sets rules that how the icons should be applied. Default IconSet ThreeArrows has only three icons, so the list will have three objects of type IConditionValue by default. If we change it to an IconSet which has more icons like FiveArrows then the list size increases to five. Here for example, we access the default objects in the IconCriteria property list and set Type, Value and Operator 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 = "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();
        }

IconSet

Gets or sets the type of the icon set conditional formatting.

Declaration

ExcelIconSetType IconSet { get; set; }

Property Value

Type
ExcelIconSetType

Remarks

To know more about advanced conditional formatting type refer this link.

Examples

By default ThreeArrows will be applied when IconSet is applied. The default Type is Percent. From the name we can say ThreeArrows has 3 icons, So the values in the cells are set icons based on their percentage ranges 0 to 33, 33 to 67 and 67 to 100. where 100 is the maximum value in the conditional formatting range. Here for example, we load 10 values and apply conditional formatting of FormatType as IconSet. we set ThreeFlags to IconSet 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 IconSet.
            iconSet.IconSet = ExcelIconSetType.ThreeFlags;

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

PercentileValues

Gets or sets a Boolean value indicating if the thresholds for an icon set conditional format are determined using percentiles. Default value is false.

Declaration

bool PercentileValues { get; set; }

Property Value

Type
System.Boolean

Examples

To change the IconSet criteria to apply icons based on the percentile value of the value present in the cells, we can set PercentileValues property to "True". By default it is set to "False" and the conditions are applied based on percentage values instead of percentile.

        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 PercentileValues.
            iconSet.PercentileValues = true;

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

ReverseOrder

Gets or sets a Boolean value indicating if the order of icons are reversed for an icon set. Default value is false.

Declaration

bool ReverseOrder { get; set; }

Property Value

Type
System.Boolean

Remarks

To know more about advanced conditional formatting types refer this link.

Examples

The icons in the IconSet will be applied in a predefined way (from lowest value to highest). This order of applying icons can be reversed setting ReverseOrder property to "True". By default the ReverseOrder property is set to "False". Here for in our example, we set it

        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 Order of IconSets.
            iconSet.ReverseOrder = true;

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

ShowIconOnly

Gets or sets a Boolean value indicating if only the icon is displayed for an icon set conditional format. Default value is false.

Declaration

bool ShowIconOnly { get; set; }

Property Value

Type
System.Boolean

Remarks

To know more about advanced conditional formatting types refer this link.

Examples

If IconSet is applied then the values in the cells will be shown along with an icon. The values can be made hidden by setting ShowIconOnly 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["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 ShowIconOnly.
            iconSet.ShowIconOnly = true;

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