How do you use Excel server-side?

Solution 1:

It is possible, but not advisable (and officially unsupported).

You can interact with Excel through COM or the .NET Primary Interop Assemblies, but this is meant to be a client-side process.

On the server side, no display or desktop is available and any unexpected dialog boxes (for example) will make your web app hang – your app will behave flaky.

Also, attaching an Excel process to each request isn't exactly a low-resource approach.

Working out the black box and re-implementing it in a proper programming language is clearly the better (as in "more reliable and faster") option.

Related reading: KB257757: Considerations for server-side Automation of Office

Solution 2:

You definitely don't want to be using interop on the server side, it's bad enough using it as a kludge on the client side.

I can see two options:

Figure out the spreadsheet logic. This may benefit you in the long term by making the business logic a known quantity, and in the short term you may find that there are actually bugs in the spreadsheet (I have encountered tons of monster spreadsheets used for years that turn out to have simple bugs in them - everyone just assumed the answers must be right)

Evaluate SpreadSheetGear.NET, which is basically a replacement for interop that does it all without Excel (it replicates a huge chunk of Excel's non-visual logic and IO in .NET)

Solution 3:

Although this is certainly possible using ASP.NET, it's very inadvisable. It's un-scalable and prone to concurrency errors.

Your best bet is to analyze the spreadsheet calculations and duplicate them. Now, granted, your business is not going to like the time it takes to do this, but it will (presumably) give them a more usable system.

Alternatively, you can simply serve up the spreadsheet to users from your website, in which case you do almost nothing.

Edit: If your stakeholders really insist on using Excel server-side, I suggest you take a good hard look at Excel Services as @John Saunders suggests. It may not get you everything you want, but it'll get you quite a bit, and should solve some of the issues you'll end up with trying to do it server-side with ASP.NET.

That's not to say that it's a panacea; your mileage will certainly vary. And Sharepoint isn't exactly cheap to buy or maintain. In fact, short-term costs could easily be dwarfed by long-term costs if you go the Sharepoint route--but it might the best option to fit a requirement.

I still suggest you push back in favor of coding all of your logic in a separate .NET module. That way you can use it both server-side and client-side. Excel can easily pass calculations to a COM object, and you can very easily publish your .NET library as COM objects. In the end, you'd have a much more maintainable and usable architecture.