Newbie: returning dynamicly built lists (using win32com)

[Ransom]

Very newb here, but my question will hopefully be obvious to someone.

Code:

import string
from win32com.client import Dispatch
docdir = 'E:\\scripts\\Python\\RSAutomation\\'

def getOldData(testcases):

excel = Dispatch("Excel.Application")
excel.Workbooks.Open(docdir + 'FILE.xls')

# load and create list from file (testcases.csv)
for rsinput in testcases.xreadlines():

inputlist = string.split(rsinput, ',')


# iterate through and update spreadsheet input
cellx = range(3,51)
values = range(0,48)
for i,r in zip(cellx, values):

excel.ActiveSheet.Cells(i,2).Value = inputlist[r]

# TODO: read output from cell 32,6 into a tuple or list and
then return list to __main__

[THIS IS WHERE I AM HAVING A PROBLEM]
print excel.ActiveSheet.Cells(32,6) <--This prints properly
as loop executes

excel.ActiveWorkbook.Close(SaveChanges=0)
excel.Quit()

if __name__ == "__main__":
csv_testcases = open('arse_testcases.csv','r')
getOldData(csv_testcases)

OK, so what is happening is that I am sending a list of data to an
overly complicated spreadsheet that produces it's own output (in cell
32,6). As I loop through multiple test cases, the print statement
calling into COM for the cell data seems to be printing out results
just fine. But when I try and put the output from the spreadsheet into
a dynamic list after the TODO section thusly:

outputlist = []
outputlist.extend(excel.ActiveSheet.Cells(32,6)
return outputlist

I get an error like:
[<win32com.gen_py.Microsoft Excel 9.0 Object Library.Range instance at
0x15450880>]

I need to be able to return the dynamically generated built up by the
responses from the spreadsheet lookup call (the exce.Activesheet
thingy). Is there a better way to get this dynamically built list out
of the funtion?

Thanks!!!

 

[Nick Smallbone]

I'm afraid I don't have a Windows machine to test on, but..

I get an error like:
[<win32com.gen_py.Microsoft Excel 9.0 Object Library.Range instance at
0x15450880>]

This isn't an error. This is a list with one element, where the element
apparently represents a range of Excel cells. So by using that element
you can do things like changing the formatting of the cell, as well as
finding out what data is in there.

It looks like you might need to use excel.ActiveSheet.Cells(32,
6).Value to get the contents of cell (32, 6). (It depends on what Excel
calls it, of course, so if it's not that have a look at Excel's VBA
documentation to see if it mentions anything.)

Nick

 

[Steve Holden]

Very newb here, but my question will hopefully be obvious to someone.

Code:

import string
from win32com.client import Dispatch
docdir = 'E:\\scripts\\Python\\RSAutomation\\'

def getOldData(testcases):

excel = Dispatch("Excel.Application")
excel.Workbooks.Open(docdir + 'FILE.xls')

# load and create list from file (testcases.csv)
for rsinput in testcases.xreadlines():

inputlist = string.split(rsinput, ',')


# iterate through and update spreadsheet input
cellx = range(3,51)
values = range(0,48)
for i,r in zip(cellx, values):

excel.ActiveSheet.Cells(i,2).Value = inputlist[r]

# TODO: read output from cell 32,6 into a tuple or list and
then return list to __main__

[THIS IS WHERE I AM HAVING A PROBLEM]
print excel.ActiveSheet.Cells(32,6) <--This prints properly
as loop executes

excel.ActiveWorkbook.Close(SaveChanges=0)
excel.Quit()

if __name__ == "__main__":
csv_testcases = open('arse_testcases.csv','r')
getOldData(csv_testcases)

OK, so what is happening is that I am sending a list of data to an
overly complicated spreadsheet that produces it's own output (in cell
32,6). As I loop through multiple test cases, the print statement
calling into COM for the cell data seems to be printing out results
just fine. But when I try and put the output from the spreadsheet into
a dynamic list after the TODO section thusly:

outputlist = []
outputlist.extend(excel.ActiveSheet.Cells(32,6)
return outputlist

I get an error like:
[<win32com.gen_py.Microsoft Excel 9.0 Object Library.Range instance at
0x15450880>]

That's not an error, that's a list containing a single Python COM object.

I need to be able to return the dynamically generated built up by the
responses from the spreadsheet lookup call (the exce.Activesheet
thingy). Is there a better way to get this dynamically built list out
of the funtion?

Thanks!!!

I suspect that you need to apply judicious conversions to string or
numeric to grab the values of the cells you are interested in,
unencumbered by the COM wrappings.

regards
Steve

 

[Fredrik Lundh]

Very newb here, but my question will hopefully be obvious to someone.

OK, so what is happening is that I am sending a list of data to an
overly complicated spreadsheet that produces it's own output (in cell
32,6). As I loop through multiple test cases, the print statement
calling into COM for the cell data seems to be printing out results
just fine. But when I try and put the output from the spreadsheet into
a dynamic list after the TODO section thusly:

