Excel charts Automation using Python and Openpyxl

Introduction

This article covers how to Automate Excel charts using python and the openpyxl library. We explain the followed steps to create any chart with openpyxl, as well as give examples regrading Line, Bar, and Pie charts.

Note that there are a lot of variables that goes into creating and tweaking an Excel chart to your needs, whether you want to style it in a certain way, or give it some properties. Basically, you can do everything that MS Excel provides.

I encourage you to check the openpyxl official documentation for more information.

Why Automating Excel Charts

Automating MS Excel reports are very popular nowadays. Think of it like this: if you have some data that you make a report out of it, and that data gets updated frequently, Automating the whole process would make the report in seconds, and you can spend the rest of your time doing core tasks.

With all that being said, automating charts is easily one of the most important skills since charts are a must in reporting, and they can take some time to make multiple, and tweaking each to your preferences manually.

Openpyxl and Python

Openpyxl is a powerful open-source python library specifically used to read and write data from Excel 2010+ xlsx/xlsm files. Basically, It allows us to automate anything related to MS Excel that we usually perform manually.

For start, we must install the openpyxl library by going to the terminal or command prompt and type the below code, the hit enter.

pip install openpyxl

Understanding Excel Charts Automation using Openpyxl – Steps

Openpyxl can do a lot of Excel Automation related tasks, and one blogpost can’t explore all the functionalities. That’s why on the this article we will focus mainly on how to create charts and everything that goes into that.

To create Excel charts using openpyxl, we usually follow these steps:

1. Import the required classes from openpyxl
2. Create a new workbook or open an existing one
3. Select the desired worksheet
4. Add the data if it doesn’t exist on the worksheet
5. Reference the date using the Reference class
6. Instantiate the desired Chart Type object
7. Pass the referenced data to the Chart object
8. Add styles and properties to the Chart object
9. Add the Chart object to the worksheet
10. Save the workbook

In the Example section we will understand how to apply openpyxl following the above steps.

When the script is created for a certain report we would just change the data each time, and run the script to get an updated Excel report.

Examples on how to Automate Excel Charts using openpyxl – Tutorial

In this section we use code to explain how to Automate Excel Charts using openpyxl.

The three beginners’ friendly charts examples we use are:

• Line chart
• Bar chart
• Pie chart

Line Chart – Excel Charts Automation

To create Line charts in Openpyxl we use the LineChart class.

Each diagram has its own class. We also need the Workbook class to create a new empty Excel workbook, the Reference class to reference the rows and columns of data in our Excel spreadsheet that we want to use use for the chart, and finally the date class since we will be using dates in this example.

Here’s the code:

# Import required libraries
from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference
from datetime import date

Next we create a new workbook and assigning it to the wb variable, then getting the active sheet using the active method and assigning it to the ws variable.

We need to reference the sheet since it will store both the data and the diagram.

Note that by default, newly created workbooks only get one sheet called Sheet. That is the active spreadsheet.

# create new workbook
wb = Workbook()
# get active sheet
ws = wb.active

Note that if we have an existing excel file, we would use the load_workbook class that takes the file path as an argument.

We need data to visualize. Usually in reporting, the data would be stored in CSVs, Excel files, Databases…etc.

In real world scenarios, we first automate the process of retrieving the data and save it to the spreadsheet.

For our examples, we create some random data as a list of lists (or list of tuples) that we name rows.

Then add that data to the active ws sheet by iterating over the list and appending the rows using the append method. Note that the data will be added starting from Cell A1.

# Add data to active sheet
rows = [
    ['Date', 'Product 1', 'Product 2', 'Product 3'],
    [date(2015,9, 1), 43, 26, 28],
    [date(2015,9, 2), 38, 25, 34],
    [date(2015,9, 3), 51, 34, 35],
    [date(2015,9, 4), 37, 27, 43],
    [date(2015,9, 5), 15, 36, 30],
    [date(2015,9, 6), 27, 41, 33],
]

for row in rows:
    ws.append(row)

In our example data, we have three products, which means that the resulted Excel chart will contain three lines, each line represent a product.

Now that the data is added to our worksheet, we can pass to the chart creation part.

We instantiate a new empty LineChart object that we name c1.

Then use the object’s built-in methods to add a title, predefined styles, and the axises titles as follows:

# Setup the chart    
c1 = LineChart()
c1.title = "Line Chart Example"
c1.style = 13
c1.y_axis.title = 'Products'
c1.x_axis.title = 'Numbers'

