win32com Excel problem

[Terje Johan Abrahamsen]

I am writing a little porgram that pulls out some info from a cell in Excel.
It works great, excepy for when I try to pull out info from a spreadsheet
that I want info from. Here is the code:

import win32com.client
import win32com.client.dynamic
from win32com.client import Dispatch
import sys

class accountcurrents:
xlApp = Dispatch("Excel.Application")
#ex = Dispatch("Extra.System")
xlApp.Visible = 1
xlApp.Workbooks.Add()
xlBook = xlApp.Workbooks(1)
xlSheet = xlApp.Sheets(1)
xlrad = 2
xlpol = "Donald"
xlamount = 1.00
xldato = "1/11/11"

def __init__(self):
print"Hello"

def finnxlpolicy(self):
accountcurrents.xlrad = accountcurrents.xlrad + 1
accountcurrents.xlpol =
accountcurrents.xlSheet.Cells(accountcurrents.xlrad,1).Value
if accountcurrents.xlpol == None :
print "yes"
else:
pass

If I have a blank spreadsheet, and fill in some values in the A collumn, it
works great. However, when I use the spreadsheet that I actually want the
info from, it all crashes. The spreadsheet I want the info from looks like
this:

Policy# Effective Date Amount
MCK0259477 11/5/1998 240
MCK0278868 5/27/2001 -572.72
MCK0279107 1/14/2001 -1474.82
and so forth.

This errormessage comes:

Traceback (most recent call last):
File "<interactive input>", line 1, in ?
File "C:\Documents and Settings\dsporck\My
Documents\Python\accountcurrents\accountcurrents.py", line 23, in
finnxlpolicy
accountcurrents.xlpol =
accountcurrents.xlSheet.Cells(accountcurrents.xlrad,1).Value
File "C:\Python22\Lib\site-packages\win32com\client\__init__.py", line
347, in __getattr__
return apply(self._ApplyTypes_, args)
File "C:\Python22\Lib\site-packages\win32com\client\__init__.py", line
341, in _ApplyTypes_
return self._get_good_object_(apply(self._oleobj_.InvokeTypes, (dispid,
0, wFlags, retType, argTypes) + args), user, resultCLSID)
com_error: (-2146827864, 'OLE error 0x800a01a8', None, None)

I use win2k, and newest version of Python, PythonWin and Win32Com. I do not
change anything except for the contents of the spreadsheet. Even if I just
copy the info into the spreadsheet that normally works.

Thanks in advance,
Terje


_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com

[Mark Hammond]

When I call xlSheet.Cells a lot, sooner or later the com interface
locks up (I did not even get a traceback).

Can you provide a sample to demonstrate this?

Mark.

[Tom Wiencko]

Certainly.

Here's the class I use to open and interact with any given spreadsheet.
If you are
the Mark Hammond of Python Programming on Win32 then this will look very
familiar:

import win32com.client
import win32com.client.dynamic

class excelSS:
def __init__(self,filename=none):
self.xlApp=win32com.client.Dispatch("Excel.Application")
if (filename):
self.filename=filename
self.xlBook=self.xlApp.Workbooks.Open(filename
else:
# error processing

def close (self):
self.xlBook.Close(SaveChanges=0)
del self.xlApp

# old method I don't use any more
def getCell(self,row,col,sheet=None):
if (sheet):
sht=xlBook.Worksheets(sheet)
else:
#error processing
return (sht.Cells(row,col).Value)

# new method I use exclusively
def getRange(self,row1,col1,row2,col2,sheet=None):
if (sheet):
sht=self.xlBook.Worksheets(sheet)
else:
# error processing
return sht.Range(sht.Cells(row1,col1),sht.Cells(row2,col2)).Value

Here's some sample processing logic (untested, but abstracted from a
test program I have used):

class Program:

def __init__(self,file,sheet):
self.ss=excelSS(file)
self.sheet = sheet

def getRows(self,firstrow,firstcol,numberrows,numbercols):
return (self.ss.getRange(firstrow,firstcol,
firstrow+numberrows,firstcol+numbercols,self.sheet))

def procCellsBROKEN(self): # this fails with alarming regularity
for row in range(1,2000,100): # get rows 100 at a time
for rowindex in range (0,100): # loop over rows to process
for colindex in range (0,10): # loop over columns
cell = self.ss.getCell(row+rowindex,colindex,self.sheet)
# do something with cell

def procCells(self): # this works reliably and quickly
for row in range(1,2000,100): # get rows 100 at a time
data = self.getRows(row,1,100,10)
for rowindex in range (0,100): # loop over rows to process
for colindex in range (0,10): # loop over columns
cell = data[rowindex][colindex]
# do something with cell

Or some such. The actual code I am using has very complex processing of
the
resulting data, but the data access routines are virtually identical.

When I was using individual cell lookups, analyzing a 2000 row 20 column
sheet took
about 7-10 minutes and never did complete - the program would hang about
three-quarters
of the way through. The exact same processing logic takes about 30
seconds when I read
the cells in groups (which in my application breaks down to groups of 28
rows by 20 columns).
(This was all done on a 750MHz Pentium III running Windows 98SE.)

If "Python Programming on Win32" is yours, I have to thank you for a
well written book
without which I would not have gotten much working in the win32 world.
If you ever do a
second edition, however, I have a few things I would love to give you on
the ODBC module
description that would have saved me hours of experimenting and
frustration (not errors, but
simple easy interface characteristics that were really not documented
anywhere).

Cheers,

Tom

Post by Mark Hammond

When I call xlSheet.Cells a lot, sooner or later the com interface
locks up (I did not even get a traceback).

