Openpyxl - Plotting Bar Charts in Excel

By Lenin Mishra

Prerequisites

  1. Reading Excel data with Openpyxl
  2. Writing to Excel with Openpyxl

Consider the data shown in the below image.

MS Excel file - wb1.xlsx

For plotting a Bar Chart on an Excel sheet, you have to use BarChart class from openpyxl.chart submodule.

Example 1 - Vertical Bar Charts

Vertical Bar charts are also kown as Column Charts. Let’s plot a Vertical Bar Chart for the quantities listed for every product.

Code

import openpyxl
from openpyxl.chart import BarChart, Reference

wb = openpyxl.load_workbook('wb1.xlsx')
sheet = wb.active

# Data for plotting
values = Reference(sheet,
                   min_col=4,
                   max_col=4,
                   min_row=1,
                   max_row=11)

cats = Reference(sheet, min_col=2, max_col=2, min_row=2, max_row=11)

# Create object of BarChart class
chart = BarChart()
chart.add_data(values, titles_from_data=True)
chart.set_categories(cats)

# set the title of the chart
chart.title = "Total Inventory"

# set the title of the x-axis
chart.x_axis.title = "Products"

# set the title of the y-axis
chart.y_axis.title = "Inventory per product"

# the top-left corner of the chart
# is anchored to cell F2 .
sheet.add_chart(chart,"F2")

# save the file 
wb.save("wb1.xlsx")

Output

Bar Chat with Openpyxl

By default, the size is 15 x 7.5 cm (approximately 5 columns by 14 rows). This can be changed by setting the anchor width and height properties of the chart.

import openpyxl
from openpyxl.chart import BarChart, Reference

wb = openpyxl.load_workbook('wb1.xlsx')
sheet = wb.active

# Data for plotting
# Just take the data from last column
values = Reference(sheet,
                   min_col=4,
                   max_col=4,
                   min_row=1,
                   max_row=11)

cats = Reference(sheet, min_col=2, max_col=2, min_row=2, max_row=11)

# Create object of BarChart class
chart = BarChart()
chart.height = 20
chart.width = 30
chart.add_data(values, titles_from_data=True)
chart.set_categories(cats)

# set the title of the chart
chart.title = "Total Inventory"

# set the title of the x-axis
chart.x_axis.title = "Products"

# set the title of the y-axis
chart.y_axis.title = "Inventory per product"

# the top-left corner of the chart
# is anchored to cell F2 .
sheet.add_chart(chart,"F2")

# save the file 
wb.save("wb1.xlsx")

Bar Chat with Openpyxl

Example 2 - Horiontal Bar Charts

You can switch between vertical and horizontal bar charts by setting chart.type to col or bar respectively. By default, it is set to col for vertical Bar Charts.

Code

import openpyxl
from openpyxl.chart import BarChart, Reference

wb = openpyxl.load_workbook('wb1.xlsx')
sheet = wb.active

# Data for plotting
# Just take the data from last column
values = Reference(sheet,
                   min_col=4,
                   max_col=4,
                   min_row=1,
                   max_row=11)

cats = Reference(sheet, min_col=2, max_col=2, min_row=2, max_row=11)

# Create object of BarChart class
chart = BarChart()
chart.type = 'bar'
chart.add_data(values, titles_from_data=True)
chart.set_categories(cats)

# set the title of the chart
chart.title = "Total Inventory"

# set the title of the x-axis
chart.x_axis.title = "Products"

# set the title of the y-axis
chart.y_axis.title = "Inventory per product"

# the top-left corner of the chart
# is anchored to cell F2 .
sheet.add_chart(chart,"F2")

# save the file 
wb.save("wb1.xlsx")

Output

Horizontal Bar Chat with Openpyxl

Example 3 - Stacked Bar Chart

Let’s add one more row to our data - Quantity Sold.

New Sales Data in Excel

To create a stacked Bar chart, the overlap needs to be set to 100.

Code

import openpyxl
from openpyxl.chart import BarChart, Reference

wb = openpyxl.load_workbook('wb1.xlsx')
sheet = wb.active

# Data for plotting
# Just take the data from last column
values = Reference(sheet,
                   min_col=4,
                   max_col=5,
                   min_row=1,
                   max_row=11)

cats = Reference(sheet, min_col=2, max_col=2, min_row=2, max_row=11)

# Create object of BarChart class
chart = BarChart()
chart.type = 'col'
chart.grouping = "stacked"
chart.overlap = 100
chart.add_data(values, titles_from_data=True)
chart.set_categories(cats)

# set the title of the chart
chart.title = "Total Inventory/Sales - Stacked"

# set the title of the x-axis
chart.x_axis.title = "Products"

# set the title of the y-axis
chart.y_axis.title = "Inventory/Sales data per product"

# the top-left corner of the chart
# is anchored to cell G2 .
sheet.add_chart(chart,"G2")

# save the file 
wb.save("wb1.xlsx")

Output

Stacked Bar Chat with Openpyxl

Example 4 - Percent Stacked Bar Chart

To draw Percentage Stacked Bar Charts with Openpyxl, use percentStacked grouping for your chart.

Code

import openpyxl
from openpyxl.chart import BarChart, Reference

wb = openpyxl.load_workbook('wb1.xlsx')
sheet = wb.active

# Data for plotting
# Just take the data from last column
values = Reference(sheet,
                   min_col=4,
                   max_col=5,
                   min_row=1,
                   max_row=11)

cats = Reference(sheet, min_col=2, max_col=2, min_row=2, max_row=11)

# Create object of BarChart class
chart = BarChart()
chart.type = 'col'
chart.grouping = "percentStacked"
chart.overlap = 100
chart.add_data(values, titles_from_data=True)
chart.set_categories(cats)

# set the title of the chart
chart.title = "Total Inventory/Sales - Stacked"

# set the title of the x-axis
chart.x_axis.title = "Products"

# set the title of the y-axis
chart.y_axis.title = "Inventory/Sales data per product"

# the top-left corner of the chart
# is anchored to cell G2 .
sheet.add_chart(chart,"G2")

# save the file 
wb.save("wb1.xlsx")

Output

Percentage Stacked Bar Chat with Openpyxl

Example 5 - 3D Bar Charts

To convert your Bar charts from 2D to 3D, you need to use the BarChart3D class from openpyxl.chart submodule.

Code

import openpyxl
from openpyxl.chart import Reference, BarChart3D

wb = openpyxl.load_workbook('wb1.xlsx')
sheet = wb.active

# Data for plotting
# Just take the data from last column
values = Reference(sheet,
                   min_col=4,
                   max_col=4,
                   min_row=1,
                   max_row=11)

cats = Reference(sheet, min_col=2, max_col=2, min_row=2, max_row=11)

# Create object of BarChart class
chart = BarChart3D()

chart.add_data(values, titles_from_data=True)
chart.set_categories(cats)

# set the title of the chart
chart.title = "Total Inventory"

# set the title of the x-axis
chart.x_axis.title = "Products"

# set the title of the y-axis
chart.y_axis.title = "Inventory per product"

# the top-left corner of the chart
# is anchored to cell F2 .
sheet.add_chart(chart,"F2")

# save the file 
wb.save("wb1.xlsx")

Output

3D Bar Chat with Openpyxl

  1. Openpyxl Tutorial: Handling Excel sheets in Python
  2. Combining multiple Excel sheets into one in Python
  3. Openpyxl - Plotting Bubble Charts in Excel
  4. Openpyxl - Plotting Line Charts in Excel
  5. Openpyxl - Adding hyperlinks to cells in Excel