Is there a better way (besides COM) to remote-control Excel?

Question

I'm working on a regression-testing tool that will validate a very large number of Excel spreadsheets. At the moment I control them via COM from a Python script using the latest version of the pywin32 product. Unfortunately COM seems to have a number of annoying drawbacks:

For example, the slightest upset seems to be able to break the connection to the COM-Server, once severed there seems to be no safe way to re-connect to the Excel application. There's absolutely no safety built into the COM Application object.

The Excel COM interface will not allow me to safely remote-control two seperate instances of the Excel application operating on the same workbook file, even if they are read-only.

Also when something does go wrong I seldom get any useful error-messages... at best I can except a numerical error-code or a barely useful message such as "An Exception has occurred". It's almost impossible to know why something went wrong.

Finally, COM lacks the ability to control some of the most fundamental aspects of Excel? For example there's no way to do a guaranteed close of just the Excel process that a COM client is connected to. You cannot even use COM to find Excel's PID.

So what if I were to completely abandon COM? Is there an alternative way to control Excel?

All I want to do is run macros, open and close workbooks and read and write cell-ranges? Perhaps some .NET experts know a trick or two which have not yet bubbled into the Python community? What about you office-hackers? Could there be a better way to get at Excel's innards than COM?

Answer 1

There is no way that completely bypasses COM. You can use VSTO (Visual Studio Tools for Office), which has nice .NET wrappers on the COM objects, but it is still COM underneath.

Answer 2

It is also possible to run Excel as a server application and use it as a calculation engine. This allows non IT users to specify business rules within Excel and call them through webservices. I have not worked with this myself, but I know a coworker of mine used this once. Walkthrough: Developing a Custom Application Using Excel Web Services could be a good starting point. A first glance at that page looks like it requires Sharepoint. This might not be suiteable for every environment.

Answer 3

Have you looked at the xlrd and xlwt packages? I'm not in need of them any more, but I had good success with xlrd on my last project. Last I knew, they couldn't process macros, but could do basic reading and writing of spreadsheets. Also, they're platform independent (the program I wrote was targetted to run on Linux)!

Answer 4

You could use Jython with the JExcelApi (http://jexcelapi.sourceforge.net/) to control your Excel application. I've been considering implementing this solution with one of my PyQt projects, but haven't gotten around to trying it yet. I have effectively used the JExcelApi in Java applications before, but have not used Jython (though I know you can import Java classes).

NOTE: the JExcelApi may be COM under the hood (I'm not sure).

Answer 5

The Excel COM interface will not allow me to safely remote-control two seperate instances of the Excel application operating on the same workbook file, even if they are read-only.

This is not a limitation of COM, this is a limitation of Excel. Excel will not even let you open two files with the same name at the same time if they exist in different directories. It is a fundamental limitation of the Excel program.

To answer your other questions

If you check your python documentation, there should be a way to connect to an existing server if the connection is lost.

The lack of useful error messages again may be to do with Python.

You cannot even use COM to find Excel's PID.

COM is an internal object model and exposed what it wishes. PID are available to outside processes as much as they are to internal, there is no real reason to expose as a COM interface.