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

Edit: A lot of people are saying I need to recreate the formulas in code. However, this would be staggeringly complex. It sounds like this could be a solved problem -- does anyone know of a component of some type that would do it for me? Someone has to have created some "Excel-formula-to-code" generation system. I'd gladly pay for it.

Edit/Solution: SpreadsheetGear is what I was looking for. Among other things, it lets you use an XLS as a calculator. You can set the raw values of selected cells and then read out values of calculated cells. Exactly what I was looking for.

Answer 1

It sounds like you're talking that the user has the spreadsheet open on their local system, and you want a web site to manipulate that local spreadsheet?

If that's the case, you can't really do that. Even Office automation won't help, unless you want to require them to upload the sheet to the server and download a new altered version.

What you can do is create a web service to do the calculations and add some vba or vsto code to the Excel sheet to talk to that service.

Answer 2

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.

Answer 3

Neglecting the discussion whether it makes sense to manipulate an excel sheet on the server-side, one way to perform this would probably look like adopting the

Microsoft.Office.Interop.Excel.dll

Using this library, you can tell Excel to open a Spreadsheet, change and read the contents from .NET. I have used the library in a WinForm application, and I guess that it can also be used from ASP.NET.

Still, consider the concurrency problems already mentioned... However, if the sheet is accessed unfrequently, why not...

Answer 4

The simplest way to do this might be to:

Upload the Excel workbook to Google Docs -- this is very clean, in my experience

Use the Google Spreadsheets Data API to update the data and return the numbers.

Here's a link to get you started on this, if you want to go that direction:

http://code.google.com/apis/spreadsheets/overview.html

Answer 5

Let me be more adamant than others have been: do not use Excel server-side. It is intended to be used as a desktop application, meaning it is not intended to be used from random different threads, possibly multiple threads at a time. You're better off writing your own spreadsheet than trying to use Excel (or any other Office desktop product) form a server.

This is one of the reasons that Excel Services exists. A quick search on MSDN turned up this link: http://blogs.msdn.com/excel/archive/category/11361.aspx. That's a category list, so contains a list of blog posts on the subject. See also Microsoft.Office.Excel.Server.WebServices Namespace.

Answer 6

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)