openpyxl Part 18 – Bar Charts

In the previous part we added an image to the workbook. In this part we’ll see how to add a chart. There are many different types of charts. In this article we’ll use the bar chart and in the following articles we’ll also have a look at some other types.

For the purpose of this and the following articles I’ll create a new workbook that will hold some random data concerning the sales of three products in five consecutive annual periods.

So, we’ll need the Workbook class and the randint method from the random module to generate random integers:

 >>> from openpyxl import Workbook
 >>> from random import randint 

Then we’ll need the BarChart class to create a bar chart and we’ll also need the Reference class. The Reference class is used to specify the data that we want to use in the chart. It’s also used for the other types of charts. So, let’s import the two classes:

 >>> from openpyxl.chart import BarChart, Reference 

Now let’s create the workbook and select the active sheet:

 >>> workbook = Workbook()
 >>> sheet = workbook.active 

Now let’s create some random data. In the header row we want the periods (2015-2019) and in the first column we want the products (‘tables’, ‘closets’, ‘chairs’). Let’s start by defining the rows:

 rows = [
 ...     ['', '2015', '2016', '2017', '2018', '2019'],
 ...     ['tables', ],
 ...     ['closets', ],
 ...     ['chairs', ]]
 ...  

Now let’s append the rows to the active worksheet:

 >>> for row in rows:
 ...     sheet.append(row)
 ...  

Now let’s iterate over the cells where the random data should be added and let’s set the values to random numbers between 1000 and 2000:

 >>> for row in sheet.iter_rows(min_row=2, max_row=4, min_col=2, max_col=6):
 ...     for cell in row:
 ...         cell.value = randint(1000, 2000)
 ...  

Our workbook is now filled with data. Let’s create a BarChart object:

 >>> chart = BarChart() 

Now let’s create the references for the data and the title. The data reference will hold the data that should be visualized in the chart. The titles reference will hold the titles, so the names of the product in column 1, rows 2-4:

 >>> 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 also add the title of the chart:

>>> chart.title = 'Furniture Sales'

Now we must add the data to the chart and set the titles_from_data argument to True. This way we’ll be able to see the dates (2015, 2016, etc.) in the color legend that will be added:

>>> chart.add_data(data, titles_from_data=True)

Let’s also set the categories so that we can see the names of the products on the chart:

 >>> chart.set_categories(titles)

Let’s specify the cell where the top left corner of the chart should be:

>>> sheet.add_chart(chart, 'D10')

Let’s save the workbook:

  >>> workbook.save("wb3.xlsx") 

Now when you open the wb3.xlsx file, you will see the following:

Naturally, as we’re working on random integers, your data and chart will differ. In the next part of the series we’ll be talking about line charts.