After that use the Reference class to wrap the data previously added n order to use it. Basically, If we have other data on sheet that we don’t want to visualize, we simply don’t reference it.

The Reference class takes as arguments the fowwling:

• The spreadsheet the contains the data.
• The number of the two columns that limit our data horizontally.
• The number of rows that limit our data vertically.

After referencing the data we add it to the chart object using the add_data method.

data = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=7)
c1.add_data(data, titles_from_data=True)

After that comes the styling of each line in the diagram. The series method is used to access each line:

• series[0] represents line 1
• series[1] represents line 2
• series[2] represents line 3

We use built-in methods to add styling like: the colors, the symbols, the fill types, the width… etc.

You need to check out the openpyxl documentation the find all the available styles.

# Style Line 1
s1 = c1.series[0]
s1.marker.symbol = "triangle"
s1.marker.graphicalProperties.solidFill = "FF0000" # Marker filling
s1.marker.graphicalProperties.line.solidFill = "FF0000" # Marker outline
s1.graphicalProperties.line.noFill = True

# Style Line 2
s2 = c1.series[1]
s2.graphicalProperties.line.solidFill = "00AAAA"
s2.graphicalProperties.line.dashStyle = "sysDot"
s2.graphicalProperties.line.width = 100050 # width in EMUs

# Style line 3
s3 = c1.series[2]
s3.smooth = True # Make the line smooth

After styling our chart, we add it to the sheet using the add_chart method which takes to arguments, one is the Chart object, and the second is the cell that we put the chart in (the cell where the upper right corner of the chart will be placed). In our example, cell F1.

# Add chart to sheet 
ws.add_chart(c1, "F1")

Finally we save the Workbook using the save method which takes a name that should end with .xlsx which is the Excel extension.

# Save Workbook
wb.save('line.xlsx')

Here’s the resulted Excel file from our python-openpyxl automation script.

From the left you can see the data we added, and on the right the Line chart which contains thee inner lines, the continues green line that represent Product 3, the circle-shaped dotted blue line that reference Product 2, and the triangle-shaped red line that represent Product1.

Note how the diagram starts at the predefined F1 Cell.

Also note that how the file is name as we intended, line (for line.xlsx).

Bar Chart – Excel Charts Automation

Now that the large part of the general explanation was done on the Line chart section above, This section wouldn’t require much explanation, we only need to follow the above mentioned steps.

As always, we start by importing the required classes. The required class for Bar charts in BarChart. The Series class is also needed for this example.

# import reauired libreries
from openpyxl import Workbook
from openpyxl.chart import BarChart, Series, Reference

Then, comes the creation of the workbook, and referencing the default sheet we have by creating an empty worksheet.

# create new workbook and get active sheet
wb = Workbook(write_only=True)
ws = wb.create_sheet()

After that, the creation and append of data.

Note how in this example we created the data as a list of tuples and not as a list of lists.

# create and add data to the worksheet
rows = [
    ('Number', 'Product 1', 'Product 2'),
    (2, 10, 30),
    (3, 40, 60),
    (4, 50, 70),
    (5, 20, 10),
    (6, 10, 40),
    (7, 50, 30),
]

for row in rows:
    ws.append(row)

After that, we pass to the initializations of the Bar chart object, and setting up the type, style, title, and axises tiles.

chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Bar Chart"
chart1.y_axis.title = 'Test number'
chart1.x_axis.title = 'Sample length (mm)'

We reference the data, as well as the column that is used as category.

data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)
cats = Reference(ws, min_col=1, min_row=2, max_row=7)

Then, add the data and category to the chart object. For the category, use the set_categories method. We set the shape (check the documentation for the available shapes).

We add the chart to our sheet at Cell A10.

chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
ws.add_chart(chart1, "A10")

At this point we can save the Workbook and check the results. However, we will add three more separated diagrams to visualize the differences when playing with parameters and properties.

To simplify the process, we use the deepcopy class the create new chart objects out of the one we already created above. Then change some styles and properties, and save each in a cell:

from copy import deepcopy

# Bar chart 2
chart2 = deepcopy(chart1)
chart2.style = 11
chart2.type = "bar"
chart2.title = "Horizontal Bar Chart"

ws.add_chart(chart2, "J10")

