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

python excel openpyxl
human picture human · Nov 5, 2015 · Viewed 74.9k times · Source

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

Charlie Clark picture Charlie Clark · Nov 5, 2015

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.

Related questions

Pandas: Looking up the list of sheets in an excel file

The new version of Pandas uses the following interface to load Excel files: read_excel('path_to_file.xls', 'Sheet1', index_col=None, na_values=['NA']) but what if I don't know the sheets that are available? For example, …

Read More
getting sheet names from openpyxl

I have a moderately large xlsx file (around 14 MB) and OpenOffice hangs trying to open it. I was trying to use openpyxl to read the content, following this tutorial. The code snippet is as follows: from openpyxl import load_workbook …

Read More
how to write to a new cell in python using openpyxl

I wrote code which opens an excel file and iterates through each row and passes the value to another function. import openpyxl wb = load_workbook(filename='C:\Users\xxxxx') for ws in wb.worksheets: for row in ws.rows: print …

Read More