Python Find highest row in a given column

Question

I'm quite new in stackoverflow and quite recently learnt some basic Python. This is the first time I'm using openpyxl. Before I used xlrd and xlsxwriter and I did manage to make some useful programs. But right now I need a .xlsx reader&writer.

There is a File which I need to read and edit with data already stored in the code. Let's suppose the .xlsx has five columns with data: A, B, C, D, E. In column A, I've over 1000 rows with data. On Column D, I've 150 rows with data.

Basically, I want the program to find the last row with data on a given column (say D). Then, write the stored variable data in the next available row (last row + 1) in column D.

The problem is that I can't use ws.get_highest_row() because it returns the row 1000 on column A.

Basically, so far this is all I've got:

data = 'xxx'
from openpyxl import load_workbook
wb = load_workbook('book.xlsx', use_iterators=True)
ws = wb.get_sheet_by_name('Sheet1')
last_row = ws.get_highest_row()

Obviously this doesn't work at all. last_row returns 1000.

Answer 1

The problem is that get_highest_row() itself uses row dimensions instances to define the maximum row in the sheet. RowDimension has no information about the columns - which means we cannot use it to solve your problem and have to approach it differently.

Here is one kind of "ugly" openpyxl-specific option that though would not work if use_iterators=True:

from openpyxl.utils import coordinate_from_string

def get_maximum_row(ws, column):
    return max(coordinate_from_string(cell)[-1]
               for cell in ws._cells if cell.startswith(column))

Usage:

print get_maximum_row(ws, "A")
print get_maximum_row(ws, "B")
print get_maximum_row(ws, "C")
print get_maximum_row(ws, "D")

Aside from this, I would follow the @LondonRob's suggestion to parse the contents with pandas and let it do the job.