Technology Engineered

Why Excel OLE Automation through the web is Bad

In the category of "sub-optimal things that I have to maintain," one of the ugly ones that rears its head from time to time is Excel automation. We have a client that sends us data tables in the format they're comfortable with, which happens to be MS Excel spreadsheets. We then have a legacy Perl script that uses a Win32::OLE connection to the Excel.Application COM object to open the Excel file, which first gets uploaded to the server through a plain old CGI multipart form attachment.

The issue at hand, as described in MS KB 257757, Considerations for server-side Automation of Office, is that running Excel server-side through IIS causes an instance of Excel to be spawned within the memory space of IIS, or something to that effect. Whatever is really going on at the low-level, the end effect is that an instance of Excel is left running on the server, even after you go through the normal procedure of calling the Quit() method and setting the application reference to nothing/null. And with the number of files that we process, we get a lot of zombies on our test server. Occasionally I get notified that an update process returned a blank page rather than a confirmation, and I check out the process monitor, and sure enough there are a bunch of Excels that aren't doing anything other than hogging resources and preventing Excel from actually running. Normally you can't even end those processes, since the IIS user spawned them, so you have to either reboot or use a fancy non-standard "kill" script.

If you ever find yourself in a similar situation, there are a few ways around it. I've found that running the script on the server via Scheduled Tasks allows Excel to quit. So you can set up a task that runs your script periodically, checking for the existence of the uploaded file. Your processing won't be instantaneous, but it'll be more reliable. There's also a possibility of using Office Web Components, which includes a COM object for a Spreadsheet with a similar object model to Excel Automation. I'm actually going to check that out to see if a) we have it already on the server, and b) if it can open Excel files on the server the same way that the Automation does, and if so, I'll convert our scripts to use that rather than Automation.

posted by Adam Payne @ 3:30 PM permalink