Interface IIconSet
Represents a set of icons that are used in an icon set conditional formatting rule.
Namespace: Syncfusion.XlsIO
Assembly: Syncfusion.XlsIO.UWP.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();
}