+

Python | Building charts in an Excel worksheet using the openpyxl module | Set 3

Openpyxl — is a Python library that can perform several operations on Excel files, such as reading, writing, arithmetic, and graphing.

Charts are composed of at least one series of one or more data points. The series themselves are made up of references to ranges of cells. Let`s see how to plot Doughnot, Radar, Surface, 3D Surface Chart on an Excel sheet using openpyxl.

To plot charts on an Excel sheet, first, create a chart object of a specific chart class (e.g. SurfaceChart, RadarChart, etc.). After creating the chart objects, paste the data into it and finally add this chart object to the sheet object. Let`s see how to plot various charts using real-time data.

Code # 1: plotting a donut chart

Donut charts are similar to pie charts except that they are using a ring instead of a circle. They can also plot multiple datasets as concentric rings. To plot a donut chart on an Excel sheet, use the DonutChart class from the openpyxl.chart submodule.

# import book from openpyxl

from openpyxl import Workbook

 
# import DonutChart, Link from openpyxl.chart submodule.

from openpyxl.chart import DonutChart, Reference

 
# import DataPoint from the openpyxl class .chart.series

from openpyxl.chart.series import DataPoint

 
# Function call Workbook () from openpyxl
# create a new empty Workbook object

wb = Workbook ()

 
# Get the worksheet of the active sheet
# from the active attribute.

ws = wb.active

 
# data given

data = [

[ ` Pie` , 2014 ],

[ `Plain` , 40 ],

[ `Jam` , 2 ],

[ `Lime` , 20 ],

[ `Chocolate` , 30 ],

]

  
# write the contents of each line in 1st and 2nd
# active sheet column respectively.

for row in data:

ws.append (row)

 
# Create class object DonutChart

chart = DonutChart ()

 
# create data for plotting

labels = Reference (ws, min_col = 1 , min_row = 2 , max_row = 5 )

data = Reference (ws, min_col = 2 , min_row = 1 , max_row = 5 )

 
# add data to the Donut chart

chart .add_data (data, titles_from_data = True )

 
# set labels on the chart object
chart.set_categories (labels)

  
# mouth update chart title

chart.title = "Donuts Chart"

 
# set chart style

chart.style = 26

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

ws.add_chart (chart , "E1" )

 
# save file

wb.save ( " donut.xlsx " )

Output:

Code # 2: plotting a radar chart

Data arranged in columns or rows on a worksheet can be plotted on a radar chart. Radar charts compare the aggregate values ​​of multiple data series. This is actually the projection of the area chart onto the circular X axis. To build a Radar chart in an Excel worksheet, use the RadarChart class from the openpyxl.chart submodule.

# import book from openpyxl

from openpyxl import Workbook

 
# import RadarChart, Link from submodule openpyxl.chart.

from openpyxl.chart import RadarChart, Reference

  
# Call the Workbook () function from openpyxl
# create a new empty Workbook object

wb = Workbook ()

 
# Get the worksheet of the active sheet
# from the active attribute.

ws = wb.active

 
# data given

data = [

[ ` Month` , "Bulbs" , "Seeds" , "Flowers" , "Trees & amp; shrubs " ],

  [ `Jan` , 0 , 2500 , 500 , 0 ,],

[ ` Feb` , 0 , 5500 , 750 , 1500 ],

[ `Mar` , 0 , 9000 , 1500 , 2500 ],

[ `Apr` , 0 , 6500 , 2000 , 4000 ],

[ `May` , 0 , 3500 , 5500 , 3500 ] ,

[ ` Jun` , 0 , 0 , 7500 , 1500 ],

[ `Jul` , 0 , 0 , 8500 , 800 ],

[ `Aug` , 1500 , 0 , 7000 , 550 ],

[ ` Sep` , 5000 , 0 , 3500 , 2500 ],

[ `Oct` , 8500 , 0 , 2500 , 6000 ],

[ `Nov` , 3500 , 0 , 500 , 5500 ],

[ `Dec` , 500 , 0 , 100 , 3000 ],

]

 
# write the contents of each line in the 1st and 2nd
# active sheet column respectively.

