How do you use Excel server-side?

Question

A client wants to "Web-enable" a spreadsheet calculation -- the user to specify the values of certain cells, then show them the resulting values in other cells.

(They do NOT want to show the user a "spreadsheet-like" interface. This is not a UI question.)

They have a huge spreadsheet with lots of calculations over many, many sheets. But, in the end, only two things matter -- (1) you put numbers in a couple cells on one sheet, and (2) you get corresponding numbers off a couple cells in another sheet. The rest of it is a black box.

I want to present a UI to the user to enter the numbers they want, then I'd like to programatically open the Excel file, set the numbers, tell it to re-calc, and read the result out.

Is this possible/advisable? Is there a commercial component that makes this easier? Are their pitfalls I'm not considering?

(I know I can use Office Automation to do this, but I know it's not recommended to do that server-side, since it tries to run in the context of a user, etc.)

A lot of people are saying I need to recreate the formulas in code. However, this would be staggeringly complex.

Answer 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

Answer 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)