Reading and analyzing data from Excel sheets using pywin32

Question

I've seen plenty of examples on how to write to excel, and my program requires reading from existing data. I need to read through a column and pick out numbers from it (the column is empty cells with numbers at arbitrary intervals), and then break out when I reach the end of the data. The code I have now is below, where xlsht is the sheet name, x and y are the cell indices, and address is the list all the numbers are going.

while y < xlsht.UsedRange:
    if str(xlsht.Cells(x,y).Value).isdigit:
        address.append(xlsht.Cells(x,y).Value)
        y += 1
        continue
    else:
        y += 1
return address

As it stands now, it crashes at line 2. I tried using

if xlsht.Cells(x,y).Value not None:

but that didn't work. I dont know how else to do this. Also, I wanted to verify that using

xlsht.UsedRange

is the correct way of detecting the last row of the sheet.

@AaronDigulla Its long and i dont know what parts are relevant, heres a screencap of the consol — zakparks31191
– zakparks31191, Commented Jun 19, 2012 at 19:40
The important part would be Exception occurred; this is the place were sensible programmers put useful error messages that help to figure out what happened ... — Aaron Digulla
– Aaron Digulla, Commented Jun 19, 2012 at 19:55
I know that. I already stated I dont know the correct way to determine the values of cells, so coding in error messages is meaningless at this stage. — zakparks31191
– zakparks31191, Commented Jun 19, 2012 at 20:00
I was referring to the fact that Microsoft is only hiring the world's best and smartest and can't even get a simple error message right :-( — Aaron Digulla
– Aaron Digulla, Commented Jun 19, 2012 at 20:02

Aaron Digulla · Accepted Answer · 2012-06-19 20:01:15Z

1

The error message doesn't give any useful clues what the problem might be.

Soltuion: Poor man's debugger. Print all and any variables so you can see what is going on:

print 'xlsht=',xlsht
print 'x=',x
print 'usedRange=',xlsht.UsedRange
while y < xlsht.UsedRange:
    print 'y=',y
    print 'cell=',xlsht.Cells(x,y)
    print 'value=',xlsht.Cells(x,y).Value
    if str(xlsht.Cells(x,y).Value).isdigit:
        address.append(xlsht.Cells(x,y).Value)
        y += 1
        continue
    else:
        y += 1
return address

You get the idea. If you're using Python 3.x, you'll need print('x=',repr(x))

answered Jun 19, 2012 at 20:01

Aaron Digulla

330k111 gold badges626 silver badges840 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

zakparks31191 Over a year ago

rigtho, this helped me get a few bugs out. Im stuck at how to determine what is in the cell now: if str(xlsht.Cells(x,y).Value).isdigit: isn't doing anything good. I just realized that this makes it a string, then checks if that string is a digit. Obviously not gonna work :p

zakparks31191 Over a year ago

and as a note the other way i tried doesnt work, if xlsht.Cells(x,y).Value.isdigit:

Aaron Digulla Over a year ago

Try dir(xlsht.Cells(x,y)) - that should give you a list of methods and properties of whatever Cells() returns.

zakparks31191 Over a year ago

it lists these:

['__add__', '__class__', '__contains__', '__delattr__', '__doc__', '__eq__', '__format__', '__ge__', '__getattribute__', '__getitem__', '__getnewargs__', '__getslice__', '__gt__', '__hash__', '__init__', '__iter__', '__le__', '__len__', '__lt__', '__mul__', '__ne__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__rmul__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__', 'count', 'index'

, any clues you see in there? I was thinking of contains or eq but im not sure what to compare against

Aaron Digulla Over a year ago

What do you get for xlsht.Cells(x,y).__class__?

|

Collectives™ on Stack Overflow

Reading and analyzing data from Excel sheets using pywin32

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related