Consuming Sharepoint Lists in Excel Services

Using UDFs and Excel Formulas to Get a Query Table on Excel Services

LuisBE on Services — Mon, 11 Dec 2006 22:58:27 GMT

As you may or may not know by now, one of the limitations in this version of Excel Services is the inability

Working Around Dimensional Limitations of Using Array Formulas for UDF Return Values.

LuisBE on Services — Tue, 09 Jan 2007 22:44:22 GMT

As you have noticed, many of my samples below use an Array Formula in Excel to retrieve an array of data

re: Consuming Sharepoint Lists in Excel Services

Swapna — Fri, 02 Feb 2007 07:53:33 GMT

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,

Swapna

re: Consuming Sharepoint Lists in Excel Services

LuisBE — Fri, 02 Feb 2007 08:05:01 GMT

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.aspx

re: Consuming Sharepoint Lists in Excel Services

Starkman — Sat, 03 Feb 2007 05:52:53 GMT

Are there currently any plans to provide an update or patch to allow Excel Services to access a SharePoint List with out-of-box functionality?

re: Consuming Sharepoint Lists in Excel Services

LuisBE — Sat, 03 Feb 2007 09:59:21 GMT

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.

re: Consuming Sharepoint Lists in Excel Services

SP — Tue, 13 Feb 2007 17:08:10 GMT

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,

re: Consuming Sharepoint Lists in Excel Services

LuisBE — Tue, 13 Feb 2007 18:53:09 GMT

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%)

re: Consuming Sharepoint Lists in Excel Services

Nick Vasiliev — Thu, 15 Mar 2007 08:38:03 GMT

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?

re: Consuming Sharepoint Lists in Excel Services

LuisBE — Thu, 15 Mar 2007 20:37:24 GMT

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.

re: Consuming Sharepoint Lists in Excel Services

Nick Vasiliev — Fri, 16 Mar 2007 07:56:43 GMT

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.

re: Consuming Sharepoint Lists in Excel Services

Nick Vasiliev — Fri, 16 Mar 2007 12:14:00 GMT

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)?

re: Consuming Sharepoint Lists in Excel Services

LuisBE — Fri, 16 Mar 2007 18:43:57 GMT

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).

re: Consuming Sharepoint Lists in Excel Services

Jackie — Wed, 28 Mar 2007 03:02:29 GMT

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?

利用UDF在Excel Service 中使用SharePoint 列表的数据

dosboy — Mon, 16 Apr 2007 16:05:54 GMT

当我们用SharePoint进行开发的时候, 我们的数据都是存放在SharePoint的列表中的,可喜的是 Excel Service提供了一个UDF功能,我们可以写一个UDF的函数来使用列表中的数据.

re: Consuming Sharepoint Lists in Excel Services

AndersR — Tue, 01 May 2007 23:43:17 GMT

Thow shalt always dispose thy SPSite/SPWeb objects ;-)

Anders

re: Consuming Sharepoint Lists in Excel Services

LuisBE — Wed, 02 May 2007 01:08:08 GMT

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!!

re: Consuming Sharepoint Lists in Excel Services

AndersR — Thu, 03 May 2007 00:46:25 GMT

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)

Anders

re: Consuming Sharepoint Lists in Excel Services

Padmaja — Thu, 17 May 2007 14:51:27 GMT

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,

Padmaja

re: Consuming Sharepoint Lists in Excel Services

LuisBE — Thu, 17 May 2007 20:18:56 GMT

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.aspx

re: Consuming Sharepoint Lists in Excel Services

Jeffrey — Wed, 18 Jul 2007 21:02:34 GMT

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 Bin\Release 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?

re: Consuming Sharepoint Lists in Excel Services

LuisBE — Wed, 18 Jul 2007 21:16:08 GMT

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).

re: Consuming Sharepoint Lists in Excel Services

Jeffrey — Wed, 18 Jul 2007 21:23:57 GMT

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...

re: Consuming Sharepoint Lists in Excel Services

LuisBE — Thu, 19 Jul 2007 01:35:25 GMT

Most likely the code is failing when trying to impersonate the user opening the workbook.

You need to enable delegation for the impersonation to work. You can do this with the following command:

"stsadm -o set-ecssecurity -accessmodel delegation" followed by "iisreset".

You can be sure that the impersonation is the issue by either attaching VS to the w3wp process and debugging it (see http://blogs.msdn.com/cumgranosalis/archive/2006/07/12/DebugUDFs.aspx) or by moving the line of code starting with "using(..." into the Try block (this should cause "An error has occurred" instead of #VALUE to show in the cell if impersonation is the issue).

re: Consuming Sharepoint Lists in Excel Services

Jeffrey — Thu, 19 Jul 2007 22:01:56 GMT

Thanks! the impersonation was the problem, your command example needed the ssp part but worked perfectly!!!

Thanks again,

Jeffrey

re: Consuming Sharepoint Lists in Excel Services

damao — Mon, 23 Jul 2007 05:41:13 GMT

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?

re: Consuming Sharepoint Lists in Excel Services

damao — Mon, 23 Jul 2007 08:56:33 GMT

don't worry about my previouse post. i have worked it out.

I forgot to enabling UDFs on sharepoint server.

re: Consuming Sharepoint Lists in Excel Services

damao — Tue, 24 Jul 2007 07:06:06 GMT

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.

re: Consuming Sharepoint Lists in Excel Services

LuisBE — Tue, 24 Jul 2007 07:20:09 GMT

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.

re: Consuming Sharepoint Lists in Excel Services

damao — Tue, 24 Jul 2007 08:21:02 GMT

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).

re: Consuming Sharepoint Lists in Excel Services

Jeffrey — Mon, 27 Aug 2007 17:53:24 GMT

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,

Jeffrey

re: Consuming Sharepoint Lists in Excel Services

LuisBE — Mon, 27 Aug 2007 19:41:18 GMT

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