Python | Building charts in an Excel worksheet using the openpyxl module | Kit — one

Openpyxl — is a Python library with which you can perform several operations on Excel files, such as reading, writing, arithmetic, and graphing. Let's take a look at how to plot various charts using real-time data.

Charts consist of at least one series of one or more data points. The series themselves consist of references to ranges of cells.

To plot charts on an Excel worksheet, first, create a chart object of a specific chart class (eg BarChart, LineChart, etc.). After creating the chart objects, paste the data into it and finally add this chart object to the sheet object.

Code # 1: Draw a bar chart
To build a bar chart on an Excel sheet use the BarChart class BarChart from the openpyxl.chart submodule.

# import openpyxl module

import openpyxl

 
# import BarChart class from openpyxl.chart sub_module

from openpyxl .chart import BarChart, Reference

 
# Calling the Workbook () function from openpyxl
# create a new blank Workbook object

wb = openpyxl.Workbook ()

 
# Get the active sheet worksheet
# from active attribute.

sheet = wb.active

 
# write o to 9 in the 1st column of the active sheet

for i in range ( 10 ):

sheet.append ([ i])

 
# create post data Graph definitions

values ​​ = Reference (sheet , min_col = 1 , min_row = 1 ,

  max_col = 1 , max_row = 10 )

 
# Create BarChart object

chart = BarChart ()

  
# adding data to the histogram object
chart.add_data (values)

 
# set chart title

chart.title = "BAR-CHART"

 
# set the X-axis title

chart. x_axis.title = "X_AXIS"

  
# set Y-axis title

chart.y_axis.title = "Y_AXIS"

 
# add the chart to the sheet
# upper left corner of the chart
# anchored to cell E2.

sheet.add_chart (chart, "E2" )

 
# save file

wb.save ( "barChart.xlsx " )

Exit:

Code # 2: building a three-dimensional histogram

To build a three-dimensional histogram on an Excel sheet use BarChart3D class BarChart3D from openpyxl.chart submodule.

# import openpyxl module

import openpyxl

 
# import BarChart3D class from openpyxl.chart sub_module

from openpyxl.chart import BarChart3D, Reference

  
# write o up to 9 in the 1st column of the active sheet

for i in range ( 10 ):

sheet.append ([i])

 

values ​​ = Reference (sheet, min_col = 1 , min_row = 1 ,

max_col = 1 , max_row = 10 )

 
# Create an object of the BarChart3D class

chart = BarChart3D ()

 
chart.add_data (values)

 
# set the title of the chart

chart.title = "BAR-CHART3D"

 
# set X axis title

chart.x_axis.title = "X AXIS "

  
# set Y-axis title

chart.y_axis.title = "Y AXIS"

 
# add chart to sheet
# top left corner of the chart
# attached to cell E2.

sheet .add_chart (chart, "E2" )

 
# save file

wb.save ( "BarChart3D.xlsx" )

Output:

Code # 3: Terrain Plot

Use the AreaChart class AreaChart from the openpyxl.chart submodule to plot an area chart on an Excel sheet.

Exit :

Code # 4: 3D area plotting

Use the AreaChart3D class AreaChart3D from the openpyxl.chart submodule to build a 3D area chart on an Excel sheet.

import openpyxl

 
# import of the AreaChart class from openpyxl.chart sub_module

from openpyxl.chart import AreaChart, Reference

 

 

wb = openpyxl.Workbook ()

sheet = wb.active

 
# write o before 9 in the 1st column of the active sheet

for i in range ( 10 ):

sheet.append ([i])

 

values ​​ = Reference (sheet, min_col = 1 , min_row = 1 ,

max_col = 1 , max_row = 10 )

 
# create an ObjectChart class

chart = AreaChart ()

 
chart.add_data (values)

 
# set the title of the chart

chart.title = "AREA-CHART"

 
# set axis title X

chart.x_axis.title = " X-AXIS "

  
# set Y-axis title

chart.y_axis.title = "Y-AXIS"

 
# add chart to sheet
# top-left corner of the chart
# anchored to cell E2.

sheet.add_chart (chart, " E2 " )

 
# save file

wb.save ( "AreaChart.xlsx" )

import openpyxl

 
# import of the AreaChart3D class from openpyxl.chart sub_module

from openpyxl.chart import AreaChart3D, Reference

  

wb = openpyxl.Workbook ()

sheet = wb.active

 
# write o to 9 in the 1st column of the active sheet

for i in range ( 10 ):

sheet.append ([i])

 

values ​​ = Reference (sheet, min_col = 1 , min_row = 1 ,

max_col = 1 , max_row = 10 )

 
# Create an AreaChart3D object

chart = AreaChart3D ()

 
chart.add_data (values)

  
# set the title of the chart

chart.title = "AREA-CHART3D"

 
# set the X-axis title

chart.x_axis.title = "X-AXIS"

 
# install y axis title

chart.y_axis.title = " Y-AXIS "

  
# add chart to sheet
# top left corner of the chart
# anchored to cell E2.

sheet.add_chart (chart, "E2" )

 
# save file

wb.save ( " AreaChart3D.xlsx " )

Exit :

Code # 5: Build a linear diagram.
Use the LineChart class LineChart from the openpyxl.chart submodule to build a line chart on an Excel sheet.

# module import openpyxl

import openpyxl

 
# import LineChart class from openpyxl.chart sub_module

from openpyxl.chart import LineChart, Reference

 

wb = openpyxl.Workbook ()

sheet = wb.active

  
# write o to 9 in 1st column of active sheet

for i in range ( 10 ):

sheet.append ([i])

 

values ​​ = Reference (sheet, min_col = 1 , min_row = 1 ,

max_col = 1 , max_row = 10 )

  
# Create an object of the LineChart class

chart = LineChart ()

  
chart.add_data (values)

 
# set title chart

chart.title = "LINE -CHART "

  
# set X axis title

chart.x_axis.title = "X-AXIS"

 
# set Y-axis title

chart.y_axis.title = "Y-AXIS"

 
# add chart to sheet
# top left corner of the chart
# attached to cell E2.

sheet.add_chart (chart, "E2" )

 
# save file

wb.save ( " LineChart.xlsx " )

Exit:

Code # 6: Build a 3D line chart.
To build a 3D line chart on an Excel sheet, we must use the LineChart3D class LineChart3D from the openpyxl.chart submodule.

import openpyxl

 
# import of LineChart3D class from openpyxl.chart sub_module

from openpyxl.chart import LineChart3D, Reference

 

wb = openpyxl.Workbook ()

sheet = wb.active

 
# n Write o to 9 in the 1st column of the active sheet

for i in range ( 10 ):

sheet.append ([i])

 

values ​​ = Reference (sheet, min_col = 1 , min_row = 1 ,

max_col = 1 , max_row = 10 )

 
# Create an object of the LineChart3D class

chart = LineChart3D ()

 
chart.add_data (values)

 
# set the title of the chart

chart.title = "LINE-CHART3D"

 

 
# set the X-axis title

chart.x_axis.title = "X-AXIS"

 
# set Y-axis title

chart.y_axis.title = "Y-AXIS"

 
# add the chart to the sheet
# top left corner of the chart
# attached to cell E2.

sheet.add_chart (chart, " E2 " )

  
# save file

wb.save ( "LineChart3D.xlsx" )

Exit:





Get Solution for free from DataCamp guru