Skip to content

Advanced Color Options Example

Conditionalformattings can apply other colors than just .Color. You can also apply theme, index or auto color.

Here's an example of applying theme color using .Theme instead on a AboveAverageRule:

using (var pck = new ExcelPackage())
{
   var sheet = pck.Workbook.Worksheets.Add("NewWorksheet");

   var cf = sheet.ConditionalFormatting.AddAboveAverage("A1:B5");

   cf.Style.Fill.BackgroundColor.Theme = eThemeSchemeColor.Accent4;
}

Using pck As New ExcelPackage()
    Dim sheet = pck.Workbook.Worksheets.Add("NewWorksheet")

    Dim cf = sheet.ConditionalFormatting.AddAboveAverage("A1:B5")

    cf.Style.Fill.BackgroundColor.Theme = eThemeSchemeColor.Accent4
End Using

The same logic applies to most other conditional formattings. With the exception of Databars and Colorscales.

Below only available from Epplus 7.0 onwards

As of Epplus 7.0 databars and color scales have built in support for theme, index or auto color options.

Here are some examples of applying theme colors on

A databar:

var cfDatabar = sheet.ConditionalFormatting.AddDatabar("A1:B5", Color.Red);

cfDatabar.FillColor.Theme = eThemeSchemeColor.Accent1;
cfDatabar.BorderColor.SetColor(eThemeSchemeColor.Accent2);

Dim cfDatabar = sheet.ConditionalFormatting.AddDatabar("A1:B5", Color.Red)

cfDatabar.FillColor.Theme = eThemeSchemeColor.Accent1
cfDatabar.BorderColor.SetColor(eThemeSchemeColor.Accent2)

A ThreeColorScale:

var threeColor = sheet.ConditionalFormatting.AddThreeColorScale("A1:B5");

threeColor.LowValue.ColorSettings.Theme = eThemeSchemeColor.Background1;
threeColor.MiddleValue.ColorSettings.Theme = eThemeSchemeColor.Accent2;
threeColor.HighValue.ColorSettings.Theme = eThemeSchemeColor.Accent3;

Dim threeColor = sheet.ConditionalFormatting.AddThreeColorScale("A1:B5")

threeColor.LowValue.ColorSettings.Theme = eThemeSchemeColor.Background1
threeColor.MiddleValue.ColorSettings.Theme = eThemeSchemeColor.Accent2
threeColor.HighValue.ColorSettings.Theme = eThemeSchemeColor.Accent3

Note that there are some differences in how to set the colors due to ensuring backwards compatability, and because Databars has multiple options on one whole whereas colorScale has different needs as these two are more complex and diverse types.

Back to Conditional Formatting