Use the Excel Export API to Format Cell Values that are Above or Below the Average
- 5 minutes to read
The following example demonstrates how to apply “Above or Below Average” conditional formatting rule to a range of cells.
- 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 XlCondFmtRuleAboveAverage object, representing the new formatting rule.
- Set the object’s XlCondFmtRuleAboveAverage.Condition property to the corresponding XlCondFmtAverageCondition enumeration value to specify the formatting condition.
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 cell 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 corresponding collection contained in the XlConditionalFormatting object by calling the Add method.
- To activate the created conditional formatting rule, add the object created in step 1 to the 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 rule should be applied (A1:A11).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 0, 10));
// Create the rule highlighting values
// that are above the average in the cell range.
XlCondFmtRuleAboveAverage rule = new XlCondFmtRuleAboveAverage();
rule.Condition = XlCondFmtAverageCondition.Above;
// 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 highlighting values
// that are above or equal to the average value in the cell range.
rule = new XlCondFmtRuleAboveAverage();
rule.Condition = XlCondFmtAverageCondition.AboveOrEqual;
// 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 (C1:C11).
formatting.Ranges.Add(XlCellRange.FromLTRB(2, 0, 2, 10));
// Create the rule highlighting values
// that are below the average in the cell range.
rule = new XlCondFmtRuleAboveAverage();
rule.Condition = XlCondFmtAverageCondition.Below;
// 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);
// Create an instance of the XlConditionalFormatting class.
formatting = new XlConditionalFormatting();
// Specify the cell range to which
// the conditional formatting rule should be applied (D1:D11).
formatting.Ranges.Add(XlCellRange.FromLTRB(3, 0, 3, 10));
// Create the rule highlighting values
// that are below or equal to the average value in the cell range.
rule = new XlCondFmtRuleAboveAverage();
rule.Condition = XlCondFmtAverageCondition.BelowOrEqual;
// 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);
As a result, the cells with values less or equal to 6 are be formatted using the “Bad” cell style, and cells with values that are above 6 are formatted using the “Good” cell style.
