Count rows in excel sheet in Python with xlwings

Question

I have a script in Python that uses xlwings to open up an Excel file, and read and process the values of a certain column row by row. Here is the for statement:

for row in range(2, rownum):

I would like to repeat this function over every row in the sheet that actually contains something. It starts at 2 and ends at 'rownum'. My question is how to automatically count the number of rows and pass that value to 'rownum'. I'm sure xlwings has a way to do this, but I can't figure it out- perhaps the Autofit tool?

Thanks for the help!

Answer 1

Unless I've missed something while reading their API documentation it doesn't seem possible. You might need to use other libraries, for example pandas:

import pandas as pd

df = pd.read_excel(excel_file_path, sheetname="Sheet1")
print len(df)

If you don't want to use another library just for that, you can do it the hard and ugly way:

last_row = 0
while True:
    if cell_value is not None:  # replace cell_value with however 
                                # xlwings accesses a cell's value
       last_row += 1
    else:
        break

print last_row