win32com Excel problem

[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

	
Mark Hammond wrote:
> 
> Tom Wiencko wrote:
> 
> > 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                                              tew at wiencko.com
President - Wiencko & Associates, Inc.                    (404) 255-2330
Telecom Consulting & Project Development -- Wireline, Wireless, Internet