tech guy in midtown

For the past week I’ve been programming a collection of Excel worksheets in C# .NET using VSTO. Put simply, the Excel sheet needs to pull data from a database into some spreadsheets, where the user will play with it and sometimes write new data back out to the database. Excel was selected simply because that’s what was requested.

I’ve never programmed an Excel application before, so the past week has been a learning experience. Unfortunately, pain has outweighed pleasure.

First, the documentation is abysmal. Each feature spawns a peck and hunt through a maze of MSDN pages. The functions have cryptic arguments. Examples are rare. Each page has supports user comments, but I’ve yet to see a single user comment on any of the MSDN pages.

Second, the C# code that you do manage to hammer out shares no resemblance to the plain Excel functions that you often are trying to replicate. I’ve often found myself building up cell references and ranges, and all the references are rigid (like if somebody were to insert a row or column, it would all fall apart).

This week I wasted time trying to use a ListObject (a table that supports column filters and sorts). The ListObject is seductive because you can bind it to a data source and institute rules such as read-only columns, typed columns, and primary keys. Unfortunately the ListObject falls short in some critical ways — most critically with formulas. Formulas for databound ListObjects seem to be unsupported, and even if you don’t bind the ListObject to data, every cell within a column is forced to contain the same formula.

I do think Microsoft has created a good thing with VSTO. Programming Excel from C# feels more robust than hacking some Visual Basic together (which, in fairness, I’ve never attempted). Visual Studio is a joy to use, and I’ve always said that Excel is the greatest programming application ever created (because of all the novice users who can do powerful, custom things with it).

I wish Microsoft would do two things:

1. Improve the documentation.
2. Add a declarative programming method for specifying ranges and relative cells. I’d like a way to say “okay, this cell contains a function that references the cell two to its left, and then I want to copy that all the way down.” You can partly accomplish that by working within the xls sheet, but once you bounce into C#, it becomes difficult.