Open MS Excel Spreadsheet with Python

[Koczian]

Cy Edmunds schrieb:
> "Allison Bailey" <allisonb at terralogicgis.com> wrote in message
> news:mailman.1058229111.12248.python-list at python.org...
> 
>>I would like to open an existing MS Excel spreadsheet and extract
>>information from specific worksheets and cells.
>>
First things first:

  Python programming on Win32 : [help for Windows programmers] / Mark 
Hammond and Andy Robinson. - 1. ed.
Beijing [u.a.] : O'Reilly, 2000. - XVII, 652 S. : Ill.
ISBN 1-56592-621-8

Lots of material about MS Excel <-> Python. Uses Python 1.5.2, but most 
of the code works with later versions.

And of course, use your Excel VBA help to get the object and method 
names right.

>>I'm not really sure how to get started with this process.
>>I ran the COM Makepy utility from my PythonWin (IDE from ActiveSTate),
>>using Microsoft Excel 10.0 Object Library, then
>>import win32com.client
>>xl = win32com.client.Dispatch("Excel.Application")
>>wb = xl.Workbooks.Open ("c:\\data\\myspreadsheet.xls")
>>
>>Then, I get errors when I try the following:
>>sh = wb.worksheets(1)
>>

Most probably a simple case sensitivity problem:

sh = wb.Worksheets(1)

sh = wb.ActiveSheet might be better, depending on context.

>>
>>Also, do I need to run the makepy utility every time I run my script?

No, just once. I don't know anything about deploying, though.

> 
> Your code worked on my system. However, I have found this interface to be
> pretty tricky. It often happens that while you are debugging the program you
> generate an instance of Excel which then makes later operations bomb
> mysteriously. On XP, hit Ctrl-Alt-Del tp bring up the task manager and then
> look at the Processes tab. If you have an instance of Excel running you
> should see it there. Use End Process to get rid of it.
>
Very true (but as I read the original post, the exceptions start at 
first try).

> You can avoid these problems by making sure that any COM references you make
> get released properly if an exception occurs. Your script would look like
> this:
> 
> import win32com.client
> xl = win32com.client.Dispatch("Excel.Application")
> try:
>     wb = xl.Workbooks.Open ("c:\\data\\myspreadsheet.xls")
>     try:
>         sh = wb.worksheets(1)
>         try:
>             print sh.Cells(2,3)
>         except:
>             sh = None
>             raise
>         sh = None
>     except:
>         wb.Close()
>         wb = None
>         raise
>     wb.Close()
>     wb = None
> except:
>     xl = None
>     raise
> xl = None
> 
> There are more elegant ways of doing this using classes. However, with this
> code I think you will never see one of those mysterious Excel instances in
> your task manager.
> 
I think try / finally would be better. Explicitly deleting all three: 
sh, wb and xl is the key to not having Excel zombies, if there is an 
exception or not.

That's a small bug in the easyExcel class of the Hammond book: it only 
deletes xl, not the variable representing the Workbook. So you still get 
your zombie.

HTH,
Koczian

-- 
Dr. Sibylle Koczian
Universitaetsbibliothek, Abt. Naturwiss.
D-86135 Augsburg

Tel.: (0821) 598-2400, Fax : (0821) 598-2410
e-mail : Sibylle.Koczian at Bibliothek.Uni-Augsburg.DE