Excel Automation: Interop vs. OfficeWriter vs. Work Horse

[ianlukekane]

I've spent the past few weeks looking into various reporting mechanisms that will enable our company to create automated Excel reports in a "better" way.

Currently, we're running a copy of Excel on the server and using VB.NET to create Excel objects through Interop. It works just fine (mostly), but I hate to keep running Excel on the server for the standard reasons.

Basically, I have two alternatives in mind, and I see pluses and minuses to both. I'm wondering if anyone has any experience with either, or has any advice in general.

First, I've been looking into SoftArtisan's OfficeWriter. I've heard good things about it, and it obviously takes the burden off of running Excel on the server. It also integrates with SSRS, which we'll definitely be using in the future. It's a clean solution, isn't terrible processor intensive, and the price isn't bad for the standard version (which is what we'd be interested in purchasing).

Second, we have a lot of old equipment sitting around. One of these old machines could be used as a "work horse" machine, whose job is basically to create the reports from a locally running copy of Excel. A user would request a report through an ASP page, and the request would send a message to the work horse machine that would create the Excel report, etc. This method wouldn't require any further purchase. The maintainability of this option seems to be a bit higher, though. The method also seems to be somewhat archaic.

I go back and forth between the two options. I can see goods and bads in each. Does anyone have any experience of this type that could help sway my decision either way?

[camel]

Advice wise, I can't say I would have thought of using any of these mechanisms anytime since 2002 when .Net came out, especially when it sounds like the reports are initiated via ASP which may or may not mean the reports actually run in real time? In fact even pre 2002 I would have used Crystal or Active Reports over Excel as either format can be exported to Excel and is better suited to the report generation task.

Nowadays the obvious candidate from ASP page for me would be create reports via the .Net Report class, which is free and basically the same as SSRS without the server side baggage. Alternatively Crystal is still there and is also relatively free, distribution rights coming as part of a typical VS licence.

In either case you get a report which can a) be presented directly in the browser using a Report Viewer, b) has built in options to export to Excel and c) can be exported to PDF too. Also from perspective of what you are talking about with Excel instances hogging a server, these methods are lightweight and run inside managed environment.

[ianlukekane]

As a clarification, I should say that we'll mainly be doing batch processes whereby we'll generate around 400 reports and distribute them in some way (e.g. by zipping/emailing them out). Based upon what I know about using the .NET report projects, doing batch processes of this type aren't very efficient, and the Excel outputs take a long time to generate from ASP and open afterwards. That's why I've steered clear of these methods thus far. But given my lack of experience with the reporting projects, perhaps I'm missing something.

Any other opinions/thoughts from anyone? Thank you.

[camel]

Its entirely up to you, since you were looking for a different perspective I am happy to provide one. FYI I was batch creating 100s of precompiled reports to send to network folders and by email more than 10 years ago with the then Crystal distribution engine. This is 10 years & umpteen versions on so presumably better, whilst even 7 years ago used Active Reports same way.

Obviously tried various versions of Excel along the way too and with these other tools to compare to I can't see Excel is the best option in terms of scaling up or out. That you get easy ASP access is a bonus, its certainly not main feature.

Guess if specialist reporting tools like these don't offer significant benefits you have to wonder how they have acquired the market share they seem to enjoy.