Icon Sets in Conditional Formatting

24 Jul 20269 minutes to read

Icon sets annotate each cell with an icon chosen from three to five categories that are distinguished by threshold values. Each icon represents a range of values, and each cell is annotated with the icon whose range contains the cell value.

The following code example illustrates how to apply icon sets using the IIconSet interface in XlsIO. Key members are:

  • IconSet – selects the icon family using the ExcelIconSetType enumerator (for example, ThreeSymbols, ThreeFlags, ThreeSigns, FourRating, FiveBoxes).
  • IconCriteria – the array of IIconCriteria (3, 4, or 5 entries depending on the icon family) that defines each icon’s threshold.
  • ShowIconOnly – when true, hides the cell value and shows only the icon.
  • ReverseIconOrder – when true, reverses the order in which the icons are assigned.

NOTE

Icon-set conditional formatting is supported in Excel 2007 and later formats (.xlsx, .xlsm).

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	IWorkbook workbook = application.Workbooks.Open(Path.GetFullPath(@"Data/InputTemplate.xlsx"), ExcelOpenType.Automatic);
	IWorksheet worksheet = workbook.Worksheets[0];

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

	//Apply a three-symbol icon set and hide the cell values
	iconSet.IconSet = ExcelIconSetType.ThreeSymbols;
	iconSet.IconCriteria[1].Type = ConditionValueType.Percent;
	iconSet.IconCriteria[1].Value = "50";
	iconSet.IconCriteria[2].Type = ConditionValueType.Percent;
	iconSet.IconCriteria[2].Value = "75";
	iconSet.ShowIconOnly = true;

	#region Save
	//Saving the workbook
	workbook.SaveAs(Path.GetFullPath("Output/Output.xlsx"));
	#endregion
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Open("InputTemplate.xlsx");
    IWorksheet worksheet = workbook.Worksheets[0];

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

    //Apply a three-symbol icon set and hide the cell values
    iconSet.IconSet = ExcelIconSetType.ThreeSymbols;
    iconSet.IconCriteria[1].Type = ConditionValueType.Percent;
    iconSet.IconCriteria[1].Value = "50";
    iconSet.IconCriteria[2].Type = ConditionValueType.Percent;
    iconSet.IconCriteria[2].Value = "75";
    iconSet.ShowIconOnly = true;

    //Saving the workbook
    workbook.SaveAs("Output.xlsx");
}
Using excelEngine As New ExcelEngine()
    Dim application As IApplication = excelEngine.Excel
    application.DefaultVersion = ExcelVersion.Xlsx
    Dim workbook As IWorkbook = application.Workbooks.Open("InputTemplate.xlsx")
    Dim worksheet As IWorksheet = workbook.Worksheets(0)

    ' Create icon sets for the data in specified range
    Dim conditionalFormats As IConditionalFormats = worksheet.Range("E7:E46").ConditionalFormats
    Dim conditionalFormat As IConditionalFormat = conditionalFormats.AddCondition()
    conditionalFormat.FormatType = ExcelCFType.IconSet
    Dim iconSet As IIconSet = conditionalFormat.IconSet

    ' Apply a three-symbol icon set and hide the cell values
    iconSet.IconSet = ExcelIconSetType.ThreeSymbols
    iconSet.IconCriteria(1).Type = ConditionValueType.Percent
    iconSet.IconCriteria(1).Value = "50"
    iconSet.IconCriteria(2).Type = ConditionValueType.Percent
    iconSet.IconCriteria(2).Value = "75"
    iconSet.ShowIconOnly = True

    ' Saving the workbook
    workbook.SaveAs("Output.xlsx")
End Using

A complete working example to apply Icon sets in C# is present on this GitHub page.

Custom Icon Sets

You can customize the icon set by changing the IconSet, Index, and Operator properties of each icon criterion. To access these properties, cast each IconCriteria entry to IIconConditionValue. The Index is the zero-based position of the icon within the selected IconSet family (for example, Index = 0 selects the first icon in that family, Index = 3 the fourth).

Custom icon sets can be created and customized in XlsIO as follows.

// Create icon sets for the data in specified range
IConditionalFormats conditionalFormats = sheet.Range["H1:K6"].ConditionalFormats;
IConditionalFormat conditionalFormat = conditionalFormats.AddCondition();
conditionalFormat.FormatType = ExcelCFType.IconSet;
IIconSet iconSet = conditionalFormat.IconSet;
iconSet.IconSet = ExcelIconSetType.ThreeFlags;

