Interface IConditionalFormat
Represents a conditional format.
Inherited Members
Namespace: Syncfusion.XlsIO
Assembly: Syncfusion.XlsIO.Base.dll
Syntax
public interface IConditionalFormat : IParentApplication, IOptimizedUpdateRemarks
The formatting properties are applied if the conditional formatting rule evaluates to True.
Properties
AboveBelowAverage
Gets AboveBelowAverage conditional formatting rule. Read-only.
Declaration
IAboveBelowAverage AboveBelowAverage { get; }Property Value
| Type | Description |
|---|---|
| IAboveBelowAverage |
Remarks
Valid only if FormatType is set to AboveBelowAverage.
Examples
AboveBelowAverage conditional formatting rule can be applied by setting AboveBelowAverage to FormatType property. To customize the AboveBelowAverage rule we have to change properties of IAboveBelowAverage interface. Here for example, we set values to IAboveBelowAverage interface 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 = "Above Average";
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 AboveBelowAverage.
format.FormatType = ExcelCFType.AboveBelowAverage;
IAboveBelowAverage aboveBelowAverage = format.AboveBelowAverage;
//Set values the IAboveBelowAverage interface properties.
aboveBelowAverage.AverageType = ExcelCFAverageType.AboveStdDev;
aboveBelowAverage.StdDevValue = 1;
//Set color.
format.BackColor = ExcelKnownColors.Red;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}BackColor
Gets or sets the background color from predefined colors (ExcelKnownColors).
Declaration
ExcelKnownColors BackColor { get; set; }Property Value
| Type | Description |
|---|---|
| ExcelKnownColors |
Examples
Background color to cells within the conditional formatting range can be set color from ExcelKnownColors enumeration using BackColor 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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Set Color.
format.BackColor = ExcelKnownColors.Red;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}BackColorRGB
Gets or sets the background RGB color.
Declaration
Color BackColorRGB { get; set; }Property Value
| Type | Description |
|---|---|
| System.Drawing.Color |
Remarks
Similar to BackColorRGB property, we can use BackColor property. In BackColor we can only select a color from ExcelKnownColors enumeration . But, in BackColorRGB we can create RGB color objects by changing RGB values.
Examples
BackColorRGB property is used to set RGB color to the cell's background, if the conditions of conditional formats get satisfied.
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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Set RGB color.
format.BackColorRGB = System.Drawing.Color.FromArgb(255, 0, 0);
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}BottomBorderColor
Gets or sets the bottom border color from predefined colors (ExcelKnownColors).
Declaration
ExcelKnownColors BottomBorderColor { get; set; }Property Value
| Type | Description |
|---|---|
| ExcelKnownColors |
Examples
To set a color from the ExcelKnownColors enumeration we use BottomBorderColor property. RGB color can be applied by using BottomBorderColorRGB 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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Apply Conditional format.
format.BottomBorderStyle = ExcelLineStyle.Thick;
format.LeftBorderStyle = ExcelLineStyle.Thick;
format.TopBorderStyle = ExcelLineStyle.Thick;
format.RightBorderStyle = ExcelLineStyle.Thick;
//Set Border color.
format.BottomBorderColor = ExcelKnownColors.Red; //Set color
format.LeftBorderColor = ExcelKnownColors.Red;
format.TopBorderColor = ExcelKnownColors.Red;
format.RightBorderColor = ExcelKnownColors.Red;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}BottomBorderColorRGB
Gets or sets RGB color to bottom border.
Declaration
Color BottomBorderColorRGB { get; set; }Property Value
| Type | Description |
|---|---|
| System.Drawing.Color |
Examples
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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Apply Conditional format.
format.BottomBorderStyle = ExcelLineStyle.Thick;
format.LeftBorderStyle = ExcelLineStyle.Thick;
format.TopBorderStyle = ExcelLineStyle.Thick;
format.RightBorderStyle = ExcelLineStyle.Thick;
//Set Border color.
format.BottomBorderColorRGB = System.Drawing.Color.Red;
format.LeftBorderColorRGB = System.Drawing.Color.Red;
format.RightBorderColorRGB = System.Drawing.Color.Red;
format.TopBorderColorRGB = System.Drawing.Color.Red;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}BottomBorderStyle
Gets or sets the bottom border line style.
Declaration
ExcelLineStyle BottomBorderStyle { get; set; }Property Value
| Type | Description |
|---|---|
| ExcelLineStyle |
Examples
To change the default line style of bottom border we set a line style from ExcelLineStyle enumeration to BottomBorderStyle property. Here for example, we set Thick to BottomBorderStyle 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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Apply Conditional format.
format.BottomBorderStyle = ExcelLineStyle.Thick; //Set LineStyle
format.LeftBorderStyle = ExcelLineStyle.Thick;
format.TopBorderStyle = ExcelLineStyle.Thick;
format.RightBorderStyle = ExcelLineStyle.Thick;
//Set Border color.
format.BottomBorderColor = ExcelKnownColors.Red;
format.LeftBorderColor = ExcelKnownColors.Red;
format.TopBorderColor = ExcelKnownColors.Red;
format.RightBorderColor = ExcelKnownColors.Red;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}Color
Gets or sets the pattern foreground color from predefined colors (ExcelKnownColors).
Declaration
ExcelKnownColors Color { get; set; }Property Value
| Type | Description |
|---|---|
| ExcelKnownColors |
Examples
Color property is used to set the foreground color for FillPattern based on the condition applied. RGB color can be applied by setting RGB color to ColorRGB property. Here for example, we set DarkHorizontal to FillPattern and Red to Color.
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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Set Pattern.
format.FillPattern = ExcelPattern.DarkHorizontal;
//Set Color.
format.Color = ExcelKnownColors.Red;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}ColorRGB
Gets or sets the pattern foreground RGB color.
Declaration
Color ColorRGB { get; set; }Property Value
| Type | Description |
|---|---|
| System.Drawing.Color |
Examples
ColorRGB property is used to set the foreground RGB color for FillPattern based on the condition applied. Color from ExcelKnownColors can be applied by setting color to Color property. Here for example, we set DarkHorizontal to FillPattern and Red color to ColorRGB.
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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Set Pattern.
format.FillPattern = ExcelPattern.DarkHorizontal;
//Set RGB color.
format.ColorRGB = System.Drawing.Color.FromArgb(255, 0, 0);
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}ColorScale
Gets color scale conditional formatting rule. Read-only.
Declaration
IColorScale ColorScale { get; }Property Value
| Type | Description |
|---|---|
| IColorScale |
Remarks
Valid only if FormatType is set to ColorScale.
Examples
ColorScale can be set by setting ColorScale to FormatType property. To customize criteria for applying color scale we have to set values for properties in IColorScale interface. Here for example, we set values for Criteria property and use SetConditionCount(Int32) method.
//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);
//Set Criteria for applying ColorScale
colorScale.Criteria[0].FormatColorRGB = System.Drawing.Color.White;
colorScale.Criteria[0].Type = ConditionValueType.Percentile;
colorScale.Criteria[0].Value = "0";
colorScale.Criteria[1].FormatColorRGB = System.Drawing.Color.Red;
colorScale.Criteria[1].Type = ConditionValueType.Percentile;
colorScale.Criteria[1].Value = "50";
colorScale.Criteria[2].FormatColorRGB = System.Drawing.Color.Blue;
colorScale.Criteria[2].Type = ConditionValueType.Percentile;
colorScale.Criteria[2].Value = "100";
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
excelEngine.Dispose();DataBar
Gets data bar conditional formatting rule. Read-only.
Declaration
IDataBar DataBar { get; }Property Value
| Type | Description |
|---|---|
| IDataBar |
Remarks
Valid only if FormatType is set to DataBar.
Examples
DataBar can be set by setting DataBar to FormatType property. To customize the criteria for applying data bars we have to change properties of IDataBar interface. Here for example, we set Red color to BarColor 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 Bar color for DataBar
dataBar.BarColor = System.Drawing.Color.Red;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}FillPattern
Gets or sets the fill pattern style.
Declaration
ExcelPattern FillPattern { get; set; }Property Value
| Type | Description |
|---|---|
| ExcelPattern |
Examples
To set pattern for cells within the conditional formatting range FillPattern property can be used. Here for example, we set DarkHorizontal to FillPattern 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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Set Pattern.
format.FillPattern = ExcelPattern.DarkHorizontal;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}FirstFormula
Gets or sets the value or expression associated with the conditional format.
Declaration
string FirstFormula { get; set; }Property Value
| Type | Description |
|---|---|
| System.String |
Remarks
Relative cell addresses can be used to apply formatting by using FirstFormulaR1C1 and SecondFormulaR1C1. To know more about R1C1 notation refer Using FormulaR1C1 property in Conditional Formats.
Examples
FirstFormula property is used to set value that is used for setting conditions. CellValue applies conditional formatting based on the condition from ExcelComparisonOperator enumeration. This requires atleast one value to apply conditional format. Here for example, we set "60" to FirstFormula 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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Apply Conditional format.
format.BottomBorderStyle = ExcelLineStyle.Thick;
format.LeftBorderStyle = ExcelLineStyle.Thick;
format.TopBorderStyle = ExcelLineStyle.Thick;
format.RightBorderStyle = ExcelLineStyle.Thick;
//Set Border color.
format.BottomBorderColor = ExcelKnownColors.Red;
format.LeftBorderColor = ExcelKnownColors.Red;
format.TopBorderColor = ExcelKnownColors.Red;
format.RightBorderColor = ExcelKnownColors.Red;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}FirstFormulaR1C1
Gets or sets the value or expression associated with the conditional format in R1C1 notation.
Declaration
string FirstFormulaR1C1 { get; set; }Property Value
| Type | Description |
|---|---|
| System.String |
Remarks
To know more about FirstFormulaR1C1 refer Using FormulaR1C1 property in Conditional Formats.
Examples
To apply formatting by comparing the relative cell addresses we can use FirstFormulaR1C1. Here for example, we compare the adjacent cell values and apply formatting. SecondFormulaR1C1 can also used along with FirstFormulaR1C1 to add additional condition.
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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 1;
worksheet["B3"].Number = 2;
worksheet["B4"].Number = 3;
worksheet["B5"].Number = 4;
worksheet["B6"].Number = 5;
worksheet["B7"].Number = 6;
worksheet["B8"].Number = 7;
worksheet["B9"].Number = 8;
worksheet["B10"].Number = 9;
worksheet["B11"].Number = 10;
//Data to be compared.
worksheet["C2"].Number = 11;
worksheet["C3"].Number = 1;
worksheet["C4"].Number = 3;
worksheet["C5"].Number = 2;
worksheet["C6"].Number = 5;
worksheet["C7"].Number = 3;
worksheet["C8"].Number = 7;
worksheet["C9"].Number = 9;
worksheet["C10"].Number = 19;
worksheet["C11"].Number = 20;
//Create instance of IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
//Add condition.
IConditionalFormat format = formats.AddCondition();
//Set FormatType.
format.FormatType = ExcelCFType.Formula;
//Set Formula.
format.FirstFormulaR1C1 = "=RC>RC[1]";
//Set color.
format.BackColor = ExcelKnownColors.Red;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}FontColor
Gets or sets the font color from predefined colors (ExcelKnownColors).
Declaration
ExcelKnownColors FontColor { get; set; }Property Value
| Type | Description |
|---|---|
| ExcelKnownColors |
Remarks
Similar to FontColor and FontColorRGB properties, cell colors can be set using BackColor and BackColorRGB properties.
Examples
To set font color for the text in the cells within the conditional formatting range FontColor property can be used. Colors can be selected from the ExcelKnownColors enumeration which has all the colors that are included in Excel by default. FontColorRGB property can be used to set colors in RGB representation.
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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Set Color.
format.FontColor = ExcelKnownColors.Red;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}FontColorRGB
Gets or sets the font color.
Declaration
Color FontColorRGB { get; set; }Property Value
| Type | Description |
|---|---|
| System.Drawing.Color |
Remarks
Similar to FontColor and FontColorRGB properties, cell colors can be set using BackColor and BackColorRGB properties.
Examples
To set font color for the text in the cells within the conditional formatting range FontColorRGB property can be used. Colors can be set by creating Color object by specfifying RGB values or can be selected from predefined RGB color objects. FontColor property can be used to set colors that are available from the ExcelKnownColors enumeration.
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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Set RGB color.
format.FontColorRGB = System.Drawing.Color.FromArgb(255, 0, 0);
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}FormatType
Gets or sets the type of the conditional format.
Declaration
ExcelCFType FormatType { get; set; }Property Value
| Type | Description |
|---|---|
| ExcelCFType |
Remarks
By default the FormatType is set to CellValue.
Examples
Type of conditional formatting to be applied is based on the value set from ExcelCFType enumeration to FormatType property. Here for example, we set TimePeriod to FormatType 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["B1"].Text = "Conditional Formats";
worksheet["B2"].DateTime = DateTime.Today;
worksheet["B3"].DateTime = DateTime.Parse("1/18/2015");
worksheet["B4"].DateTime = DateTime.Today;
worksheet["B5"].DateTime = DateTime.Today;
worksheet["B6"].DateTime = DateTime.Parse("1/18/2016");
worksheet["B7"].DateTime = DateTime.Today;
worksheet["B8"].DateTime = DateTime.Today;
worksheet["B9"].DateTime = DateTime.Parse("1/18/2017");
worksheet["B10"].DateTime = DateTime.Today;
worksheet["B11"].DateTime = DateTime.Today;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set FormatType.
format.FormatType = ExcelCFType.TimePeriod;
//Set TimePeriodType. (Condition)
format.TimePeriodType = CFTimePeriods.ThisMonth;
//Set NumberFormat.
format.NumberFormat = "d-mmm";
//Set color.
format.BackColor = ExcelKnownColors.Red;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}IconSet
Gets icon set conditional formatting rule. Read-only.
Declaration
IIconSet IconSet { get; }Property Value
| Type | Description |
|---|---|
| IIconSet |
Remarks
Valid only if FormatType is set to IconSet.
Examples
IconSet can be set by setting IconSet to FormatType property. To customize the criteria for applying IconSets we have to change properties of IIconSet interface. Here for example, we set values for IconCriteria 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();
}IsBackgroundColorPresent
True if background color is present. otherwise, False.
Declaration
bool IsBackgroundColorPresent { get; set; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Examples
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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Check Background color is present or not.
Console.WriteLine(format.IsBackgroundColorPresent);
//Set Color.
format.BackColor = ExcelKnownColors.Red;
//Check Background color is present or not.
Console.WriteLine(format.IsBackgroundColorPresent);
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}
//Output will be
//False
//TrueIsBold
Gets or sets a boolean value indicating whether the font is bold.
Declaration
bool IsBold { get; set; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Remarks
Similar to IsBold property, we can use IsItalic and IsStrikeThrough properties also.
Examples
To format data in the cells to Bold within the conditional formatting range IsBold property can be used.
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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Set Bold.
format.IsBold = true;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}IsBorderFormatPresent
Declaration
bool IsBorderFormatPresent { get; set; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
IsBottomBorderModified
True if bottom border line style is modified. otherwise, False.
Declaration
bool IsBottomBorderModified { get; set; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Examples
We use IsBottomBorderModified property to check whether the bottom border is modifed or not. Returns True if the border is modifed else returns False.
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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Check whether default border is modified or not.
Console.WriteLine(format.IsBottomBorderModified);
Console.WriteLine(format.IsLeftBorderModified);
Console.WriteLine(format.IsRightBorderModified);
Console.WriteLine(format.IsTopBorderModified);
//Apply Conditional format.
format.BottomBorderStyle = ExcelLineStyle.Thick; //Set LineStyle
format.LeftBorderStyle = ExcelLineStyle.Thick;
format.TopBorderStyle = ExcelLineStyle.Thick;
format.RightBorderStyle = ExcelLineStyle.Thick;
//Set Border color.
format.BottomBorderColor = ExcelKnownColors.Red; //Set color
format.LeftBorderColor = ExcelKnownColors.Red;
format.TopBorderColor = ExcelKnownColors.Red;
format.RightBorderColor = ExcelKnownColors.Red;
//Check whether default border is modified or not.
Console.WriteLine(format.IsBottomBorderModified);
Console.WriteLine(format.IsLeftBorderModified);
Console.WriteLine(format.IsRightBorderModified);
Console.WriteLine(format.IsTopBorderModified);
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}
//Output will be
//False
//False
//False
//False
//True
//True
//True
//TrueIsFontColorPresent
True if font color is present. otherwise, False.
Declaration
bool IsFontColorPresent { get; set; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Examples
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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Check Font color is present or not.
Console.WriteLine(format.IsFontColorPresent);
//Set Color.
format.FontColor = ExcelKnownColors.Red;
//Check Font color is present or not.
Console.WriteLine(format.IsFontColorPresent);
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}
//Output will be
//False
//TrueIsFontFormatPresent
True if the conditional format contains font formatting. otherwise, False.
Declaration
bool IsFontFormatPresent { get; set; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Examples
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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Check font is formatted or not.
Console.WriteLine(format.IsFontFormatPresent);
//Set Color.
format.FontColor = ExcelKnownColors.Red;
//Check font is formatted or not.
Console.WriteLine(format.IsFontFormatPresent);
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}
//Output will be
//False
//TrueIsItalic
Gets or sets a boolean value indicating whether the font is italic.
Declaration
bool IsItalic { get; set; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Remarks
Similar to IsItalic property, we can use IsBold and IsStrikeThrough properties also.
Examples
To format the data in the cells to Italic within the conditional formatting range IsItalic property can be used.
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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Set Color.
format.IsItalic = true;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}IsLeftBorderModified
True if left border line style is modified. otherwise, False.
Declaration
bool IsLeftBorderModified { get; set; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Examples
We use IsLeftBorderModified property to check whether the left border is modifed or not. Returns True if the border is modifed else returns False.
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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Check whether default border is modified or not.
Console.WriteLine(format.IsBottomBorderModified);
Console.WriteLine(format.IsLeftBorderModified);
Console.WriteLine(format.IsRightBorderModified);
Console.WriteLine(format.IsTopBorderModified);
//Apply Conditional format.
format.BottomBorderStyle = ExcelLineStyle.Thick;
format.LeftBorderStyle = ExcelLineStyle.Thick; //Set LineStyle
format.TopBorderStyle = ExcelLineStyle.Thick;
format.RightBorderStyle = ExcelLineStyle.Thick;
//Set Border color.
format.BottomBorderColor = ExcelKnownColors.Red;
format.LeftBorderColor = ExcelKnownColors.Red; //Set color
format.TopBorderColor = ExcelKnownColors.Red;
format.RightBorderColor = ExcelKnownColors.Red;
//Check whether default border is modified or not.
Console.WriteLine(format.IsBottomBorderModified);
Console.WriteLine(format.IsLeftBorderModified);
Console.WriteLine(format.IsRightBorderModified);
Console.WriteLine(format.IsTopBorderModified);
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}
//Output will be
//False
//False
//False
//False
//True
//True
//True
//TrueIsPatternColorPresent
True if pattern color is present. otherwise, False.
Declaration
bool IsPatternColorPresent { get; set; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Examples
We can use IsPatternColorPresent property to check whether pattern color is set or not. Here for example, we check whether pattern color is set or not before setting and after setting DarkHorizontal to FillPattern and Red to Color.
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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Check whether pattern color is set or not.
Console.WriteLine(format.IsPatternColorPresent);
//Set Color.
format.Color = ExcelKnownColors.Red;
format.FillPattern = ExcelPattern.DarkHorizontal;
//Check whether pattern color is set or not.
Console.WriteLine(format.IsPatternColorPresent);
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}
//Output will be
//False
//TrueIsPatternFormatPresent
True if the conditional format contains pattern formatting. otherwise, False.
Declaration
bool IsPatternFormatPresent { get; set; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Examples
To check whether the cell's FillPattern is set or not, We can use the IsPatternFormatPresent property. Here for example, We check before and after applying DarkHorizontal to FillPattern.
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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Check whether pattern format is present.
Console.WriteLine(format.IsPatternFormatPresent);
//Set Pattern.
format.FillPattern = ExcelPattern.DarkHorizontal;
//Check whether pattern format is present.
Console.WriteLine(format.IsPatternFormatPresent);
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}
//Output will be
//False
//TrueIsRightBorderModified
True if right border line style is modified. otherwise, False.
Declaration
bool IsRightBorderModified { get; set; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Examples
We use IsRightBorderModified property to check whether the right border is modifed or not. Returns True if the border is modifed else returns False.
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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Check whether default border is modified or not.
Console.WriteLine(format.IsBottomBorderModified);
Console.WriteLine(format.IsLeftBorderModified);
Console.WriteLine(format.IsRightBorderModified);
Console.WriteLine(format.IsTopBorderModified);
//Apply Conditional format.
format.BottomBorderStyle = ExcelLineStyle.Thick;
format.LeftBorderStyle = ExcelLineStyle.Thick;
format.TopBorderStyle = ExcelLineStyle.Thick;
format.RightBorderStyle = ExcelLineStyle.Thick; //Set LineStyle
//Set Border color.
format.BottomBorderColor = ExcelKnownColors.Red;
format.LeftBorderColor = ExcelKnownColors.Red;
format.TopBorderColor = ExcelKnownColors.Red;
format.RightBorderColor = ExcelKnownColors.Red; //Set color
//Check whether default border is modified or not.
Console.WriteLine(format.IsBottomBorderModified);
Console.WriteLine(format.IsLeftBorderModified);
Console.WriteLine(format.IsRightBorderModified);
Console.WriteLine(format.IsTopBorderModified);
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}
//Output will be
//False
//False
//False
//False
//True
//True
//True
//TrueIsStrikeThrough
Gets or sets a Boolean value indicating whether the font is strike through.
Declaration
bool IsStrikeThrough { get; set; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Remarks
Similar to IsStrikeThrough, we can use Underline, IsBold and IsItalic properties to format the values in the cells within the conditional formatting range.
Examples
Data in the cells within the conditional formats range can be struck with the help of IsStrikeThrough property. To strike values in the cells, we have to set the IsStrikeThrough property 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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Set StrikeThrough.
format.IsStrikeThrough = true;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}IsSubScript
Gets or sets a Boolean value indicating whether the font is subscript.
Declaration
bool IsSubScript { get; set; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
IsSuperScript
Gets or sets a Boolean value indicating whether the font is superscript.
Declaration
bool IsSuperScript { get; set; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
IsTopBorderModified
True if top border line style is modified. otherwise, False.
Declaration
bool IsTopBorderModified { get; set; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Examples
We use IsTopBorderModified property to check whether the top border is modifed or not. Returns True if the border is modifed else returns False.
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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Check whether default border is modified or not.
Console.WriteLine(format.IsBottomBorderModified);
Console.WriteLine(format.IsLeftBorderModified);
Console.WriteLine(format.IsRightBorderModified);
Console.WriteLine(format.IsTopBorderModified);
//Apply Conditional format.
format.BottomBorderStyle = ExcelLineStyle.Thick;
format.LeftBorderStyle = ExcelLineStyle.Thick;
format.TopBorderStyle = ExcelLineStyle.Thick; //Set LineStyle
format.RightBorderStyle = ExcelLineStyle.Thick;
//Set Border color.
format.BottomBorderColor = ExcelKnownColors.Red;
format.LeftBorderColor = ExcelKnownColors.Red;
format.TopBorderColor = ExcelKnownColors.Red; //Set color
format.RightBorderColor = ExcelKnownColors.Red;
//Check whether default border is modified or not.
Console.WriteLine(format.IsBottomBorderModified);
Console.WriteLine(format.IsLeftBorderModified);
Console.WriteLine(format.IsRightBorderModified);
Console.WriteLine(format.IsTopBorderModified);
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}
//Output will be
//False
//False
//False
//False
//True
//True
//True
//TrueLeftBorderColor
Gets or sets the left border color from predefined colors (ExcelKnownColors).
Declaration
ExcelKnownColors LeftBorderColor { get; set; }Property Value
| Type | Description |
|---|---|
| ExcelKnownColors |
Examples
To set left border color for the cell within conditional formatting range from the ExcelKnownColors enumeration we use LeftBorderColor property. RGB color can be applied by using LeftBorderColorRGB 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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Set Border Style.
format.BottomBorderStyle = ExcelLineStyle.Thick;
format.LeftBorderStyle = ExcelLineStyle.Thick;
format.TopBorderStyle = ExcelLineStyle.Thick;
format.RightBorderStyle = ExcelLineStyle.Thick;
//Set Border color.
format.BottomBorderColor = ExcelKnownColors.Red;
format.LeftBorderColor = ExcelKnownColors.Red; //Set color
format.TopBorderColor = ExcelKnownColors.Red;
format.RightBorderColor = ExcelKnownColors.Red;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}LeftBorderColorRGB
Gets or sets the left border RGB color.
Declaration
Color LeftBorderColorRGB { get; set; }Property Value
| Type | Description |
|---|---|
| System.Drawing.Color |
Examples
To set left border color for the cell within conditional formatting range from the RGB colors we use LeftBorderColorRGB property. Colors from ExcelKnownColors enumeration can be applied by using LeftBorderColor 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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Apply Conditional format.
format.BottomBorderStyle = ExcelLineStyle.Thick;
format.LeftBorderStyle = ExcelLineStyle.Thick;
format.TopBorderStyle = ExcelLineStyle.Thick;
format.RightBorderStyle = ExcelLineStyle.Thick;
//Set Border color.
format.BottomBorderColorRGB = System.Drawing.Color.Red;
format.LeftBorderColorRGB = System.Drawing.Color.Red; //Set RGB color.
format.RightBorderColorRGB = System.Drawing.Color.Red;
format.TopBorderColorRGB = System.Drawing.Color.Red;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}LeftBorderStyle
Gets or sets the left border line style.
Declaration
ExcelLineStyle LeftBorderStyle { get; set; }Property Value
| Type | Description |
|---|---|
| ExcelLineStyle |
Examples
To change the default line style of left border we set a line style from ExcelLineStyle enumeration to LeftBorderStyle property. Here for example, we set Thick to LeftBorderStyle 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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Set Border Style.
format.BottomBorderStyle = ExcelLineStyle.Thick;
format.LeftBorderStyle = ExcelLineStyle.Thick; //Set LineStyle
format.TopBorderStyle = ExcelLineStyle.Thick;
format.RightBorderStyle = ExcelLineStyle.Thick;
//Set Border color.
format.BottomBorderColor = ExcelKnownColors.Red;
format.LeftBorderColor = ExcelKnownColors.Red;
format.TopBorderColor = ExcelKnownColors.Red;
format.RightBorderColor = ExcelKnownColors.Red;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}NumberFormat
Gets or sets number format of the conditional format rule.
Declaration
string NumberFormat { get; set; }Property Value
| Type | Description |
|---|---|
| System.String |
Examples
Number format for the values in the cells within the conditional formatting range can be set by using NumberFormat property. Here for example, we set "d-mmm" to NumberFormat.
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["B1"].Text = "Conditional Formats";
worksheet["B2"].DateTime = DateTime.Today;
worksheet["B3"].DateTime = DateTime.Parse("1/18/2015");
worksheet["B4"].DateTime = DateTime.Today;
worksheet["B5"].DateTime = DateTime.Today;
worksheet["B6"].DateTime = DateTime.Parse("1/18/2016");
worksheet["B7"].DateTime = DateTime.Today;
worksheet["B8"].DateTime = DateTime.Today;
worksheet["B9"].DateTime = DateTime.Parse("1/18/2017");
worksheet["B10"].DateTime = DateTime.Today;
worksheet["B11"].DateTime = DateTime.Today;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set FormatType.
format.FormatType = ExcelCFType.TimePeriod;
//Set TimePeriodType. (Condition)
format.TimePeriodType = CFTimePeriods.ThisMonth;
//Set NumberFormat.
format.NumberFormat = "d-mmm";
//Set color.
format.BackColor = ExcelKnownColors.Red;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}Operator
Gets or sets the comparison operator for the conditional format.
Declaration
ExcelComparisonOperator Operator { get; set; }Property Value
| Type | Description |
|---|---|
| ExcelComparisonOperator |
Examples
If conditional formats applied based on the CellValue then the Operator should be used to apply conditional formats based on the value present in the cell. We can choose condition that is required from the ExcelComparisonOperator enumeration. Here for example, we apply borders for the cells which have values between "20" and "60". For Operator we set Between to apply conditional formats for the cells with values within the range "20" and "60".
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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
//Set Operator.
format.Operator = ExcelComparisonOperator.Between;
//Apply Conditional format.
format.BottomBorderStyle = ExcelLineStyle.Thick;
format.LeftBorderStyle = ExcelLineStyle.Thick;
format.TopBorderStyle = ExcelLineStyle.Thick;
format.RightBorderStyle = ExcelLineStyle.Thick;
//Set Border color.
format.BottomBorderColor = ExcelKnownColors.Red;
format.LeftBorderColor = ExcelKnownColors.Red;
format.TopBorderColor = ExcelKnownColors.Red;
format.RightBorderColor = ExcelKnownColors.Red;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}RightBorderColor
Gets or sets the right border color from predefined colors (ExcelKnownColors).
Declaration
ExcelKnownColors RightBorderColor { get; set; }Property Value
| Type | Description |
|---|---|
| ExcelKnownColors |
Examples
To set a color from the ExcelKnownColors enumeration we use RightBorderColor property. RGB color can be applied by using RightBorderColorRGB 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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Apply Conditional format.
format.BottomBorderStyle = ExcelLineStyle.Thick;
format.LeftBorderStyle = ExcelLineStyle.Thick;
format.TopBorderStyle = ExcelLineStyle.Thick;
format.RightBorderStyle = ExcelLineStyle.Thick;
//Set Border color.
format.BottomBorderColor = ExcelKnownColors.Red;
format.LeftBorderColor = ExcelKnownColors.Red;
format.TopBorderColor = ExcelKnownColors.Red;
format.RightBorderColor = ExcelKnownColors.Red; //Set color.
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}RightBorderColorRGB
Gets or sets the right border color.
Declaration
Color RightBorderColorRGB { get; set; }Property Value
| Type | Description |
|---|---|
| System.Drawing.Color |
Examples
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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Apply Conditional format.
format.BottomBorderStyle = ExcelLineStyle.Thick;
format.LeftBorderStyle = ExcelLineStyle.Thick;
format.TopBorderStyle = ExcelLineStyle.Thick;
format.RightBorderStyle = ExcelLineStyle.Thick;
//Set Border color.
format.BottomBorderColorRGB = System.Drawing.Color.Red;
format.LeftBorderColorRGB = System.Drawing.Color.Red;
format.RightBorderColorRGB = System.Drawing.Color.Red; //Set RGB color.
format.TopBorderColorRGB = System.Drawing.Color.Red;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}RightBorderStyle
Gets or sets the right border line style.
Declaration
ExcelLineStyle RightBorderStyle { get; set; }Property Value
| Type | Description |
|---|---|
| ExcelLineStyle |
Examples
To change the default line style of right border we set a line style from ExcelLineStyle enumeration to RightBorderStyle property. Here for example, we set Thick to RightBorderStyle 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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Apply Conditional format.
format.BottomBorderStyle = ExcelLineStyle.Thick;
format.LeftBorderStyle = ExcelLineStyle.Thick;
format.TopBorderStyle = ExcelLineStyle.Thick;
format.RightBorderStyle = ExcelLineStyle.Thick; //Set LineStyle.
//Set Border color.
format.BottomBorderColor = ExcelKnownColors.Red;
format.LeftBorderColor = ExcelKnownColors.Red;
format.TopBorderColor = ExcelKnownColors.Red;
format.RightBorderColor = ExcelKnownColors.Red;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}SecondFormula
Gets the value or expression associated with the second part of a conditional format.
Declaration
string SecondFormula { get; set; }Property Value
| Type | Description |
|---|---|
| System.String |
Remarks
Relative cell addresses can be used to apply formatting by using FirstFormulaR1C1 and SecondFormulaR1C1. To know more about R1C1 notation refer Using FormulaR1C1 property in Conditional Formats.
Examples
To set condition like Between two values are needed. So, we set these values to FirstFormula and SecondFormula properties. Here for example, we apply conditional formats to the cells that has values between "20" and "60".
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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Apply Conditional format.
format.BottomBorderStyle = ExcelLineStyle.Thick;
format.LeftBorderStyle = ExcelLineStyle.Thick;
format.TopBorderStyle = ExcelLineStyle.Thick;
format.RightBorderStyle = ExcelLineStyle.Thick;
//Set Border color.
format.BottomBorderColor = ExcelKnownColors.Red;
format.LeftBorderColor = ExcelKnownColors.Red;
format.TopBorderColor = ExcelKnownColors.Red;
format.RightBorderColor = ExcelKnownColors.Red;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}SecondFormulaR1C1
Gets the value or expression associated with the second part of a conditional format in R1C1 notation.
Declaration
string SecondFormulaR1C1 { get; set; }Property Value
| Type | Description |
|---|---|
| System.String |
Remarks
To know more about SecondFormulaR1C1 refer Using FormulaR1C1 property in Conditional Formats.
Examples
To apply formatting by comparing the relative cell addresses we can use SecondFormulaR1C1. Here for example, we compare the adjacent cell values and apply formatting. SecondFormulaR1C1 can also used along with FirstFormulaR1C1 to add additional condition.
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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 1;
worksheet["B3"].Number = 2;
worksheet["B4"].Number = 3;
worksheet["B5"].Number = 4;
worksheet["B6"].Number = 5;
worksheet["B7"].Number = 6;
worksheet["B8"].Number = 7;
worksheet["B9"].Number = 8;
worksheet["B10"].Number = 9;
worksheet["B11"].Number = 10;
//Data to be compared.
worksheet["C2"].Number = 11;
worksheet["C3"].Number = 1;
worksheet["C4"].Number = 3;
worksheet["C5"].Number = 2;
worksheet["C6"].Number = 5;
worksheet["C7"].Number = 3;
worksheet["C8"].Number = 7;
worksheet["C9"].Number = 9;
worksheet["C10"].Number = 19;
worksheet["C11"].Number = 20;
//Create instance of IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
//Add condition.
IConditionalFormat format = formats.AddCondition();
//Set FormatType.
format.FormatType = ExcelCFType.Formula;
//Set Formula.
format.SecondFormulaR1C1 = "=RC>RC[1]";
//Set color.
format.BackColor = ExcelKnownColors.Red;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}StopIfTrue
Gets or sets a boolean value that determines if additional formatting rules on the cell should be evaluated if the current rule evaluates to True.
Declaration
bool StopIfTrue { get; set; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Examples
StopIfTrue is set to False by default. If we set it to True formatting is applied based on the priority. If a higher priority rule for particular cells gets satisfied then the preceding rules are not applied for those cells.
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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
IConditionalFormat format2 = formats.AddCondition();
IConditionalFormat format3 = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Set Color.
format.BackColor = ExcelKnownColors.Red;
//Set StopIfTrue.
format.StopIfTrue = true;
//Set Condition.
format2.FirstFormula = "30";
format2.SecondFormula = "70";
format2.Operator = ExcelComparisonOperator.Between;
//Set Color.
format2.BackColor = ExcelKnownColors.Blue;
//Set StopIfTrue.
format2.StopIfTrue = true;
//Set Condition.
format3.FirstFormula = "30";
format3.SecondFormula = "50";
format3.Operator = ExcelComparisonOperator.Between;
//Set Color.
format3.BackColor = ExcelKnownColors.Green;
//Set StopIfTrue.
format3.StopIfTrue = true;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}Text
Gets or sets the text value used in
Declaration
string Text { get; set; }Property Value
| Type | Description |
|---|---|
| System.String |
Examples
To apply conditional formats based on the text present in the cells within conditional formatting range, then we set the text we want to apply formatting to Text and set SpecificText to FormatType. Here for example, we set "Test" to Text and set SpecificText to FormatType.
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["B1"].Text = "Conditional Formats";
worksheet["B2"].Text = "Test";
worksheet["B3"].Text = "Test";
worksheet["B4"].Text = "Text";
worksheet["B5"].Text = "Text";
worksheet["B6"].Text = "Test";
worksheet["B7"].Text = "Text";
worksheet["B8"].Text = "Test";
worksheet["B9"].Text = "Text";
worksheet["B10"].Text = "Text";
worksheet["B11"].Text = "Test";
//Create instance of IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
//Add condition.
IConditionalFormat format = formats.AddCondition();
//Set FormatType.
format.FormatType = ExcelCFType.SpecificText;
//Set color.
format.BackColor = ExcelKnownColors.Red;
//Set Text.
format.Text = "Test";
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
Console.ReadKey();
}TimePeriodType
Gets or sets one of the constants of CFTimePeriods enumeration which represents the type of the time period.
Declaration
CFTimePeriods TimePeriodType { get; set; }Property Value
| Type | Description |
|---|---|
| CFTimePeriods |
Remarks
The applicable time period in a "date occurring�" conditional formatting rule. This can be ignored if FormatType is not equal to TimePeriod.
Examples
For TimePeriod conditional formatting type we can set the criteria by setting a value from CFTimePeriods enumeration to TimePeriodType property. Here for example, we set ThisMonth to TimePeriodType 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["B1"].Text = "Conditional Formats";
worksheet["B2"].DateTime = DateTime.Today;
worksheet["B3"].DateTime = DateTime.Parse("1/18/2015");
worksheet["B4"].DateTime = DateTime.Today;
worksheet["B5"].DateTime = DateTime.Today;
worksheet["B6"].DateTime = DateTime.Parse("1/18/2016");
worksheet["B7"].DateTime = DateTime.Today;
worksheet["B8"].DateTime = DateTime.Today;
worksheet["B9"].DateTime = DateTime.Parse("1/18/2017");
worksheet["B10"].DateTime = DateTime.Today;
worksheet["B11"].DateTime = DateTime.Today;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set FormatType.
format.FormatType = ExcelCFType.TimePeriod;
//Set TimePeriodType. (Condition)
format.TimePeriodType = CFTimePeriods.ThisMonth;
//Set NumberFormat.
format.NumberFormat = "d-mmm";
//Set color.
format.BackColor = ExcelKnownColors.Red;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}TopBorderColor
Gets or sets the top border color from predefined colors (ExcelKnownColors).
Declaration
ExcelKnownColors TopBorderColor { get; set; }Property Value
| Type | Description |
|---|---|
| ExcelKnownColors |
Examples
To set a color from the ExcelKnownColors enumeration we use TopBorderColor property. RGB color can be applied by using TopBorderColorRGB 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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Apply Conditional format.
format.BottomBorderStyle = ExcelLineStyle.Thick;
format.LeftBorderStyle = ExcelLineStyle.Thick;
format.TopBorderStyle = ExcelLineStyle.Thick;
format.RightBorderStyle = ExcelLineStyle.Thick;
//Set Border color.
format.BottomBorderColor = ExcelKnownColors.Red;
format.LeftBorderColor = ExcelKnownColors.Red;
format.TopBorderColor = ExcelKnownColors.Red; //Set color
format.RightBorderColor = ExcelKnownColors.Red;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}TopBorderColorRGB
Gets or sets RGB color to top border.
Declaration
Color TopBorderColorRGB { get; set; }Property Value
| Type | Description |
|---|---|
| System.Drawing.Color |
Examples
To set top border color for the cell within conditional formatting range from the RGB colors we use TopBorderColorRGB property. Colors from ExcelKnownColors enumeration can be applied by using TopBorderColor 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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Apply Conditional format.
format.BottomBorderStyle = ExcelLineStyle.Thick;
format.LeftBorderStyle = ExcelLineStyle.Thick;
format.TopBorderStyle = ExcelLineStyle.Thick;
format.RightBorderStyle = ExcelLineStyle.Thick;
//Set Border color.
format.BottomBorderColorRGB = System.Drawing.Color.Red;
format.LeftBorderColorRGB = System.Drawing.Color.Red;
format.RightBorderColorRGB = System.Drawing.Color.Red;
format.TopBorderColorRGB = System.Drawing.Color.Red; //Set color
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}TopBorderStyle
Gets or sets the top border line style.
Declaration
ExcelLineStyle TopBorderStyle { get; set; }Property Value
| Type | Description |
|---|---|
| ExcelLineStyle |
Examples
To change the default line style of top border we set a line style from ExcelLineStyle enumeration to TopBorderStyle property. Here for example, we set Thick to TopBorderStyle 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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Apply Conditional format.
format.BottomBorderStyle = ExcelLineStyle.Thick;
format.LeftBorderStyle = ExcelLineStyle.Thick;
format.TopBorderStyle = ExcelLineStyle.Thick; //Set LineStyle
format.RightBorderStyle = ExcelLineStyle.Thick;
//Set Border color.
format.BottomBorderColor = ExcelKnownColors.Red;
format.LeftBorderColor = ExcelKnownColors.Red;
format.TopBorderColor = ExcelKnownColors.Red;
format.RightBorderColor = ExcelKnownColors.Red;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}TopBottom
Gets TopBottom conditional formatting rule. Read-only.
Declaration
ITopBottom TopBottom { get; }Property Value
| Type | Description |
|---|---|
| ITopBottom |
Remarks
Valid only if FormatType is set to TopBottom.
Examples
TopBottom conditional formatting rule can be applied by setting TopBottom to FormatType property. To customize the TopBottom rule we have to change properties of ITopBottom interface. Here for example, we set values to ITopBottom interface 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 = "Top10";
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 TopBottom.
format.FormatType = ExcelCFType.TopBottom;
ITopBottom topBottom = format.TopBottom;
//Set values the ITopBottom interface properties.
topBottom.Type = ExcelCFTopBottomType.Bottom;
topBottom.Percent = false;
topBottom.Rank = 5;
//Set color.
format.BackColor = ExcelKnownColors.Red;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}Underline
Gets or sets the underline type for the conditional format.
Declaration
ExcelUnderline Underline { get; set; }Property Value
| Type | Description |
|---|---|
| ExcelUnderline |
Remarks
Similar to Underline, we can use IsStrikeThrough to strike out values in the cells within conditional formatting range.
Examples
Data in the cells can be given underline with the help of conditional formats. To give underline Underline property should be set to any of the value in the ExcelUnderline enumeration. Here for example, we set Double to Underline 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["B1"].Text = "Conditional Formats";
worksheet["B2"].Number = 10;
worksheet["B3"].Number = 20;
worksheet["B4"].Number = 30;
worksheet["B5"].Number = 40;
worksheet["B6"].Number = 50;
worksheet["B7"].Number = 60;
worksheet["B8"].Number = 70;
worksheet["B9"].Number = 80;
worksheet["B10"].Number = 90;
worksheet["B11"].Number = 100;
//Create instance of IConditonalFormat and IConditionalFormats.
IConditionalFormats formats = worksheet["B2:B11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Condition.
format.FirstFormula = "60";
format.SecondFormula = "20";
format.Operator = ExcelComparisonOperator.Between;
//Set UnderLine.
format.Underline = ExcelUnderline.Double;
//Save and Dispose.
workbook.SaveAs("ConditionalFormats.xlsx");
workbook.Close();
}