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:

openpyxl-last-row-sam

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.

Output:

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:

Result:

What if our sheet contains empty rows?

To check that, we added a few empty rows between the existing data as shown below:

openpyxl-last-row

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:

Output:

By max_row attribute:

Result:

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:

Output:

openpyxl-last-row-app

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.