Interface IConditionValue
Represents a condition value for IDataBar, IColorScale and IIconSet conditions.
Namespace: Syncfusion.XlsIO
Assembly: Syncfusion.XlsIO.NET.dll
Syntax
public interface IConditionValue
Properties
Operator
Gets or sets the operator for the threshold values in the conditional format.
Declaration
ConditionalFormatOperator Operator { get; set; }
Property Value
Type |
---|
ConditionalFormatOperator |
Remarks
To set the operator for IColorScale refer Operator property.
Examples
By default Operator is set to GreaterThanorEqualTo and it can be changed to GreaterThan. In our example, we change the Operator to GreaterThan.
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();
}
Similar to IconSets, DataBars also have Operator property set to GreaterThanorEqualTo by default which can be changed to GreaterThan.
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 = "DataBar";
worksheet["A2"].Number = 10;
worksheet["A3"].Number = 20;
worksheet["A4"].Number = 30;
worksheet["A5"].Number = 40;
worksheet["A6"].Number = 50;
worksheet["A7"].Number = 60;
worksheet["A8"].Number = 70;
worksheet["A9"].Number = 80;
worksheet["A10"].Number = 90;
worksheet["A11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["A2:A11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set FormatType as DataBar.
format.FormatType = ExcelCFType.DataBar;
IDataBar dataBar = format.DataBar;
//Set MaxPoint for DataBar
dataBar.MaxPoint.Value = "70";
dataBar.MaxPoint.Type = ConditionValueType.Percent;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}
Type
Gets or sets a condition value which specifies how the threshold values for a IDataBar, IColorScale and IIconSet conditional format are determined.
Declaration
ConditionValueType Type { get; set; }
Property Value
Type |
---|
ConditionValueType |
Remarks
To set the ConditionValueType for IColorScale refer Type property.
Examples
By default for IconSets the Type property is set to Percent. Here for example, we set Percentile to Type 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 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();
}
For DataBars the Type is set to Automatic by default. Here for example, we set Percent to Type 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 = "DataBar";
worksheet["A2"].Number = 10;
worksheet["A3"].Number = 20;
worksheet["A4"].Number = 30;
worksheet["A5"].Number = 40;
worksheet["A6"].Number = 50;
worksheet["A7"].Number = 60;
worksheet["A8"].Number = 70;
worksheet["A9"].Number = 80;
worksheet["A10"].Number = 90;
worksheet["A11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["A2:A11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set FormatType as DataBar.
format.FormatType = ExcelCFType.DataBar;
IDataBar dataBar = format.DataBar;
//Set MinPoint for DataBar
dataBar.MinPoint.Value = "30";
dataBar.MinPoint.Type = ConditionValueType.Percent;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}
Value
Gets or sets the shortest bar or longest bar threshold value for a data bar conditional format.
Declaration
string Value { get; set; }
Property Value
Type |
---|
System.String |
Remarks
To set the value for IColorScale refer Value property.
Examples
IconSets use IconCriteria list property to set icons, So each IConditionValue object has individual Value property. By default the IconCriteria list property holds three objects and those object's Value property set "0", "33" and "67" respectively. In our example, we set "20" and "70" to Value property of second and third objects in IconCriteria list 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 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();
}
For DataBars the default value of Value property is set to "0". This property is can be changed by getting MinPoint or MaxPoint properties. These two properties define the shortest and longest bars of DataBar. For example, we have set the Value of MinPoint to "30".
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 = "DataBar";
worksheet["A2"].Number = 10;
worksheet["A3"].Number = 20;
worksheet["A4"].Number = 30;
worksheet["A5"].Number = 40;
worksheet["A6"].Number = 50;
worksheet["A7"].Number = 60;
worksheet["A8"].Number = 70;
worksheet["A9"].Number = 80;
worksheet["A10"].Number = 90;
worksheet["A11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["A2:A11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set FormatType as DataBar.
format.FormatType = ExcelCFType.DataBar;
IDataBar dataBar = format.DataBar;
//Set MinPoint for DataBar
dataBar.MinPoint.Value = "30";
dataBar.MinPoint.Type = ConditionValueType.Percent;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}