Interface IConditionValue

Represents a condition value for IDataBar, IColorScale and IIconSet conditions.

Namespace: Syncfusion.XlsIO

Assembly: Syncfusion.XlsIO.Portable.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();
        }