How to find the last row in a column using openpyxl normal workbook?

Question

I'm using openpyxl to put data validation to all rows that have "Default" in them. But to do that, I need to know how many rows there are.

I know there is a way to do that if I were using Iterable workbook mode, but I also add a new sheet to the workbook and in the iterable mode that is not possible.

Answer 1

ws.max_row will give you the number of rows in a worksheet.

Since version openpyxl 2.4 you can also access individual rows and columns and use their length to answer the question.

len(ws['A'])

Though it's worth noting that for data validation for a single column Excel uses 1:1048576.

Answer 2

This works for me well. It gives number of non empty rows in each column, assuming there are no empty rows in between.

from openpyxl import load_workbook as lw from openpyxl.utils import get_column_letter  wb = lw(your_xlsx_file) ws = wb[sheet_name]  for col in range(1, ws.max_column + 1):     col_letter = get_column_letter(col)     max_col_row = len([cell for cell in ws[col_letter] if cell.value])     print("Column: {}, Row numbers: {}".format(col_letter, max_col_row)