By now, you should feel pretty comfortable using
openpyxl for a number of different operations when handling Excel spreadsheets. We’re going to wrap up with a very cool topic. You’re going to learn how to add charts to your worksheets with Python.
openpyxl has support for just about all the chart types in Excel, but we’re only going to look at a couple here because the process of creating them is almost identical between each type.
The big thing to keep in mind is that you need to find the data for the chart, and then the type of chart, and then where you want it. So to get started, create a new script. I’m going to call mine
All right. Now that you have that data, go ahead and append this to the worksheet. So,
for row in rows:
sheet.append(row). Okay. So now that you have the data set up, you can make the chart, so say,
chart = BarChart(), and then the
data is going to equal a
Reference. Now, define where the data is.
You can say
worksheet=sheet and the
min_col=2, and the
max_col=3. Now, take your chart, add the data to it, pass in the
data, and then the
titles_from_data you can set equal to
True, and that’s because this first row has the titles for what the data represents.
sheet.add_chart(), pass in
chart, and then where you want it. So, let’s say something like
"E2"—and that’ll be the top-left corner of where the chart is, or where the chart’s top-left corner will be. So from here, you can say
workbook.save(), and I’m just going to call this something like
"chart.xlsx". Save it, go ahead and run it.
02:54 No errors, and it’s in the directory, so let’s open it up. And there we go! Here’s the sample data that was appended to the worksheet, and then starting in cell E2, here’s the chart. Now, one thing that’s cool here—because you didn’t pass in the data, you passed in the reference—if you click here, you can see that it’s pointing to the data that’s still on the worksheet.
That means that a user could edit the data as needed and the chart would then reflect that edited data. So now, let’s take a look at line charts. I’m just going to take all of this code here and comment it out and then start over. I’ll make a new section, call this
Line Chart, and start over with the imports.
and grab the active sheet. Okay. And also, like before, I’m going to make some fake sample data. This will be a little different.
rows is still going to be a list of lists. This will be a blank string and then each of the months.
for row in sheet.iter_rows() and this will be starting at the minimum row of
2, to not include the headers. Then, set
max_row=4, and then
min_col is going to start with
2 because that first column is just blank.
max_col is going to be
13 to include all 12 months. And then now,
for cell in row—because this is not
values_only, it’s going to return the
Cell object—you can say the
Cell object is going to equal
random and just pass into
That should set you up for sample data. Now, you’ll actually make the charts and you can say
chart = LineChart(), and then
data = Reference() and pass in the
Worksheet as the
Okay, now that the
chart is ready, you can take your
sheet and then add the chart to it, pass in the
chart, and then this time have it go to
"C6". Now, take your workbook and save it. You can call this one
06:31 You’ve got all your sample data here for three different products, and for each month of the year. These are all random, so yours will probably look a little different. You can see that all of these have been plotted out.
One thing to note here is that when you created the chart, you set this
from_rows=True, which was not the case during the bar chart. If you did not set that equal to
True instead of the chart being made up of each row, it would have gone column by column, so the data would kind of be flipped around.
So, this chart looks nice and somebody seeing this and seeing 1 through 12 might assume that that’s month, but they might not.
openpyxl will let you add categories to your chart based on your data.
I’m going to close this out and go back to the script. And now, before adding the chart, you’re going to add categories. Just call this
cats and set this equal to a
Reference. This will be on the same worksheet, so
min_row=1, and then
max _row=1—this is just to get that first row.
07:55 If you rerun the script and then reopen up the chart or spreadsheet, take a look at it and you’ll see those numbers have been replaced with the month names, which makes it a lot more readable for someone to take a quick glance at the chart.
You can say
chart.x_axis.title and set this equal to
"Months" and then
chart.y_axis.title—and actually, these are properties, so change these underscores to periods. Set this to
"Sales", then do something like
"(per unit)". All right, save this, rerun the script, and let’s open it up and see what it looks like. All right, look at that!
All your sample data is still there and charts are still there, but now you see axis titles: Months and Sales. Cool! So, that just about covers everything you need to know how to add charts to
openpyxl. Like I said before, there are plenty of different types of charts that you can use with
openpyxl, but they all have just about the same workflow to getting them set up, so if you need to use a certain type of chart, take a look at the
openpyxl documentation and you can find the specifics on how to use them there. And with that, we’re going to wrap up the how to write the spreadsheets using
openpyxl section of the course. In the next video, you’re going to take a look at how
openpyxl works with
pandas, which is a popular data analysis library.
Become a Member to join the conversation.