Easy example of openpyxl iter_rows in Python

Microsoft Excel is one the most popular and widely used spreadsheet software for mathematical and graphical computations. Openpyxl is a python module that helps you to manage and work with excel files. You can use it with Excel 2010 and above files with xlsx/xlsm/xltx/xltm extensions.
In this tutorial, you will understand the iter_rows method of the openpyxl library.

Installing openpyxl

Firstly, make sure that you install the openpyxl library. You can do the same by running the below command on your terminal.

pip install openpyxl

You can check How to install openpyxl in Python

openpyxl iter_rows()

Excel worksheets consist of a table-like structure made up of rows and columns. The rows go with labelling from 1-.. while columns go from A -.. . Each intersection of a row and column forms the cell.

The iter_rows() method returns the cells of the worksheets in the form of rows. This is usually called on an instance of the worksheet. You can understand better by looking into examples of the same.

Example 1: Using iter_rows on an existing excel file.

Let us consider an example excel file codespeedy.xlsx as shown below;

Easy example of openpyxl iter_rows in Python

import openpyxl
worksheet = openpyxl.load_workbook("codespeedy.xlsx")
sheet = worksheet.active
for row in sheet.iter_rows(min_row=1, min_col=1, max_row=6, max_col=2):
    for cell in row:
        print(cell.value, end=" ")
    print()
Languages No. of articles 
Python 24 
Java 45 
C 66 
C++ 43 
Swift 12

In the above code;

  • Firstly, we import the openpyxl module and then open our worksheet by specifying the path.
  • Further, we iterate through each row of the sheet and display the values accordingly.
  • Also, the range of cells to be iterated through and displayed is specified as an argument to the iter_rows() method.

Example 2: Creating a new excel file using openpyxl and using iter_rows() on it.

Here, we first create the excel sheet through python directly without using any third-party app.

from openpyxl import Workbook
book = Workbook()
sheet = book.active
sheet['A1'] = "Languages"
sheet['A2'] = "Python"
sheet['A3'] = "Java"
sheet['A4'] = "C"
sheet['A5'] = "C#"
sheet['A6'] = "Swift"
sheet['B1'] = "No. of articles"
sheet['B2'] = 24
sheet['B3'] = 45
sheet['B4'] = 66
sheet['B5'] = 43
sheet['B6'] = 12
book.save("codespeedy1.xlsx")

Easy example of openpyxl iter_rows in Python

Once you have created the sheet as shown, you work on the rows just like in the first example.

from openpyxl import Workbook
book = Workbook()
sheet = book.active
sheet['A1'] = "Languages"
sheet['A2'] = "Python"
sheet['A3'] = "Java"
sheet['A4'] = "C"
sheet['A5'] = "C#"
sheet['A6'] = "Swift"
sheet['B1'] = "No. of articles"
sheet['B2'] = 24
sheet['B3'] = 45
sheet['B4'] = 66
sheet['B5'] = 43
sheet['B6'] = 12
book.save("codespeedy1.xlsx")
for row in sheet.iter_rows(min_row=1, min_col=1, max_row=6, max_col=2):
    for cell in row:
        print(cell.value, end=" ")
    print()
Languages No. of articles 
Python 24 
Java 45 
C 66 
C# 43 
Swift 12

So, this was about the iter_rows() method.
You can explore more options for working with excel using python by following  Get values of all rows in a particular column in openpyxl in Python

Leave a Reply

Your email address will not be published.