Add utility functions for Colors to help converting between theme + tint to RGB

Issue #987 closed
Andrew Thornton
created an issue

Hi!

It would excellent if openpyxl had some utility functions for converting between theme+tints and RGB values.

Example

I have a xlsx file where xl/styles.xml contains a <patternFill> element with the child element:

<fgColor theme="9" tint="-0.249977111117893"/>

The theme is a reference to the 9th child of <a:clrScheme> in xl/theme/theme1.xml:

<a:accent6>
        <a:srgbClr val="F79646"/>
</a:accent6>

And the value of the tint and theme together should give: E46C0A

How to do this

In order to apply the tint you need to convert the RGB value to 240-based HLS values and then reconvert back.

Now the information to find the theme can be found in: https://groups.google.com/forum/#!topic/openpyxl-users/I0k3TfqNLrc and https://groups.google.com/forum/#!msg/openpyxl-users/v2FDsbDDTqU/rQWLAXZFkeUJ

Applying the tint is a little more difficult and requires looking in a few places:

Leading to some working python 3 code for this:

def get_theme_colors(wb):
    from openpyxl.xml.functions import QName, fromstring
    xlmns = 'http://schemas.openxmlformats.org/drawingml/2006/main'
    root = fromstring(wb.loaded_theme)
    themeEl = root.find(QName(xlmns, 'themeElements').text)
    colorSchemes = themeEl.findall(QName(xlmns, 'clrScheme').text)
    firstColorScheme = colorSchemes[0]

    colors = []

    for c in ['lt1', 'dk1', 'lt2', 'dk2', 'accent1', 'accent2', 'accent3', 'accent4', 'accent5', 'accent6']:
        accent = firstColorScheme.find(QName(xlmns, c).text)

        if 'window' in accent.getchildren()[0].attrib['val']:
            colors.append(accent.getchildren()[0].attrib['lastClr'])
        else:
            colors.append(accent.getchildren()[0].attrib['val'])

    return colors

HLSMAX = 240
RGBMAX = 255

