Excel Services and User Defined Functions in Managed Code

So I just finished coding the solution for my chapter on Excel Services. The more I dig deep in this feature of MOSS the more I am impressed. The thing is you have to look past the fact that this allows an organization to post thin-client read-only versions of spreadsheets. This is a great feature and a lot of the customers I have worked with see it as a help to low-bandwidth requirements as well as a way to distribute spreadsheet information. But of course my book is for developers and there is another side to the Excel Services story that is just way cool...

It shouldn't be difficult to imagine going into a consulting gig and the customer throws you a bunch of spreadsheets that contain the logic that they want coded into their enterprise application. This usually results in the development team re-inventing the wheel and rebuilding all of this logic into their app spread through stored procedures, business objects, and even the presentation layer. The problem is that such an approach reduces the organization's ability to maintain the logic that was in the spreadsheet. Things like ranges for price discounts, percentage of markup from their base prices, etc. This should be the developers view fo Excel Services. This approach allows the organization to still have access to these calculations in a spreadsheet form and once published a custom application can interact with it through the Excel Services web service.

Of course, the spreadsheet once a part of an enterprise solution, probably needs to communicate with databases, other web services, etc. This can be done as well with user defined functions. Basically these are methods of a .NET class that has been decorated with attributes so that it can be called as a function within Excel!

The solution in Chapter 6 of my book takes this exact approach. We dive into a spreadsheet for calculating prices of products, add connectivity to a database and web services, and then publish it to Excel Services. From there, we show you how to invoke the spreadsheet through web services thus including it as part of a custom application. Here are some great references I have found in researching these techniques:

Shahar's Blog: https://blogs.msdn.com/cumgranosalis/default.aspx

Luis' Blog: https://blogs.msdn.com/luisbeonservices/default.aspx

A primer: https://blogs.msdn.com/cumgranosalis/archive/2006/04/04/UdfsPrimer.aspx

Great list of links to get started: https://blogs.msdn.com/luisbeonservices/archive/2006/09/28/pre-requisites.aspx

Video: https://msdn.microsoft.com/msdntv/episode.aspx?xml=episodes/en/20060427exceldk/manifest.xml

So back to writing. Should have this one written up quickly as it has lots of good stuff. I'll update the TOC for the book once I am done. In case you have no clue as to what book I am taking about: Read more about my SharePoint/Office 2007 development book: https://blogs.msdn.com/edhild/pages/pro-sharepoint-solution-development-combining-net-sharepoint-and-office-2007.aspx

Susie is hard at work finishing up the InfoPath and Forms Services chapter. I'll have a post on that likely next week.