Use the Excel Export API to Format Cells based on the Text in the Cell
- 2 minutes to read
The following example demonstrates how to apply a text-based conditional formatting rule.
- Create a new 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 XlCondFmtRuleSpecificText object that represents the new formatting rule. Pass the following parameters.
- One of the XlCondFmtSpecificTextType enumeration values to specify the formatting rule condition.
- The string text that will be used as a formatting criterion.
Specify the formatting parameters to the 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 XlCondFmtRuleWithFormatting.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 rule should be applied (B2:B7). formatting.Ranges.Add(XlCellRange.FromLTRB(1, 1, 1, 6)); // Create the rule to highlight cells that contain the given text. XlCondFmtRuleSpecificText rule = new XlCondFmtRuleSpecificText(XlCondFmtSpecificTextType.Contains, "worldwide"); // Specify formatting settings to be applied to cells if the condition is true. rule.Formatting = XlCellFormatting.Neutral; formatting.Rules.Add(rule); // Add the specified format options to the worksheet collection of conditional formats. sheet.ConditionalFormattings.Add(formatting);
The following image illustrates the result. The cells with the text that contains the word “worldwide” are formatted using the “Neutral” cell style.