Friday, June 20, 2008

surrounded by win32com's python and not liking it

I am using the win32com library to write an application to populate data that are in encrypted excel files that are kept in a specific folder into a mysql database. Most of the part is there except that I do not find much of win32com library documentation around. One part of my code really irks me and it's the excel visible part. Everytime it is processing an excel file, it has to open up the file and close it up again. When I set the visible part of the code to 0 or false, my application doesn't run correctly anymore. Some of my code are as follows :

import win32com.client
xl=win32com.client.Dipatch('Excel.Application')
xl.Visible=1

Grr ! I wonder why this thing doesn't work if you set the Visible to 0. Can't this work in the background? Woe !

9 comments:

Ross Jones said...

If you can avoid automating Excel at all, you should do so. Same goes for any Office app as any un-expected popups (a previous version has been found etc) will block your app and working around this is very difficult.

If the documents are pretty well structured, you should be able to access the spreadsheet through ODBC instead? It might be faster and safer.

Easier to write SELECT * FROM [sheet1$] too :)

ggivler said...

I have had issues with this in the past. When you open Excel in the background it may not close properly. So to get around this which may not work for you.

I use wmi to find all the tasks called excel then kill them.

Not the best way to handle it but it does not leave orphaned Excel processes in my Task List either.


BTW, there is a book about using win32com with Python. It is published by O'Reilly, "Python Programming on Win32" I have examples of what I do if you need something to look at, not the best code in the world but it works. :-)

Kevin H said...

Have you tried using: http://sourceforge.net/projects/pyexcelerator ?

Avoids the possible nightmare of COM. I haven't used it, but I hear good things.

lowks said...

Hi all thanks for the comments.

ross jones :

These excel are supplied by the users. Not sure if I can add odbc to them.

ggivler :

ugh ! How many files were you processing ?

kevin h:

Don't know if pyA can chomp through encrypted excel files.

Fuzzyman said...

You might have more luck with IronPython - as it is more closely integrated with the Windows APIs.

Here is some examples:
http://www.ironpython.info/index.php/Interacting_with_Excel

Michael

Luciano Rodrigues da Silva said...

What ross jones is trying to say is that you can open the excel docs in your computer using odbc, if you on windows.

swheatley said...

ross:

I believe it's actually OLE DB and not ODBC. Not a huge difference, but could help in finding more information on the subject.

lowks: You don't actually have to add anything to the database to support an OLE DB connection. You connect to it as a database via a connection string. Check this link out for more information:

http://connectionstrings.com/?carrier=excel

lowks said...

swheatley:

Oh Man !

"If the Excel workbook is protected by a password, you cannot open it for data access, even by supplying the correct password with your connection string. If you try, you receive the following error message: "Could not decrypt file.""

My files are password protected. I guess that rules out this way for me, but still being able to treat my excel files like a db is something intriguing.

David Avraamides said...

We ran into enough problems with win32com and all of the pure Python Excel modules that we finally bit the bullet and bought a third-party solution: http://officewriter.softartisans.com/

It's a Win32 DLL that can read/write and manipulate Excel binary files without having Excel installed on the machine. This makes it nice for generating complicated Excel sheets on web servers so you don't have the risk of leaving the excel.exe process hanging in the background.

It's not exactly cheap (~$5k for a license?), but it's probably paid itself back in saved maintenance hours. It doesn't use a true automation API but it's simple and close enough that it works pretty well.

While they don't strictly advertise Python bindings, we just used MakePy and then the samples pretty much worked as is.