Interface IColorConditionValue
Represents a condition value for color scale conditional format.
Namespace: Syncfusion.XlsIO
Assembly: Syncfusion.XlsIO.NET.dll
Syntax
public interface IColorConditionValue : IConditionValueProperties
FormatColorRGB
Gets or sets the threshold of a color scale conditional format.
Declaration
Color FormatColorRGB { get; set; }Property Value
| Type |
|---|
| Color |
Remarks
The default color scale conditions can be customized by changing Type, Value and Operator Properties. To know more about advanced conditional formatting types refer this link.
Examples
The criteria for applying ColorScale can be accessed from the Criteria Property. Since the default size of the Criteria list is 2, In our example we access those two IColorConditionValue objects and set values for FormatColorRGB. Here we set each object a color and haven't mentioned any additional condition, So the default conditions will apply shades of these two colors depending on the values in the cells and set cell colors.
//Create a worksheet.
ExcelEngine excelEngine = new ExcelEngine();
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 = "ColorScale";
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 ColorScale.
format.FormatType = ExcelCFType.ColorScale;
IColorScale colorScale = format.ColorScale;
//Set Color for FormatColorRGB Property.
colorScale.Criteria[0].FormatColorRGB = System.Drawing.Color.White;
colorScale.Criteria[1].FormatColorRGB = System.Drawing.Color.Red;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
excelEngine.Dispose();Operator
Gets or sets the format operator of condition value for color scale conditional format.
Declaration
ConditionalFormatOperator Operator { get; set; }Property Value
| Type |
|---|
| ConditionalFormatOperator |
Remarks
To know more about customizing the color scale refer Value, Type properties. To check other types of conditional formatting types refer this link.
Examples
By default the Operator property is set to GreaterThanorEqualTo, This will set color scale to the cells above the specified threshold value starting from it. GreaterThan can be used to change the color scale to set colors above the specified threshold value.
//Create a worksheet.
ExcelEngine excelEngine = new ExcelEngine();
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 = "ColorScale";
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 ColorScale.
format.FormatType = ExcelCFType.ColorScale;
IColorScale colorScale = format.ColorScale;
//Set threshold value and type
colorScale.Criteria[0].Value = "70";
colorScale.Criteria[0].Type = ConditionValueType.Percent;
//Set operator to GreaterThan
colorScale.Criteria[0].Operator = ConditionalFormatOperator.GreaterThan;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
excelEngine.Dispose();Type
Gets or sets the ConditionValueType for the color scale conditional format.
Declaration
ConditionValueType Type { get; set; }Property Value
| Type |
|---|
| ConditionValueType |
Examples
The Type property is used to change the ConditionValueType of the IColorConditionValue object. Default values will be LowestValue and HighestValue if the SetConditionCount(Int32) is not set. If the count is set to 3, then the values will be LowestValue, Percentile and HighestValue. In our example we are change the default value of first object in list to Percentile and set Value to 60.
//Create a worksheet.
ExcelEngine excelEngine = new ExcelEngine();
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 = "ColorScale";
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 ColorScale.
format.FormatType = ExcelCFType.ColorScale;
IColorScale colorScale = format.ColorScale;
//Set Type and Value
colorScale.Criteria[0].Type = ConditionValueType.Percent;
colorScale.Criteria[0].Value = "60";
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
excelEngine.Dispose();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
The default color applied in the color scale can be changed by changing the FormatColorRGB property. To know more about the conditional formatting types refer this link.
Examples
The color bars use Threshold value to set colors to the cells based on the values present in the cells. It can be customized using Value property. For default settings, the threshold will be average value of the data present in the cells. Here in our example, we set the threshold value to 80;
//Create a worksheet.
ExcelEngine excelEngine = new ExcelEngine();
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 = "ColorScale";
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 ColorScale.
format.FormatType = ExcelCFType.ColorScale;
IColorScale colorScale = format.ColorScale;
//Set 3 as count for color scale
colorScale.SetConditionCount(3);
//Change Threshold value for 2nd object in Critera list.
colorScale.Criteria[1].Value = "80";
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
excelEngine.Dispose();