Use the Excel Export API to Format Unique or Duplicate Values

• Sep 19, 2023
• 2 minutes to read

The following example demonstrates how to specify the rule that identifies duplicate and unique values.

1. Create new XlConditionalFormatting instance that contains formatting rules and settings.
2. 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.
3. Create new XlCondFmtRuleDuplicates object, representing the new formatting rule for duplicate values.
4. Create new XlCondFmtRuleUnique object, representing the new formatting rule for unique values.

5. 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.)
6. Add the newly created rule to the collection of rules contained in the XlConditionalFormatting object. To do that, use the Add method.
7. 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.

View Example

ConditionalFormattingActions.cs

// 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:D11).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 3, 10));

// Create the rule to identify duplicate values in the cell range.
formatting.Rules.Add(new XlCondFmtRuleDuplicates()
{
    Formatting = XlCellFormatting.Bad
});

// Create the rule to identify unique values in the cell range.
formatting.Rules.Add(new XlCondFmtRuleUnique()
{ 
    Formatting = XlCellFormatting.Good
});

// Add the specified format options
// to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);

ConditionalFormattingActions.vb

' Create an instance of the XlConditionalFormatting class.
Dim formatting As New XlConditionalFormatting()

' Specify the cell range
' to which the conditional formatting rules should be applied (A1:D11).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 3, 10))

' Create the rule to identify duplicate values in the cell range.
formatting.Rules.Add(New XlCondFmtRuleDuplicates() With
    {.Formatting = XlCellFormatting.Bad})

' Create the rule to identify unique values in the cell range.
formatting.Rules.Add(New XlCondFmtRuleUnique() With
    {.Formatting = XlCellFormatting.Good})

' Add the specified format options
' to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting)

The image below illustrates the result of code execution. The cells with duplicate values are formatted using the “Bad” cell style, the cells with unique values are formatted using the “Good” cell style.