Consuming Sharepoint Lists in Excel Services
<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)
Usage:
Basically you select the range in an Excel grid that you would like the List data returned to, type “=getSharepointView(“https://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…
Code:
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!
Comments
Anonymous
December 11, 2006
As you may or may not know by now, one of the limitations in this version of Excel Services is the inabilityAnonymous
January 09, 2007
As you have noticed, many of my samples below use an Array Formula in Excel to retrieve an array of dataAnonymous
February 01, 2007
In the attached .CS file may I know what for the following sections: CLIENT COMPATIBILITY VARIABLES CLIENT COMPATIBILITY If you dont include these sections also will this UDF works. It would be better if you can tell me how it works and how can we debug this UDF using sharepoint list. Thanks, SwapnaAnonymous
February 01, 2007
The Client Compatibility sessions are only required if you want to register the DLL with COM Interop so you can use it in the Excel Client as well as Excel Services. You don't need that section if you are only using Excel Services. More info on that here: http://blogs.msdn.com/cumgranosalis/archive/2006/08/03/ServerClientUDFsCompat1.aspx And here is some info on debugging UDFs in general: http://blogs.msdn.com/cumgranosalis/archive/2006/07/12/DebugUDFs.aspxAnonymous
February 02, 2007
The comment has been removedAnonymous
February 02, 2007
It is certainly a feature we're looking at and taking into account the customer requests we have already gotten for it there is a very good chance we'll include it in our next release. It's too early to tell right now as to when/how the feature would be added, though looking at this historically I wouldn't think it would be through a one-off patch. Given customer demand I'm fairly confident we'll be looking at implementing this as soon as it is feasible.Anonymous
February 13, 2007
Hi I have followed all th steps mentioned but while opening the excel sheet in Excel am getting following error. "values does not fall on the esxpected range". Is there any restrictions like we need to select the range in excel workbook exactly same as the columns and rows of sharepoint list and call the function. will it be a problem if i select less or more than the list dimensions.Please let me know about this as i got stuck here and not to display the list in excel workbook. Thanks,Anonymous
February 13, 2007
This is probably because the servername/sitename/listname combination does not correspond to a valid list in your SharePoint site. Double check that you have the correct spellings of your server, site, and most importantly the List name and the View name as I would suspect these two to be your culprit. If you navigate to your list you can get all this information, for example on a sample setup you would have: ServerName = Your Server SiteName = "" (empty string which == default site) listname = Your List Name viewname = "All items" (I'm pretty sure this is the default view, though I don't have a server in front of me to be 100%)Anonymous
March 14, 2007
I have a probelm with that UDF. There are "#VALUE!" in every cells of my excel table when I'm using “=getSharepointView(“http://myserver.com”; “MySSPSite”; “MyList”; “MyView”; TRUE). As u can see I'm using ";" because Excel didn't let me to use "," in formulas. And one more question. Did this solution works when function installed on SharePoint server or I must install UDF on every client computer?Anonymous
March 15, 2007
You must be using a localized build of Excel where ; is the separator instead of , which is fine and shouldn't be a problem. This UDF is meant for Excel Services, and while you could use it through a COM interface on the client the main purpose is to have it on an Excel Server on Sharepoint. #VALUE! means the code is throwing an exceptions. It could be a lot of things causing that. One thing I just noticed is that the version of the code I have attached on this comment won't work through COM on the Client right now because the impersonation should not be done on the client and I'm not checking that in this version, so make sure you're using this on the server. I've updated the attached file, can you try again with that and let me know what you get? Also, let me know wether you're using the UDF on Excel Services or on the client.Anonymous
March 15, 2007
Thaks for answer. Sorry for double post. This just because my comments doesn't publishing about few hours. I'm using this UDF on the client. Did you change just a text of Imersonation exception in the .cs file? I was trying to do this early but get the same result. This because exceptions doesn not dispaly in excel cells. I know it, but didn't know why this exceprions occuring (I didn't know that impersonalization doesn't work). I will try to use this UDF on server and let u know about result later.Anonymous
March 15, 2007
I has try to use this UDF on the Server. GetSharepointView functions works fine. I have some problem with externalRef function that was published by you on microsoft.com ("An error has occuried." is in the cells). But I think that because I do something wrong(may be it's because of localization of Excel). I'm not good in Excel. Is it possible to automatically set a range of table (if sharepoint list will grown table in Excel must grown to)?Anonymous
March 16, 2007
The latest CS file does two things... 1) It includes the impersonation in the Try block so we catch and handle any exceptions there by returning the Exception message. 2) It checks whether it is running in the client, and if so does not impersonate so it'll work on the client as well. To get more info on the error you should update the Catch block of the function you are getting "An error is occurred" from to return ex.Message instead of the hardcoded error message. This will tell you more about what is happening. It is hardcoded in the sample for securiy reasons (information disclosure).Anonymous
March 27, 2007
I am unfamiliar with step 1. How do I install the CS on the server so that when I select a range in Excel it will call on the CS code?Anonymous
April 16, 2007
当我们用SharePoint进行开发的时候, 我们的数据都是存放在SharePoint的列表中的,可喜的是 Excel Service提供了一个UDF功能,我们可以写一个UDF的函数来使用列表中的数据.Anonymous
May 01, 2007
Thow shalt always dispose thy SPSite/SPWeb objects ;-) AndersAnonymous
May 01, 2007
You're completely right Anders!! :o) I fixed this issue for this code on my MSDN article -> http://msdn2.microsoft.com/en-us/library/bb267252.aspx and apparently forgot to do so here. I'll try to update the code soon, in the meanwhile check out the code attached to that article instead (it has other fixes as well besides the disposal problem). Thanks!!Anonymous
May 02, 2007
NP :-) Another small performance addition would be to use foreach ( SPField field in currVal.Fields ) instead of a for loop. I know its a small thing, but for larger lists the performance impact is quite big (try using StopWatch on the two) since a stored procedure is called behind every time in a for-loop, but only one time in a foreach loop :o) AndersAnonymous
May 16, 2007
I have tried this with my sharepoint list, it worked as expected. The same logic I want to apply fro getting the data from Datasource(SQl server). Here I am having a problem, because for selecting a range in Excel Sheet client while specifying the UDF, I don't know thw number of rows to be selected for the Range as the data is dynamic. And if I select may rows by assuming the number of rows, Excel sheet is displayed with #N/A for the empty cells. Is there a way that I can sove the range selection problem with Excel client? Thanks, PadmajaAnonymous
May 17, 2007
Padmaja, for info on this see: http://blogs.msdn.com/luisbeonservices/archive/2007/02/01/enable-paging-in-your-array-formula-udfs.aspx http://blogs.msdn.com/luisbeonservices/archive/2007/01/09/working-around-dimensional-limitations-of-using-array-formulas-for-udf-return-values.aspx http://blogs.msdn.com/luisbeonservices/archive/2006/12/11/using-udfs-and-excel-formulas-to-get-a-query-table-on-excel-services.aspxAnonymous
July 18, 2007
I've used the XlUnlimitedUDFsSampleWB, etc from the office framwork and built the project. I can get the external references to work on a spreadsheet I open from Sharepoint via Excel, however I am unable to the Sharepoint Query table to work via Excel or Excel Services and the External References doesn't work via Excel Services. I've put the contents of the BinRelease folder into a c:UDFs folder on the server at this is where I've setup the UDFs config in sharepoint from. I've got "trusted locations" setup to use http://ms-sharepoint/sitedirectory/IT/shared documents How can I troubleshoot the problem?Anonymous
July 18, 2007
What errors are you seeing when trying this? Check the EventViewer for logs that could help pinpoint the issue. If you see #VALUES, you can get more info by modifying the code to return the Exception Message to Excel so you can identify the error (I don't do it in the sample since it could be considered information disclosure on some environments).Anonymous
July 18, 2007
Thanks for the reply! there are no entries in the logs that have anything to do with this... Yes, I've go the #Value! Could you please point me in the right direction for setting up the exception message? I'm not a programmer and am learning VStudio presently...Anonymous
July 18, 2007
The comment has been removedAnonymous
July 19, 2007
Thanks! the impersonation was the problem, your command example needed the ssp part but worked perfectly!!! Thanks again, JeffreyAnonymous
July 22, 2007
The UDF works fine in excel. But when i uploaded the excel file to a sharepoint library and view in web browser, i got "#NAME?"; open in excel works fine. Could you let me know what I did wrong?Anonymous
July 22, 2007
don't worry about my previouse post. i have worked it out. I forgot to enabling UDFs on sharepoint server.Anonymous
July 23, 2007
When I made changes to the sharepoint list (eg. change one of the column name) or added a new entry to the sharepoint list, the changes don't seem to be updated in Excel unless I reselect the range in Excel grid that the List data returned to, type “=getSharepointView(B1,B2,B3,B4,B5) and CTRL+SHFT+ENTER. I would like to auto-refresh the changes to the sharepoint list in Excel & EWA, is this possible? Can you show me how to achieve this plase? Thanks.Anonymous
July 23, 2007
Since we are using a UDF to grab the data from Sharepoint that data will only get retrieved upon calculation (since that is the only time the UDF is running). There is no way to set up an automatic re-calc for the server unfortunately, your users would have to trigger a re-calc whenever they want to see the latest data.Anonymous
July 23, 2007
can you point me another way of achiveing this please since UDF doesn't auto re-calc. I would like to be able to analyze the data from sharepoint lists and present the resluts in a nice format to the users via EWA (pivet tables perhaps).Anonymous
August 27, 2007
How can I remove out the #N/A that is retrieved if there isn't any further entries in the array area? I've got an array that will be 20x100 when completely filled by the weekly data, however that is going to take about 3 months. I'm summing the running data... Thanks, JeffreyAnonymous
August 27, 2007
The #N/A you see is the error Excel uses when it does not receive a value for a cell in an array formula. What this means is that the array formula on the workbook has a bigger dimension than you are returning. To "fix" this, you can always return from your UDF the same dimension array as you have in the workbook, and leave the empty items blank. For example, if your array formula occupies a 4x4 range in Excel, return a 4x4 array from C# instead of anything smaller as Excel would pad the missing cells with #N/As