Use the Excel Export API to Format Cells Using Color Scales

  • 3 minutes to read

This example demonstrates how to create a color scale formatting rule.

  1. Create a new XlConditionalFormatting instance that contains formatting rules and settings.
  2. 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.
  3. Create a new XlCondFmtRuleColorScale object that represents the new formatting rule.
  4. By default, the three-scale color rule is created. To create a two-scale color rule, set the object’s XlCondFmtRuleColorScale.ColorScaleType property to the XlCondFmtColorScaleType.ColorScale2 enumeration value.
  5. Set a color corresponding to the minimum, midpoint and maximum value in the cell range by specifying the XlCondFmtRuleColorScale.MinColor, XlCondFmtRuleColorScale.MidpointColor and XlCondFmtRuleColorScale.MaxColor properties, respectively.
  6. Add the newly created rule to the corresponding collection contained in the XlConditionalFormatting object by calling 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.
// Create an instance of the XlConditionalFormatting class. 
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify cell ranges to which the conditional formatting rule should be applied (A1:A11 and C1:C11).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 0, 10)); 
formatting.Ranges.Add(XlCellRange.FromLTRB(2, 0, 2, 10));
// Create the default three-color scale rule to differentiate low, medium and high values in cell ranges.
XlCondFmtRuleColorScale rule = new XlCondFmtRuleColorScale();
formatting.Rules.Add(rule);
// Add the specified format options to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);

// Create an instance of the XlConditionalFormatting class. 
formatting = new XlConditionalFormatting();
// Specify cell ranges to which the conditional formatting rule should be applied (B1:B11 and D1:D11).
formatting.Ranges.Add(XlCellRange.FromLTRB(1, 0, 1, 10));
formatting.Ranges.Add(XlCellRange.FromLTRB(3, 0, 3, 10));
// Create the two-color scale rule to differentiate low and high values in cell ranges. 
rule = new XlCondFmtRuleColorScale();
rule.ColorScaleType = XlCondFmtColorScaleType.ColorScale2;
// Set a color corresponding to the minimum value in the cell range.
rule.MinColor = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
// Set a color corresponding to the maximum value in the cell range.
rule.MaxColor = XlColor.FromTheme(XlThemeColor.Accent1, 0.5);
formatting.Rules.Add(rule);
// 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.

XLExport_Examples - ConditionalFormatting_ColorScales