How to stop reading a spreadsheet with xlrd at the first empty row?

Question

I'm using xlrd to sniff through a directory structure and pull out spreadsheets, reading the second row (row 1) down to "do stuff." The issue is that I don't know how to stop the reading/printing at the first empty row. I understand that rows are not "empty" objects, but I'd appreciate a little help showing how to check if all cells are empty. Here is the code I'm working with:

import xlrd
import os

def excel_file_filter(filename, extensions=['.xls', '.xlsx']):
    return any(filename.endswith(e) for e in extensions)

def get_filenames(root):
    filename_list = []
    for path, subdirs, files in os.walk(root):
        for filename in filter(excel_file_filter, files):
            filename_list.append(os.path.join(path, filename))
    return filename_list

spreadsheets = get_filenames('C:\\Temp')
for s in spreadsheets:
    with xlrd.open_workbook(s) as wb:
        cs = wb.sheet_by_index(0)
        num_cols = cs.ncols
        for row_index in range(1, cs.nrows):
            print('Row: {}'.format(row_index))
            for col_index in range(0, num_cols):
                cell_object = cs.cell(row_index, col_index)
                if cell_obj is not xlrd.empty_cell:
                    print('Col #: {} | Value: {}'.format(col_index, cell_obj))

What ends up happening is that it prints all the way through almost 1000 rows, when only the first say, 25 rows have content in them. The amount of content between the spreadsheets varies, so a generic solution (that does not rely on other optional libraries) that helps me understand how to detect an empty row and then break, would be appreciated.

Check these questions they could be helpful: [How to detect if a cell is empty...](http://stackoverflow.com/questions/11603009/how-to-detect-if-a-cell-is-empty-when-reading-excel-files-using-the-xlrd-library) and [Validating a cell value using XLRD](http://stackoverflow.com/questions/29907072/validating-a-cell-value-using-xlrd) . If none of these solve your problem, I'll be glad to help. — , Mar 18 '17 at 16:57
@Mimx I'm sorry, I should have mentioned that I checked those first. I do understand some of the general theory, but with all the nested loops to get down to cell values, I get very overwhelmed with xlrd very quickly. I would appreciate some demonstrative help for this case :) — auslander, Mar 20 '17 at 14:39

score 11 · Accepted Answer · edited May 23 '17 at 12:00

First: To get the cell value then check if it is empty, use one of the methods explained in the answer of question How to detect if a cell is empty when reading Excel files using the xlrd library?

When using cell_val= cs.cell(row_index, col_index).value to get the value:
- To check if it is empty: simply write if cell_vel == ''
When using cell_object = cs.cell(row_index, col_index) to get the value:
- To check if it is empty:
  -first get the cell_type cell_type = cs.cell_type(row_index, col_index)
  -then check if cell_type == xlrd.XL_CELL_EMPTY

Second: To check if the whole row is empty you can do the following:

Define a counter (count_empty=0) to count number empty cells in the row & boolean (empty_cell = False)
Check if the cell is empty
If it is > increment counter & change empty_cell to True
If not > set empty_cell False
Check if empty_cell is False > print the value of the cell
After looping through columns in the row
If count_empty is equal to the number of columns > means the whole row is empty > break and stop looping through rows

The Code:

# define empty_cell boolean
empty_cell= False
with xlrd.open_workbook(s) as wb:
    cs= wb.sheet_by_index(0)
    num_cols= cs.ncols
    num_rows= cs.nrows
    for row_index in range(1, num_rows):
        # set count empty cells
        count_empty = 0
        print('Row: {}'.format(row_index))
        for col_index in range(0,num_cols):
            # get cell value
            cell_val= cs.cell(row_index, col_index).value
            # check if cell is empty
            if cell_val== '': 
                # set empty cell is True
                empty_cell = True
                # increment counter
                count_empty+= 1
            else:
                # set empty cell is false
                empty_cell= False

            # check if cell is not empty
            if not empty_cell:
                # print value of cell
                print('Col #: {} | Value: {}'.format(col_index, cell_val))

        # check the counter if is = num_cols means the whole row is empty       
        if count_empty == num_cols:
            print ('Row is empty')
            # stop looping to next rows
            break

Note: I used the first method cell_val= cs.cell(row_index, col_index).value to get the value of cells, I see it's simpler. If you want to use the other method change the following:

    cell_val= cs.cell(row_index, col_index) # remove .value
    cell_type= cs.cell_type(row_index, col_index) # add this line
    # check if cell is empty
    if cell_type == xlrd.XL_CELL_EMPTY: # change if cell_val== '':

Other links that helped me understand how to check if a cell is empty:
xlrd.XL_CELL_EMPTY and Validating a cell value using XLRD

This is what I needed to break the logjam and get on with the rest of the code. Thanks for not only the code but for explaining why it works. In my mind, this sort of answer is the stackoverflow ideal. Appreciate your time! — auslander, Mar 21 '17 at 13:49

score 1 · Answer 2 · answered Sep 10 '19 at 17:36

To check if a single cell is empty, inspect its ctype attribute. To check an entire row, use the all function on a list comprehension:

workbook = xlrd.open_workbook(filepath)
sheet = workbook.sheets()[0]
rows = sheet.get_rows()
next(rows) # skip first row
for row in rows:
    if all([cell.ctype in (xlrd.XL_CELL_EMPTY, xlrd.XL_CELL_BLANK)
            for cell in row]):
        break
    # process this non-empty row here...

How to stop reading a spreadsheet with xlrd at the first empty row?

2 Answers2