Openpyxl Module In Python

Introduction

The article explains how to create an excel file, add data to the file and read data from the excel sheet along with creating charts in Excel sheets using the Openpyxl module in Python.

Openpyxl is a library written in Python for reading/writing excel sheets, using Openpyxl API various types of charts can be created like 2D Area Charts, 3D Area Chars, Bubble Charts, Line Charts, 3D Bar Charts, Pie Charts, 3D Pie Charts, Doughnut Charts, etc.

The article covers

• Openpyxl installation
• Creating Excel file using Openpyxl
• Reading excel files using Openpyxl
• Creating Doughnut Charts

Let’s explore.

Openpyxl Installation

Just like all Python libraries, Openpyxl installation is straightforward using the pip command

!pip install openpyxl

Creating excel file

The excel file creation is a 4-step process

1. Step One is to import the ‘openpyxl’ module.
2. Step two is to define the data.
3. Step three is adding the data to the active sheet
4. Step five is to save the file.

import openpyxl
from openpyxl import Workbook

wb = Workbook() 
ws = wb.active

data = [
    ['USA', 10],
    ['UK', 9],
    ['Germany', 5],
    ['China', 4],
    ['France', 3]
]

for row in data:
    ws.append(row)
    
wb.save(filename = "AI_Development.xlsx")

The contents of file 

Reading excel file

Reading data from the excel sheet is a 3-step process

1. Step One is to import load_workbook class
2. Get the sheet and fetch the cell values (A / B)
3. Iterate through the rows and columns

from openpyxl import load_workbook
file = load_workbook('AI_Development.xlsx')
print(file.sheetnames)
data = file['Sheet']

for row in data:
    for cell in row:
        print(cell.value)

Creating Doughnut charts using Openpyxl

In this section, the article covers creating the Doughnut chart using Openpyxl, the process is very similar, after appending the data to the sheet. We need to instantiate the Doughnut object and provide the references and titles from the data, set style, and save the file.

import openpyxl
from openpyxl.chart import DoughnutChart, Reference

wb = Workbook() 
ws = wb.active

data = [
    ['USA', 10],
    ['UK', 9],
    ['Germany', 5],
    ['China', 4],
    ['France', 3]
]

for row in data:
    ws.append(row)
    
chart = DoughnutChart()
 
labels = Reference(ws, min_col = 1, min_row = 2, max_row = 5)
data = Reference(ws, min_col = 2, min_row = 1, max_row = 5)

chart.add_data(data, titles_from_data = True)

chart.set_categories(labels)
chart.title = "AI Ranking"

chart.style = 26
ws.add_chart(chart, "E4")

wb.save("doughnut_chart.xlsx")

Summary

The article explained how to create, write and read data from the excel sheet using the Openpyxl module also explained how to create charts using openpyxl.