Creating Charts using openpyxl
Last Updated :
04 Sep, 2024
Charts are a powerful way to visualize data, making it easier to analyze and present information. While working with Excel files in Python, the library openpyxl comes into the picture and can be used as a tool for automating tasks like reading data, writing data, formatting cells, and of course creating charts. In this article, we will learn to create charts covering both fundamental concepts and detailed step-by-step code implementation.
What is Openpyxl?
The openpyxl is a Python library used to read and write Excel files. It provides a comprehensive API to manage Excel worksheets, including the ability to create, modify, and style cells, as well as to generate charts. Since charts are very useful for identifying trends, patterns, and insights for businesses, openpyxl is an essential tool for creating charts for data visualization. It is also useful for automating tasks related to Excel, such as generating reports or processing data in bulk.
Firstly we'll understand fundamental key concepts of openpyxl Python library and create charts using it, then we will deep dive into the code example for chart creation.
- Workbook and Worksheet: As we know a workbook is an Excel file, and a worksheet is basically a single sheet within a workbook file. In the Python library openpyxl, we need to create a new workbook or open an existing workbook and we should select the worksheet within the workbook, where we can add the chart.
- Chart Types: The Python library openpyxl can support various charts like Line chart, Bar Chart, Scatter Chart, Pie chart, etc. Each and every chart helps us in different situation and serves different purposes depending on the nature of data we have.
- Chart Data Series: A chart is usually comprised of one or more data series, out of which each series represents a collection of related data points that are plotted on the chart.
- Chart Axes: As we know, charts often have two two axes X-axis (horizontal) and the Y-axis (vertical), these axes are used to map the data series to the chart area.
- Anchoring the Chart: The charts must be positioned within the worksheet in our workbook. This can be done by specifying the cell where the top left corner of the chart should be anchored.
Creating Charts using openpyxl
Let us see a step-by-step process to create charts using openpyxl in Python.
Intall 'openpyxl'
If openpyxl is not already installed in ourr system, we can install it by using the following command in the command line or terminal. Use pip if the OS is Windows, and pip3 if the OS is Mac or Linux.
pip/pip3 install openpyxl
Import Necessary Modules
Next, import the necessary modules from the openpyxl python library such as Workbook, Bar Chart, etc.
Python
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
Create a Workbook and Add Data
Now create a new or load an existing workbook and add sample data to the worksheet.
Python
wb = Workbook()
ws = wb.active
data = [
["Year", "Sales"],
[2020, 300],
[2021, 400],
[2022, 350],
[2023, 500],
]
for row in data:
ws.append(row)
Create a Basic Chart
In this step, we will create a basic chart using the Class 'BarChart', then define the data series and categories, now add the chart to the worksheet.
Python
chart = BarChart()
data = Reference(ws, min_col=2, min_row=1, max_row=5)
categories = Reference(ws, min_col=1, min_row=2, max_row=5)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
ws.add_chart(chart, "E5")
Save the Workbook
In this final step, save the workbook to an Excel File.
Python
# ...
wb.save("sales_chart.xlsx")
Creating Charts
Let us see a few different examples, to see how we can create charts using openpyxl.
1. Bar Graph
In this example, we will create a bar graph using openpyxl library. The bar chart will be created using the BarChart() function.
Python
# import necessary library
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
# Create a new Workbook
wb = Workbook()
ws = wb.active
# Add data to the worksheet
data = [
["Year", "Sales"],
[2020, 300],
[2021, 400],
[2022, 350],
[2023, 500],
]
for row in data:
ws.append(row)
# Create a BarChart object
chart = BarChart()
# Define the data (sales values) for the chart
data = Reference(ws, min_col=2, min_row=1, max_row=5)
# Define the categories (years)
categories = Reference(ws, min_col=1, min_row=2, max_row=5)
# Add data and categories to the chart
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
# Add the chart to the worksheet
ws.add_chart(chart, "E5")
# Save the Workbook to a file
wb.save("sales_chart.xlsx")
Output:
Bar Chart using openpyxl2. Line Chart
In this example, we will create a Line chart using the LineChart() function.
Python
from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference
# Create a new Workbook
wb = Workbook()
ws = wb.active
# Add data to the worksheet
data = [
["Year", "Sales"],
[2020, 300],
[2021, 400],
[2022, 350],
[2023, 500],
]
for row in data:
ws.append(row)
# Create a LineChart object
chart = LineChart()
# Define the data (sales values) for the chart
data = Reference(ws, min_col=2, min_row=1, max_row=5)
# Define the categories (years)
categories = Reference(ws, min_col=1, min_row=2, max_row=5)
# Add data and categories to the chart
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
# Add the chart to the worksheet
ws.add_chart(chart, "E5")
# Save the Workbook to a file
wb.save("sales_line_chart.xlsx")
Output:
Line Chart using openpyxl3. Scattered Chart
In this example, we will create a Scatter Chart using the ScatterChart() function.
Python
from openpyxl import Workbook
from openpyxl.chart import ScatterChart, Reference, Series
# Create a new Workbook
wb = Workbook()
ws = wb.active
# Add data to the worksheet
data = [
["X", "Y1", "Y2"],
[1, 10, 20],
[2, 20, 30],
[3, 30, 40],
[4, 40, 50],
[5, 50, 60],
]
for row in data:
ws.append(row)
# Create a ScatterChart object
chart = ScatterChart()
chart.title = "Scatter Chart Example"
chart.style = 13
chart.x_axis.title = 'X Axis'
chart.y_axis.title = 'Y Axis'
# Define the X and Y data for the first series
x_values = Reference(ws, min_col=1, min_row=2, max_row=6)
y_values = Reference(ws, min_col=2, min_row=2, max_row=6)
series1 = Series(y_values, x_values, title="Y1 Series")
# Define the Y data for the second series
y_values2 = Reference(ws, min_col=3, min_row=2, max_row=6)
series2 = Series(y_values2, x_values, title="Y2 Series")
# Add the series to the chart
chart.series.append(series1)
chart.series.append(series2)
# Add the chart to the worksheet
ws.add_chart(chart, "E5")
# Save the Workbook to a file
wb.save("scatter_chart.xlsx")
Output:
Scatter Chart using openpyxl4. Pie Chart
In this example, we will create a Pie Chart using the PieChart() function.
Python
from openpyxl import Workbook
from openpyxl.chart import PieChart, Reference
# Create a new Workbook
wb = Workbook()
ws = wb.active
# Add data to the worksheet
data = [
["Category", "Value"],
["A", 40],
["B", 30],
["C", 20],
["D", 10],
]
for row in data:
ws.append(row)
# Create a PieChart object
chart = PieChart()
# Define the data (values) for the chart
data = Reference(ws, min_col=2, min_row=2, max_row=5)
# Define the categories (labels)
categories = Reference(ws, min_col=1, min_row=2, max_row=5)
# Add data and categories to the chart
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
# Add the chart to the worksheet
ws.add_chart(chart, "E5")
# Save the Workbook to a file
wb.save("category_pie_chart.xlsx")
Output:
Pie Chart using openpyxlConclusion
Creating charts using openpyxl in Python is a straightforward process that allows for dynamic data visualization within Excel files. Whether we need bar charts, line charts, or other types of charts, openpyxl provides the tools necessary to automate and customize our Excel-based reports. With these examples, we should be well-equipped to start integrating charts into our own Excel projects.
Similar Reads
Formatting Cells using openpyxl in Python
When it comes to managing Excel files programmatically, Python offers a powerful tool in the form of the openpyxl library. This library not only allows us to read and write Excel documents but also provides extensive support for cell formatting. From fonts and colors to alignment and borders, openpy
4 min read
PYGLET - Creating window
In this article we will see how we can create a window in PYGLET module in python. Pyglet is easy to use but powerful library for developing visually rich GUI applications like games, multimedia etc on Windows, Mac OS and Linux. This library is created purely in Python and it supports many features
2 min read
Create an empty file using Python
File handling is a very important concept for any programmer. It can be used for creating, deleting, and moving files, or to store application data, user configurations, videos, images, etc. Python too supports file handling and allows users to handle files i.e., to read and write files, along with
3 min read
Create a Memory Puzzle Game Using Pygame
Playing memory puzzle games provides entertainment and boosts mental abilities such as focus and memory recall. This article guides you through the steps to create a straightforward memory puzzle game using the Python Pygame Library. After completing the article, you will have a fully functioning ga
10 min read
Creating the Workbook and Worksheet using openpyxl in Python
Openpyxl is a Python library designed to read and write Excel (xlsx/xlsm/xltx/xltm) files. It's a great tool for automating Excel operations in Python, making it easier to create, modify, and extract information from Excel files. Openpyxl is a Python library that lets us work with Excel files direct
6 min read
Adding Conditional Formatting to Excel Using Python Openpyxl
Adding conditional formatting using openpyxl is an easy and straightforward process. While working with Excel files, conditional formatting is useful for the visualization of trends in the data, highlighting crucial data points, and making data more meaningful and understandable. In this article, we
5 min read
C Extension Module using Python
Writing a simple C extension module directly using Python’s extension API and no other tools. It is straightforward to make a handcrafted extension module for a simple C code. But first, we have to make sure that the C code has a proper header file. Code #1 : #include <math.h> extern int gcd(i
4 min read
Python code formatting using Black
Writing well-formatted code is very important, small programs are easy to understand but as programs get complex they get harder and harder to understand. At some point, you can’t even understand the code written by you. To avoid this, it is needed to write code in a readable format. Here Black come
3 min read
Merge and Unmerge Excel Cells using openpyxl in Python
In this article, we are going to learn about Python programs to merge and unmerge excel cells using openpyxl. IntroductionOpenpyxl is a Python library commonly used for reading and writing Excel Files. In this article, we will be using this library to merge cells in Excel. Merging is helpful when we
4 min read
Python | Plotting charts in excel sheet using openpyxl module | Set - 1
Prerequisite: Reading & Writing to excel sheet using openpyxl Openpyxl is a Python library using which one can perform multiple operations on excel files like reading, writing, arithmetic operations and plotting graphs. Let's see how to plot different charts using realtime data. Charts are compo
6 min read