How To Read And Write Excel Files In Python Using Openpyxl

Reading and writing to excel files in Test Automation is very common and python has a wide variety of libraries which allow us to do the same. These are a few of the libraries which we can use like openpyxl, pandas, xlsxwriter, pyxlsb, xlrd, xlwt, etc.

Step 1- Install openpyxl

pip install openpyxl

Read multiple records

​import openpyxl # Location for Excel File path = "C:\\Users\\Desktop\\Project\\TestXL.xlsx" # Creating WorkBook Object and Storing it in workbook variable workbook = openpyxl.load_workbook(path) # Number of sheets sheets = workbook.sheetnames # Return Active sheet name print(workbook.active.title) # Specify which sheet you would like to read sh1 = workbook['Jan21'] # 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 r in range(1, row + 1): for c in range(1, column + 1): print(sh1.cell(row=r, column=c).value, end=" ") print()

TestXL: Workbook sheet 1(Jan21)

Output:

Write multiple records to excel

from openpyxl import Workbook # Create workbook instance workbook = Workbook() # Update sheet name workbook['Sheet'].title = "Batch 1" sh1 = workbook.active # Creating list of tuples data = [('Num', 'Name', 'Result'), (1, 'Joshua', 89), (2, 'Ava', 99), (3, 'Abigail', 90)] # Loop and append the records for i in data: sh1.append(i) # save the workbook workbook.save("C:\\Users\\Desktop\\Project\\NewXL.xlsx")