Time course stackbar chart with openpyxl

Question

I want to create a stack bar chart with openpyxl. On my excel sheet, each col is a time point with data of different categories. From openpyxl tutorial, it seems to be straightforward to create bar charts if I have each time point per row. But I don't have the freedom to switch rows and cols. Is there a way to create stack chart with each bar for a time point and each category of that time point stacking up, please? Below are some sample data:

I want to create a chart like:

Do I understand this right? you want to swap titles and categories with respect to the example here without changing your actual cell data? — Aaron
I find with openpyxl I almost always have to just look at the source code. In particular, I fire up spyder so I can use its "go to definition (Ctrl-G)" feature to follow the source. — Aaron
As Aaron says, this is really just a question of passing in the from_rows=True parameter. The docs were based on the default behaviour in GUI. Improvements are always welcome. — Charlie Clark

Aaron Aaron · Accepted Answer · 2019-02-04T22:10:59

I modified the example from here to use your example. The primary change is to tell the add_data() method of the chart that you have rows of data instead of columns using the keyword argument: from_rows=True. The only other changes are the row and column numbers to get the correct references.

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

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

rows = [
    ('Mon/Cat', '2018-08', '2018-09', '2018-10', '2018-11'),
    ('Cat 101', 885, 3378, 0, 2155),
    ('Cat 102', 0, 458, 1255, 0),
    ('Cat 103', 474, 0, 1554, 1655),
    ('Cat 104', 1250, 250, 502, 845),
]


for row in rows:
    ws.append(row)


chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.grouping = "stacked"
chart1.overlap = 100
chart1.title = "Chart Title"
#chart1.y_axis.title = 'y-axis'
#chart1.x_axis.title = 'x-axis'

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

wb.save("bar.xlsx")

Time course stackbar chart with openpyxl

1 Answers