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.

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.

Copy
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")

• 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.

Copy
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)

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

Here is an example code to create a bar chart.

Copy
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")

• 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

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.