Newbie: returning dynamicly built lists (using win32com)

Discussion in 'Python' started by Ransom, Jun 6, 2006.

  1. Ransom

    Ransom Guest

    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!!!
     
    Ransom, Jun 6, 2006
    #1
    1. Advertising

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

    Ransom wrote:
    > 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
     
    Nick Smallbone, Jun 6, 2006
    #2
    1. Advertising

  3. Ransom

    Steve Holden Guest

    Ransom wrote:
    > 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
    --
    Steve Holden +44 150 684 7255 +1 800 494 3119
    Holden Web LLC/Ltd http://www.holdenweb.com
    Love me, love my blog http://holdenweb.blogspot.com
    Recent Ramblings http://del.icio.us/steve.holden
     
    Steve Holden, Jun 6, 2006
    #3
  4. Ransom wrote:

    > 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>
     
    Fredrik Lundh, Jun 6, 2006
    #4
  5. Ransom

    Paul McGuire Guest

    "Ransom" <> wrote in message
    news:...
    > Very newb here, but my question will hopefully be obvious to someone.

    <snip>
    > 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
     
    Paul McGuire, Jun 6, 2006
    #5
  6. Ransom

    Ransom Guest

    Re: Newbie: returning dynamicly built lists (using win32com) (SOLVED)

    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 wrote:
    > 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!!!
     
    Ransom, Jun 6, 2006
    #6
  7. Ransom

    Ransom Guest

    > 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


    Paul McGuire wrote:
    > "Ransom" <> wrote in message
    > news:...
    > > Very newb here, but my question will hopefully be obvious to someone.

    > <snip>
    > > 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, Jun 6, 2006
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Raaijmakers, Vincent (IndSys,GE Interlogix)

    Newbie in win32com: getters and setters

    Raaijmakers, Vincent (IndSys,GE Interlogix), Oct 23, 2003, in forum: Python
    Replies:
    0
    Views:
    276
    Raaijmakers, Vincent (IndSys,GE Interlogix)
    Oct 23, 2003
  2. kbperry
    Replies:
    1
    Views:
    476
    Giovanni Bajo
    Apr 12, 2006
  3. =?UTF-8?B?w4FuZ2VsIEd1dGnDqXJyZXogUm9kcsOtZ3Vleg==

    List of lists of lists of lists...

    =?UTF-8?B?w4FuZ2VsIEd1dGnDqXJyZXogUm9kcsOtZ3Vleg==, May 8, 2006, in forum: Python
    Replies:
    5
    Views:
    424
    =?UTF-8?B?w4FuZ2VsIEd1dGnDqXJyZXogUm9kcsOtZ3Vleg==
    May 15, 2006
  4. ardief
    Replies:
    14
    Views:
    749
    Paddy
    Feb 3, 2007
  5. master44
    Replies:
    2
    Views:
    401
    Thomas 'PointedEars' Lahn
    Sep 4, 2009
Loading...

Share This Page