Use the Excel Export API to Format Cells that are Less than, Greater than or Between a Value
- 4 minutes to read
This example demonstrates how to create the rule that uses a relational operator as a formatting criterion.
- Create new XlConditionalFormatting instance that contains formatting rules and settings.
- Specify the range to which the formatting is going to be applied. To do that, add the target range to the object’s ranges collection, accessible through the XlConditionalFormatting.Ranges property.
- Create new XlCondFmtRuleCellIs object, representing the new formatting rule.
- Set the object’s XlCondFmtRuleCellIs.Operator property to the corresponding XlCondFmtOperator enumeration value to specify the formatting condition.
- To specify the threshold value, use the XlCondFmtRuleCellIs.Value property. Note that the value can be represented by a formula.
Specify the formatting parameters to the cells, conforming to the condition.
- To use one of the built-in cell styles, set the Formatting property to the corresponding XlCellFormatting enumeration value.
- To apply a custom background color, set the XlFormatting.Fill property or use one of the static XlFill methods as a Formatting property value. The XlFormatting.Font property allows you to set the desired font parameters (color, size, etc.)
- Add the newly created rule to the collection of rules contained in the XlConditionalFormatting object. To do that, use the Add method.
- To activate the created conditional formatting rule, add the object created in step 1 to the worksheet collection of conditional formatting rules. The collection can be accessed through the IXlSheet.ConditionalFormattings property.
// Create an instance of the XlConditionalFormatting class.
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify the cell range
// to which the conditional formatting rules should be applied (A1:A11).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 0, 10));
// Create the rule to highlight cells whose values are less than 5.
XlCondFmtRuleCellIs rule = new XlCondFmtRuleCellIs();
rule.Operator = XlCondFmtOperator.LessThan;
rule.Value = 5;
// Specify formatting settings to be applied to cells
// if the condition is true.
rule.Formatting = XlCellFormatting.Bad;
formatting.Rules.Add(rule);
// Create the rule to highlight cells whose values are between 5 and 8.
rule = new XlCondFmtRuleCellIs();
rule.Operator = XlCondFmtOperator.Between;
rule.Value = 5;
rule.SecondValue = 8;
// Specify formatting settings to be applied to cells
// if the condition is true.
rule.Formatting = XlCellFormatting.Neutral;
formatting.Rules.Add(rule);
// Create the rule to highlight cells whose values are greater than 8.
rule = new XlCondFmtRuleCellIs();
rule.Operator = XlCondFmtOperator.GreaterThan;
rule.Value = 8;
// Specify formatting settings to be applied to cells
// if the condition is true.
rule.Formatting = XlCellFormatting.Good;
formatting.Rules.Add(rule);
// Add the specified format options
// to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
// Create an instance of the XlConditionalFormatting class.
formatting = new XlConditionalFormatting();
// Specify the cell range
// to which the conditional formatting rule should be applied (B1:B11).
formatting.Ranges.Add(XlCellRange.FromLTRB(1, 0, 1, 10));
// Create the rule to highlight cells
// whose values are greater than a value calculated by a formula.
rule = new XlCondFmtRuleCellIs();
rule.Operator = XlCondFmtOperator.GreaterThan;
rule.Value = "=$A1+3";
// Specify formatting settings to be applied to cells
// if the condition is true.
rule.Formatting = XlCellFormatting.Bad;
formatting.Rules.Add(rule);
// Add the specified format options
// to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
The image below illustrates the result of code execution.