Can you provide a sample to demonstrate this?
Mark.

[Mark Hammond]

Post by Tom Wiencko
Certainly.
Here's the class I use to open and interact with any given spreadsheet.
If you are
the Mark Hammond of Python Programming on Win32 then this will look very

:)

Post by Tom Wiencko
Here's some sample processing logic (untested, but abstracted from a

...

Post by Tom Wiencko
Or some such. The actual code I am using has very complex processing of
the
resulting data, but the data access routines are virtually identical.

Is it possible for you to put together an actual program that you know
works, and you know will reliably hang the win32com interface. If you
have a program I need do nothing more to than run, then I will at least
be able to see where the failure is.

Post by Tom Wiencko
If "Python Programming on Win32" is yours, I have to thank you for a
well written book
without which I would not have gotten much working in the win32 world.

Thank you!

Post by Tom Wiencko
If you ever do a
second edition, however, I have a few things I would love to give you on
the ODBC module
description that would have saved me hours of experimenting and
frustration (not errors, but
simple easy interface characteristics that were really not documented
anywhere).

Thanks for the suggestion. A second edition hasn't been started yet,
but I am sure it will be one day ;)

Mark.

[Mike Brenner]

Post by Tom Wiencko
# old method I don't use any more
...
# new method I use exclusively

I can't verify your failure, but I can attest to the fact that reading in a large rectangle is tremendously faster than reading in one cell at a time. There is a large overhead for each call to Cells.

That same observation holds for reading the Interior.Color and the Boundaries(Top).Color. If you want to read a block of colors, copy the block to another block; then set the value of that new block in Visual Basic to each cells Interior.Color; then, from python read in the whole block. Same for the boundaries (I use the top boundary color of each cell).

It would be nice to control this, especially for colors and boundaries which do not have a corresponding block method available to Python.

[Tom Wiencko]

I have had a terrible time calling xlSheet.Cells a lot of times. What
I have done instead, with good results, is to grab ranges into lists
of lists, and then parse the lists.

When I call xlSheet.Cells a lot, sooner or later the com interface
locks up (I did not even get a traceback).

-Tom

Post by Terje Johan Abrahamsen
I am writing a little porgram that pulls out some info from a cell in Excel.
It works great, excepy for when I try to pull out info from a spreadsheet
... code deleted ...
Thanks in advance,
Terje
_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com

[Gabe Newcomb]

I'm still learning how to do this stuff myself, but here are some
thoughts:

1) this doesn't seem to be your problem, but be sure to be explicit
about the types of data you expect from each cell (I use str() and
int(), etc. when reading Cells.Value

2) more likely: perhaps this original spreadsheet has strangely
formatted columns? Just a stab in the dark.

3) be sure to kill any EXCEL tasks left open in taskmanager. This can
make access to the spreadsheet funky. I had to learn to add code to
close the spreadsheet in order to prevent leaving these open after
execution.


good luck! I hope this helps in some way

Gabe Newcomb

-----Original Message-----
From: Terje Johan Abrahamsen [mailto:terjeja at hotmail.com]
Sent: Wednesday, May 22, 2002 3:32 PM
To: python-list at python.org
Subject: win32com Excel problem


I am writing a little porgram that pulls out some info from a cell in
Excel.
It works great, excepy for when I try to pull out info from a
spreadsheet
that I want info from. Here is the code:

import win32com.client
import win32com.client.dynamic
from win32com.client import Dispatch
import sys

class accountcurrents:
xlApp = Dispatch("Excel.Application")
#ex = Dispatch("Extra.System")
xlApp.Visible = 1
xlApp.Workbooks.Add()
xlBook = xlApp.Workbooks(1)
xlSheet = xlApp.Sheets(1)
xlrad = 2
xlpol = "Donald"
xlamount = 1.00
xldato = "1/11/11"

def __init__(self):
print"Hello"

def finnxlpolicy(self):
accountcurrents.xlrad = accountcurrents.xlrad + 1
accountcurrents.xlpol =
accountcurrents.xlSheet.Cells(accountcurrents.xlrad,1).Value
if accountcurrents.xlpol == None :
print "yes"
else:
pass

If I have a blank spreadsheet, and fill in some values in the A collumn,
it
works great. However, when I use the spreadsheet that I actually want
the
info from, it all crashes. The spreadsheet I want the info from looks
like
this:

Policy# Effective Date Amount
MCK0259477 11/5/1998 240
MCK0278868 5/27/2001 -572.72
MCK0279107 1/14/2001 -1474.82
and so forth.

This errormessage comes:

Traceback (most recent call last):
File "<interactive input>", line 1, in ?
File "C:\Documents and Settings\dsporck\My
Documents\Python\accountcurrents\accountcurrents.py", line 23, in
finnxlpolicy
accountcurrents.xlpol =
accountcurrents.xlSheet.Cells(accountcurrents.xlrad,1).Value
File "C:\Python22\Lib\site-packages\win32com\client\__init__.py", line
347, in __getattr__
return apply(self._ApplyTypes_, args)
File "C:\Python22\Lib\site-packages\win32com\client\__init__.py", line
341, in _ApplyTypes_
return self._get_good_object_(apply(self._oleobj_.InvokeTypes,
(dispid,
0, wFlags, retType, argTypes) + args), user, resultCLSID)
com_error: (-2146827864, 'OLE error 0x800a01a8', None, None)

I use win2k, and newest version of Python, PythonWin and Win32Com. I do
not
change anything except for the contents of the spreadsheet. Even if I
just
copy the info into the spreadsheet that normally works.

Thanks in advance,
Terje


_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com