How to find row numbers in Excel by using openpyxl
In this tutorial, I will show you how to search an Excel sheet by value or based on certain criteria and return the cell number(s) if records are found.
For example, returning Sheet “Cell” numbers (and/or values) like D5, D6, F6, etc. for names that start with “A”.
Similarly, in a Product Detail sheet, you may want to get all products whose prices are more than $5.
Another example can be, finding the “Cells”/values of products whose quantity is less than 10 and so on.
So let us start with a basic example.
Our Sample Test Sheet
We have a sample test sheet that we will use for all our examples below. The Sheet name is: “Product Information” and this is in the “test_Excel.xlsx” Workbook. (See the image below with sample data):
Loading the Workbook and sheet
First, let us load the Workbook and sheet for our examples below:
The code:
#Search Data and return cell numbers from openpyxl import load_workbook #Loading sample Workbook wb_access = load_workbook('test_Excel.xlsx') #Accessing Product Information Sheet sheet = wb_access.active
Find Cell Number for the given Product Name
In our first example, we will display the Cell number of the Product Name: “Maze”.
We will use:
irer_rows function which syntax is:
First, have a look at the code and result and we will explain how it worked:
The code:
# Searching Data Examples from openpyxl import load_workbook,styles #Loading required workbook wb_access = load_workbook('test_Excel.xlsx') #Accessing Active Worksheet sheet = wb_access.active #Accessing Cell Logic Here for row in sheet.iter_rows(min_row=1, min_col=2, max_row=11, max_col=5, values_only=False): for cell in row: if cell.value =="Maze": print("Record Exists at ", sheet.cell(row=cell.row, column=cell.column))
Result:
You can see in the above sample sheet that “Maze” exists in the B5 cell.
- min_col (int) – Index of the minimum column where the search should start. The index starts at 1.
- min_row (int) – It’s the minimum row to start searching from. We specified 2, as the first row is headers.
- max_col (int) – maximum column number to look at.
- max_row (int) – Maximum row number. We gave 11 as we have a total of 11 rows in our sample sheet.
- values_only (bool) – whether only cell values should be returned
Return type:
Generator
Search row numbers for the quantity column
In this example, we will search the cell numbers of products whose quantity is greater than or equal to 15.
The code:
#Search Data and return cell numbers from openpyxl import load_workbook #Loading sample Workbook wb_access = load_workbook('test_Excel.xlsx') #Accessing Product Information Sheet sheet = wb_access.active #Searching Cell Logic Here for row in sheet.iter_rows(min_row=2, min_col=4, max_row=11, max_col=4, values_only=False): for cell in row: if cell.value >=15: print("Record Exists at row #", sheet.cell(row=cell.row, column=cell.column))
Output:
Record Exists at row # <Cell ‘My_sheet1’.D2>
Record Exists at row # <Cell ‘My_sheet1’.D6>
Record Exists at row # <Cell ‘My_sheet1’.D7>
Record Exists at row # <Cell ‘My_sheet1’.D8>
Record Exists at row # <Cell ‘My_sheet1’.D10>
Record Exists at row # <Cell ‘My_sheet1’.D11>
You can compare these cells with the above-shared sample sheet and see which cell quantity is more than or equal to 15.
Notice our criteria in this line:
Minimum row = 2 means start searching from the second row as our first row is the header.
Minimum column is 4 as we know our quantity column is number 4th.
Similarly, the maximum column is 4 as we only want to search in the quantity column.