Conditional Formatting ‐ Databars

This page has moved. The current version of this page is at https://epplussoftware.com/docs/styling/conditional-formatting/databars/. The EPPlus documentation is no longer updated in this wiki; please use the EPPlus guides and update your bookmarks.

Databars when edited in Excel have the following options:

Type, Value Fill type, FillColor BorderType, BorderColor Bar-Direction

In EPPlus Databars need to specify a color when created, like so:

var bar = sheet.ConditionalFormatting.AddDatabar(new ExcelAddress("A1:A12"), Color.DarkRed);

In addition a variety of options are accessible via the HighValue and LowValue properties. The Value.Type property is set by the eExcelConditionalFormattingValueObjectType enum. Represents the Type fields in the image above and can be set e.g. like this:

var cfRule = worksheet.ConditionalFormatting.AddDatabar(cfAddress44, Color.DarkBlue);

cfRule.HighValue.Type = eExcelConditionalFormattingValueObjectType.Percent;
cfRule.LowValue.Type = eExcelConditionalFormattingValueObjectType.Percentile;

Similarly Value and Formula can be set in the value field using those properties.

The color of the dataBar can also be changed after creation e.g. like this: cfRule.Color = Color.DarkRed;

As of EPPlus 7.0 full-support for Databars was added and the following properties can now be changed directly from EPPlus using the DataBar conditional formatting rule:

• Direction
• Gradient
• Border
• NegativeBarColorSameAsPositive
• NegativeBarBorderColorSameAsPositive
• AxisPosition
• FillColor
• BorderColor
• NegativeFillColor
• NegativeBorderColor
• AxisColor

Which encompasses all options in the image above and in the Negative Colors and Axis button. As seen in this menu:

In addition all color properties can be set to Auto, Index or Theme color instead of only cfRule.Color. See more on the advanced color options page

They are all accessible from the cfRule itself. For example the AxisPosition (position of the axis between negative and positive databar values) can be changed with the eExcelDatabarAxisPosition enum:

cfRule.AxisPosition = eExcelDatabarAxisPosition.Middle;