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

$$\color{#ff4545}\textsf{\Largeⓘ\kern{0.2cm}\normalsize 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 conditionalFormatting

• 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 In addition all color properties can be set to Auto, Index or Theme color instead of only e.g. Color.X

They are all accessable 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;