Conditional Formatting ‐ Databars

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 accessable via the HighValue and LowValue Properties. The value.Type property 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;

Similarily 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

Below only available from Epplus 7.0 onwards

|-----------------------------------------|

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 encompases 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;