top of page
Writer's pictureGeetika

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")









2,567 views

Recent Posts

See All
bottom of page