How to set criteria for icon set conditional formats in Excel using XlsIO?
This article explains how to set criteria for icon set conditional formats in Excel document using WinForms XlsIO.
What are IconSets?
Icon sets present data in three to five categories that are distinguished by a threshold value. Each icon represents a range of values and each cell is annotated with the icon that represents that range.
Code snippet to set icon criteria using XlsIO
IIconSet iconSet = conditionalFormat.IconSet;
iconSet.IconSet = ExcelIconSetType.ThreeSymbols;
//Icon set has 3 criteria of which second and third criteria can be changed.
//The first criterion type should not be changed as it is percent by default.
//The value in its condition changes with respect to the value assigned to second criteria.
iconSet.IconCriteria[1].Type = ConditionValueType.Number;
iconSet.IconCriteria[1].Value = "-1";
iconSet.IconCriteria[2].Type = ConditionValueType.Number;
iconSet.IconCriteria[2].Value = "1";
Rule of conditional formatting after setting criteria for icon set conditional formats using XlsIO
1. The first criterion type should not be changed and should be left as “percent” by default.
2. Changing or setting icon criteria for conditional formats holds good for data including negative values as well.
The following C#/VB complete code snippet shows how to set criteria for icon set conditional formats in Excel document using XlsIO.
C#
using Syncfusion.XlsIO;
using System.IO;
namespace ConditionalFormatting
{
class Program
{
static void Main(string[] args)
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Instantiate the excel application object
IApplication application = excelEngine.Excel;
//The workbook is opened
IWorkbook workbook;
//Creating a workbook with a worksheet
workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed
IWorksheet worksheet = workbook.Worksheets[0];
//Adding data into worksheet
worksheet.Range["A1"].Number = -3;
worksheet.Range["A2"].Number = -2;
worksheet.Range["A3"].Number = -1;
worksheet.Range["A4"].Number = 0;
worksheet.Range["A5"].Number = 1;
worksheet.Range["A6"].Number = 2;
worksheet.Range["A7"].Number = 3;
worksheet.Range["B1"].Number = 3;
worksheet.Range["B2"].Number = 2;
worksheet.Range["B3"].Number = 1;
worksheet.Range["B4"].Number = 0;
worksheet.Range["B5"].Number = -1;
worksheet.Range["B6"].Number = -2;
worksheet.Range["B7"].Number = -3;
//Applying conditional formatting to worksheet
IConditionalFormats conditionalFormats = worksheet.Range["A1:B7"].ConditionalFormats;
IConditionalFormat conditionalFormat = conditionalFormats.AddCondition();
//Appyling icon set
conditionalFormat.FormatType = ExcelCFType.IconSet;
IIconSet iconSet = conditionalFormat.IconSet;
iconSet.IconSet = ExcelIconSetType.ThreeSymbols;
//Icon set has 3 criteria of which second and third criteria can be changed.
//The first criterion type should not be changed as it is percent by default.
//The value in its condition changes with respect to the value assigned to the second criteria.
iconSet.IconCriteria[1].Type = ConditionValueType.Number;
iconSet.IconCriteria[1].Value = "-1";
iconSet.IconCriteria[2].Type = ConditionValueType.Number;
iconSet.IconCriteria[2].Value = "1";
//Save the workbook
Stream stream = File.Create("Output.xlsx");
workbook.SaveAs(stream);
}
}
}
}
VB
Imports Syncfusion.XlsIO
Imports System.IO
Namespace ConditionalFormatting
Class Program
Private Shared Sub Main(ByVal args() As String)
Dim excelEngine As ExcelEngine = New ExcelEngine
'Instantiate the excel application object
Dim application As IApplication = excelEngine.Excel
'The workbook is opened
Dim workbook As IWorkbook
'Creating a workbook with a worksheet
workbook = application.Workbooks.Create(1)
'The first worksheet object in the worksheets collection is accessed
Dim worksheet As IWorksheet = workbook.Worksheets(0)
'Adding data into worksheet
worksheet.Range("A1").Number = -3
worksheet.Range("A2").Number = -2
worksheet.Range("A3").Number = -1
worksheet.Range("A4").Number = 0
worksheet.Range("A5").Number = 1
worksheet.Range("A6").Number = 2
worksheet.Range("A7").Number = 3
worksheet.Range("B1").Number = 3
worksheet.Range("B2").Number = 2
worksheet.Range("B3").Number = 1
worksheet.Range("B4").Number = 0
worksheet.Range("B5").Number = -1
worksheet.Range("B6").Number = -2
worksheet.Range("B7").Number = -3
'Applying conditional formatting to worksheet
Dim conditionalFormats As IConditionalFormats = worksheet.Range("A1:B7").ConditionalFormats
Dim conditionalFormat As IConditionalFormat = conditionalFormats.AddCondition
'Appyling icon set
conditionalFormat.FormatType = ExcelCFType.IconSet
Dim iconSet As IIconSet = conditionalFormat.IconSet
iconSet.IconSet = ExcelIconSetType.ThreeSymbols
'Icon set has 3 criteria of which second and third criteria can be changed.
'The first criterion type should not be changed as it is percent by default.
'The value in its condition changes with respect to the value assigned to the second criteria.
iconSet.IconCriteria(1).Type = ConditionValueType.Number
iconSet.IconCriteria(1).Value = "-1"
iconSet.IconCriteria(2).Type = ConditionValueType.Number
iconSet.IconCriteria(2).Value = "1"
'Save the workbook
Dim stream As Stream = File.Create("Output.xlsx")
workbook.SaveAs(stream)
End Sub
End Class
End Namespace
Conclusion
I hope you enjoyed learning about how to use up down bar for Excel chart in C#, VB.NET.
You can refer to our WinForms Excel’s feature tour page to know about its other groundbreaking feature representations. You can also explore our WinForms Excel documentation to understand how to present and manipulate data.
For current customers, you can check out our WinForms components from the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our WinForms Excel and other WinForms components.
If you have any queries or require clarifications, please let us know in comments below. You can also contact us through our support forums, Direct-Trac, or feedback portal. We are always happy to assist you!
