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.