detect EOF of excel file in Python

Question

I have written a code for detecting the EOF of an excel file using python:

row_no = 1
while True:
    x = xlws.Cells(row_no,1).value
    if type(x) is None:
        break
    else:
        print(len(x))
        print(x)
    row_no = row_no + 1

i expect the while loop will stop then x becomes a "blank cell", which I support to be None, but it doesn't work, and it go to len(x) and prompt me an error of NoneType has no len. Why?

Thanks!

Answer 1

This here is your problem:

if type(x) is None:

If x is None, its type is NoneType. Therefore, this is never true, so you never see the blank cell and you end up trying to get the length of None.

Instead, write:

if x is None:

Answer 2

It looks like you are using pywin32com ... you don't need to loop around finding "EOF" (you mean end of Sheet, not end of File).

If xlws refers to a Worksheet object, you can use this:

used = xlws.UsedRange
nrows = used.Row + used.Rows.Count - 1

to get the effective number of rows in the worksheet. used.Row is the 1-based row number of the first used row, and the meaning of used.Rows.Count should be rather obvious.

Alternative: use xlrd ... [dis]claimer: I'm the author.