What Is Excel Services 2007, and What Is a User Defined Function and Why Should I Care?

A

s I said in my previous post, I've been up to my ears in Excel 2007 and Excel Services for the last week or so. Here's the first in my series of posts describing my experiences.

First of all, what is Excel Services? If you really want to hear this from experts, go read Shahar (Cum Grano Salis) and David's (Excel 2007) blogs. They work on the team, so they both have great insight. Shahar also has very good programming tips, and photos of a soccer ball about to be born.

Here's my outsider's take on what's going on with Excel Services. Microsoft has done a bunch of work to add capability to SharePoint Server 2007 to integrate with Excel. You can now take a spreadsheet, publish it to SharePoint, and have SharePoint make that spreadsheet available in 3 ways:

1. Share the document itself (SharePoint has been able to do this for a while)
2. Make the spreadsheet available as a web service. You designate certain fields in the spreadsheet as inputs, certain fields as outputs, and you publish it to SharePoint. The logic behind your spreadsheet is now available as a web service. Cool stuff.
3. SharePoint can now dynamically render that spreadsheet as HTML in a browser. Again, you can specify certain fields as inputs. When your users view the spreadsheet in their browser, they can enter the inputs. Excel Services recalculates the spreadsheet based on their inputs and re-renders it to HTML.
   

This last thing is really cool stuff. This means that you can now give access to a spreadsheet to anyone in your organization without e-mailing it around, maintaining control over it without worrying that people will mess it up, etc.

Imagine you're a loan company, and you have different loans you can offer. You write a spreadsheet that models your loans, and you publish it up to your SharePoint server. Now, when any of your CSRs is on the phone with a potential client, s/he can use a browser, open the loan spreadsheet, and fill in the principle, term and interest rate of the loan. Heck, you could get fancy and write a UDF (see below). Excel Services would calculate the loan, providing instant numbers for payment size, total interest paid, total cost of the loan, etc. And that whole thing was accomplished without a programmer getting involved.

Better yet, you still have total control over that spreadsheet. If your company's loan fee structure changes, you can modify your spreadsheet and republish it. There's no need to worry that everyone has the latest version, there's no e-mail that clogs your server with new spreadsheets, there's no frantic deleting of old versions. Again, all without a programmer. It's very cool.

So, that's Excel Services. What's all this about User Defined Functions?

Well, as powerful as it is, Excel 2007 can't do everything. Some things require a programmer. A User Defined Function allows a programmer to write a function that is available within formulae in Excel. In other words, if I write a UDF called MyCoolFunction, I can type "=MyCoolFunction(A1:B3)" into a cell, and it will evaluate MyCoolFunction (which exists in a DLL) when that spreadsheet is calculated in Excel Services.

This takes that previous functionality and makes it even more valuable. If you need a developer to write a function that gets used in your spreadsheet, he can do it. If that method already exists in a DLL, it may be as simple as adding an attribute ([UdfMethod]) to a particular method in order to make it accessible from Excel. The developer doesn't have to mess with putting VBA behind your spreadsheet (in fact, you can't put VBA behind a spreadsheet that will be rendered by Excel Services). The developer doesn't have to mess with your spreadsheet at all. Any spreadsheet on the server can use the method. It's very powerful.

So, back to the loan company example, let's say your company has developed an algorithm for credit approval. It's too complicated to model in Excel, and it involves a web service call to a credit bureau. You have a developer write a method that takes in some info (maybe a social security number, income, total asset value, debt value, etc)--and now you can use that method in the loan calculator spreadsheet that every one of your CSRs uses. I'm telling you, this is powerful stuff.

Ok, the stage is set. One of my good friends at Microsoft asked me to take one of their existing demonstrations (one that runs a financial model behind Excel 2007 on a cluster using the Digipede Network) and port it to run on Excel Services. I'm an optimist, and I'm burdened with overconfidence. I told him, "No problem," even though I had only a week to prepare it and had never so much as seen a SharePoint site, had no idea what a UDF was, and had never worked at with Excel Services.

That's a big promise. I delivered it to him yesterday, but it was a stretch. Coming next: what it takes to go from Excel to Excel Services.

Technorati tags: excel services, excel2007