<UPDATE: New CS file attached with a minor bug fix and some major code reduction since I found the SharePoint format converter method :o) >
There are a few features that the v1 of Excel Services will not support out of the box. One of these is grabbing data from Sharepoint Lists.
Luckily enough the UDF framework allows us to write some pretty simple code that can grab the data from Sharepoint and return it to the Excel grid through an array formula.
Since I ran into this limitation a few times and numerous people have been asking for this feature I decided to write a UDF that does just that.
The goal here is to have an UDF that can be called as an array formula, whose function is to grab data from a Sharepoint and return it to the Excel grid.
Here is the signature for the UDF I created:
public object[,] getSharepointView(string serverName, string siteName, string listName, string viewName, bool getTitles)
Basically you select the range in an Excel grid that you would like the List data returned to, type “=getSharepointView(“http://myserver.com”, “MySSPSite”, “MyList”, “MyView”, TRUE)” and CTRL+SHFT+ENTER.
That last parameter will return only the list data when it is FALSE, and will also return the list column titles when it is TRUE.
Once you load the workbook to a server that has the UDF loaded (or on a client that has the COM add-in for this dll registered) you will get the data that user would see had he gone to the Sharepoint site himself.
At that point you can do with the data as you please… add some Conditional Formatting, use it as the source data for a Pivot, etc…
The code is a bit extensive to paste inline here, so I’m attaching the .cs file to this post. You will notice that I’m using the Microsoft.Sharepoint namespace which can be found in the Microsoft.Sharepoint.dll binary located in C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\ISAPI on the Sharepoint server. I have also decorated my class so that you can register it as a COM add-in and use it in the Client as well as the Server.
SEE .CS FILE ATTACHED!