2005.08.03 12:25 PM

Surprise

[Updated 8/5/05: Got a note from SpreadsheetGear's Joe Erickson today describing some mods they've made to their product based on feedback. They're planning another beta release later this month. I've updated the post below to reflect the anticipated changes.]

My world changed this morning. Really. And I'm still coming to grips with the implications.

For the last many years I've been engaged in work involving the control, manipulation, and execution of Excel spreadsheets. The work has never been about the spreadsheets themselves as much as it's been about finding ways to encapsulate and reuse portions of them in broader functional abstractions. In doing this, I've pushed Excel to its client-side and (unsupported) server-side automation limits, I've explored and/or utilized numerous alternatives (listed at bottom), and for the last couple of years I've been semi-seriously engaged in developing my own .NET-based Excel-compatible engine. In fact, I was still up at 1AM this morning perfecting my sparse matrix storage and indexed addressing logic.

Not anymore.

This morning I followed a link from Mike Gunderloy's Daily Grind over to SpreadsheetGear and found this:

  • Create, modify, calculate, read and write Excel 97/2000/XP/2003 workbooks.
  • Supports multiple worksheets, unlimited workbooks, and unlimited workbook sets.
  • High performance calculation engine.
  • Royalty free deployment.
  • All financial, date, time, text, lookup, math, trigonometry, statistical and database functions.
  • All types, operators, defined names, data tables and arrays.
  • Support for multiple worksheets and multiple workbooks in formulas.
  • Worksheet specific defined names and global defined names.
  • Supports Microsoft .NET Framework version 1.1, Microsoft .NET Framework version 2.0 and 64-Bit Microsoft .NET Framework version 2.0.
  • Microsoft Visual Studio .NET 2003 and Visual Studio .NET 2005 integration, including IntelliSense and Dynamic Help.
  • The security of 100% safe managed code.
  • Hassle free deployment with a single assembly (SpreadsheetGear.dll).
  • No license keys, registry entries or configuration files to deal with.

Wow. Hop over and read the whole thing, including the long list of supported functions.

Of course I immediately signed up and grabbed a copy of the free beta. Simple as pie to install and begin using. I used it to open a handful of Excel workbooks, the largest being about 12MB with 60+ sheets, 80,000+ formulas, and 2,500+ named ranges. The workbooks all opened very fast, much faster than in Excel. I programmatically changed some values, recalculated and compared the results with Excel's and found no differences. While this hardly qualifies as a broad test of SpreadsheetGear's compatibility, it does put it ahead of more than half the products listed below.

After gathering myself up a little (did I mention I was up late?) and sending this info to some customers and associates who I figured to be as interested as me, I had the pleasure of speaking by phone with SpreadsheetGear founder Joe Erickson. Very nice fellow. Mr. Erickson has accumulated years of experience developing spreadsheet software, first with Innovative Software's Smart Spreadsheet, Informix Wingz for the Macintosh, and then Tidestone's Formula One. We talked about his experience developing spreadsheet software (it's a self-described "obsession") and his relationship with former employer Tidestone (now Actuate), which is also located near Lenexa. We also discussed SpreadsheetGear's plans for licensing, pricing, tentative release dates, various .NET techie things, the difficulty of matching Excel numbers to the 15th digit, my related spreadsheet projects, and blogging. I'm not going to repeat all the details (we didn't really discuss what parts were for public consumption), but I do urge you to call Mr. Erickson and get the details for yourself. I don't think you'll be disappointed. Honestly, I feel like they've raised the bar and we're likely to be seeing a lot more of them in the future.

Enough praise. So, what's missing? It is a beta, after all. Here's a quick list based on my short look this morning:

  • I really need to be able to open Excel workbooks in SpreadsheetML XML format. My problem domain is all about slicing, dicing, attributing, storing, and loading portions of Excel workbooks, and XML is the only way for me to make this happen. 8/5/2005 Mr. Erickson's waiting for the next version of Excel before committing to support for an XML format. I'm going to code my own SpreadsheetML loader and see how it performs.
  • The library's various collection interfaces (IWorksheets, IRange, etc.) need to implement IEnumerable and/or provide a GetEnumeration method so that we can use for each loops. 8/5/2005 Done, cool.
  • In a similar vein, the IRange interface needs a Cells collection (IWorksheet has one), and it needs a linear indexer. 8/5/2005 Done, cool.
  • There's something up with SpreadsheetGear' interface-based approach that requires explicit casts to the interface type and/or implicit casts via assignment to variables of the interface types. For instance, this works: 8/4/2005 Determined to be related to the workbook containing Excel 5.0 dialog sheets; I've passed the details on to SpreadsheetGear. 8/5/2005 Mr. Erickson reports that they now exclude dialog sheets; it's no longer necessary to check for nulls.

Also, their site is experiencing some FireFox difficulties, but I have it on good word that they're getting right on it. 8/4/2005 Fixed.

Anyway, I'll keep testing and watching the beta and will report anything I find of interest. And, it looks like I'll also be refactoring a large part of my developer life. Oh well, onward and upward.


