Use Theme Colours and Tints in openpyxl

Excel's colour picker has two halves. The bottom row of standard colours are fixed RGB values. The grid above — Theme Colors, with columns such as Blue, Accent 1 and lighter and darker shades beneath — is relative: those cells store "accent 1, lightened 40%", and their actual colour comes from the workbook's theme. Change the theme and every cell that uses a theme colour changes with it, which is how a report template can be switched from one brand palette to another in one step. openpyxl can write theme colours and tints too, but its API exposes the raw theme index, the ordering is not the one Excel's picker suggests, and reading a theme colour back gives you an index and a tint rather than a colour. This page writes theme colours, explains the index order and the tint arithmetic, resolves theme colours to hex values — checked against a rendered copy of the sheet — and rebrands a workbook by editing its theme. It belongs to Using openpyxl for Excel File Manipulation.

Prerequisites

pip install openpyxl pillow

LibreOffice (soffice) and pdftoppm render the sheet so the colours can be compared with what the code expects. openpyxl writes the classic Office theme into new workbooks, whose Accent 1 is the blue 4F81BD.

Step 1 — Write theme colours with tints

A theme colour is an openpyxl Color with a theme index and an optional tint:

from openpyxl import Workbook
from openpyxl.styles import Color, Font, PatternFill

SWATCHES = [("Accent 1", 4, 0.0), ("Accent 1, lighter 40%", 4, 0.4), ("Accent 1, lighter 80%", 4, 0.8),
            ("Accent 1, darker 25%", 4, -0.25), ("Accent 2", 5, 0.0), ("Accent 2, lighter 60%", 5, 0.6),
            ("Accent 6, darker 50%", 9, -0.5), ("Text 1, lighter 50%", 1, 0.5)]

wb = Workbook()
ws = wb.active
ws.title = "Swatches"
for row, (label, theme, tint) in enumerate(SWATCHES, start=1):
    ws.cell(row, 1, label)
    ws.cell(row, 2).fill = PatternFill("solid", fgColor=Color(theme=theme, tint=tint))
    ws.row_dimensions[row].height = 24
ws["C1"].font = Font(color=Color(theme=4), bold=True)
ws["C1"] = "Text in Accent 1"
ws.column_dimensions["A"].width = 26
ws.column_dimensions["B"].width = 14
wb.save("theme_colours.xlsx")

tint runs from −1 to 1: positive values lighten towards white, negative values darken towards black, and Excel's picker uses the steps 0.8, 0.6 and 0.4 for "lighter 80/60/40%" and −0.25 and −0.5 for "darker 25/50%". Theme colours work anywhere openpyxl takes a colour — fills, fonts, borders, chart elements — so a whole report can be styled from the theme. Excel shows such a cell's colour in the picker with the theme swatch selected, so people editing the report later naturally pick from the same palette instead of adding near-matching fixed colours. Conditional formatting rules can use theme colours in the same way.

Step 2 — The index order

The theme index counts the twelve colours of the theme's colour scheme, but the first four are swapped in pairs compared with the order in the theme file:

The theme file lists the dark colour first (dk1, lt1, dk2, lt2), but cell colours use index 0 for the light colour and 1 for the dark one. Getting this wrong is a classic bug: Color(theme=0) for "text" gives white text on a white background. Accents need no translation: Accent 1 is index 4 and Accent 6 is index 9.

Step 3 — Resolve theme colours to hex, and check them

Reading a cell's colour back gives theme and tint, not RGB. To show the colour elsewhere — in an HTML version of the report, a legend, a test — resolve it from the workbook's theme and apply the tint the way Excel does, by changing luminance in HLS colour space:

import colorsys, re, subprocess
from openpyxl import load_workbook
from PIL import Image

ORDER = ["lt1", "dk1", "lt2", "dk2", "accent1", "accent2", "accent3", "accent4", "accent5", "accent6", "hlink", "folHlink"]

def theme_palette(wb):
    xml = wb.loaded_theme.decode()
    return dict(re.findall(r"<a:(dk1|lt1|dk2|lt2|accent\d|hlink|folHlink)>.*?(?:srgbClr val|lastClr)=\"([0-9A-Fa-f]{6})\"", xml, re.S))

def resolve(palette, theme, tint=0.0):
    base = palette[ORDER[theme]]
    h, l, s = colorsys.rgb_to_hls(*(int(base[i:i + 2], 16) / 255 for i in (0, 2, 4)))
    l = l * (1 + tint) if tint < 0 else l * (1 - tint) + tint
    return "".join(f"{round(v * 255):02X}" for v in colorsys.hls_to_rgb(h, l, s))

subprocess.run(["soffice", "--headless", "--convert-to", "pdf", "theme_colours.xlsx"], check=True, capture_output=True)
subprocess.run(["pdftoppm", "-png", "-r", "72", "-f", "1", "-l", "1", "theme_colours.pdf", "swatches"], check=True)
rendered = {c for n, c in Image.open("swatches-1.png").convert("RGB").getcolors(maxcolors=10**6) if n > 100}

