Last active
April 13, 2024 18:00
-
-
Save Mike-Honey/b36e651e9a7f1d2e1d60ce1c63b9b633 to your computer and use it in GitHub Desktop.
For python openpyxl, translates a cells theme and tint to an rgb color code.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# found at: https://pastebin.com/B2nGEGX2, WRT https://stackoverflow.com/a/58443509/1787137 | |
from colorsys import rgb_to_hls, hls_to_rgb | |
#https://bitbucket.org/openpyxl/openpyxl/issues/987/add-utility-functions-for-colors-to-help | |
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)) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
THX