2

How can I find the number of the last non-empty row of an whole xlsx sheet using python and openpyxl?

The file can have empty rows between the cells and the empty rows at the end could have had content that has been deleted. Furthermore I don't want to give a specific column, rather check the whole table.

For example the last non-empty row in the picture is row 13.

enter image description here

I know the subject has been extensively discussed but I haven't found an exact solution on the internet.

Charalamm
  • 1,547
  • 1
  • 11
  • 27
  • Does this answer your question? [How to find the last row in a column using openpyxl normal workbook?](https://stackoverflow.com/questions/33541692/how-to-find-the-last-row-in-a-column-using-openpyxl-normal-workbook) – Charlie Clark Apr 30 '20 at 17:44
  • no because it refers to one or more defined columns. My answer and my question refer to the whole table. – Charalamm Apr 30 '20 at 19:19
  • Does this answer your question? [Openpyxl : need the max number of rows in a column that has data in Excel](https://stackoverflow.com/questions/52813973/openpyxl-need-the-max-number-of-rows-in-a-column-that-has-data-in-excel) – stovfl Apr 30 '20 at 20:00
  • The principle is the same. – Charlie Clark May 01 '20 at 09:57
  • @stvfl no because a specific column has to be specified. – Charalamm May 01 '20 at 17:08
  • @Charlie Clark although it could be the same in the core it is not easy to figure out the code that examines the whole table. After all the specific function of the code seems to be missing from stack overflow – Charalamm May 01 '20 at 17:11

3 Answers3

3
# Open file with openpyxl
to_be = load_workbook(FILENAME_xlsx)
s = to_be.active

last_empty_row = len(list(s.rows))
print(last_empty_row)
## Output: 13

s.rows is a generator and its list contains arrays of each rows cells.

Charalamm
  • 1,547
  • 1
  • 11
  • 27
  • This appears to be the same as `s.max_rows`, which in my case returns the last row although it it empty :/ – Guillaume Jan 23 '23 at 09:37
1

openpyxl's class Worksheet has the attribute max_row

johnson
  • 3,729
  • 3
  • 31
  • 32
  • PS: In case you just want to append to the `worksheet` you can use [`append`](https://openpyxl.readthedocs.io/en/latest/api/openpyxl.worksheet.worksheet.html#openpyxl.worksheet.worksheet.Worksheet.append) – johnson Apr 22 '22 at 16:40
1

If you are looking for the last non-empty row of an whole xlsx sheet using python and openpyxl.

Try this:

import openpyxl

def last_active_row():
    workbook = openpyxl.load_workbook(input_file)
    wp = workbook[sheet_name]
    last_row = wp.max_row
    last_col = wp.max_column
    
    for i in range(last_row):
        for j in range(last_col):
            if wp.cell(last_row, last_col).value is None:
                last_row -= 1
                last_col -= 1 
            else:
                print(wp.cell(last_row,last_col).value) 
    print("The Last active row is: ", (last_row+1)) # +1 for index 0

if __name__ = '___main__':
last_active_row()

This should help.

Ranjeet R Patil
  • 453
  • 6
  • 10