# Bar chart 3
chart3 = deepcopy(chart1)
chart3.type = "col"
chart3.style = 12
chart3.grouping = "stacked"
chart3.overlap = 100
chart3.title = 'Stacked Chart'

ws.add_chart(chart3, "A27")

# Bar chart 4
chart4 = deepcopy(chart1)
chart4.type = "bar"
chart4.style = 13
chart4.grouping = "percentStacked"
chart4.overlap = 100
chart4.title = 'Percent Stacked Chart'

ws.add_chart(chart4, "J27")

Finally, saving the file. remember that the file’s name has to end with .xlsx.

wb.save("bar.xlsx")

Here’s the resulted file.

The file contains the added data, and below are all four Bar charts. Each Bar chart has its predefined style and properties, and saved in the preassigned Cell.

Pie Chart – Excel Charts Automation

The same process applies to automating the creation of an Excel Pie chart using openpyxl.

First, we import the required classes. In this example we need the following classes: PieChart, DataPoint, ProjectedPieChart, Reference, and Workbook.

As the name applies, PieChart is used for creating standard Pie charts, Whereas ProjectedPieChart creates Projected Pie charts.

We need the DataPoint class when exploding a slice of the Pie.

from openpyxl import Workbook

from openpyxl.chart import (
    PieChart,
    ProjectedPieChart,
    Reference
)
from openpyxl.chart.series import DataPoint

After that comes the creation of the workbook, and selecting the sheet that will contain the data.

wb = Workbook()
ws = wb.active

Then, we create some data and append it to the spreadsheet.

data = [
    ['Pie', 'Sold'],
    ['Apple', 50],
    ['Cherry', 30],
    ['Pumpkin', 10],
    ['Chocolate', 40],
]

for row in data:
    ws.append(row)

After that, we instantiate the PieChart Object, set the referenced data and the category (labels), add both of them to the chart object, and finally set a title for the chart.

pie = PieChart()

labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=1, max_row=5)

pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)

pie.title = "Pies sold by category"

To explode the first slice of the Pie (Apple value) and separate it from the rest, we use the following code:

# Cut the first slice out of the pie
slice = DataPoint(idx=0, explosion=20)
pie.series[0].data_points = [slice]

Finally, we add the chart to the worksheet at Cell D1.

ws.add_chart(pie, "D1")

To take the this example further, we will create a new sheet that we name Projection, and use it to save two Projected Pie diagrams that we will create, one of type Pie, and other of type Bar.

Here’s the code for creating the new spreadsheet, and the data used.

# create a new worksheet
ws = wb.create_sheet(title="Projection")

data = [
    ['Page', 'Views'],
    ['Search', 95],
    ['Products', 4],
    ['Offers', 0.5],
    ['Sales', 0.5],
]

for row in data:
    ws.append(row)

As always, first we instantiate the chart object which we named projected_pie.

For Projected Pies, specify the type and splitType at first, then reference both the data and the categories in order to assign them to the chart object and finally save the diagram at Cell E1.

For the second projected Pie chart, deepcopy the first diagram which gives us an exact separate copy.

We change the type and splitType of the diagram before saving it at Cell E16.

# creating the projected pie chart of type pie
projected_pie = ProjectedPieChart()

projected_pie.type = "pie"
projected_pie.splitType = "val" # split by value

labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=1, max_row=5)

projected_pie.add_data(data, titles_from_data=True)
projected_pie.set_categories(labels)

ws.add_chart(projected_pie, "E1")

# creating the projected pie chart of type bar 
from copy import deepcopy

projected_bar = deepcopy(projected_pie)

projected_bar.type = "bar"
projected_bar.splitType = 'pos' # split by position

ws.add_chart(projected_bar, "E16")

Finally, save the Excel file. The chosen name is pie.xlsx.

wb.save("pie.xlsx")

Here are the two resulted sheets inside the pie.xlsx file:

The first contains data on the left and a Pie diagram on the right, notice how the Apple slice is separated from the rest.

The second sheet, which we name Projection, contains data on the left, and two Project Pie graphs on the right. The upper projection is of type pie, whereas the lower is of type bar.

Conclusion

By following the given steps in this article to perform the desired Excel charts Automation for your use case using openpyxl and python, the process becomes easier and straight forward.

Note that there are a lot of variables when it comes to openpyxl diagrams, same as when using Microsoft Excel manually, that is why one should visit the official documentation of the library if needs to some specific properties.