Skip/ignore empty/blank cells

[Eric Smith]

I have a spreadsheet of which I am trying to read cells from a workbook sheet and setting them as variables to use later in the code, manipulate and write to a new workbook.  I have it all working fine EXCEPT if not all 9 cells (see code) are filled out I receive the following error.

    cell5 = sheet.cell(5,0)

  File "/Library/Python/2.7/site-packages/xlrd/sheet.py", line 406, in cell

    self._cell_types[rowx][colx],

IndexError: list index out of range

I understand why but want to know how to make it stop checking cells when it comes to a blank/empty cell and move on to the rest of the code using the active variables.

CODE: (Using 2.7, have xlrd, xwlt)

#xlrd open and create book

book = open_workbook('myfile.xls')

sheet = book.sheet_by_index(0)

#Set variables based on excel document

cell1 = sheet.cell(1,0)

cell2 = sheet.cell(2,0)

cell3 = sheet.cell(3,0)

cell4 = sheet.cell(4,0)

cell5 = sheet.cell(5,0)   <---- cell has no value

cell6 = sheet.cell(6,0)

cell7 = sheet.cell(7,0)

cell8 = sheet.cell(8,0)

cell9 = sheet.cell(9,0)

Also if there is an easier way to iterate this by assigning a variable a value by going down the worksheet and stopping when the cell is empty would be cool.

Thanks in advance.

Eric

[LRN]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 13.04.2013 20:11, Eric Smith wrote:
> I have a spreadsheet of which I am trying to read cells from a
> workbook sheet and setting them as variables to use later in the
> code, manipulate and write to a new workbook. I have it all
> working fine EXCEPT if not all 9 cells (see code) are filled out I
> receive the following error.
>
> cell5 = sheet.cell(5,0) File
> "/Library/Python/2.7/site-packages/xlrd/sheet.py", line 406, in
> cell self._cell_types[rowx][colx], IndexError: list index out of
> range

Use exception handling (i.e., try: ... except: ... finally: ...)

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (MingW32)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQEcBAEBAgAGBQJRaYVWAAoJEOs4Jb6SI2CwYK8H/14ufSKwS5JUtQd0A7RsUsLb
MHibz+VXGOmGFT3UKNg1lYAVpNMrFuUNQZb1mA/OaPsvargP92FVfYZla9HJZJ+V
HTfA9HbMV8B8nJLEYKmCm3N8DQDoJiAfY72xPRDg0oZc/VICrft5bsqbLUwYmp86
wDhIzHY0EeV7LlVLC1e9zRkch93r+4gwkzu3tI5ls5Er70dlrbG2uUhisNB/FGL8
iT74yjAw/Dl6N1Ruk12DFjoS8VLMShChRfqXvGgzMF+3z2OhTiioh2rBMCYy5LYf
5MY7ZJxuFC6LZNRvbovqc7MZfkalP5GPot609AM8MIFWhCnv5cQ0hJmz37RDrow=
=/oyq
-----END PGP SIGNATURE-----

[Eric Smith]

I appreciate the quick response. I am having trouble getting this to work and am most likely doing this wrong. Not sure I understand.

try:

cell5 = sheet.cell(5,0)

except xlrd.XLRDError:

cell5 is empty_cell

Can you correct this?

Thank you,

Eric

[LRN]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 13.04.2013 21:03, Eric Smith wrote:
> I appreciate the quick response. I am having trouble getting this
> to work and am most likely doing this wrong. Not sure I
> understand.
>
>
>
> try: cell5 = sheet.cell(5,0) except xlrd.XLRDError: cell5 is
> empty_cell
>
try:
cell5 = sheet.cell(5,0)

except:
do_something_meaningful_here

"do_something_meaningful_here" depends on what you're doing.
If your program MUST read a value from a cell 5:0, and cannot ever
proceed if there's no such cell, then there's really nothing you can
do, and try...except won't help you either.
If you're OK with just having some value (0, for example) for
non-existing cells, then just do:
cell5 = 0
inside the except block.

Usually you have some pretty good idea about the layout of the xls
file, and can rely on that layout staying precisely the same. In which
case an xls that does not have a 5:0 cell is broken (from your
program's point of view), and crashing is the right thing to do.

Or you know what kinds of peciliarities the layout might have, and
what to do if you hit them. For example, if you have values neatly
arranged in a row, usually, but sometimes they are in a row, but with
an empty cell in the middle, you can say "ok, data is probably shifted
to 1 cell to the right, so i'm just going to skip over the empty cell
and continue reading values as if nothing happened". That requires you
to NOT to hard-code cell indices into your program, and maybe use a
loop or something like that.

But all that has nothing to do with xlrd, really, it's just normal
programming, using loops, catching exceptions, etc. Go read some
Python books.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (MingW32)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQEcBAEBAgAGBQJRaZOJAAoJEOs4Jb6SI2CwDrgIALjkX5Ifqi2rVo8gOb2HlRZv
yKGcRV+IdKh7D9KqnRWy/82kNlPsxX8KJkCKmuXhOl6ME78LuVITKy6b0Id4WtzP
PbBZckqo7gM8AmXRE2EjHadDmU9Qj9Yr0Al8XNFbehlILZ+s3uo9rofJS/qtSq3I
38kenEYyLx09Wer/h/VBSzfIVOCmvVw4bXeffP4IF+1o5YS5XLTS1ICWl3NhFnm+
DZz+72Klp63b4kQInTyMyNCCcUZSSZJU1i0b68p0EYmHTWQPUnEYXesORcB4CALJ
Tyin+EbjvlfOCmn54pTHUReZ9DqzcrPK/ZrxDNunH0kihQhExH3M+Mc2YNTBfxQ=
=kXho
-----END PGP SIGNATURE-----

[Eric Smith]

This works out well. As you said I know the layout of the spreadsheet but am not sure how many cells would be filled, cells will not be skipped, just filled until a certain point.

Thank you for the quick input. I really do appreciate you explanations.