Use the Excel Export API to Format Cells Using Data Bars

Sep 19, 2023
5 minutes to read

The following example describes how to apply a data bar 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 XlCondFmtRuleDataBar object that represents the new formatting rule.
Customize the data bar appearance by specifying its fill color (XlCondFmtRuleDataBar.FillColor), the color of the borders (XlCondFmtRuleDataBar.BorderColor) and axis (XlCondFmtRuleDataBar.AxisColor). To use the gradient fill type, set the XlCondFmtRuleDataBar.GradientFill to true. Setting the XlCondFmtRuleDataBar.MinLength and XlCondFmtRuleDataBar.MaxLength properties allow you to specify the minimum and maximum length of the data bar.
If necessary, set the minimum and maximum threshold values using the XlCondFmtRuleDataBar.MinValue and XlCondFmtRuleDataBar.MaxValue properties.
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.

ConditionalFormattingActions.cs
ConditionalFormattingActions.vb

csharp

// Create an instance of the XlConditionalFormatting class.
XlConditionalFormatting formatting = new XlConditionalFormatting();

// Specify the cell range
// to which the conditional formatting rule should be applied (A1:A11).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 0, 0, 10));

// Create the rule to compare values in the cell range using data bars.
XlCondFmtRuleDataBar rule = new XlCondFmtRuleDataBar();

// Specify the bar color.
rule.FillColor = XlColor.FromTheme(XlThemeColor.Accent1, 0.2);

// Specify the solid fill type.
rule.GradientFill = false;
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 the cell range
// to which the conditional formatting rule should be applied (B1:B11).
formatting.Ranges.Add(XlCellRange.FromLTRB(1, 0, 1, 10));

// Create the rule to compare values
// in the cell range using data bars.
rule = new XlCondFmtRuleDataBar();

// Set the positive bar color to green.
rule.FillColor = Color.Green;

// Set the border color of positive bars to green.
rule.BorderColor = Color.Green;

// Set the axis color to brown.
rule.AxisColor = Color.Brown;

// Use the gradient fill type
rule.GradientFill = true;
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 the cell range
// to which the conditional formatting rule should be applied (C1:C11).
formatting.Ranges.Add(XlCellRange.FromLTRB(2, 0, 2, 10));

// Create the rule to compare values in the cell range using data bars.
rule = new XlCondFmtRuleDataBar();

// Specify the bar color.
rule.FillColor = XlColor.FromTheme(XlThemeColor.Accent4, 0.2);

// Set the minimum length of the data bar.
rule.MinLength = 10;

// Set the maximum length of the data bar.
rule.MaxLength = 90;

// Set the value corresponding to the shortest bar.
rule.MinValue.ObjectType = XlCondFmtValueObjectType.Number;
rule.MinValue.Value = 3;

// Set the direction of data bars.
rule.Direction = XlDataBarDirection.RightToLeft;

// Hide values of cells to which the rule is applied.
rule.ShowValues = false;
formatting.Rules.Add(rule);

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

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

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

' Create the rule to compare values in the cell range using data bars.
Dim rule As New XlCondFmtRuleDataBar()

' Specify the bar color.
rule.FillColor = XlColor.FromTheme(XlThemeColor.Accent1, 0.2)

' Specify the solid fill type.
rule.GradientFill = False
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 the cell range
' to which the conditional formatting rule should be applied (B1:B11).
formatting.Ranges.Add(XlCellRange.FromLTRB(1, 0, 1, 10))

' Create the rule to compare values in the cell range using data bars.
rule = New XlCondFmtRuleDataBar()

' Set the positive bar color to green.
rule.FillColor = Color.Green

' Set the border color of positive bars to green.
rule.BorderColor = Color.Green

' Set the axis color to brown.
rule.AxisColor = Color.Brown

' Use the gradient fill type
rule.GradientFill = True
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 the cell range
' to which the conditional formatting rule should be applied (C1:C11).
formatting.Ranges.Add(XlCellRange.FromLTRB(2, 0, 2, 10))

' Create the rule to compare values in the cell range using data bars.
rule = New XlCondFmtRuleDataBar()

' Specify the bar color.
rule.FillColor = XlColor.FromTheme(XlThemeColor.Accent4, 0.2)

' Set the minimum length of the data bar.
rule.MinLength = 10

' Set the maximum length of the data bar.
rule.MaxLength = 90

' Set the value corresponding to the shortest bar.
rule.MinValue.ObjectType = XlCondFmtValueObjectType.Number
rule.MinValue.Value = 3

' Set the direction of data bars.
rule.Direction = XlDataBarDirection.RightToLeft

' Hide values of cells to which the rule is applied.
rule.ShowValues = False
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 the code execution.