Use the Excel Export API to Format Blank Cells
- 3 minutes to read
The following example demonstrates how to specify the rule that highlights the blank cells.
- Create new XlConditionalFormatting instance that contains formatting rules and settings.
- Specify the range to which the formatting is going to be applied by adding it to the ranges collection, accessible through the XlConditionalFormatting.Ranges property.
- Create new XlCondFmtRuleBlanks object, representing the new formatting rule.
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, 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 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:A10).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 0, 9));
// Create the rule to highlight blank cells in the range.
XlCondFmtRuleBlanks rule = new XlCondFmtRuleBlanks(true);
// 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 non-blank cells in the range.
rule = new XlCondFmtRuleBlanks(false);
// 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);
The image below illustrates formatted worksheet. The blank cells are formatted using the “Bad” cell style, the non-empty cells are formatted using the “Good” cell style.