outputlist = []
outputlist.extend(excel.ActiveSheet.Cells(32,6)
return outputlist

I get an error like:
[<win32com.gen_py.Microsoft Excel 9.0 Object Library.Range instance at
0x15450880>]

the Cells call returns some kind of internal win32com object, not strings.

Python has two different ways of converting an object to a string of
characters; str() and repr():

http://pyref.infogami.com/str
http://pyref.infogami.com/repr

when you print an object, Python uses str() to do the conversion.

however, when you print a container, the container object's str()
implementation often uses repr() on the members.

to apply str() to all list members, you can simply do:

outputlist = map(str, outputlist)
print outputlist

or

print map(str, outputlist)

or some other variation thereof.

</F>

 

[Paul McGuire]

Very newb here, but my question will hopefully be obvious to someone.

But when I try and put the output from the spreadsheet into
a dynamic list after the TODO section thusly:

outputlist = []
outputlist.extend(excel.ActiveSheet.Cells(32,6)
return outputlist

I get an error like:
[<win32com.gen_py.Microsoft Excel 9.0 Object Library.Range instance at
0x15450880>]

1. First of all, this is not the code you are running. I know this because
the unbalanced parens wont even compile. It really doesn't help when you
ask for help, but post the wrong code.

2. What you are getting is NOT an error. Read it very carefully. What you
have added to outputlist is a Range object. Look into the Excel COM
documentation (you can open up VB from Excel by pressing Alt-F11, then open
the Object Browser to see the object API) for how to access the methods and
properties of an Excel Range. I'm guessing one of the properties Value,
Value2, Text, or Formula will give you what you want.

3. The reason your print statement appears to work is because print
implicitly applies the str method to objects, while applying the repr method
to contents of a list. So "print excel.ActiveSheet.Cells(32,6)" will output
"42" or whatever - for grins, try "print
type(excel.ActiveSheet.Cells(32,6))" (taking care to insert enough parens
)

All that is gold does not glitter, not all who wander are lost, don't judge
a book by its cover, or a variable by its output, etc...

-- Paul

 

[Ransom]

Thanks folks! I had just gotten myself into a blind rut, apparently.
Adding the .Value attribute to the com object does strip all the other
messaging returning from Excel so I could then populate my list and
return out of the function normally.

I had tried that earlier, but had used the .Value attribute incorrectly
( Cells.Value(x,y) instead of Cells(x.y).Value.

Cheers,
G

 

[Ransom]

1. First of all, this is not the code you are running. I know this because

the unbalanced parens wont even compile. It really doesn't help when you
ask for help, but post the wrong code.

"Ok! Ok! I must have, I must have put a decimal point in the wrong
place
or something. Shit. I always do that. I always mess up some mundane
detail."
-Michael Bolton

Actually, this was a typo in my e-mail. And yes, I realize how
annoying that can be when someone is seeking help. Thanks for your
otherwise excellent response, though!

G

Very newb here, but my question will hopefully be obvious to someone.

But when I try and put the output from the spreadsheet into
a dynamic list after the TODO section thusly:

outputlist = []
outputlist.extend(excel.ActiveSheet.Cells(32,6)
return outputlist

I get an error like:
[<win32com.gen_py.Microsoft Excel 9.0 Object Library.Range instance at
0x15450880>]

1. First of all, this is not the code you are running. I know this because
the unbalanced parens wont even compile. It really doesn't help when you
ask for help, but post the wrong code.

2. What you are getting is NOT an error. Read it very carefully. What you
have added to outputlist is a Range object. Look into the Excel COM
documentation (you can open up VB from Excel by pressing Alt-F11, then open
the Object Browser to see the object API) for how to access the methods and
properties of an Excel Range. I'm guessing one of the properties Value,
Value2, Text, or Formula will give you what you want.

3. The reason your print statement appears to work is because print
implicitly applies the str method to objects, while applying the repr method
to contents of a list. So "print excel.ActiveSheet.Cells(32,6)" will output
"42" or whatever - for grins, try "print
type(excel.ActiveSheet.Cells(32,6))" (taking care to insert enough parens
)

All that is gold does not glitter, not all who wander are lost, don't judge
a book by its cover, or a variable by its output, etc...

-- Paul