Below, in no particular order, are some of the products that I've used and/or evaluated over the years that intersect this space and that have all come up short in some way (features, compatibility, language, licensing or something). Note that some of my comments are rather old and it's likely that some products have improved considerably since I last saw/used them. In other words, don't take my word for anything, check them out for yourself.

  • KDCalc
    • Java/.NET (J# conversion?)
    • Results in an opaque binary interpreted at run-time
    • Many functions still unsupported (limited array support?)
    • Expensive (server and author/developer licensing)
  • Java Spreadsheet Component
    • Java
    • German (difficult to follow some things)
    • Uses workbooks directly (Excel 97-2003)
    • Very limited function support (A big part of the standard Excel™ functions are Microsoft- / Windows- oder Excel-specific and have got nothing to do with business logic. Therefore the realization of all functions was not necessary)
    • Some VBA (custom function) support
    • Expensive
  • ExcelEverywhere
    • Converts to browser-based workbook (presentation and calculation)
    • Targets Javascript/VBScript/C# (ASP and ASP.NET)
    • Nice set of functions
    • Client-side Javascript exposes calculation logic and supporting libraries
    • Interesting how they convert all multi-cell references to arrays
    • Been around forever
    • $99 for HTML/Javascript, $149 for ASP.NET (per author); no distribution/server license
  • TurboExcel
    • Produces C/C++
    • Has trouble with large/complex workbooks
    • Requires explicit selection by user of range to convert from within Excel
    • Very complete function set
    • Expensive
    • InfoWorld articles: here and here
  • SpreadScript
    • Raw
    • Limited Excel formula syntax
    • Broad function support
    • Something odd going on with their calculation tree
    • SpreadScript is a scriptable spreadsheet engine designed to provide spreadsheet processing solutions for the Web, or any application where the automated handling of spreadsheet data is required. With SpreadScript, native Excel and NExS spreadsheets can be created, read, written, and calculated from Perl, Python, or Tcl. The SpreadScript engine is derived from the tried and true NExS spreadsheet for Unix and Linux. Its small footprint and tightly written C code make it ideal for the most demanding Internet or Intranet applications. With APIs for the most popular web scripting languages, SpreadScript makes it extremely easy to develop web applications for manipulating Excel spreadsheets.

I've also explored and/or utilized the following products, which also missed my mark for one reason or another:

  • OpenOffice Calc (check out the great Excel info they've accumulated)
  • FarPoint's Spread
  • Actuate's (Tidestone) Formula One ActiveX (they also have a Java-based e.Spreadsheet Engine, but I've never bothered to evaluate because I got burned so bad on the whole Formula One licensing thing back before it went to Tidestone)


Comments

I'm also looking for a solution: I was using a combination of HTML (with Excel extensions) for export and Excel-ODBC for import (user had to "Save As"). I recently switched to Office 2003 SpreadsheetML and am VERY happy with the results.

IMHO, they shouldn't wait for Excel 12 for XML support. The current version of SpreadsheetML works in Office XP and 2003, and the next version will be completely different anyway (a zip file of XML and other files rather than a single XML file), so it stands to reason that they should plan on supporting both independently.

Richard Tallent | 2005.10.03 12:25 PM

Hi Richard,

That was sort of the case I tried to make, too. But Joe explained their priorities and resource limits to me, and I understand now why they wanted to wait. In any case, if I ever make it through my move, I'll finish my own SpreadsheetML reader/writer and post it for everyone to use.

Thanks for stopping by!

ewbi.develops | 2005.10.04 07:45 PM

One of the notable features of KDCalc is that it can generate complete ASP.NET, ASP Classic, JSP and HTML applications as a user interface.

The next release will support live charting as well. There is a preview of the charting feature on the web site.

Mike Smialek | 2005.10.19 03:14 AM

Hi Mike,

Thanks for the info. That's never been a significant part of the work I do, where instead I've been concentrating on non-visual calculation engines, but I've talked to Joe at SpreadsheetGear about this and they are aware of the need to compete in the visual WinForms and ASP.NET space and I understand they are making progress in that regard. It will be interesting to see what they come up with.

Thanks again for the comment.

ewbi.develops | 2005.11.08 03:50 PM

Guys

Really thought the blog most helpful, am researching this space now for server resident hi performance calculation engines that offer more than Excel. Bumped into Mathematica - offers richer and more scalable solution. Any experiences to report???

• Mathematica from Wolfram ( www.wolfram.com ), which integrates a numeric and symbolic computational engine, graphics system, programming language, documentation system, and advanced connectivity to other applications. It can be server resident and will operate within a Service Orientated Archtecture.

John | 2006.02.21 08:51 AM

Hi John,

No direct experience with Mathematica, but I participated in a review of another contractor's proposal to one of my clients for a server-side calculation engine based on Mathematica. From that brief review I can confirm that it is definitely richer than an Excel-compatible spreadsheet-oriented solution. I'm not sure it's inherently more scalable, though. It certainly includes a number of supporting pieces that make scaling easier and faster to realize (e.g., gridMathematica, webMathematica, etc.), but there's nothing preventing one from developing similar scaling infrastructure for many of the solutions above, too.

What really set Mathematica apart from the other proposals I reviewed was the cost - it was prohibitively expensive, both in terms of licensing and re-training (of both functional and developer staff). As a result, my client eventually settled on a spreadsheet-oriented solution, which proved to be more than capable for their needs.

If you do eventually go with Mathematica, please come back and let us know what you think.

Thanks!

ewbi.develops | 2006.02.21 10:55 PM


TrackBack

TrackBack URL:  http://www.typepad.com/services/trackback/6a00d8341c7bd453ef00d8349e735269e2

Listed below are links to weblogs that reference Surprise: