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:

	2015	2016	2017	2018	2019
tables	1343	1113	1139	1761	1827
closets	1572	1492	1637	1067	1824
chairs	1097	1752	1991	1512	1587

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:

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.

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

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.