How do i plot a bar graph in excel using python?

import xlsxwriter

workbook = xlsxwriter.Workbook['chart_bar2.xlsx']

worksheet = workbook.add_worksheet[]

bold = workbook.add_format[{'bold': 1}]

headings = ['Number', 'Batch 1', 'Batch 2']

data = [

    [2, 3, 4, 5, 6, 7],

    [80, 80, 100, 60, 50, 100],

    [60, 50, 60, 20, 10, 20],

]

worksheet.write_row['A1', headings, bold]

worksheet.write_column['A2', data[0]]

worksheet.write_column['B2', data[1]]

worksheet.write_column['C2', data[2]]

chart1 = workbook.add_chart[{'type': 'bar', 'subtype': 'stacked'}]

chart1.add_series[{

    'name':       '= Sheet1 !$B$1',

    'categories': '= Sheet1 !$A$2:$A$7',

    'values':     '= Sheet1 !$B$2:$B$7',

}]

chart1.add_series[{

    'name':       ['Sheet1', 0, 2],

    'categories': ['Sheet1', 1, 0, 6, 0],

    'values':     ['Sheet1', 1, 2, 6, 2],

}]

chart1.set_title [{'name': 'Results of data analysis'}]

chart1.set_x_axis[{'name': 'Test number'}]

chart1.set_y_axis[{'name': 'Data length [mm]'}]

chart1.set_style[11]

worksheet.insert_chart['E2', chart1]

workbook.close[]

Prerequisites

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

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

1. Vertical Bar Charts
2. Horizontal Bar Charts
3. Stacked Bar Chart
4. Percent Stacked Bar Chart
5. 3D Bar Charts

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

How do you make a bar graph in Excel in Python?

How do you run a bar graph in Python?

Which function is used to draw a bar graph in Python?

How do I create a bar graph from CSV in Python?