openpyxl Part 19 – Line Charts

Let’s talk about line charts. In the previous part of the openpyxl series we created a new workbook with some random data and added a bar chart to it. The workbook contains the following data:

Naturally, this is random data, so yours is definitely different if you were following along. Here’s the bar chart we generated for the data:

Learn how to make beautiful GUI apps

in Python using the Kivy framework.

Comprehensive, for Kivy beginners, easy to follow.

Get the book here (PDF) or on Amazon:

ebook / paperback (black and white) / paperback (full color)

Line Charts

In this article we’ll create a line chart, which is also one of the most popular chart types. Let’s start by loading the workbook:

>>> from openpyxl import load_workbook
>>> workbook = load_workbook(filename="wb3.xlsx")
>>> sheet = workbook.active

We must import the LineChart and Reference classes:

>>> from openpyxl.chart import LineChart, Reference

Let’s create the chart and the references for the data and titles:

>>> chart = LineChart()
>>> data = Reference(worksheet=sheet, min_row=1, max_row=4, min_col=2, max_col=6)
>>> titles = Reference(worksheet=sheet, min_row=2, max_row=4, min_col=1)

Let’s set the title to the same string as before:

>>> chart.title = 'Furniture Sales'

Now we’re ready to add the data to the chart, set the categories so that we can see the names of the products on the chart and set the titles for both axes:

>>> chart.add_data(data, titles_from_data=True)
>>> chart.set_categories(titles)
>>> chart.x_axis.title = 'Products'
>>> chart.y_axis.title = 'Sales'

Finally we must decide where the chart should appear and save the workbook:

>>> sheet.add_chart(chart, 'D10')
>>> workbook.save("wb4.xlsx")

After you’re done with all that, you can open the wb4.xlsx file that was just created and you will see the chart:

This is a very basic chart. Let’s tweak it a bit. One thing we can do is change the styles of the lines. We can even do it on an individual basis, so for each line independently. Each line is a separate series. So, let’s leave the chart we already have and add another one below. This time we’ll change the line for 2019 to triangles and the line for 2018 to a dotted line.

Your Panda3D Magazine

Make Awesome Games and Other 3D Apps

with Panda3D and Blender using Python.

Cool stuff, easy to follow articles.

Get the magazine here (PDF).

So, first let’s create the other chart like before:

>>> chart = LineChart()
>>> data = Reference(worksheet=sheet, min_row=1, max_row=4, min_col=2, max_col=6)
>>> titles = Reference(worksheet=sheet, min_row=2, max_row=4, min_col=1)
>>> chart.title = 'Furniture Sales'
>>> chart.add_data(data, titles_from_data=True)
>>> chart.set_categories(titles)
>>> chart.x_axis.title = 'Products'
>>> chart.y_axis.title = 'Sales'

Now let’s select the last series (it’s the one for year 2019, with index 4) and style it. We want it to represented as blue triangles with a red outline:

>>> line2019 = chart.series[4]
>>> line2019.marker.symbol = 'triangle'
>>> line2019.marker.graphicalProperties.solidFill = "0000FF" # blue fill
>>> line2019.marker.graphicalProperties.line.solidFill = "FF0000" # red outline
>>> line2019.graphicalProperties.line.noFill = True

Python Jumpstart Course

Learn the basics of Python, including OOP.

with lots of exercises, easy to follow

The course is available on Udemy.

Now let’s select the 2018 series and change the line style to dotted and the color to red:

>>> line2018 = chart.series[3]
>>> line2018.graphicalProperties.line.solidFill = "FF0000"
>>> line2018.graphicalProperties.line.dashStyle = "sysDot"

Finally, let’s add the chart and save the workbook.

>>> sheet.add_chart(chart, 'D27')
>>> workbook.save("wb4a.xlsx")

If you now open the workbook, you will see this:

You can see the styles in the second chart. In the next article in the series we’ll have a look at pie charts.

Blender Jumpstart Course

Learn the basics of 3D modeling in Blender.

step-by-step, easy to follow, visually rich

The course is available on Udemy and on Skillshare.