Newbie - Exporting Excel Spreadsheet

stalin woodsplitter at rocketmail.com
Thu Dec 6 20:46:43 EST 2001


[James Fugard wants to automate Excel with Python]

I've never used ODBC to access data stored in an Excel spreadsheet,
but once you have a cursor you might do something along these lines:

while 1:
   record = cursor.fetchone()
   if record is None:
       break
   print record
   # whatever calculations


When it comes to writing any changes back to the spreadsheet, I
imagine you'd need an ID column and an update statement such as:

update "Client Data Log$" set ResultCol = theResultOfMyCalculations
where IDCol = id

As I said, I've never accessed an Excel spreadsheet with ODBC, so I
don't have much advice.  I have automated Excel with COM, however;
here's some sample code and random comments that I sloppily threw
together, targeted at newbie level.  If posting mangles the code, I
can e-mail it to you as a zipped .py:

------------------------------------------------------------

# Disorganized, rambling Python->Excel 97 automation sample
# Requires the ActiveState Win32 extensions:
# http://aspn.activestate.com/ASPN/Downloads/ActivePython/Extensions/Win32all

# Take a look at this page for a chart of the Excel object model:
# http://msdn.microsoft.com/library/default.asp?url=/library/en-us/modcore/html/deovrmicrosoftexcel2000.asp
# and this one for documentation:
# http://msdn.microsoft.com/library/default.asp?url=/library/en-us/modcore/html/deovrworkingwithmicrosoftexcelobjects.asp

# This is, of course, sloppy code.  You should definitely
# take careful error-handling precautions in a "real"
# program, lest you create objects and never destroy them
# because an exception gets thrown midway through your
# program (try... finally is your friend).

# Of course, you'd also want to input the filenames from
# some external source such as sys.argv or a through a
# client module's function parameters.
WORKBOOK_IN_FILENAME = r'c:\temp\test.xls'
WORKBOOK_OUT_FILENAME = r'c:\temp\test-out.xls'

import os
from win32com.client import Dispatch

# Get a reference to a running Excel instance or create a
# new instance if there is not a running instance available.
xlApp = Dispatch('Excel.Application')

# You definitely want the application visible for the
# development phase in case an unhandled exception ends
# your program prematurely, leaving the application
# object open.
xlApp.Visible = 1

# Load the workbook (i.e., .xls file).
xlApp.Workbooks.Add(WORKBOOK_IN_FILENAME)

# Get first worksheet in first workbook (1-based).
# You could use the book/sheet names instead of their indices.
dataWorkbook = xlApp.ActiveWorkbook
dataSheet = dataWorkbook.Worksheets(1)

# xlApp.Workbooks is an example of a Collection.  "Calling"
# the collection (e.g., xlApp.Workbooks(x)) looks up workbook
# x, much as applying the lookup operator (square bracket) to
# a sequence or mapping in Python looks up the value at the
# specified index or key.  Collection elements can usually
# can be referenced either by index or key, as demonstrated
# on the following line:
# xlApp.Workbooks(1).Worksheets('Sheet1').Name

# In my opinion Collections are an unholy union of sequences
# and mappings; the dual access options are confusing, but
# I suppose it seems natural to some.  Not very Pythonic,
# certainly (not that anyone claimed Collections were
# Pythonic).  Oh, and their 1-based nature drives me nuts.

# When one "calls" a collection, one is actually invoking
# its default method, Item.  Thus,
#   xlApp.Workbooks(1)
# is equivalent to
#   xlApp.Workbooks.Item(1)

# The most important collections are:
# - the Workbooks collection of the Application object
# - the Worksheets collection of Workbook objects
# - the Charts collection of Workbook objects
# - the Cells collection of Worksheet and Range objects

# Collections have the following methods:
# - Item(index) (the default method discussed above)
# - Add(item[, key])
# - Remove(index)

# Collections have one read-only property:
# - Count

# There are also some handy shortcut variables defined:
# - ActiveWorkbook
# - ActiveSheet
# - ActiveCell

print 'Manipulating worksheet named "%s"' % dataSheet.Name

# Note that COM returns Unicode strings rather than ASCII strings.

# Print the value of cell G12.
g12 = dataSheet.Range('G12')
print 'The value of cell G12 is', g12.Value

# Set the value of cell G12 to 'hello'.
g12.Value = 'hello'
print 'The value of cell G12 is now', g12.Value

# Print the value of each cell in A1:B10.
subRange = dataSheet.Range('A1', 'B10')
rowCount, colCount = subRange.Rows.Count, subRange.Columns.Count
print ('About to loop through a range of %d rows by %d columns.'
      % (rowCount, colCount))
for row in range(1, rowCount + 1): # Remember that Collections are
1-based (gag)!
    for col in range(1, colCount + 1):
        print dataSheet.Cells(row, col).Value,
    print

# If you don't want Excel to whine about unsaved files and the like,
# set the Application object's DisplayAlerts property to false, as in
# xlApp.DisplayAlerts = 0
# In this case I'm programmatically saving the changes, so user
prompts
# won't be a problem.

# Save the changes to another file and exit.
if os.path.exists(WORKBOOK_OUT_FILENAME):
    os.remove(WORKBOOK_OUT_FILENAME)

print 'Saving changes to', WORKBOOK_OUT_FILENAME
dataWorkbook.SaveAs(WORKBOOK_OUT_FILENAME)
dataWorkbook.Close()

xlApp.Quit()

------------------------------------------------------------



More information about the Python-list mailing list