Epplus get correct cell background rgb color

Question

i'm having trouble getting the true RGB value for Background colors with EPPLUS.

My code it's only working for colors that are setted as RGB on excel, cells with pallete colors are not getting identified.

Here's the code, hope somebody can help me out:

ExcelRangeBase c = sheet.Cells[k, j];
var wbs = sheet.Workbook.Styles;
var fill = c.Style.Fill;
string rgb = "";
if (fill.PatternType == OfficeOpenXml.Style.ExcelFillStyle.Solid)
{
  rgb = !String.IsNullOrEmpty(fill.BackgroundColor.Rgb) ? fill.BackgroundColor.Rgb :
  fill.PatternColor.LookupColor(fill.BackgroundColor);
}
else if (fill.PatternType != OfficeOpenXml.Style.ExcelFillStyle.None)
{
  rgb = !String.IsNullOrEmpty(fill.PatternColor.Rgb) ? fill.PatternColor.Rgb :
  fill.PatternColor.LookupColor(fill.PatternColor);
}
if (rgb.StartsWith("#")) rgb.Replace("#", "");
rgb = rgb.Trim();

// Removes ALPHA from ARGB
if (rgb.Length == 8 || rgb.Length == 5) rgb = rgb.Substring(2);
else if (rgb.Length > 8) rgb = rgb.Substring(rgb.Length - 6);

if (!rgb.StartsWith("#")) rgb = "#" + rgb;

string bg = "";
// I got this validation because most times lookupColor returns FF000;
if (rgb != null && rgb != "" && rgb != "#000" && rgb != "#000000")
{
  bg = "background: " + rgb + "; ";
}

Answer 1

If you have selected one of the 'Theme colors' in the Excel colour dropdown rather than 'Standard colors' or from the colour picker it doesn't seem to work, as described in the answer to this question here: EPPlus Excel Change cell color

It seems that themes are not supported - EPPlus FAQ

What is NOT supported by the library (these are the most obvious features)? [...] * Themes

Answer 2

It turns out that this is a quirk of how LookupColor works in EPPlus. Specifically, the format of color returned in this case is AA{R}{G}{B}, with two characters for alpha and a constant length sequence for each of R, G, and B respectively, specifying a shade of grey. HOWEVER, if you look at the code, you could get some really quite weird colors out (i.e. it's probably bugged). That's because the constant length used can range from 1 to 3 characters with a ceiling of 0x0200.

For instance, ((int)(decimal.Round(-1M * -512))).ToString("X") returns "200", which by inference, would result in a return of #FF200200200. However, short of submitting a patch to change how this is handled, probably the way to do this is to trust that that is the ceiling that can be returned for a channel, and then scale between 0->FF.

For an approach to doing this, see below. Please note that if this is ever fixed in EPPlus itself, the below will scale things incorrectly (since the actual ceiling will be FF, not 0x0200).

private string EPPLookupColorFixed(ExcelColor sourceColor)
    {
        var lookupColor = sourceColor.LookupColor();
        const int maxLookup = 63;
        bool isFromTable = (0 <= sourceColor.Indexed) && (maxLookup > sourceColor.Indexed);
        bool isFromRGB = (null != sourceColor.Rgb && 0 < sourceColor.Rgb.Length);
        if (isFromTable || isFromRGB)
            return lookupColor;

        // Ok, we know we entered the else block in EPP - the one 
        // that doesn't quite behave as expected.

        string shortString = "0000";
        switch (lookupColor.Length)
        {
            case 6:
                // Of the form #FF000
                shortString = lookupColor.Substring(3, 1).PadLeft(4, '0');
                break;
            case 9:
                // Of the form #FFAAAAAA
                shortString = lookupColor.Substring(3, 2).PadLeft(4, '0');
                break;
            case 12:
                // Of the form #FF200200200
                shortString = lookupColor.Substring(3, 3).PadLeft(4, '0');
                break;
        }
        var actualValue = short.Parse(shortString, System.Globalization.NumberStyles.HexNumber);
        var percent = ((double)actualValue) / 0x200d;
        var byteValue = (byte)Math.Round(percent * 0xFF,0);
        var byteText = byteValue.ToString("X");
        byteText = byteText.Length == 2 ? byteText : byteText.PadLeft(2, '0');
        return $"{lookupColor.Substring(0, 3)}{byteText}{byteText}{byteText}";
    }