Introduction to iter_rows() in openpyxl
The iter_rows() method in openpyxl is used to loop through rows in an Excel sheet efficiently.
It is commonly used in:
- Excel automation
- Bulk data processing
- Report analysis
- File validation systems
Full Syntax of iter_rows() in openpyxl
worksheet.iter_rows(
min_row=1,
max_row=None,
min_col=1,
max_col=None,
values_only=False
)
Explanation of All Parameters
min_row
This tells from which row the reading should start.
Example: min_row=2 means skip the header and start from row 2.
max_row
This tells where to stop reading rows.
If you set max_row=10, it will read only up to row 10.
If you leave it as None, it will read till the last row in the sheet.
min_col
This tells from which column the reading should start.
Example: min_col=1 means start from column A.
max_col
This tells where to stop reading columns.
If max_col=5, it will read only up to column E.
If it is None, it will read till the last column.
values_only
If this is set to True, it returns only the cell values (like text, numbers).
If it is False, it returns full Cell objects (like <Cell A1>), which are harder to use.
Example 1: Read All Rows from Excel File
from openpyxl import load_workbook
wb = load_workbook("students.xlsx")
ws = wb.active
for row in ws.iter_rows():
print(row)
Output :
(<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>, <Cell 'Sheet1'.D1>, <Cell 'Sheet1'.E1>)
(<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>, <Cell 'Sheet1'.D2>, <Cell 'Sheet1'.E2>)
(<Cell 'Sheet1'.A3>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.C3>, <Cell 'Sheet1'.D3>, <Cell 'Sheet1'.E3>)
(<Cell 'Sheet1'.A4>, <Cell 'Sheet1'.B4>, <Cell 'Sheet1'.C4>, <Cell 'Sheet1'.D4>, <Cell 'Sheet1'.E4>)
(<Cell 'Sheet1'.A5>, <Cell 'Sheet1'.B5>, <Cell 'Sheet1'.C5>, <Cell 'Sheet1'.D5>, <Cell 'Sheet1'.E5>)
(<Cell 'Sheet1'.A6>, <Cell 'Sheet1'.B6>, <Cell 'Sheet1'.C6>, <Cell 'Sheet1'.D6>, <Cell 'Sheet1'.E6>)
(<Cell 'Sheet1'.A7>, <Cell 'Sheet1'.B7>, <Cell 'Sheet1'.C7>, <Cell 'Sheet1'.D7>, <Cell 'Sheet1'.E7>)
Example 2: Read All Rows from Excel File
from openpyxl import load_workbook
wb = load_workbook("students.xlsx")
ws = wb.active
for row in ws.iter_rows(values_only=True):
print(row)
Output :
('Roll No', 'Name', 'Marks', 'Subject', 'Result')
(101, 'Arun', 85, 'Maths', 'Pass')
(102, 'Bala', 72, 'Science', 'Pass')
(103, 'Charan', 45, 'English', 'Pass')
(104, 'Divya', 30, 'Maths', 'Fail')
(105, 'Eswar', 90, 'Science', 'Pass')
(106, 'Farooq', None, 'English', 'Absent')
Example 3: Skip Header Row
for row in ws.iter_rows(min_row=2, values_only=True):
print(row)
Starts reading from second row only.
(101, 'Arun', 85, 'Maths', 'Pass')
(102, 'Bala', 72, 'Science', 'Pass')
(103, 'Charan', 45, 'English', 'Pass')
(104, 'Divya', 30, 'Maths', 'Fail')
(105, 'Eswar', 90, 'Science', 'Pass')
(106, 'Farooq', None, 'English', 'Absent')
Example 4:
for row in ws.iter_rows(min_row=2, values_only=True):
print(row[0],row[1],row[2])
Output :
101 Arun 85
102 Bala 72
103 Charan 45
104 Divya 30
105 Eswar 90
106 Farooq None
Example 5:
for row in ws.iter_rows(min_row=2, values_only=True):
print(row[0],row[1],int(row[2])+10)
Output :
101 Arun 95
102 Bala 82
103 Charan 55
104 Divya 40
105 Eswar 100
Traceback (most recent call last):
File "D:/PythonTutorials/Lab 03/lab03.py", line 7, in <module>
print(row[0],row[1],int(row[2])+10)
TypeError: int() argument must be a string, a bytes-like object or a real number, not 'NoneType'
Example 6:
for row in ws.iter_rows(min_row=2, values_only=True):
try:
print(row[0], row[1], int(row[2]) * 10)
except:
print(row[0], row[1], "Invalid number")
Output:
101 Arun 850
102 Bala 720
103 Charan 450
104 Divya 300
105 Eswar 900
106 Farooq Invalid number
Example 7:
for row in ws.iter_rows(min_row=2, values_only=True):
if row[2] is not None:
print(row[0], row[1], int(row[2]) * 10)
else:
print(row[0], row[1], "Invalid number")
Output : Same as Example 5
Example 8: Read Selected Columns Only
for row in ws.iter_rows(min_row=2, min_col=1, max_col=2, values_only=True):
rollno, name = row
print(rollno, name)
Useful when you need specific columns.
Example 9:
for row in ws.iter_rows(min_row=2, values_only=True):
rollno, name, marks, subject, result = row
if marks is None:
status = "Absent"
elif marks >= 35:
status = "Pass"
else:
status = "Fail"
print(rollno, name, marks, subject, status)
Output:
101 Arun 85 Maths Pass
102 Bala 72 Science Pass
103 Charan 45 English Pass
104 Divya 30 Maths Fail
105 Eswar 90 Science Pass
106 Farooq None English Absent
Example 10: Find Highest Mark Using iter_rows()
marks = []
for row in ws.iter_rows(min_row=2, min_col=2, max_col=2, values_only=True):
marks.append(row[0])
print("Highest Mark:", max(marks))
Used for real-time result analysis.
Example 11: Skip Empty Rows
for row in ws.iter_rows(min_row=2, values_only=True):
if None not in row:
print(row)
Prevents errors in automation scripts.
Why iter_rows() is Important in Automation
- Faster than manual looping
- Reduces memory usage
- Works well with large Excel files
- Cleaner code
- Essential for data validation
Conclusion
The iter_rows() method makes reading Excel data easy and efficient in Python.
If you are learning:
- Excel Automation
- Report Generation
- Bulk Data Processing
Then iter_rows() is a must-learn concept in openpyxl.