def rgb_to_ms_hls(red, green=None, blue=None):
    if green is None:
        if isinstance(red, str):
            if len(red) > 6:
                red = red[-6:]
            blue = int(red[4:], 16)
            green = int(red[2:4], 16)
            red = int(red[0:2], 16)
        else:
            blue = red[2]
            green = red[1]
            red = red[0]

    c_max, c_min = (max(red, green, blue), min(red, green, blue))
    c_diff, c_sum = c_max - c_min, c_max + c_min
    c_avg = c_sum // 2

    lightness = (c_sum * HLSMAX + RGBMAX) // (2 * RGBMAX)

    if c_max == c_min:
        saturation = 0
        hue = HLSMAX * 2 // 3
    else:
        if lightness <= HLSMAX // 2:
            saturation = ((c_diff * HLSMAX) + c_avg) // c_sum
        else:
            saturation = ((c_diff * HLSMAX) + ((2 * RGBMAX - c_sum) // 2)) // (2 * RGBMAX - c_sum)

    Rdelta = (((c_max - red) * (HLSMAX // 6)) + c_avg) // c_diff
    Gdelta = (((c_max - green) * (HLSMAX // 6)) + c_avg) // c_diff
    Bdelta = (((c_max - blue) * (HLSMAX // 6)) + c_avg) // c_diff

    if (red == c_max):
        hue = Bdelta - Gdelta
    elif (green == c_max):
        hue = (HLSMAX // 3) + Rdelta - Bdelta
    else: # B == c_max
        hue = ((2 * HLSMAX) // 3) + Gdelta - Rdelta

    if (hue < 0):
        hue = hue + HLSMAX
    if (hue > HLSMAX):
        hue = hue - HLSMAX

    return (hue, lightness, saturation)

def ms_hue_to_col(m1, m2, hue):
    if hue < 0:
        hue = hue + HLSMAX
    elif hue > HLSMAX:
        hue = hue - HLSMAX

    md = m2 - m1
    # return r,g, or b value from this tridrant
    if hue < HLSMAX // 6:
        return m1 + ((md * hue + (HLSMAX // 12)) // (HLSMAX // 6))
    elif hue < HLSMAX // 2:
        return m2
    elif hue < HLSMAX * 2 // 3:
        return m1 + ((md * ((HLSMAX * 2 // 3) - hue) + HLSMAX // 12) // (HLSMAX // 6))
    else:
        return m1

def ms_hls_to_rgb(hue, lightness=None, saturation=None):
    if lightness is None:
        hue, lightness, saturation = hue

    if saturation == 0:
        red = lightness * RGBMAX // HLSMAX
        green = red
        blue = red
    else:
        if lightness <= HLSMAX // 2:
            m2 = (lightness * (HLSMAX + saturation) + HLSMAX // 2) // HLSMAX
        else:
            m2 = lightness + saturation - ((lightness * saturation) + HLSMAX // 2) // HLSMAX
        m1 = 2 * lightness - m2

        red = (ms_hue_to_col(m1, m2, hue + HLSMAX // 3) * RGBMAX + HLSMAX // 2) // HLSMAX
        green = (ms_hue_to_col(m1, m2, hue) * RGBMAX + HLSMAX // 2) // HLSMAX
        blue = (ms_hue_to_col(m1, m2, hue - HLSMAX // 3) * RGBMAX + HLSMAX // 2) // HLSMAX
    return (red, green, blue)

def rgb_to_hex(red, green=None, blue=None):
    if green is None:
       red, green, blue = red
    return ('%02x%02x%02x' % (red, green, blue)).upper()

def tint_luminance(tint, lum):
    if tint < 0:
        return int(round(lum * (1.0 + tint)))
    else:
        return int(round(lum * (1.0 - tint) + (HLSMAX - HLSMAX * (1.0 - tint))))

def theme_and_tint_to_rgb(wb, theme, tint):
    rgb = get_theme_colors(wb)[theme]
    h, l, s = rgb_to_ms_hls(rgb)
    return rgb_to_hex(ms_hls_to_rgb(h, tint_luminance(tint, l), s))

I think it would excellent if there was a way that the Color objects could cache this sort of information so that you could just interrogate the Color in whatever form and get the RGB values for it no matter what.

Comments (6)

  1. CharlieC

    Puh! While I think that the code could be simplified a lot I don't really see this really being an addition to the library. Apart from the them lookups it's not really got much to do with openpyxl and I wouldn't want to be responsible for maintaining or explaining it!

  2. Andrew Thornton reporter

    The trouble is without something like this - the colors are uninterpretable.

    Looking at this again, it's possible to use the colorsys module to get rid of most of the code:

    from colorsys import rgb_to_hls, hls_to_rgb
    
    RGBMAX = 0xff  # Corresponds to 255
    HLSMAX = 240  # MS excel's tint function expects that HLS is base 240. see:
    # https://social.msdn.microsoft.com/Forums/en-US/e9d8c136-6d62-4098-9b1b-dac786149f43/excel-color-tint-algorithm-incorrect?forum=os_binaryfile#d3c2ac95-52e0-476b-86f1-e2a697f24969
    
    def rgb_to_ms_hls(red, green=None, blue=None):
        """Converts rgb values in range (0,1) or a hex string of the form '[#aa]rrggbb' to HLSMAX based HLS, (alpha values are ignored)"""
        if green is None:
            if isinstance(red, str):
                if len(red) > 6:
                    red = red[-6:]  # Ignore preceding '#' and alpha values
                blue = int(red[4:], 16) / RGBMAX
                green = int(red[2:4], 16) / RGBMAX
                red = int(red[0:2], 16) / RGBMAX
            else:
                red, green, blue = red
        h, l, s = rgb_to_hls(red, green, blue)
        return (int(round(h * HLSMAX)), int(round(l * HLSMAX)), int(round(s * HLSMAX)))
    
    def ms_hls_to_rgb(hue, lightness=None, saturation=None):
        """Converts HLSMAX based HLS values to rgb values in the range (0,1)"""
        if lightness is None:
            hue, lightness, saturation = hue
        return hls_to_rgb(hue / HLSMAX, lightness / HLSMAX, saturation / HLSMAX)
    
    def rgb_to_hex(red, green=None, blue=None):
        """Converts (0,1) based RGB values to a hex string 'rrggbb'"""
        if green is None:
            red, green, blue = red
        return ('%02x%02x%02x' % (int(round(red * RGBMAX)), int(round(green * RGBMAX)), int(round(blue * RGBMAX)))).upper()
    
    
    def get_theme_colors(wb):
        """Gets theme colors from the workbook"""
        # see: https://groups.google.com/forum/#!topic/openpyxl-users/I0k3TfqNLrc
        from openpyxl.xml.functions import QName, fromstring
        xlmns = 'http://schemas.openxmlformats.org/drawingml/2006/main'
        root = fromstring(wb.loaded_theme)
        themeEl = root.find(QName(xlmns, 'themeElements').text)
        colorSchemes = themeEl.findall(QName(xlmns, 'clrScheme').text)
        firstColorScheme = colorSchemes[0]
    
        colors = []
    
        for c in ['lt1', 'dk1', 'lt2', 'dk2', 'accent1', 'accent2', 'accent3', 'accent4', 'accent5', 'accent6']:
            accent = firstColorScheme.find(QName(xlmns, c).text)
    
            if 'window' in accent.getchildren()[0].attrib['val']:
                colors.append(accent.getchildren()[0].attrib['lastClr'])
            else:
                colors.append(accent.getchildren()[0].attrib['val'])
    
        return colors
    
    def tint_luminance(tint, lum):
        """Tints a HLSMAX based luminance"""
        # See: http://ciintelligence.blogspot.co.uk/2012/02/converting-excel-theme-color-and-tint.html
        if tint < 0:
            return int(round(lum * (1.0 + tint)))
        else:
            return int(round(lum * (1.0 - tint) + (HLSMAX - HLSMAX * (1.0 - tint))))
    
    def theme_and_tint_to_rgb(wb, theme, tint):
        """Given a workbook, a theme number and a tint return a hex based rgb"""
        rgb = get_theme_colors(wb)[theme]
        h, l, s = rgb_to_ms_hls(rgb)
        return rgb_to_hex(ms_hls_to_rgb(h, tint_luminance(tint, l), s))
    
  3. CharlieC

    I've no intention of adding the relevant dependency for this niche use case and this code will require quite a lot of tests - can probably be significantly simplified. Create Theme class using Serialisable and you don't need any further XML processing. I'm sure we already have code that can go between HEX and RGB. The parameter overloading is also not a good idea.

  4. Log in to comment