wb = load_workbook("theme_colours.xlsx")
palette = theme_palette(wb)
for row in wb["Swatches"].iter_rows(min_row=1, max_row=len(SWATCHES)):
    colour = row[1].fill.fgColor
    hexval = resolve(palette, colour.theme, colour.tint)
    found = tuple(int(hexval[i:i + 2], 16) for i in (0, 2, 4)) in rendered
    print(f"{row[0].value:<24} theme={colour.theme} tint={colour.tint:+.2f} -> #{hexval}  {'in render' if found else 'NOT FOUND'}")

wb.loaded_theme holds the theme XML of a loaded workbook; the regular expression pulls out the twelve colours, whether stored as srgbClr values or, for the system colours, as lastClr. The tint formula is Excel's: a negative tint multiplies the luminance by 1 + tint, a positive one moves it that fraction of the way towards white. Every resolved colour — #95B3D7 for Accent 1 lighter 40%, #376092 for darker 25%, #808080 for Text 1 lighter 50% — appears in the rendered page, so the resolver reproduces the colours a spreadsheet application draws.

Step 4 — Rebrand by changing the theme

Because the cells store "Accent 1" rather than a colour, changing Accent 1 in the theme recolours the whole workbook. Replace the accent values in the theme XML and save:

BRAND = {"accent1": "1F6FEB", "accent2": "D4A72C"}

xml = wb.loaded_theme.decode()
for name, hexval in BRAND.items():
    xml = re.sub(rf"(<a:{name}>\s*<a:srgbClr val=\")[0-9A-Fa-f]{{6}}", rf"\g<1>{hexval}", xml)
wb.loaded_theme = xml.encode()
wb.save("theme_colours_rebranded.xlsx")

rebranded = load_workbook("theme_colours_rebranded.xlsx")
palette = theme_palette(rebranded)
subprocess.run(["soffice", "--headless", "--convert-to", "pdf", "theme_colours_rebranded.xlsx"], check=True, capture_output=True)
subprocess.run(["pdftoppm", "-png", "-r", "72", "-f", "1", "-l", "1", "theme_colours_rebranded.pdf", "rebrand"], check=True)
rendered = {c for n, c in Image.open("rebrand-1.png").convert("RGB").getcolors(maxcolors=10**6) if n > 100}
for label, theme, tint in SWATCHES[:3] + SWATCHES[4:6]:
    hexval = resolve(palette, theme, tint)
    print(f"{label:<24} -> #{hexval}  {'in render' if tuple(int(hexval[i:i + 2], 16) for i in (0, 2, 4)) in rendered else 'NOT FOUND'}")

The cells were not touched — only two values in the theme changed — yet Accent 1 swatches now render in the brand blue #1F6FEB and its tints, #79A9F3 and #D2E2FB, and Accent 2 in the brand gold. The regular expression replaces only srgbClr values inside the named accent elements, which is where Office themes store accents; system colours such as dk1 and lt1 use a different element and are left alone. That is the practical case for theme colours in generated reports: build every template with theme colours, and a new client's palette, or a refreshed corporate identity, is a change to one XML part rather than a hunt through every style in the code. Colours that must never change — red for losses, say — are the ones to set as fixed RGB.

Common pitfalls

• theme=0 for text. Index 0 is Background 1; text is index 1.
• Expecting RGB when reading colours. Theme colours come back as an index and a tint.
• Hard-coding a palette. Read it from wb.loaded_theme so the resolver follows the workbook.
• Tints outside −1 to 1. Keep them in range; Excel's picker uses ±0.25 to 0.8.
• Theme colours for meaning. Use fixed RGB where the colour carries meaning, such as red for losses.

Conclusion

Use Color(theme=..., tint=...) in openpyxl to colour cells with the workbook theme — index 0 and 1 for Background 1 and Text 1, 2 and 3 for Background 2 and Text 2, 4 to 9 for the accents — and positive or negative tints to lighten or darken. Resolve theme colours to hex by reading wb.loaded_theme and applying Excel's luminance tint, check them against a render, and rebrand a whole workbook by changing the accent colours in its theme.

Frequently asked questions

How do I use a theme colour in openpyxl? Pass Color(theme=4) for Accent 1, or another theme index, wherever openpyxl accepts a colour, such as PatternFill(fgColor=...) or Font(color=...).

How do I make a lighter or darker theme colour in openpyxl? Add a tint between -1 and 1: Color(theme=4, tint=0.4) is Accent 1 lighter 40%, and tint=-0.25 is darker 25%.

Why is my openpyxl theme text colour white? Theme index 0 is Background 1, usually white; Text 1 is index 1.

How do I get the RGB value of a theme colour? Read the theme XML from wb.loaded_theme, take the colour for the index, and apply the tint to its luminance in HLS colour space.

Related

• Up one level: Using openpyxl for Excel File Manipulation — openpyxl essentials.
• Apply a Reusable Style Theme Across an Excel Report — named styles built on these colours.
• Read Cell Colours and Font Styles as Data with openpyxl — reading colours back.
• Apply Built-In Cell Styles Like Good, Bad and Neutral — Excel's ready-made styles.