Excel Services Is Cruising the .NET Waters; Excel 2007 Missed the Boat

I

have blogged pretty extensively about my experiences writing .NET code for Excel Services (the server-based version of Excel that will be part of
SharePoint Server 2007). To sum it up: it's awesome. It is a fantastic idea, and the seamless way they've incorporated .NET programmability (with attribute-based model) is elegant and functional.

I've been writing .NET code behind spreadsheets pretty much since they released VSTO in 2003, and this is a tremendous improvement. To make a .NET method available in Excel Services, you simply add a [UdfClass] attribute to your class, and a [UdfMethod] attribute to your method. That's it. Deploy the DLL (or EXE) to the SharePoint server, and you can now use that method from within Excel cells in Excel Services.

This is, well, amazingly simple. I was (frankly) astounded at how easy it was--and I've been using VBA and VSTO from the get-go. Finally, a very simple way to do real programming behind a spreadsheet. VSTO was great, too, but you needed an add-in for Visual Studio, and you had to deal with all sorts of .NET permissions restrictions and deployment difficulties. This, on the other hand, was quick, painless, and elegant.

The only hitch? When working with Excel Services, you build your spreadsheets in Excel 2007 (naturally). And Excel 2007 doesn't know anything about tyour User Defined Function. So when you're working in Excel, your function calls look like this:

See all of those #NAME? values? That's because Excel doesn't recognize the function.

I asked Shahar Prish, Excel Services developer and blogger extraordinaire, about this, and he promised to write some posts about how to make that same UDF available in Excel. And, in the last few weeks, he's kept his promise.

As he explained here and here, there is a "simple" way to make your .NET method available in Excel:

COM.

That's right. COM. The technology of the 90s. And, it's a typical COM pain in the ass. I'm borrowing liberally from Shahar's post for the code below. You simply:

Decorate your class like so...

Add register and unregister methods...

Use regasm to register your COM server

Tell Excel to enable your Automation Server as an add in

Whew!

And, get this, that's only if you have zero-dimensional inputs or outputs. If you have 1 or 2 dimensional inputs (my first real UDF had 10 inputs, including a 2-dimensional array), it's even more complicated.

What does all of this mean? Well, I understand that Excel Services and Excel are two different products, and that one was born in the .NET era and one came of age as the earth was cooling. Let's face it: Excel has a legacy of code older than college freshmen.

And yet, Microsoft has been pushing .NET on all of us for 6 years. They've had lots of time to improve Excel's integration with it. Forcing developers in 2006 to go through all of that COM work is, well, enough of a pain that people just won't do it.

Shahar, by the way, is a great guy and did a great job with his posts. I continue to be impressed with that team and with their product.

I only hope that the rest of the Office teams catch up with them.

Photo credit: matthew_hull

Technorati tags: excel 2007, excel services