2

I am trying to read the fill background colour of cells in Excel using ClosedXml. I am working from this sample code and have been able to read the content of a Excel document without issues, but am not able to read the Fill BackgroundColor for the cell as a hexadecimal value. I am able to see the ThemeColor and ThemeTint properties defined under BackgroundColor but have not found a way to convert these into either System.Color or a hex value. Here is my code:

// Get all categories
while (!categoryRow.Cell(coCategoryId).IsEmpty())
{
    IXLCell categoryName = categoryRow.Cell(coCategoryName);
    categories.Add(categoryName.GetString() + " " + XLColor.FromTheme(categoryName.Style.Fill.BackgroundColor.ThemeColor, categoryName.Style.Fill.BackgroundColor.ThemeTint).Color.ToHex());
    categoryRow = categoryRow.RowBelow();
}

It seems that the method XLColor.FromTheme always throws the exception "Cannot convert theme color to Color". Does anyone know another way to get the System.Color from the ThemeColor and ThemeTint values?

UPDATE:

I failed to mention that I have already tried using the Color property of BackgroundColor, unfortunately this is not correctly filled and if you view it in the debugger then you will see that this property is throwing the same exception that I get with the method XLColor.FromTheme. So this definitely looks like a bug in ClosedXml. Does anyone know a workaround?

BruceHill
  • 6,954
  • 8
  • 62
  • 114

3 Answers3

3

I think you need to evaluate the ColorType property and the workbook's theme if required. E.g. like this:

Private Function CellColor(ByVal cell As IXLCell, ByVal wb As XLWorkbook) As Drawing.Color

    Select Case cell.Style.Fill.BackgroundColor.ColorType
        Case XLColorType.Color
            Return cell.Style.Fill.BackgroundColor.Color

        Case XLColorType.Theme
            Select Case cell.Style.Fill.BackgroundColor.ThemeColor
                Case XLThemeColor.Accent1
                    Return wb.Theme.Accent1.Color
                Case XLThemeColor.Accent2
                    Return wb.Theme.Accent2.Color
                ...
            End Select
    End Select

As BruceHill pointed out this ignores tinting/shading. ClosedXML does not seem to support this so it must be calculated manually. The algorithm used by Office can be found here: http://social.msdn.microsoft.com/Forums/en-HK/oxmlsdk/thread/f6d26f2c-114f-4a0d-8bca-a27442aec4d0.

Paul B.
  • 2,394
  • 27
  • 47
  • I already tried using _categoryName.Style.Fill.BackgroundColor.Color_ but the _Color_ property unfortunately never gets filled. If I run my code in debug mode and view _BackgroundColor_ in the debugger then I see the properties ThemeColor and ThemeTint correctly filled, but the Color property shows an exception with the same message that I am getting with the method _XLColor.FromTheme_, namely: "Cannot convert theme color to Color". So this seems like a bug in ClosedXml, that is why I am looking for a workaround. – BruceHill Jun 26 '12 at 07:23
  • Learned something new about ClosedXML and updated the answer :) – Paul B. Jun 26 '12 at 07:34
  • Thanks, Paul B. I have implemented this. The only problem is that it does not take the _ThemeTint_ into account, so background colours with the same theme name but different tint values return the same System.Color. So, for example, Background1 will always return White, regardless of the tint defined. – BruceHill Jun 26 '12 at 08:08
  • Right, didn't think about this (I have never used Tint/Shade before). Looks like you need to include the tint/shade factor manually. I have found an article explaining how to it and will add the link to the answer. – Paul B. Jun 26 '12 at 09:46
  • Paul, I am giving you the credit for this answer. I have not yet written the algorithm to determine the exact colour, taking into account the tinting/shading, but will post it here when I get around to doing so. In the meantime, the solution you provided is good enough for what I am needing to do so I will flag your answer as correct. Thanks for your help. – BruceHill Jul 13 '12 at 16:52
  • Instead of checking for all possible values of `ThemeColor`, one can use `workbook.Theme.ResolveThemeColor(themeColor)`. – Sergii Volchkov Sep 30 '22 at 08:42
0

Theme color is just enumeration value (like Background1, Text1, etc), to get the actual value you need to get it from the Theme. So look for some "Theme" propeties in a Workbook, actual theme colors most probably is defined where.

Petr Abdulin
  • 33,883
  • 9
  • 62
  • 96
0

I found this article very usefull and it's working fine.:

Just found a litle bug in the "RgbToHls" function that not set the alpha value in the case the min and max values are equals:

if (max == min)
        {

            hlsColor.H = 0;

            hlsColor.S = 0;

            hlsColor.L = max;

           --> **hlsColor.L = a;**
            return hlsColor;

        }
Dirty-flow
  • 2,306
  • 11
  • 30
  • 49
jcd
  • 1