How To Read And Write Excel Files In Python Using Openpyxl

Reading and writing to excel files in Test Automation is very common and thanks to python which has a wide variety of libraries which allow us to do the same. These are a few of the libraries which you can use like openpyxl, pandas,xlsxwriter,pyxlsb,xlrd,xlwt, etc. In this article, I will guide you to read and write excel files in python using openpyxl.

List of libraries which is available for excel in Python – https://www.python-excel.org/

Before we start this post, make sure you know python basics and fundamentals. If you are new to Python then do check our Python Tutorials.

Let’s start read and write excel files in python using openpyxl

Step 1- Install openpyxl

pip install openpyxl

Once it is installed successfully then you can start using this lib and I would also suggest you to check their official documentation which has clear and informative examples and syntax.

Step 2- Use existing classes and methods to perform read and write excel sheets.

Example 1- Read excel files in python using openpyxl

import openpyxl
#load workbook
wb=openpyxl.load_workbook("C:\\Users\\USER\\Desktop\\Data.xlsx")
# this will return number of sheets
sheets=wb.sheetnames

#return sheet name which is active
print(wb.active.title)

# specify which sheet you would like to read
sh1=wb['Names']

# specify which cell to red
data=sh1['B2'].value

#option1
print(wb['Names']['A2'].value)

#option2 which accept row and column
print(sh1.cell(3,2).value)
print(sh1.cell(3,3).value)
# here we are taking different sheet for example
sh2=wb['Marks']
print(sh2.cell(2,1).value)
print(sh2.cell(3,2).value)

#option3- here you can pass keyword argument
c=sh2.cell(row=2,column=2)
print(sh2.cell(row=2,column=2).value)

Example 2- Read multiple records using for loop

import openpyxl 

#load workbook 
wb=openpyxl.load_workbook("C:\\Users\\USER\\Desktop\\Data.xlsx")

# Load sheet 
sh1=wb['Names']

#get max number of rows in sheet
row=sh1.max_row

#get max number of columns in sheet
column=sh1.max_column

#run for loop which will read all records from sheet one by one
for i in range(1,row+1):
    for j in range(1,column+1):
        print(sh1.cell(i,j).value)

Example-3 – Write an excel sheet with single records

import openpyxl

# load workbook if you want to write in existing file else use openpyxl.Workbook()
wb=openpyxl.load_workbook("C:\\Users\\USER\\Desktop\\Data.xlsx")

#set the sheet name
wb['Sheet'].title="Report of Automation"

#get the active sheet
sh1=wb.active
sh1=wb['Names']

# pass which row and column and value which you want to update
sh1.cell(row=5,column=1,value='Pytest')
sh1.cell(row=5,column=2,value='UK')
sh1.cell(row=5,column=3,value=88.88)

# save the excel with name or you can give specific location of your choice
wb.save("Report.xlsx")

Example 4- Write multiple records to excel

from openpyxl import Workbook
# create workbook instance
wb=Workbook()

# sheet name update
wb['Sheet'].title="Report of Automation"
sh1=wb.active
# create iterable object- Here we are creating list of tuples 
data=[('Num','Name','Result'),(1,'Mukesh',90),(2,'Python',99),(3,'Java',95)]
#run for loop and append the record one by one
for i in data:
    sh1.append(i)
#save the workbook 
wb.save("C:\\Users\\USER\\Desktop\\NewExcelDemo1.xlsx")

I hope it is clear regarding dealing with excel files. If you face any issue related to excel then let me know in the comment section below.