How to loop through column in excel until an empty cell

Question

I have written some python code that loops through an excel sheet and extracts the strings from the cells. I wish the loop to stop once it reaches an empty cell. I can get this to work if I put a string in the last cell eg "Stop" and have the code while sheet.cell_value(r, c) != "Stop" where r and c are the rows and column. However, if I change to != '' it doesn't work. The loop will run whilst the cells have something in them. Once the code reaches the empty cell where I want it to stop, I get the error message: "IndexError: list index out of range"

Any help appreciated!

r = 14
c = 2
      
while sheet.cell_value(r, c) != '':
    # do something....
    r+=1

Answer 1

xlrd 2.0.1

As far as xlrd is used, empty cells are filled with empty strings, not None (you may need to take into account ctype of the cell to avoid confusion with empty text). So the code is correct in this aspect. But the data area looks differently from what you can see in Excel. Unlike Excel, which always displays all possible cells on the screen (exact restrictions depend on version), the xlrd reader only addresses the used range. If there are no empty cells in the used area, then the posted code will exceed the limits of the allowed address space at some point. So it would be correct to check not only for empty cells, but also for row and column constraints, which are sheet.nrows and sheet.ncols:

r = 14
c = 2
      
while r < sheet.nrows and sheet.cell_value(r, c) != '':
    # do something....
    r+=1

We can also use sheet.col_values for the same purpose:

EXIT_VALUE = ''
for x in sheet.col_values(c, start_rowx=r):
    if x == EXIT_VALUE:
        break
    ...

In order not to confuse empty cells with empty text, you need to iterate not through cell values, but through cells themselves and check their сtype property:

from xlrd import XL_CELL_EMPTY

r, c = 14, 2
for cell in sheet.col(c, start_rowx=r):
    if cell.ctype == XL_CELL_EMPTY:
        break
    ...

# or using a while loop, incrementing the row number

while r < sheet.nrows and sheet[r, c].ctype != XL_CELL_EMPTY:
    ...
    r += 1