Blog

I recently had the opportunity to take a close look at using spreadsheets and more specifically Excel as a key component of a business process. The tasks at hand were relying heavily on Excel's calculation engine, performing some complex manipulations there, and using a larse dose of custom add-in and macro functionality, circling around and connecting to some databases (in this case, Oracle and Microsoft Access). And in updating with the times and all things going to the intertubes, the need now was to seek out a component that could comparably swap out Excel on the client side and bolt onto a new web server centric offering.

In the past, Function1 has used SpreadsheetGear to a good deal of success, it is fast, well documented, and abundantly supported by their in-house staff, but in any good recommendation providing some backstory and alternatives is always helpful. Additionally, since the last time we poked into the spreadsheet services realm, it is a good idea to see what competitors have cropped up.

(Alphabetically)

• Apache POI
• BIRT Spreadsheet
• ByteScout Spreadsheet SDK
• ExtenXLS Spreadsheet SDK
• Pagos Spreadsheet Component
• Sharepoint Server Excel Services
• SpreadsheetGear

Thankfully the review process for narrowing down our many contenders now was helped along by some key features not provided by a majority of the field.

Our inclination to use SpreadsheetGear stems from a few rather specific needs:

The ability to have custom functions handled beyond those specified by the Excel API. I need to be able to have AllansSillyAddOne function that does something wildly crazy, for example, always add two.

This requirement cuts out many of the options, the notable options that provide a framework model for handling custom function implementations were: Apache POI, Sharepoint Server Excel Services, and SpreadsheetGear. This support is usually built around developers creating "user defined functions" (Apache and Sharepoint) or "custom functions" (SpreadsheetGear). So even for Apache POI we can overlook, slightly, that their function suppor might be a smaller set of Excel built-in functions than the other alternatives because they provide you the methodology to define your functions, if needed.

Support for a majority of the Excel built-in functional calculations.

Most of the choices listed support a majority of the Excel functions that are commonly used. And on this point, most options remain solidly on the table.

Documented and readily available support.

Much of the time we browsed documentation, sample code, and scraped as much information as we could put together just by browsing the site. That said, if the most recent update was from years ago, and there are broken links littered through what should be APIs, samples, and product fact sheets, I was less confident that a deeper dive into the SDKs would reveal an appropriately solid solution. As what I was looking for was a solution that had both a clean online presence and a history that coud reassure that the product would still be here in a few years, which is less likely if the state of a website is in disrepair today.

The end options were narrowed down to roughly 3, they were the three options that supported my need for custom functions to be defined and executed on the fly by the server reading my spreadsheets.

Apache POI - which will require a heavier amount of rewritten user defined functions if the subset of Excel functions supported is insufficient. Apache POI supports approximately 100 built-in functions in Excel, while the other options were closer to 300.

Sharepoint Server Excel Services - this will involve user defined functions which then need to be registered to the server in the form of .dll files and also the non-trivial task of installing/licensing/managing a Sharepoint instance, so the footprint of the solution here is fairly large, with the potential to be huge.

SpreadsheetGear - custom functions here are supported and roughly 300 built-in functions are provided out of the box, and the footprint on the end product is relatively small when compared to Sharepoint.

The choice on which technology to settle on here has differences in costs and benefits, but laying out the choices and defining what those pros and cons are in a clear picture is what was key. Choosing the best option from a narrowed down field of many to a few can be left to the needs of an individual business, my job today was just to find what would work best for our developers on the internets.