Use the Excel Export API to Format Cells with Dates
- 3 minutes to read
The following example demonstrates how to apply a “A Date Occurring…” 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 XlCondFmtRuleTimePeriod object that represents the new formatting rule.
- Use the object’s XlCondFmtRuleTimePeriod.TimePeriod property to specify the formatting condition by setting it to the corresponding XlCondFmtTimePeriod enumeration value.
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 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 of 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 (A1:A10).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 0, 9));
// Create the rule to highlight yesterday's dates in the cell range.
XlCondFmtRuleTimePeriod rule = new XlCondFmtRuleTimePeriod();
rule.TimePeriod = XlCondFmtTimePeriod.Yesterday;
// 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 today's dates in the cell range.
rule = new XlCondFmtRuleTimePeriod();
rule.TimePeriod = XlCondFmtTimePeriod.Today;
// Specify formatting settings to be applied to cells
// if the condition is true.
rule.Formatting = XlCellFormatting.Good;
formatting.Rules.Add(rule);
// Create the rule to highlight tomorrows's dates in the cell range.
rule = new XlCondFmtRuleTimePeriod();
rule.TimePeriod = XlCondFmtTimePeriod.Tomorrow;
// 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 cell containing yesterday’s date is formatted using the “Bad” cell style, the cell containing today’s date is formatted using the “Good” cell style and the cell with tomorrow’s date is formatted using the “Neutral” cell style.
