OpenXML: Converting Excel Theme Color and Tint value to RGB value

 

Problem:

I was needed to import Excel data with rich text format and display in WPF grid. From RunProperties of OpenXml api I used to get all type of properties like Bold, Italic , Underline, Color. But there have two types of colors :  Theme Color and another RGB color. But Theme color is specific to Office and depends on theme. There have 10 sets of color for Excel 2007.


First Row contains 10 colors which is defined in Theme. In theme1.xml you will find these sets of colors like :



These 10 colors are used in Theme color palate but in different order. If we create a dictionary this will look like below snapshot where above 10 colors are ordered property according to Theme color.


When you will select first color then OpenXml Color property will give you Color.Theme = 0, which is integer value from 0 to 9. So from this dictionary you can map Color.Theme integer value to RGB value. So this is easy to get RGB value from first top 10 colors from theme colors from the above dictionary. But next 50 theme colors are not defined in theme1.xml. These colors are generated from top 10 theme colors in this dictionary by changing lightness. For example second row first color is 5% darker than Theme Color with 0 means “FFFFFF” . And these percentage value of darkness or lightness is defined in Tint value of OpenXml Color object. The value of Tint between –1 to +1 depends on lightness or darkness. Lets the color you have chosen is  from column 6 and second row which is 80% lighter than top color of  Column 6. So the Color.Theme value is 5. and Tint value will be 0.79998168889431442 a double value and for darkness this will be –0.0.79998168889431442 . So the challenge now is to convert Theme color and Tint color to RGB value.
 

Solution :

Tint value is a calculation of brightness or luminance of original color. So the final Luminance or brightness of color depend on calculation of  80% lightness/darkness. For that we need to convert of original RGB color value to HLS ( Hug, Luminance, Saturation) mode. Let say for RGB color for Theme color =5 from the above dictionary is #C0504D and we have to convert this color into HSL value and then from Tint value 0.79998168889431442  ( 80% lightness of that theme color) We have to calculate final HLS value of that color. After converting final HLS value to RGB we will get RGB value theme color 5 with 80% lightness.
Here we will need converter from RGB to HLS. I got help from codeproject article http://www.codeproject.com/Articles/49650/WPF-Color-Conversions  to get method of converting RGB to HLS. I am also mentioning code here which actually you will need.
 

RGB to HLS Converter :

 private struct HlsColor
    {

        public double A;

        public double H;

        public double L;

        public double S;

    }
    private HlsColor RgbToHls(Color rgbColor)
    {

        var hlsColor = new HlsColor();

        double r = (double)rgbColor.R / 255;

        double g = (double)rgbColor.G / 255;

        double b = (double)rgbColor.B / 255;

        double a = (double)rgbColor.A / 255;

        double min = Math.Min(r, Math.Min(g, b));

        double max = Math.Max(r, Math.Max(g, b));

        double delta = max - min;

        if (max == min)
        {

            hlsColor.H = 0;

            hlsColor.S = 0;

            hlsColor.L = max;

            return hlsColor;

        }

        hlsColor.L = (min + max) / 2;

        if (hlsColor.L < 0.5)
        {

            hlsColor.S = delta / (max + min);

        }

        else
        {

            hlsColor.S = delta / (2.0 - max - min);

        }

        if (r == max) hlsColor.H = (g - b) / delta;

        if (g == max) hlsColor.H = 2.0 + (b - r) / delta;

        if (b == max) hlsColor.H = 4.0 + (r - g) / delta;

        hlsColor.H *= 60;

        if (hlsColor.H < 0) hlsColor.H += 360;

        hlsColor.A = a;

        return hlsColor;

    }

 

HLS to RGB Converter :

    private Color HlsToRgb(HlsColor hlsColor)
    {

        var rgbColor = new Color();

        if (hlsColor.S == 0)
        {

            rgbColor = Color.FromArgb((int)(hlsColor.A * 255), (int)(hlsColor.L * 255), (int)(hlsColor.L * 255),

            (int)(hlsColor.L * 255));

            return rgbColor;

        }

        double t1;

        if (hlsColor.L < 0.5)
        {

            t1 = hlsColor.L * (1.0 + hlsColor.S);

        }

        else
        {

            t1 = hlsColor.L + hlsColor.S - (hlsColor.L * hlsColor.S);

        }

        double t2 = 2.0 * hlsColor.L - t1;

        double h = hlsColor.H / 360;

        double tR = h + (1.0 / 3.0);

        double r = SetColor(t1, t2, tR);

        double tG = h;

        double g = SetColor(t1, t2, tG);

        double tB = h - (1.0 / 3.0);

        double b = SetColor(t1, t2, tB);

        rgbColor = Color.FromArgb((int)(hlsColor.A * 255), (int)(r * 255), (int)(g * 255), (int)(b * 255));

        return rgbColor;

    }

    private double SetColor(double t1, double t2, double t3)
    {

        if (t3 < 0) t3 += 1.0;

        if (t3 > 1) t3 -= 1.0;

        double color;

        if (6.0 * t3 < 1)
        {

            color = t2 + (t1 - t2) * 6.0 * t3;

        }

        else if (2.0 * t3 < 1)
        {

            color = t1;

        }

        else if (3.0 * t3 < 2)
        {

            color = t2 + (t1 - t2) * ((2.0 / 3.0) - t3) * 6.0;

        }

        else
        {

            color = t2;

        }

        // Set return value 

        return color;

    }

 

Calculate Final Luminance ( L ) Value from Tint value :

MSDN have mentioned the algorithm to calculate final Luminance from Tint value in this article. http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.tabcolor.aspx .
I have used that algorithm to calculate Final Luminance (L) value  with following function :

    private double CalculateFinalLumValue(DoubleValue tint, float lum)
    {

        if (tint == null)
        {

            return lum;

        }

        double lum1 = 0;

        if (tint.Value < 0)
        {

            lum1 = lum * (1.0 + tint.Value);

        }

        else
        {

            lum1 = lum * (1.0 - tint.Value) + (255 - 255 * (1.0 - tint.Value));

        }

        return lum1;

    } 

For top 10 colors Tint value will be null so do not need to calculate luminance  or you can RGB value directly if you get Tint value is null.  Here HLSMAX is considered 255.
 

Calculating Final RGB Value from Theme Volor and Tint value :

Now lets call these function to calculate final RGB value from theme color and Tint value .

        var colorProperty = (runProperty as DocumentFormat.OpenXml.Spreadsheet.Color);

        var themeValue = colorProperty.Theme;

        if (themeValue != null)
        {

            string colourValue = _colorPallate[Convert.ToInt32(themeValue.Value)];

            Color fromHtml = ColorTranslator.FromHtml("#" + colourValue);

            HlsColor rgbToHls = RgbToHls(fromHtml);

            double calculateFinalLumValue = CalculateFinalLumValue(colorProperty.Tint, (float)rgbToHls.L * 255) / 255;

            rgbToHls.L = calculateFinalLumValue;

            Color hlsToRgb = HlsToRgb(rgbToHls);

        } 

Here as converting RGB value to HLS value we get L value between 0 to 1 so for converting it into range 0 to 255 it is multiplied by 255 for calculating Tint Value. Final L value is set to original HLS value and converted again to RGB value.
This is the full algorithm for converting Excel or Office theme color and Tint value to RGB color value.

My colleague Md. Masudur Rahman also worked with me to solve this problem.