Using Excel Server-Side with SpreadsheetGear

By Deane Barker on April 22, 2009

A couple weeks ago, we had a client come to us with an epic Excel spreadsheet. It did a series of calculations across about nine worksheets — it was a thing of beauty. Astoundingly complex.

They wanted to “Web-enable” it. My blood ran a little cold.

What do you do in these cases? The first instinct is to reverse engineer the calculations and write them in native code. However, this would have been a disastrous option in this case — it was that complex, and prone to change if they adjusted their formulas.

We thought about just manipulating the spreadsheet server-side using COM Interop or something, but that’s seriously frowned in Microsoft circles. When Excel opens, it expects to be in the context of a user — a human. It reserves the right to pop dialog boxes or try to get settings from a user profile. If it can’t, it also reserves the right to give you a very bad headache.

Enter SpreadsheetGear. I try not to directly pimp stuff on this blog, but this software is that good.

This is a .Net library that lets you use any Excel spreadsheet as a big calculator. You can write heinous formulas and calculations, and use the sheet as a method call, essentially. You open it, set values in cells, then read values out of other cells. (Our client was running on eZ publish on a Linux box, so we wrapped it in a nice little Web service.)

It sounds a little ho-hum, but lemme tell you — we had few other options. SpreadsheetGear turned what would have been a week of reverse-engineering and rewriting formulas into 15 minutes of C#.

Support is great. Before we bought it, I was going back and forth with the developer about what it could do, and he finally just called me up. We chatted for a few minutes (they’re just down the road in Kansas City), and he answered all my questions. Yesterday, we wanted to do something interesting, so we wrote them to ask if it was possible. It was, and they conveniently wrote all the code for us and sent it along. I sent another support email today, and my phone rang about five minutes later. I had forgotten that some people were serious about software support.

Today, our client wanted to output the source spreadsheet in a simplified form — they wanted to extract a single worksheet from it and make that available to the user. No problem. SpreadsheetGear lets you do about everything you can do from the interface, so we just copied the range, pasted it back into itself (thus removing all the formulas and pasting back the calculated values), then deleted every other worksheet. Voila — a single worksheet Excel file, with no proprietary formulas or dependencies.

In absolute terms, it’s not cheap — we paid $999 for a developer license. But in relative terms, it’s a steal — to re-write the formulas in that sheet would have taken five times that amount. MSN Money is apparently a big user of the software for their financial calculators.

So, anyway, just a quick pitch for a great piece of software that has given me faith that there are still developers who write great code to solve real problems and are serious about supporting it. Bravo to those guys.

Gadgetopia

Comments

  1. I agree that SpreadsheetGears is great. I have used it in the past and have had good things to say about it. If you are looking for a server-based solution though, Excel Services is a better choice. The flip side of that is a client-based solution which Excel client is a better choice. Since this blog article is focused on server solutions I will focus on that.

    SpreadsheetGears has a few issues that are problematic. First, they never have 100% fidelity with Excel client. Even in their “What’s New” and “Bug Fixes” section you find that they either added support for something already in Excel or fixed a bug in their product to make sure they are consistent with Excel. Of course the last comment assumes Excel did the right thing. In general though, they lag Excel features and are never 100% compatible. In my experience, customers that are even moderate to heavy spreadsheet users often run across this issue.

    Next, SpreadsheetGears is a set of controls and components. It is not a server. It relies on the application to be the host for the calculations. Therefore any performance and scalability is directly related to the application. Architecting a scalable solution around spreadsheets is more complicated that it may seem. You have to take into consideration multiple concurrent calculations, concurrent access to a spreadsheet, and much more. SpreadsheetGears offers very basic background threading capabilities. This is great for not blocking user interface threads, but is far from a server-based solution.

    Excel Services is Excel! What I mean by this is that it offers the exact same calculation engine as Excel client, a web user interface, web services, and much, much more. It doesn’t have full feature fidelity with Excel client though. For example, VBA/Macros are not supported, but even SpreadsheetGear has this limitation.

    If you want to use Excel Services over the Internet, you can get an Internet-based license which makes it relatively cheap. What is interesting is that Excel Services has features to scale calculations up and out. What this means is that the Excel Services team has taken into consideration the need to support multiple users and run multiple concurrent calculations. They even have the ability to scale-out using multiple load-balancing techniques. There is even an approach to deploying Excel Services on Windows HPC Server 2008 so you can use grid computing. Can we say parametric sweep against your Excel spreadsheets? Very cool!

    Excel Services goes beyond just being a calculation engine though. It is part of Microsoft Office SharePoint Server (MOSS). Because of this it enables some useful capabilities like a rich web user interface that offers the same rich formatting of Excel 2007 as well as multiple browser support. This feature is called Excel Web Access. It allows you to view spreadsheet in a web browser. I have seen Excel Services shown in Safari on a Macintosh many times and the spreadsheets look great.

    Excel Services even takes advantage of Document Libraries in MOSS for storing of spreadsheet so they can be managed and versioned. Yes, document versioning of Excel spreadsheets. One of the more interesting security aspects of this is that you can publish an Excel spreadsheet to MOSS and protect the spreadsheet. If you want, you can give access to portions of the spreadsheet by only publishing worksheets, named ranges and/or charts. You can even prevent someone from downloading the actual spreadsheet or allow them to download only the data (with formatting) so that you can protect any intellectual property.

    My favorite feature is the Excel Web Services. This allows you to access Excel spreadsheets using web services. This is great for models to be created in Excel and then reused in production applications. Previously developers had to re-implement the model in code. No longer!

    The best is that all of these features with Excel Services are “out-of-the-box”. This means “No Code”. Of course if you are writing an application around these capabilities you will have to write code for that application, but you will write much less code since Excel Services supports these features in a fully supported server based product. With SpreadsheetGears you have to write code in order for it to do anything. Many of my customers are deploying spreadsheets directly to Excel Services and immediately using them over the web or from within other applications using web services.

    After architecting many server-based spreadsheet solution, I have found that 100% fidelity matters to customers, scalability matters, and Total Cost of Ownership (TCO) matters. Excel Services already handles the first two things. With respect to cost, SpreadsheetGears is a build decision with a buy component while Excel Services is a buy solution. There is addition upfront costs for choosing Excel Services, but over time (and a short period of time too) the solution pays for itself very quickly.

  2. The problem with Excel Services is that it is a Microsoft product. This company do not respect it’s customers and pay very little attention to legacy. For such a big and rich company it would be rather easy to launch a product 99.9% compatible with legacy apps.

    Instead, they hire “smart” young guns from the best universities who think the world was waiting for them to solve the human kind’s problems. Don’t ask those geniuses anything otherwise you’ll get a condescendent and disdainful “it’s by designnnnnnnnn” as an answer.

    We compare two products, one from a billionaire and another one from a company who wants to help and will certainly do it’s best. And offers great support

Comments are closed. If you have something you really want to say, email editors@gadgetopia.com and we‘ll get it added for you.