for row in data:

ws.append (row)

 
# Create an object of the RadarChart class

chart = RadarChart ()

 
# filled radar chart type

chart. type = "filled"

 
# create data for plotting

labels = Reference (ws, min_col = 1 , min_row = 2 , max_row = 13 )

data = Reference (ws, min_col = 2 , max_col = 5 , min_row = 2 , max_row = 13 )

 
# adding data to radar chart object

chart.add_data (data, titles_from_data = True )

 
# set labels on the chart object
chart.set_categories (labels)

  
# set chart title

chart.title = "Radar Chart"

 
# set chart style

chart.style = 26

  
# remove Y-axis from the chart

chart.y_axis.delete = True

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

ws.add_chart (chart, "G2" )

 
# save file

wb.save ( "Radar.xlsx" )

Exit:

Code # 3: Surface plot

Data arranged in columns or rows on a worksheet can be plotted on a surface diagram. Surface chart is useful when you want to find optimal combinations between two datasets. As with a topographic map, colors and patterns indicate areas that are in the same range of values. To plot a surface chart on an Excel worksheet, use the SurfaceChart class from the openpyxl.chart submodule.

# import book from openpyxl

from openpyxl import Workbook

 
# import SurfaceChart, Reference, Series from openpyxl.chart sub_module.

from openpyxl.chart import SurfaceChart, Reference, Series

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

wb = Workbook ()

 
# Get worksheet active sheet
# from active attribute.

ws = wb.active

 
# data

data = [

[ None , 10 , 20 , 30 , 40 , 50 ,],

[ 0.1 , 15 , 65 , 105 , 65 , 15 ,],

[ 0.2 , 35 , 105 , 170 , 105 , 35 ,],

[ 0.3 , 55 , 135 , 215 , 135 , 55 ,],

[ 0.4 , 75 , 155 , 240 , 155 , 75 , ],

[ 0.5 , 80 , 190 , 245 , 190 , 80 ,],

[ 0.6 , 75 , 155 , 240 , 155 , 75 ,],

[ 0.7 , 55 , 135 , 215 , 135 , 55 ,],

  [ 0.8 , 35 , 105 , 170 , 105 , 35 ,],

[ 0.9 , 15 , 65 , 105 , 65 , 15 ],

]

 
# write the contents of each line in 1st and 2nd
# active sheet column respectively.

for row in data:

  ws .append (row)

 
# Create SurfaceChart object

chart = SurfaceChart ()

 
# create data for plotting

labels = Reference (ws, min_col = 1 , min_row = 2 , max_row = 10 )

data = Reference (ws, min_col = 2 , max_col = 6 , min_row = 1 , ma x_row = 10 )

 
# add data to the surface chart object

chart.add_data (data, titles_from_data = True )

 
# set labels in the chart object
chart.set_categories (labels)

 
# install chart title

chart.title = " Surface Chart "

  
# set chart style

chart.style = 26

 
# add diagram per sheet
# top left corner of the chart
# anchored to cell H2.

ws.add_chart (chart, "H2" )

 
# save file

wb.save ( "Surface.xlsx" )

Exit:

Code # 4: 3D plotting surfaces

Use the SurfaceC class to plot a 3D surface chart in an Excel worksheet hart3D from the openpyxl.chart submodule.

# import book from openpyxl

from openpyxl import Workbook

 
# import SurfaceChart3D, Reference, Series from open_xl.chart sub_module.

from openpyxl.chart import SurfaceChart3D, Reference, Series

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

wb = Workbook ()

 
# Get the active sheet worksheet
# from the active attribute.

ws = wb .active


# set the title of the chart

chart.title = "Surface Chart"

 
# set chart style

chart.style = 26

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

ws.add_chart (chart, "H2" )

 
# sav