Loop through Excel Rows Python

 

Spreadsheets in Excel are something you will have to deal with at some point. You will be expected to learn how to work with spreadsheets, either because your manager likes them or because marketing needs them, and this is where learning openpyxl comes in handy.

In previous article we seen how to access single element at a time from excel. Now we will see how to loop through each rows in excel sheet.

Loop through Excel Rows Python

In most of the automation we need reading and writing of a file, generally many times we use excel to store the data. Consider task  you want to insert data into web page and do some operations on the web page one by one from Excel. We need a file reader library which can read the file line by line. Once reader is setup we can use any looping technique to traverse through all the data in the file. Here we are using openpyxl library. 

Install the Library: Use the below code to install the library in command prompt

pip install openpyxl

Steps:

1. Get the workbook in python by providing file path.

2. Specify the Sheet Name which you want read from workbook.

3.Put the loop to access each rows sequentially.

4. Access the cell using column and row indexes.

Here we are using sheet.max_row to get the index last row of the excel.

You can refer this article to explore more worksheet properties.

Loop through Excel Rows Python Code:

#import the library
import openpyxl

#provide excel file location
wb=openpyxl.load_workbook(r"File path"\Book1.xlsx") 
# use active worksheet in the workbook
ws=wb["SheetName"]

for x_row in range(2,ws.max_row+1):
        var1=ws.cell(row=x_row,column=2).value
        print(var1)
        ws.cell(row=x_row,column=4).value="Status"

wb.save(r"filepath\Book1.xlsx")