Visualizing Data with Charts

Using a Chart can help you visualize data in a more intuitive way.

While simple data can be quickly visualized by manually creating charts in Excel, automating chart creation through a program is much more efficient when dealing with large-scale or frequently updated data.

openpyxl offers useful methods to create charts via code.

In this session, we will learn how to add charts to an Excel file using openpyxl.

Note: This session covers how to create Line Charts and Bar Charts. However, openpyxl also supports various chart types like Donut Charts, Pie Charts, etc.

Creating a Line Chart

Line Charts are often used to visually represent changes in data over time.

Here is an example code to create a line chart using openpyxl.

Creating a Line Chart

from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference

# Creating a new workbook
wb = Workbook()

# Selecting the active sheet
ws = wb.active

# Adding data (e.g., monthly sales)
data = [
    ['Month', 'Sales'],
    ['January', 30],
    ['February', 40],
    ['March', 50],
    ['April', 20],
    ['May', 60],
]

for row in data:
    ws.append(row)

# Creating a chart
line_chart = LineChart()

# Referencing chart data (from B2 to B6)
data = Reference(ws, min_col=2, min_row=2, max_row=6)

# Referencing X-axis (categories) labels (from A2 to A6)
cats = Reference(ws, min_col=1, min_row=2, max_row=6)

# Adding data to the chart without including the title
line_chart.add_data(data, titles_from_data=False)
line_chart.set_categories(cats)
line_chart.title = "Monthly Sales"
line_chart.x_axis.title = "Month"
line_chart.y_axis.title = "Sales"

# Adding the chart to the sheet (at cell A10)
ws.add_chart(line_chart, "A10")

# Saving the file
wb.save("output_file.xlsx")

Code Explanation

• line_chart = LineChart(): Create a LineChart object

• Reference: Reference the range of data and categories (months) for the chart

• line_chart.add_data(data, titles_from_data=False): Add data to the chart without including titles

• line_chart.set_categories(cats): Set the X-axis (categories) labels

• line_chart.title: Set the chart title

• line_chart.x_axis.title: Set the X-axis title

• line_chart.y_axis.title: Set the Y-axis title

• ws.add_chart(line_chart, "A10"): Add the chart to the sheet

The above code defines the monthly sales data in a list for simplicity.

However, using openpyxl, you can specify a file path (e.g., C:/Users/your_username/Desktop/output_file.xlsx) to directly load and read data from an Excel file.

Reading Data from an Excel File

from openpyxl import load_workbook

# Loading the Excel file
wb = load_workbook("C:/Users/your_username/Desktop/input_file.xlsx")
ws = wb.active

# Reading data
data = []

for row in ws.iter_rows(min_row=2, values_only=True):
    data.append(row)

Creating a Bar Chart

Bar Charts are commonly used to compare the size of values between different categories.

Here is an example code to create a bar chart.

Creating a Bar Chart

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

# Creating a new workbook
wb = Workbook()

# Selecting the active sheet
ws = wb.active

# Adding data (e.g., monthly revenue)
data = [
    ['Month', 'Revenue'],
    ['January', 1000],
    ['February', 1500],
    ['March', 1200],
    ['April', 1700],
    ['May', 1300],
]

for row in data:
    ws.append(row)

# Creating a chart
bar_chart = BarChart()

# Referencing chart data (from B2 to B6)
data = Reference(ws, min_col=2, min_row=2, max_row=6)

# Referencing X-axis (categories) labels (from A2 to A6)
cats = Reference(ws, min_col=1, min_row=2, max_row=6)

# Adding data to the chart without including the title
bar_chart.add_data(data, titles_from_data=False)
bar_chart.set_categories(cats)
bar_chart.title = "Monthly Revenue"
bar_chart.x_axis.title = "Month"
bar_chart.y_axis.title = "Revenue"

# Adding the chart to the sheet (at cell A10)
ws.add_chart(bar_chart, "A10")

# Saving the file
wb.save("output_file.xlsx")

Code Explanation

• bar_chart = BarChart(): Create a BarChart object

• Reference: Reference the range of data and categories (months) for the chart

• bar_chart.add_data(data, titles_from_data=False): Add data to the chart without including titles

• bar_chart.set_categories(cats): Set the X-axis (categories) labels

• bar_chart.title: Set the chart title

• bar_chart.x_axis.title: Set the X-axis title

• bar_chart.y_axis.title: Set the Y-axis title

• ws.add_chart(bar_chart, "A10"): Add the chart to the sheet

Creating Various Types of Charts

In addition to Line and Bar Charts, openpyxl supports a variety of chart types such as Pie Charts, Doughnut Charts, and Histograms.

You can learn more about each chart type in the chart module documentation.

Want to learn more?

Join CodeFriends Plus membership or enroll in a course to start your journey.

Learn moreSign In