IIconConditionValue iconValue1 = iconSet.IconCriteria[0] as IIconConditionValue;
iconValue1.IconSet = ExcelIconSetType.FiveBoxes;
iconValue1.Index = 3;
iconValue1.Type = ConditionValueType.Percent;
iconValue1.Value = "25";
iconValue1.Operator = ConditionalFormatOperator.GreaterThan;

IIconConditionValue iconValue2 = iconSet.IconCriteria[1] as IIconConditionValue;
iconValue2.IconSet = ExcelIconSetType.ThreeSigns;
iconValue2.Index = 2;
iconValue2.Type = ConditionValueType.Percent;
iconValue2.Value = "50";
iconValue2.Operator = ConditionalFormatOperator.GreaterThan;

IIconConditionValue iconValue3 = iconSet.IconCriteria[2] as IIconConditionValue;
iconValue3.IconSet = ExcelIconSetType.FourRating;
iconValue3.Index = 0;
iconValue3.Type = ConditionValueType.Percent;
iconValue3.Value = "75";
iconValue3.Operator = ConditionalFormatOperator.GreaterThan;
// Create icon sets for the data in specified range
IConditionalFormats conditionalFormats = sheet.Range["H1:K6"].ConditionalFormats;
IConditionalFormat conditionalFormat = conditionalFormats.AddCondition();
conditionalFormat.FormatType = ExcelCFType.IconSet;
IIconSet iconSet = conditionalFormat.IconSet;
iconSet.IconSet = ExcelIconSetType.ThreeFlags;

IIconConditionValue iconValue1 = iconSet.IconCriteria[0] as IIconConditionValue;
iconValue1.IconSet = ExcelIconSetType.FiveBoxes;
iconValue1.Index = 3;
iconValue1.Type = ConditionValueType.Percent;
iconValue1.Value = "25";
iconValue1.Operator = ConditionalFormatOperator.GreaterThan;

IIconConditionValue iconValue2 = iconSet.IconCriteria[1] as IIconConditionValue;
iconValue2.IconSet = ExcelIconSetType.ThreeSigns;
iconValue2.Index = 2;
iconValue2.Type = ConditionValueType.Percent;
iconValue2.Value = "50";
iconValue2.Operator = ConditionalFormatOperator.GreaterThan;

IIconConditionValue iconValue3 = iconSet.IconCriteria[2] as IIconConditionValue;
iconValue3.IconSet = ExcelIconSetType.FourRating;
iconValue3.Index = 0;
iconValue3.Type = ConditionValueType.Percent;
iconValue3.Value = "75";
iconValue3.Operator = ConditionalFormatOperator.GreaterThan;
Dim conditionalFormats As IConditionalFormats = sheet.Range("H1:K6").ConditionalFormats
Dim conditionalFormat As IConditionalFormat = conditionalFormats.AddCondition()
conditionalFormat.FormatType = ExcelCFType.IconSet
Dim iconSet As IIconSet = conditionalFormat.IconSet
iconSet.IconSet = ExcelIconSetType.ThreeFlags

Dim iconValue1 As IIconConditionValue = CType(iconSet.IconCriteria(0), IIconConditionValue)
iconValue1.IconSet = ExcelIconSetType.FiveBoxes
iconValue1.Index = 3
iconValue1.Type = ConditionValueType.Percent
iconValue1.Value = "25"
iconValue1.Operator = ConditionalFormatOperator.GreaterThan

Dim iconValue2 As IIconConditionValue = CType(iconSet.IconCriteria(1), IIconConditionValue)
iconValue2.IconSet = ExcelIconSetType.ThreeSigns
iconValue2.Index = 2
iconValue2.Type = ConditionValueType.Percent
iconValue2.Value = "50"
iconValue2.Operator = ConditionalFormatOperator.GreaterThan

Dim iconValue3 As IIconConditionValue = CType(iconSet.IconCriteria(2), IIconConditionValue)
iconValue3.IconSet = ExcelIconSetType.FourRating
iconValue3.Index = 0
iconValue3.Type = ConditionValueType.Percent
iconValue3.Value = "75"
iconValue3.Operator = ConditionalFormatOperator.GreaterThan

NOTE

The custom icon-set feature is available in newer XlsIO versions with backward compatibility for advanced conditional formatting.