[Tutor] problems with win32com.client and excel.

[Bob Gailer]

At 09:01 PM 8/31/2003 +1200, Thomi Richards wrote:
>For the last month or so I've been trying to develop a piece of a program
>which enters numbers into an excel spreadsheet. I've managed to get it to go
>from the interactive interpreter, but as soon as I put those same commands
>into a file, I get errors:
>
>--<snip>--
>Traceback (most recent call last):
>   File "wedderburn.py", line 467, in idle
>     self.xlapp.adddata((command,args))
>   File "C:\Documents and
>Settings\Administrator\Desktop\wedderburn\excelspread.py", line 58, in
>adddata
>     self.app.ActiveSheet.Cells(self.x,self.y).Value = d #insert the data 
> into
>the sheet.
>   File "C:\PYTHON23\lib\site-packages\win32com\client\dynamic.py", line 154,
>in __call__
>     return
>self._get_good_object_(self._oleobj_.Invoke(*allArgs),self._olerepr_.defaultDispatchName,None)
>pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, None,
>None, 0, -2146827284), None)
>--</snip>--
>
>This is the file "excelspread.py":
>
>-<snip>-
>#!/usr/bin/python
>
>import os,sys
>
>if os.name != 'nt':
>         print "we're not in a win32 environment, so excel logging will 
> not be
>available."
>         sys.exit(1)
>
>try:
>         import win32com.client
>except:
>                 print "you don't seem to have the python windows/COM 
> extensions
>installed!\n\nget them from: http://www.python.org/windows/"
>                 sys.exit(1)
>
>
>'''This file contains functions and classes to append information to an excel
>spreadsheet.'''
>
>class app:
>         def __init__(self,title="Weight SpreadSheet:"):
>
>                 #we have to start excel with a spreadsheet, and put up 
> the title.
>
>                 self.x = self.y = self.width = 0
>
>                 self.app = win32com.client.Dispatch("Excel.Application")
>                 self.app.Visible = 1
>                 self.app.Workbooks.Add()
>
>                 #set the title:
>                 self.app.ActiveSheet.Cells(1,1).Value = title
>                 self.y += 1
>
>                 self.initial = 1
>                 self.cols = {}
>
>
>                 #that's it as far as initialisation goes...
>
>         def adddata(self,values):
>                 # values will be a two part list. he first part will 
> always contain the
>                 # text string 'DATA'. THe second part will be a list of 
> lists. these
>                 # inner lists will contain two values. the first will be 
> a header code,
>                 # the second item will be the actual data. this from the 
> dummy driver:
>                 #
>                 # return
>['DATA',[['CODENO','0125846'],['NETWEIGHT',netw],['GROSSWEIGHT',grossw],['TARE',tare]]]
>                 #
>                 code,data = values      #unpack the values.
>
>                 self.x = 0
>                 self.y += 1
>
>                 if self.initial:
>                         #this is the first time we have added data to the 
> spreadsheet. we need to
>set up
>                         #the column headers.
>                         for chunk in data:
>                                 c,d = 
> chunk                                             #unpack the code chunk
>                                 self.cols[c] = 
> self.x                   #add entry to cols dictionary.
> 
>self.app.ActiveSheet.Cells(self.x,self.y).Value = d     #insert the data into
>the sheet.
>                                 self.x += 
> 1                                     #incriment the x column
>                         self.initial = 0
>                 else:
>                         for chunk in data:
>                                 c,d = chunk #unpack the code chunk.
>                                 self.x = self.cols.get(c,10)    #put 
> error messages in col 10
> 
>self.app.ActiveSheet.Cells(self.x,self.y).Value = d
>
>
>if __name__ == '__main__':
>         xl = app()
>         # this is the format that data is sent to the class:
>
>xl.adddata(['DATA'[['HEADER1','VALUE1'],['HEADER2','VALUE2'],['HEADER3','VALUE3'],['HEADER4','VALUE','4']]])

This is as far as I got running the program in PythonWin.
Traceback (most recent call last):
   File "J:\samis\python\xl.py", line 53, in ?
     xl.adddata(['DATA'[['HEADER1','VALUE1'],['HEADER2','VALUE2'],['HEADER3','VALUE3'],['HEADER4','VALUE','4']]])
TypeError: sequence index must be integer

To get past that error I had t add a comma after data:
     xl.adddata(['DATA',[['HEADER1','VALUE1'],['HEADER2','VALUE2'],['HEADER3','VALUE3'],['HEADER4','VALUE','4']]])

[snip]
Also I wonder why this line is not indented the same as xl = app(). This 
will not stop the program from working as long as its name is __main__, but 
if you import it, you'll get NameError: name 'xl' is not defined

Even in PythonWin the program then gets the error you posted. The reason is 
that you are indexing Cells(0,1) and Excel Cell indexing starts at 1.
I changed self.x = 0 to self.x = 1. Then I got yet another error that 
required me to change ['HEADER4','VALUE','4'] ot ['HEADER4','VALUE4'].

Then it works fine as a module.

I wonder how you got it to run at all!

Bob Gailer
bgailer at alum.rpi.edu
303 442 2625
-------------- next part --------------

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.506 / Virus Database: 303 - Release Date: 8/1/2003