Excel Headlines

Today's author, Dan Parish, continues his discussion on building an Excel Services solution.

In Part 3a I walked through how to create the SharePoint List that stores the data users enter. In this post, I will walk through how to create the Excel Services User Defined Function (UDF) that will pull this information from the SharePoint List into Excel Services.

Overview

Out of the box, Excel Services does not support pulling in data stored in SharePoint Lists. However, you can add this functionality using one of Excel Services' extensibility methods: User Defined Functions.

User Defined Functions (UDFs) are custom worksheet functions that can be used to extend the capabilities of Excel Services. Once implemented they work just like any other function, except that they run your own code. For more information on UDFs, see the blog posts Excel 2007 investments in UDFs Part 1, Part 2, and Part 3.

While you can build UDFs that work on both the client and server, for this post we are just going to create one that works on the server and that fetches the information from the SharePoint List and returns it into the Excel workbook.

The code for the UDF and the workbook is included at the end of this post.

Writing the code

Before you can write any code, the first thing you'll need to do is to create a Visual C# Class Library in Visual Studio. Once you've done that, you'll need to add references to Microsoft.Office.Excel.Server.Udf.dll, and Microsoft.SharePoint.dll (both of which are located your Office SharePoint Server's ISAPI folder).

When writing a UDF, the first step is to mark the class as a UDF class, like so:

…

[UdfClass]

public class Class1

{

…

This ensures that Excel Services knows that there are UDF functions within this class. If you remember back to Part 2 of this series, the function that we want to create is called GetMachines, and it takes as a parameter a string that specifies whether we want data for laptops or for desktops. It returns an array of values which we then input into our spreadsheet using an array formula. The function definition then looks like:

…

[UdfMethod(IsVolatile = true, ReturnsPersonalInformation = true)]
public object[,] GetMachines(string MachineType)
{
}

…

You'll notice that I've marked it as a UDFMethod which tells Excel Services that this is the function that will be usable in your worksheet. Setting it as volatile ensures that it calculates all the time and won't have its values cached by Excel Services, while saying that it returns personal information is required to impersonate the user that is loading the workbook, which we do in order to access the SharePoint List as them, and not as the Excel Calculation Services' process account.

To impersonate the user, we'll use a simple function called impersonateUser(). All it does is try to get the Windows identity of the user accessing the workbook, and if it can, impersonate them. The function is:

// Causes the UDF to impersonate the user that loaded it through the EWA
private WindowsImpersonationContext impersonateUser()
{

WindowsIdentity wi = Thread.CurrentPrincipal.Identity as WindowsIdentity;
if (wi == null)
throw new InvalidOperationException("Can't get Windows identity.");
return wi.Impersonate();

}

Using that function, the first thing we need to do in our GetMachines function is impersonate the user like so:

…

// Impersonate the current user using the EWA and not the ECS
using (WindowsImpersonationContext wiContext = impersonateUser())
{

…

Now that we've created the class and method and impersonated the user, we need to go ahead and get the requested data from the SharePoint List:

…

try
{

// Declare constants
string serverName = http://server/;
string siteName = "site";
string listName = "list";
string viewName = ""; 

if (MachineType.Equals("Laptops"))

viewName = "Laptops";

else

viewName = "Desktops";

// Get objects corresponding to the SharePoint Site, List, and List View
SPSite site = new SPSite(serverName);
SPList list = site.AllWebs[siteName].Lists[listName];
SPView view = list.Views[viewName];

// Get a collection of the values in the specified List View
view.Update();
SPListItemCollection values = list.GetItems(new SPQuery(view));

// Create an object array to return to the grid
object[,] toExcelGrid = new object[values.Count, view.ViewFields.Count];        

…

In this example I am hard coding the server name, site name, and SharePoint List name. These could just as easily be passed in as arguments to the function, but this approach eases the usage for the end user. Also note that I use the MachineType parameter to determine the view that we will be getting list items from. We created the views when creating the SharePoint List in Part 3a, and the views only contain items for the type of machine specified by the MachineType parameter (laptops or desktops).

Now we just need to iterate through the list items, put them into the toExcelGrid object, and then return all that data to the Excel workbook:

…

// Iterate through each of the SPListItems in the view
int a = 0;
foreach (SPListItem currVal in values)
{

// Add every field value of current item to the array that will be returned
for (int i = 0; i < view.ViewFields.Count; i++)

{

// Get the current field
SPField field = currVal.Fields.GetFieldByInternalName(view.ViewFields[i]);

// Add the item to the array
toExcelGrid[a, i] = field.GetFieldValueAsText(currVal[field.Id]);

}

a++;

}

return toExcelGrid;

}

…

Finally, we need to add our catch event to return an error message if something goes wrong:

…

catch (System.Exception ex)
{

object[,] error = new object[1, 1];
error[0, 0] = ex.Message;
return error;

}

…

That's it!

For more details on UDFs, and a step by step walkthrough of how to create one, see the Excel Services User-Defined Functions article on MSDN.

Deploying your UDF

In order to ease management of your UDF files, start by creating a special folder for them on your server, something like C:\UDFs. Next, copy the Debug DLL that you just created into that folder. Now all we need to do is tell Excel Services about it and enable it.

To get to Excel Services' administration pages, click Start > Administrative Tools > SharePoint 3.0 Central Administration. Your default web browser will launch showing Central Admin. Click on the name of your shared service on the left (SharedServices1 by default), and then in the Excel Services Settings section, click on User-defined function assemblies.

This will take you into the UDF management page. Click Add User-Defined Function Assembly to add the one we just created. Enter the file local path to the UDF (C:\UDFs\GetMachines.dll), and pick File Path as the assembly location. Click Ok, reset your server (using iisreset), and your UDF is ready to go!

Testing your UDF

If you've created the SharePoint List and updated the static variables in the UDF correctly, then all you should need to do is upload the workbook we built in Part 2 and you should start see the values from the SharePoint List in your Excel workbook when it is rendered using Excel Services.