Joel's SharePoint Architect Blog

I don’t know if there’s enough of these to make a series, but I’m going to give it a go.

I’m going to start with a MOSS Excel Services tip. If you’re already using this in anger, then I’m preaching to the converted. If you’re considering using Excel Services, or you’re about to advise a client on Excel Services, there’s a few things you need to know.

First off, some stuff you’ll probably know. Excel Services is a great platform for consolidating spreadsheets and bringing order for clients that are heavy Excel users. Investment banks – we know who you are – you folks with tens or hundreds of thousands of complex spreadsheets that get emailed about between users. Some of these spreadsheets are used to make multi-billion dollar decisions for the larger banks. Often, the authors of these spreadsheets are long since departed. Frequently, the banks daren’t make material changes to the business logic therein, just in case it all goes a bit Pete Tong.

Similar stories exist even when there aren’t decisions being made with quite such a high dollar-value. Government departments, pharmaceutical companies, accountancy firms, consultancies – the list goes on. All places with the potential to generate tonnes (can I measure digital data by weight?) of spreadsheets copied from user to user.

Microsoft Office SharePoint Server provides check-in and check-out to help restrict this kind of proliferation, as you’d expect in common with the rest of the Document Library features of MOSS. Excel Services gives us much more on top of that

When you add an Excel spreadsheet to a suitably configured Document Library, you can use Excel Web Access – kind of like Outlook Web Access - to view and interact with it. In fact, viewing through Excel Web Access is the default action on Excel spreadsheets in such a Document Library. There is also programmatic and web service access to Excel Services, which I’ll try and come to in another blogging.

Which brings us to:

Popular Misconception #1 – “You can edit the spreadsheet through the browser. The spreadsheet experience is just like the real thing – just like Google Docs.”

Nope. No you can’t. No it’s not. Sorry.

It’s good though! And what it offers is valuable. Instead, you get 60-70% of the core Excel desktop experience through the browser. Graphs, colour bars, pivot tables. All good, all decently rendered. And you can interact with the sheet through settable parameters and filters.

You can’t edit the sheet and you can’t save it. If you want to do that, you can either open the sheet in Excel client on the desktop, or create a “snapshot” – once again, for use in Excel client.

Why is this good? If you expose the sheet *solely* through Excel Web Access, none of your hard earned intellectual property – none of your business logic – ends up in the hands of your audience. You can build dashboards in MOSS that show regions of multiple spreadsheets or charts side-by-side with Key Performance Indicators (KPIs) or any other information you want to display.

Popular Misconception #2 – “I can take one of our complex spreadsheets and just stick it in Excel Services and have it run server side.”

Nope. Sorry. You’ve more than likely got some work to do. If your spreadsheet uses:

• VBA macros
• COM add-ins
• User Defined Functions

…you have some difficult decisions ahead of you.

If a lot of your business logic resides in VBA macros, you’ll need to consider recoding these as an Excel Services User Defined Function (UDF) in a .NET language. If your spreadsheet does lots of gnarly Monte Carlo analysis using a visual COM add-in, once again you’re left with a significant coding exercise.

If you’re lucky enough that your spreadsheet already uses client-side, C language User Defined Functions, you can either, once again, re-code them as an Excel Services Managed UDF – or write a managed wrapper for them.

There is a good list of unsupported and partially supported Excel client features in Excel Services here.

So, how do you do it? It can’t be easy, surely?

Popular Misconception #3 – “SharePoint Excel Services UDFs are difficult. It’s better just stick to simple spreadsheets and keep the complex ones as desktop spreadsheets.”

Very, very easy.

First, create a .NET 2.0/3.5 managed Class Library project in Visual Studio 2005/8.

Second, make a reference to Microsoft.Office.Excel.Server.UDF.dll, which is usually sitting here: C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\ISAPI on a server with MOSS installed.

Third, we’ll write our class and methods. Here’s a C# example.

   1: using System;

   2: using Microsoft.Office.Excel.Server.Udf;

   3:

   4: [UdfClass]

   5: public class MyUDFClass

   6: {

   7:     [Udfmethod]

   8: public double MyUDFMethod(double x, double y)

   9:     {

  10: return x * y + y;

  11:     }

  12: }

That’s more or less it. You’re now at liberty to fill your class and methods with as complex business logic as you see fit. The final step is configuration of the Shared Service Provider (SSP) for Excel Services, and deployment to a MOSS server – which may well be the topic of a future blogging.

To use your new UDF in a spreadsheet couldn’t be easier. You don’t even need to deploy the UDF to the desktop machine you’ll use to create the spreadsheet. Simply open a new spreadsheet, and use your function in a formula as if it meant something to Excel, thus:

Figure 1 – Using a Managed UDF in Excel Client

Yes, when you hit return it will get resolved to “#NAME?” as if it doesn’t recognise it. This is, of course, because it really doesn’t recognise it, and it won’t be recognised until you deploy that spreadsheet to a correctly configured Document Library.

That’s it for this time! :)

If you’ve been evangelising Excel Services having never used it, I hope this has cleared up a couple of common misconceptions. If you’ve been tempted to, but never quite made the jump, I hope this has given you a push to just *try* Excel Services. They’re pretty cool and, as I hope you’ll see, offer a compelling business story for bringing Excel spreadsheets back under control.

Technorati Tags: Excel Services, Excel Web Access, MOSS, SharePoint, UDF, User Defined Function, WSS