How to create charts in excel using Python with openpyxl?

In this tutorial, we'll create Excel charts using Python's openpyxl module. We'll build a spreadsheet with tennis players' Grand Slam titles and create a bar chart to visualize the data.

What is openpyxl?

The openpyxl module is a powerful Python library for working with Excel files (.xlsx). Unlike the xlrd module which is read-only, openpyxl supports both reading and writing operations, making it ideal for creating charts and manipulating Excel data.

Installation

First, install the openpyxl module ?

pip install openpyxl

Creating a Spreadsheet with Data

Let's start by creating a new Excel file with tennis player data ?

import openpyxl

# Define file name and data
file_name = "charts.xlsx"
file_data = [
    ['player', 'titles'], 
    ['Federer', 20], 
    ['Nadal', 20], 
    ['Djokovic', 17], 
    ['Murray', 3]
]

# Create a new workbook
workbook = openpyxl.Workbook()
print(f"Sheets in workbook: {workbook.sheetnames}")

# Get the default sheet
worksheet = workbook['Sheet']

# Add data to the worksheet
for row, (player, titles) in enumerate(file_data, 1):
    worksheet[f'A{row}'].value = player
    worksheet[f'B{row}'].value = titles

# Save the workbook
workbook.save(file_name)
print("Data saved to charts.xlsx")

Sheets in workbook: ['Sheet']
Data saved to charts.xlsx

Creating a Bar Chart

Now let's add a bar chart to visualize the tennis players' Grand Slam titles ?

from openpyxl.chart import BarChart, Reference
import openpyxl

# Load the existing workbook
workbook = openpyxl.load_workbook("charts.xlsx")
worksheet = workbook['Sheet']

# Create a bar chart object
chart = BarChart()

# Set chart properties
chart.title = "Players & Grand Slams"
chart.y_axis.title = 'Titles'
chart.x_axis.title = 'Tennis Players'

# Create reference to data (excluding header row for titles, including for labels)
data = Reference(worksheet, min_row=2, max_row=5, min_col=2, max_col=2)
categories = Reference(worksheet, min_row=2, max_row=5, min_col=1, max_col=1)

# Add data and categories to chart
chart.add_data(data, titles_from_data=False)
chart.set_categories(categories)

# Add chart to worksheet at position A7
worksheet.add_chart(chart, "A7")

# Save the workbook
workbook.save("charts.xlsx")
print("Chart added to Excel file successfully!")

Chart added to Excel file successfully!

Complete Example

Here's the complete code that creates both the data and chart in one go ?

import openpyxl
from openpyxl.chart import BarChart, Reference

# Define file name and data
file_name = "tennis_charts.xlsx"
file_data = [
    ['Player', 'Grand Slam Titles'], 
    ['Federer', 20], 
    ['Nadal', 22], 
    ['Djokovic', 21], 
    ['Murray', 3]
]

# Create workbook and worksheet
workbook = openpyxl.Workbook()
worksheet = workbook.active
worksheet.title = "Tennis Stats"

# Add data to worksheet
for row_num, (player, titles) in enumerate(file_data, 1):
    worksheet.cell(row=row_num, column=1, value=player)
    worksheet.cell(row=row_num, column=2, value=titles)

# Create bar chart
chart = BarChart()
chart.title = "Tennis Players Grand Slam Titles"
chart.y_axis.title = 'Number of Titles'
chart.x_axis.title = 'Players'

# Set up data references
data = Reference(worksheet, min_row=2, max_row=5, min_col=2, max_col=2)
categories = Reference(worksheet, min_row=2, max_row=5, min_col=1, max_col=1)

chart.add_data(data, titles_from_data=False)
chart.set_categories(categories)

# Add chart to worksheet
worksheet.add_chart(chart, "D2")

# Save the file
workbook.save(file_name)
print(f"Excel file '{file_name}' created with chart!")

# Verify the data
print("\nData in spreadsheet:")
for row in worksheet.iter_rows(min_row=1, max_row=5, values_only=True):
    print(row)

Excel file 'tennis_charts.xlsx' created with chart!

Data in spreadsheet:
('Player', 'Grand Slam Titles')
('Federer', 20)
('Nadal', 22)
('Djokovic', 21)
('Murray', 3)

Key Chart Parameters

Conclusion

Using openpyxl, you can easily create Excel files with data and charts programmatically. The Reference object defines data ranges, while chart objects like BarChart provide visualization capabilities. This approach is perfect for automating report generation with visual data representation.