Openpyxl - Plotting Bar Charts in Excel
Learn to plot Bar Charts in Excel with Openpyxl module in Python 3.
Prerequisites
Consider the data shown in the below image.
For plotting a Bar Chart on an Excel sheet using Python, you have to use BarChart class from openpyxl.chart submodule.
Topics Covered
Example 1 - Vertical Bar Charts
Vertical Bar charts are also known 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
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.
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.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")Output
Example 2 - Horizontal 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
Example 3 - Stacked Bar Chart
Let’s add one more row to our data - Quantity Sold.
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
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
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
