Search code examples
c#.netexcelclosedxml

"Cannot convert theme color to Color" in ClosedXml

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?

Solution

  • 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.