How to Iterate through Excel Rows in Python?

by

Openpyxl is a Python library that reads and writes Excel files (with the extensions xlsx/xlsm/xltx/xltm). Python openpyxl module allows the program to read and modify Excel files.

Also Read: Iterate over string c++ – C++ iterate over set – Different Ways to Iterate Over a Set in C++

For example, a user will have to go through hundreds of rows of data and select a few handful of information to make minor changes based on some criterion. These activities can be completed quickly and easily with the Openpyxl module.

Let us take an example of demo.xlsx excel spreadsheet as shown below:

How to Iterate through Excel Rows in Python?

Example1

Creating an openpyxl object and then iterating through all rows from top to bottom.

Approach:

  • Import openpyxl module using the import keyword.
  • Take a variable and initialize it with the path of the file.
  • Pass the above file path as an argument to the load_workbook() function to create a workbook object and store it in a variable.
  • Choose the active sheet using the active attribute and store it in another variable.
  • Get the total number of columns present in the above given Excel sheet by using the max_column attribute and store it in another variable.
  • Get the total number of rows present in the above given Excel sheet by using the max_row attribute and store it in another variable.
  • Iterate from 1 to the above total number of rows using the for loop.
  • Iterate from 1 to the above total number of columns using the another nested for loop.
  • Get the value of the cell using cell() function by passing arguments row_no and col_no as the iterator value.
  • Print the value of the cell using the value attribute.
  • The Exit of the Program.

Below is the implementation:

# Import openpyxl module using the import keyword
import openpyxl
# Take a variable and initialize it with the path of the file.
file_path = "demo.xlsx"  
# Pass the above file path as an argument to the load_workbook() 
# function to create a workbook object and store it in a variable.
work_book = openpyxl.load_workbook(file_path) 
# Choose the active sheet using the active attribute and store it in another variable.
actve_sheet = work_book.active

# Get the total number of columns present in the above given 
# Excel sheet by using the max_column attribute and store it in another variable.
tot_cols = actve_sheet.max_column

# Get the total number of rows present in the above given 
# Excel sheet by using the max_row attribute and store it in another variable.
tot_rows = actve_sheet.max_row

# Iterate from 1 to the above total number of rows using the for loop
for m in range(1, tot_rows+1):
    print("\n")   
    print(m,"- Row Data: ",)     
    # Iterate from 1 to the above total number of columns using the another nested for loop
    for n in range(1, tot_cols+1):
        # Get the value of the cell using cell() function by passing arguments row_no and 
        # col_no as the iterator value
        cell = actve_sheet.cell(row=m, column=n)
        # Print the value of the cell using the value attribute
        print(cell.value, end=" ")

Output:

1 - Row Data: 
S.No First Name Last Name Gender Country Age Date Id

2 - Row Data: 
1 Dulce Abril Female United States 32 15/10/2017 1562

3 - Row Data: 
2 Mara Hashimoto Female Great Britain 25 16/08/2016 1582

4 - Row Data: 
3 Philip Gent Male France 36 21/05/2015 2587

5 - Row Data: 
4 Kathleen Hanner Female United States 25 15/10/2017 3549

6 - Row Data: 
5 Nereida Magwood Female United States 58 16/08/2016 2468

7 - Row Data: 
6 Gaston Brumm Male United States 24 21/05/2015 2554

8 - Row Data: 
7 Etta Hurn Female Great Britain 56 15/10/2017 3598

9 - Row Data: 
8 Earlean Melgar Female United States 27 16/08/2016 2456

10 - Row Data: 
9 Vincenza Weiland Female United States 40 21/05/2015 6548

Example2

Creating an openpyxl object and then looping through all rows with the iter_rows() method.

Approach:

  • Import openpyxl module using the import keyword.
  • Take a variable and initialize it with the path of the file.
  • Pass the above file path as an argument to the load_workbook() function to create a workbook object and store it in a variable.
  • Choose the active sheet using the active attribute and store it in another variable.
  • Iterate through the selective rows and columns using the for loop.
  • Traverse in the cells of the row using the another nested for loop.
  • Print the value of the cell using the value attribute.
  • The Exit of the Program.

Below is the implementation:

# Import openpyxl module using the import keyword
import openpyxl
# Take a variable and initialize it with the path of the file.
file_path = "demo.xlsx"  
# Pass the above file path as an argument to the load_workbook() 
# function to create a workbook object and store it in a variable.
work_book = openpyxl.load_workbook(file_path) 
# Choose the active sheet using the active attribute and store it in another variable.
actve_sheet = work_book.active

# Iterate through the selective rows and columns using the for loop.
for xl_row in actve_sheet.iter_rows(min_row=1, min_col=1, max_row=12, max_col=3):
    # Traverse in the cells of the row using the another nested for loop
    for cell in xl_row:
        # Print the value of the cell using the value attribute
        print(cell.value, end=" ")
    print()

Output:

S.No First Name Last Name 
1 Dulce Abril 
2 Mara Hashimoto 
3 Philip Gent 
4 Kathleen Hanner 
5 Nereida Magwood 
6 Gaston Brumm 
7 Etta Hurn 
8 Earlean Melgar 
9 Vincenza Weiland 
None None None 
None None None

Leave a Comment