Openpyxl: use excel theme colors in chart

Question

I'm trying to use Excel's theme colors in a chart created by openpyxl. I want to have theme colors so that the entire workbook's colors can be updated when the user loads a new theme.

Openpyxl has functionality to color the series bars in a bar chart with rgb values as shown below.

import openpyxl
wb = openpyxl.load_workbook('myBook.xlsx')
ws = wb["chartSheet"]
chart = openpyxl.chart.BarChart()
chart.type = "col"
data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)
cats = Reference(ws, min_col=1, min_row=2, max_row=7)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)


chart.series[0].graphicalProperties.solidFill = 'FFFF66'


ws.add_chart(chart, "B2")

For styling the fill color of a cell, there is an openpyxl.styles.colors.Color object that allows you to define colors in terms of a theme, like this: openpyxl.styles.colors.Color(theme=7)

However, the object that is used for bar chart styling is an openpyxl.drawing.colors object which only accepts rgb values.

It's definitely possible within the Excel application to style bars with a theme color. Theoretically, I could use some post-production script to edit the raw xml of the excel file after being saved, but that would be pretty inefficient.

Is there any way to accomplish this within openpyxl?

score 1 · Answer 1 · answered Feb 17 '20 at 21:30

In openpyxl, it is actually called a "style", rather than a theme, when changing the chart as a whole.

Using the example provided here, I highlighted the modification of the <chartnumber>.style value.

from openpyxl import Workbook
from openpyxl.chart import BarChart, Series, Reference

wb = Workbook(write_only=True)
ws = wb.create_sheet()

rows = [
    ('Number', 'Batch 1', 'Batch 2'),
    (2, 10, 30),
    (3, 40, 60),
    (4, 50, 70),
    (5, 20, 10),
    (6, 10, 40),
    (7, 50, 30),
]


for row in rows:
    ws.append(row)


chart1 = BarChart()
chart1.type = "col"
chart1.style = 10 #Style Modification
chart1.title = "Bar Chart"
chart1.y_axis.title = 'Test number'
chart1.x_axis.title = 'Sample length (mm)'

data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)
cats = Reference(ws, min_col=1, min_row=2, max_row=7)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
ws.add_chart(chart1, "A10")

from copy import deepcopy

chart2 = deepcopy(chart1)
chart2.style = 11 #Style Modification
chart2.type = "bar"
chart2.title = "Horizontal Bar Chart"

ws.add_chart(chart2, "G10")


chart3 = deepcopy(chart1)
chart3.type = "col"
chart3.style = 12 #Style Modification
chart3.grouping = "stacked"
chart3.overlap = 100
chart3.title = 'Stacked Chart'

ws.add_chart(chart3, "A27")


chart4 = deepcopy(chart1)
chart4.type = "bar"
chart4.style = 13 #Style Modification
chart4.grouping = "percentStacked"
chart4.overlap = 100
chart4.title = 'Percent Stacked Chart'

ws.add_chart(chart4, "G27")

wb.save("bar.xlsx")

Output

Yes, the chart.style attribute allows you to select from among one of Excel's chart presets. That said, it will not allow you to color a specific series with a theme color. If, say, you wanted one series to be black and another to be based on a theme, you cannot do this with .style — deseosuho, Feb 19 '20 at 01:22
@deseosuho How can you do it then? Trying to build LineChart here, when using graphicalProperties.solidFill or graphicalProperties.line.solidFill the output Excel fails to open. style seems to be the only way for me to 'play with colors' — Mez13, Nov 28 '22 at 16:17

Openpyxl: use excel theme colors in chart

1 Answers1