Use the Excel Export API to Use a Formula to Determine what Cells to Format
- 3 minutes to read
The following example demonstrates how to use a formula as a formatting criterion.
- Create a new XlConditionalFormatting XlConditionalFormatting instance that contains formatting rules and settings.
- Specify the cell range to which the formatting is going to be applied. To do that, add the target range to the ranges collection, accessible through the XlConditionalFormatting.Ranges property.
- Create a new XlCondFmtRuleExpression object with the passed string formula that will be used as a rule criterion.
Specify the formatting parameters to cells conforming to the condition.
- To use one of the built-in cell styles, set the XlCondFmtRuleWithFormatting.Formatting property to the corresponding XlCellFormatting enumeration value.
- To apply a custom background color, use 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 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 (A2:C7). formatting.Ranges.Add(XlCellRange.FromLTRB(0, 1, 2, 6)); // Create the rule that uses a formula to highlight cells if a value in the column "C" is greater than 0 and less than 50. XlCondFmtRuleExpression rule = new XlCondFmtRuleExpression("AND($C2>0,$C2<50)"); // Specify formatting settings to be applied to cells if the condition is true. rule.Formatting = XlFill.SolidFill(Color.FromArgb(0xff, 0xff, 0xcc)); formatting.Rules.Add(rule); // Create the rule that uses a formula to highlight cells if a value in the column "C" is less than or equal to 0. rule = new XlCondFmtRuleExpression("$C2<=0"); // 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 following image demonstrates the result.