Find row number that contains a specific value using Openpyxl

In this tutorial, we are going to see how to find the row number that contains a specific value in an excel sheet in Python. This task can be done using the openpyxl Python library. Openpyxl library is used to work with excel files in Python.

First of all, make sure you have installed the openpyxl library and then import it to use in the code.

import openpyxl

Note: We will be using this Excel file in this code to work on.
Find row number that contains a specific value using Openpyxl

Now using the load_workbook(path) function from the openpyxl library access the excel file. The parameter for this function is the excel file location path.

path = "abc.xlsx" #excel file path
wb = openpyxl.load_workbook(path)

Now select the sheet from the Excel file. .active is used to select the currently active sheet from the excel file. If you want to select any other sheet you can specify the sheet name.

sheet = wb.active

Now iterate through the rows and access the cell values using any loop eg. for loop, while loop etc. Check if the cell value is equal to your specified value using the if else condition. If yes then print the cell number.

for row in sheet.iter_rows(min_row=1, min_col=1, max_row=6, max_col=3):
    for cell in row:
        if cell.value ==90:
            print(sheet.cell(row=cell.row, column=cell.column))

In the above code snippet:

  1. iter_rows(): This is a function from the openpyxl library used to iterate through excel sheet rows. The minimum and the maximum number of rows and columns need to be set as a parameter for this function.
  2. sheet.cell(): this function is used to access the particular cell from an excel sheet. Row and Column are two attributes that need to be set. cell.row returns the row number of the cell and cell.column returns the column number of the cell.
  3. cell.value: It returns the value from that particular cell.

Output:

<Cell 'Sheet1'.C3>
<Cell 'Sheet1'.C5>

Here 90 value is present in cell c3 and c5 where c is the column name and 3,5 are row numbers.

Also, refer to the Easy example of openpyxl iter_rows in Python

Leave a Reply

Your email address will not be published. Required fields are marked *