How to get Excel Sheet last row by openpyxl
In this tutorial, we will show ways to get the last row number by using Python openpyxl library.
Getting the last row number by using Python openpyxl library
Knowing the last row in a sheet can be important for different scenarios.
For example, you want to append data to an existing sheet and have no idea which number current data exist.
In this tutorial, we will show ways to get the last row number by using the Python openpyxl library.
Our sample sheet:
First way – Using len() function of Python with sheet
The len() function in Python is used to get the length of a string, list, tuple, etc.
By using len() function and specifying the sheet and column, you can get the last row with data in that sheet.
from openpyxl import load_workbook
wb_row = load_workbook('openpyxl_ex.xlsx')
#Accessing sheet by name
sheet = wb_row["Sales"]
#Print last row number in the Sales sheet
print("Last Row Number: ", len(sheet['B']))
Output:
Last Row Number: 6
You can see the above sample sheet and our last row number is 6.
Inside the sheet object, we provided a column to be checked for the last row.
Second way – Using max_row to get the last row number in the sheet
This example uses the max_row attribute for getting the last row in the sheet object.
We are using the same sample sheet as in the above example (Sales) and see the code and output below:
from openpyxl import load_workbook
wb_row = load_workbook('openpyxl_ex.xlsx')
#Accessing sheet by name
sheet = wb_row["Sales"]
#Print last row number by max_row attribute
print("Last Row Number: ", sheet.max_row)
Result:
Last Row Number: 6
What if our sheet contains empty rows?
To check that, we added a few empty rows between the existing data as shown below:
Now let us try both ways above to see what last row number we get – by using column in len() and max_row attribute.
By len() function:
from openpyxl import load_workbook
wb_row = load_workbook('openpyxl_ex.xlsx')
#Accessing sheet by name that contains empty rows
sheet = wb_row["Sales"]
#Print last row for sheet with empty rows
print("Last Row with empty rows by using len: ", len(sheet['B']))
Output:
Last Row with empty rows by using len: 12
By max_row attribute:
from openpyxl import load_workbook
wb_row = load_workbook('openpyxl_ex.xlsx')
#Accessing sheet by name that contains empty rows
sheet = wb_row["Sales"]
#Print last row for sheet with empty rows
print("Last Row with empty rows: ", sheet.max_row)
Result:
Last Row with empty rows: 12
Using max row to append new row example
Now let us see the usage of getting the max row number from the sheet.
Consider, we want to add new rows in the sheet. we don’t know what is the last row and where to insert data.
For that, we will get the max row for appending a new row of data in our Excel sheet.
Code:
# Append data to the end of sheet - after last existing row
from openpyxl import load_workbook
#Specify the Workbook
wb_add = load_workbook("openpyxl_ex.xlsx")
sheet = wb_add["Employees"]
#convert returned last row to string
ins_row = str(len(sheet['B']) + 1 )
#New row's data
sheet["A"+ins_row] = "Javed"
sheet["B"+ins_row] = "6300"
#Save data in the Workbook
wb_add.save('openpyxl_ex.xlsx')
Output:
How did it work?
- We specified the “Employees” sheet to load from our sample workbook.
- This is followed by using the len() function to get the last row in the sheet.
- One is added to that number that represents the next row number in the current data.
- The returned number is converted to a string by str() method
- This string is concatenated to A and B columns with new data
- Finally, we saved the record.
