How to skip empty .xls cells in loop : XLRD (Python)

Question

I'm reading an .xls file and parsing columns by cells and checking them against web elements on amazon orders using xlrd. Everything seems to work fine until the .xls file contains an empty cell. Then the empty cell is parsed and is then "found" as a web element.

from selenium import webdriver
import xlrd

sheet = wb.sheet_by_index(0)
sheet.cell_value(0, 0)
for i in range(sheet.nrows):
    po = (sheet.cell_value(i, 3))
    tracking = (sheet.cell_value(i, 10))
    if driver.find_elements_by_link_text(po):
        print("FOUND!!!!", po)

The xls file looks like this: (For simplicity sake, I only used 2 columns)

+---+---------------------+--------------------+
|   |        Col3         |        col10       |
+---+---------------=-----+--------------------+
| 1 | 111-6955555-7777777 | 1Z1E199A4209992999 |
| 2 |                     | 775746666666       |
| 3 | 2008999             |                    |
| 4 | 111-5855555-7777777 | 1Z1E199E4207772696 |
+---+---------------------+--------------------+

The order numbers for amazon start with 111- in this case. The smaller order number is for another site but it's fine because it's not found as a web element. The problem is the blank cells in col3, because they are parsed and ARE found as a web element.

Column 10 is used to find the tracking number associated with the corresponding cell that is found as an web element. Blank cells are fine here because all amazon orders will have a corresponding tracking number with it.

maybe this will [help](https://stackoverflow.com/a/34478745/5720144) just add another if to check the cell value is nothing or not — Mikku, Jul 21 '19 at 14:26

score 1 · Accepted Answer · answered Jul 22 '19 at 03:10

I finally figured this out. The code looks like this:

sheet = wb.sheet_by_index(0)
sheet.cell_value(0, 0)
for i in range(sheet.nrows):
    cell = sheet.cell(i, 3)
    cty = cell.ctype
    if cty == xlrd.XL_CELL_EMPTY:
        continue
    else:
        po = (sheet.cell_value(i, 3))
        tracking = (sheet.cell_value(i, 10))
        if driver.find_elements_by_link_text(po):
            print("FOUND!!!!", po)

score 0 · Answer 2 · answered Jul 21 '19 at 16:59

@evolve710, hi. From this post, I'd recommend to use xlrd.XL_CELL_EMPTY check here.

So the whole snippet for the iteration over all non-empty cells in a workbook looks:

import xlrd
book = xlrd.open_workbook("sample.xls")
ref3d = book.name_map["moscow"][0].result.value[0]

# Iterate over each sheet
for sheet_num in range(ref3d.shtxlo, ref3d.shtxhi):
    sheet = book.sheet_by_index(sheet_num)
    print sheet.name
    row_lim = min(ref3d.rowxhi, sheet.nrows)
    col_lim = min(ref3d.colxhi, sheet.ncols)

    # Iterate over each column
    for row_num in range(ref3d.rowxlo, row_lim):

        # Iterate over each row
        for col_num in range(ref3d.colxlo, col_lim):
            col_type = sheet.cell_type(row_num, col_num)

            if not col_type == xlrd.XL_CELL_EMPTY:
                cval = sheet.cell_value(row_num, col_num)
                print "\t({0},{1}): {2}".format(
                    row_num, col_num, cval)

Hope this approach helps You.

How to skip empty .xls cells in loop : XLRD (Python)

2 Answers2