Open MS Excel Spreadsheet with Python

[Cy Edmunds]

"Allison Bailey" <allisonb at terralogicgis.com> wrote in message
news:mailman.1058229111.12248.python-list at python.org...
> Hi Folks,
>
> I'm a brand new Python programmer, so please point me in the right
> direction if this is not the best forum for this question....
>
> I would like to open an existing MS Excel spreadsheet and extract
> information from specific worksheets and cells.
>
> 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)
>
>
> I think I'm missing something fairly fundamental, but I've googled all
> over the place and can't seem to find anything very introductory about
> opening and using data from MS Excel using Python.  Any suggestions,
> including places to get more information are welcome.
>
> Also, do I need to run the makepy utility every time I run my script?
> If so, how would I do it from within my Python program, rather than with
> the GUI in the IDE?
>
> Thanks for your help,
>
> Allison
>
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Allison Bailey
> TerraLogic GIS, Inc.
> allisonb at terralogicgis.com
> 425-673-4495
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>
>

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.

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.

-- 
Cy
http://home.rochester.rr.com/cyhome/