Extending the Excel Services Programmability Framework

Summary: Optimize version 1 of the Excel Services programmability framework to extend Microsoft Office Excel 2007 and Excel Services functionalities to work with Microsoft Office SharePoint Server 2007 lists, query tables on Excel Services, external workbook references, SQL write-back, and user-defined functions (UDFs). (19 printed pages)

Luis F. Bitencourt-Emilio, Microsoft Corporation

February 2007

Applies to: Microsoft Office SharePoint Server 2007, Microsoft Office Excel 2007

Download OfficeXLSrvExtendFramework.exe.

Contents

  • Overview of Functionality in Excel Services Version 1

  • Creating a User-Defined Functions Project

  • Consuming SharePoint Lists

  • Writing to SQL Databases

  • External Workbook References

  • Building and Deploying Your UDF Library

  • Conclusion

  • Additional Resources

Overview of Functionality in Excel Services Version 1

This first version of Excel Services, available in Microsoft Office SharePoint Server 2007, provides a simple yet-powerful programmability framework that you can customize to meet your business needs. However, because it is a first version, you might encounter certain limitations common to version 1 technologies and features. This article discusses how you can overcome these limitations, and how to also expand the following built-in functionality for your Excel Services solutions:

  • Consumption of SharePoint lists

  • Use of query tables on Excel Services

  • Creation of external workbook references

  • Exploitation of user-defined functions (UDFs) compatibility with the Microsoft Office Excel 2007 client application

In addition, the article shows how to use SQL write-back to further expand this built-in functionality.

System Requirements

To create and run the samples, you must have the following software installed on your computer:

  • Microsoft Visual Studio 2005

  • Excel 2007

  • Office SharePoint Server 2007

Creating a User-Defined Functions Project

To begin, you must create a project in Visual Studio to contain the code for the UDFs.

Note

For more information about the Excel Web Services API, see Excel Services User-Defined Functions.

To create a project for the UDF code

  1. Start Visual Studio 2005.

  2. On the File menu, point to New, and then click Project.

  3. In the Project Type pane of the New Project dialog box, select Windows in the Visual C# node.

  4. In the Templates pane, click Class Library.

  5. Rename your project from ClassLibrary1 to XlUnlimitedUDFs.

Setting Up Your Project

Before you start writing the code for a class library of UDFs, you must change your project setting in the following ways (this applies to any UDF library that you create):

  • Add the necessary references to create a class library of UDFs.

  • Remove unnecessary code from the project template.

  • Sign the class library assembly with a strong name.

Adding Necessary References to Create a Class Library of UDFs

A UDF class library that is used in Excel Services must reference Microsoft.Office.Excel.Server.Udf to declare the class library attributes. Therefore, you must add a reference to this library, as described in the following procedure.

You also need the following two namespaces so that the UDFs can be used in Excel 2007 as well as Excel Services: Microsoft.Win32, and System.Runtime.InteropServices.

Note

The previous and following procedure assume that you are building the class library on a computer where Office SharePoint Server 2007 is installed. You can also find the Microsoft.Office.Excel.Server.Udf.dll at drive:\Program Files\Common Files\Microsoft Shared\Web server extensions\12\ISAPI.

To add a reference to the project

  1. On the Project menu, click Add Reference.

    Note

    You can also open the Add Reference dialog box in Solution Explorer by right-clicking References, and then selecting Add Reference.

  2. In the Add Reference dialog box, on the .NET tab, select Excel Services UDF Framework.

  3. Click OK.

Removing Unnecessary Code from the Project Template

The Class1.cs file contains stub code that is created by default when you create the class library project.

To remove unnecessary code from the project template

  1. Rename the Class1.cs file to XlUnlimitedUDFs.cs.

  2. In the XlUnlimitedUDFs.cs class, replace all code that was inserted in the file by default with the following code.

    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Runtime.InteropServices;              // Client Compat.
    using Microsoft.Win32;                             // Client Compat.
    using Microsoft.Office.Excel.Server.Udf;
    
    namespace XlUnlimitedUDFs
    {
        [UdfClass]
        [Guid(XlUnlimitedUDFs.ClsId)]                  // Client Compat.
        [ProgId(XlUnlimitedUDFs.ProgId)]               // Client Compat.
        [ClassInterface(ClassInterfaceType.AutoDual)]  // Client Compat.
        [ComVisible(true)]                             // Client Compat.
        public class XlUnlimitedUDFs
        {
            // BEGIN CLIENT COMPAT. SECTION //
            const string ClsId = "FE6C8D4D-3600-499a-9FA5-F8E252328CDD"; 
            const string ProgId = "XlUnlimitedUDFs.XlUnlimitedUDFs";  
            [ComRegisterFunction]
            public static void RegisterFunction(Type type)
            {
            if (typeof(XlUnlimitedUDFs) != type)
               {
                   return;
               }
               RegistryKey key = Registry.ClassesRoot.CreateSubKey(
                                                     @"CLSID\{" +
                                                     ClsId +
                                                     @"}\Programmable");
               key.Close();
            }
            [ComUnregisterFunction]
            public static void UnregisterFunction(Type type)
            {
                if (typeof(XlUnlimitedUDFs) != type)
                {
                    return;
                }
                Registry.ClassesRoot.DeleteSubKey(@"CLSID\{" + 
                                                  ClsId +
                                                  @"}\Programmable");
            }
            // END CLIENT COMPAT. SECTION //
            // BEGIN UDF SECTION //
            // END UDF SECTION//
        }
    }
    

    Note

    The ClsId given in the preceding code sample is only an example. If needed, you can create a ClsId by selecting Create GUID from the Tools menu. The lines of code ending in // Client Compat and the section labeled CLIENT COMPAT. SECTION are optional; they are required only if you also want to run the code in Excel 2007.

Signing the Assembly with a Strong Name

It is good practice to sign your UDF assembly with a strong name. A strong name consists of the identity of the assembly, a public key, a digital signature, and so on. You can use the strong name tool (sn.exe) to manage keys, generate signatures, and verify signatures. This tool is installed with the Microsoft .NET Framework SDK when you install Visual Studio 2005. For more information, see Excel Services Best Practices.

To assign a strong name to your assembly

  1. In Solution Explorer, right-click the XlUnlimitedUDFs project, and then select Properties.

  2. On the Signing tab, select the Sign the assembly box.

In the Choose a strong name key file list, select <New...>.

  1. Enter keypair as the file name, and clear the Protect my key file with a password box.

  2. Save your project.

Consuming SharePoint Lists

Now that you have configured your project, you can write code to implement a UDF that can be called from an Excel 2007 workbook hosted on Excel Services. In this way, you can extend Excel Services functionality such as consuming SharePoint lists, performing SQL write-back, and making external workbook references.

A common requirement when you write a UDF that is called from an Excel 2007 workbook hosted on Excel Services is that it must impersonate the current user of the workbook that contains the UDF calls. An example where impersonation is required is if you write code to read from a SharePoint list. In this case, you must access this data by using the credentials of the user who will consume that data. The following section describes how to write code that impersonates an Excel Services user.

Impersonating Excel Services Users

You must perform two simple steps to impersonate the current Excel Services user, as described in the following procedure.

To impersonate an Excel Services user

  1. Mark the UDF method with the following attribute.

    [UdfMethod(ReturnsPersonalInformation = true)]
    

    Note

    For security purposes, Excel Services does not allow UDF methods that are not marked with this attribute to access the user's Identity object.

  2. At the top of your XlUnlimitedUDFs.cs file, add the following using directive.

    using System.Security.Principal;
    
  3. Implement a helper method to perform the impersonation.

    /// <summary>
    /// Returns the WindowsImpersonationContext of the current user.
    ///</summary>
    private WindowsImpersonationContext impersonateUser()
    {
        WindowsIdentity wi = null;
    
           try
        {
            // BEGIN CLIENT COMPAT. SECTION
            // Check if code is not running on the server. 
            // If true, then it is running through a COM interface 
            // on the client and should not be impersonated.
            if (System.Diagnostics.Process.GetCurrentProcess()
                .ProcessName == "EXCEL") 
            {
                // COM case (client-only), should not impersonate.
                return null;
            }
            // END CLIENT COMPAT. SECTION
    
            // Get identity of user who loaded the workbook.
            wi = (WindowsIdentity)
            System.Threading.Thread.CurrentPrincipal.Identity;
        }
    
        catch (System.Exception ex)
        {
            throw new InvalidOperationException(
                "An error has occurred.");
        }
        // Impersonate user and return the context.
        return wi.Impersonate();
    }
    
  4. Call the helper method in a using block to ensure proper disposal of the WindowsImpersonationContext object.

    [UdfMethod(ReturnsPersonalInformation = true)]
    public void sampleUDF()
    {
        // Begin user impersonation through helper function.
        using (WindowsImpersonationContext wiContext = 
                                              impersonateUser())
        {
            // Any code in this block impersonates the user.
        }
           // No longer impersonating the user.
    }
    

Writing a UDF to Consume SharePoint Lists

Excel Services does not currently support connections to SharePoint lists. However, it is still possible to get data from a SharePoint list into an Excel 2007 workbook through a UDF. The UDF uses the Windows SharePoint Services API to get the list data and return it as an object array to the workbook (enter the UDF call as an array formula in Excel 2007 by using CTRL+SHIFT+ENTER).

To get data from a SharePoint list into an Excel 2007 workbook through a UDF, you must first reference the Windows SharePoint Services API, as described in the following procedure.

To add a reference to the project

  1. On the Project menu, click Add Reference.

    Note

    You can also open the Add Reference dialog box in Solution Explorer by right-clicking References, and then selecting Add Reference.

  2. In the Add Reference dialog box, on the .NET tab, select Windows SharePoint Services.

  3. Click OK.

    Note

    The previous steps assume that you are building the class library on a computer where Office SharePoint Server 2007 is installed. You can also find the Microsoft.SharePoint.dll at drive:\Program Files\Common Files\Microsoft Shared\Web server extensions\12\ISAPI.

  4. At the top of your XlUnlimitedUDFs.cs file, add the following using directive.

    using Microsoft.SharePoint;
    

To write a UDF to consume SharePoint lists

  1. Add the following method to XlUnlimitedUDFs.cs.

    /// <summary>
    /// Using SharePoint API to read data from a 
    /// SharePoint list and return it as an object array.
    /// </summary>
    /// <param name="serverName">Server name.</param>
    /// <param name="siteName">SharePoint site name.</param>
    /// <param name="listName">SharePoint list name.</param>
    /// <param name="viewName">View name of the SharePoint list.</param>
    /// <param name="getTitles">Include column titles.</param>
    /// <returns>Two-dimensional object array of list values.</returns>
    [UdfMethod(IsVolatile = true, ReturnsPersonalInformation = true)]
    public object[,] getSharepointView(string serverName, 
                                       string siteName, 
                                       string listName, 
                                       string viewName, 
                                       bool getTitles)
    {
        object[,] toExcel = null;
        // Impersonate current user viewing Excel Web Access.
        using (WindowsImpersonationContext wiContext = impersonateUser())
        {
            SPSite site = null;
            try
            {
                // Get Sharepoint objects.
                site = new SPSite(serverName);
                SPList list = site.AllWebs[siteName].Lists[listName];
                SPView view = list.Views[viewName];
    
                // Grab collection of Sharepoint list values.
                SPListItemCollection values = 
                                        list.GetItems(new SPQuery(view));
    
                // Add a row for the titles if requested.
                int titleRow = getTitles? 1 : 0; 
                int totalRows = values.Count + titleRow;
                // Avoid two-dimensional arrays with only 
                // one-dimensional of data 
                // by adding an empty row if needed.
                if (totalRows == 1)
                    totalRows++;
                // Create an object array to return to Excel.
                toExcel = new object[totalRows, view.ViewFields.Count];
                // Add column titles to array if needed.
                if (getTitles)
                {
                    for (int i = 0; i < view.ViewFields.Count; i++)
                    {
                        toExcel[0, i] = list.Fields.
                          GetFieldByInternalName(view.ViewFields[i]).Title;
                    }
                }
                int j = titleRow;
                // Iterate through SPListItems in the view.
                foreach (SPListItem currVal in values)
                {
                    // Add every field value of current item to array.
                    for (int i = 0; i < view.ViewFields.Count; i++)
                    {
                        // Get current field.
                        SPField field = currVal.Fields.
                                GetFieldByInternalName(view.ViewFields[i]);
                        // Get field value as a text string.
                        toExcel[j, i] = field.GetFieldValueAsText(
                                                        currVal[field.Id]); 
                    }
                    j++;
                }
            }
            catch(System.Exception ex)
            {
                object[,] error = new object[1, 1];
                error[0,0] = "An error has occurred.";
                toExcel = error;
            }
            finally
            {
                // Dispose of SharePoint objects instead of 
                // relying on the common language runtime.
                if (site != null)
                    site.Dispose();
            }
        } // End impersonation.
        return toExcel;
    }
    

Viewing the Data in a Query Table on Excel Services

Query tables are very useful for displaying refreshable relational data that is returned when you make a query against a database connection in an Excel 2007 list. In this version, Excel Services does not support query tables, but you can gain much of the functionality supported by query tables through a simple workaround. In the earlier example (and in the sample workbook that is included in the sample code download that accompanies this article), when you enter the data as an array formula in Excel 2007, an array of data is returned that is structurally similar to a query table, except without the sorting and filtering functionality. It is simple to turn this array formula into a pseudo query table (see the sample workbook included in the download). The following procedure shows how to do this.

To emulate a query table in Excel Services

  1. Start with an array formula of UDF data, such as the previous SharePoint list UDF sample.

  2. Create a table on the same worksheet by doing the following:

    1. Select a range that has the same number of rows and one more column than the array formula.

    2. Press CTRL+T.

  3. Label the right column INDEX.

  4. Fill the right column with the row numbers occupied by the array formula in the correct order, that is, from the smallest row number to the largest.

    Note

    Because the array formula in the sample workbook is located in the range B7:D12, fill the INDEX column with the following values: 7, 8, 9, 10, 11, 12. (You might want to skip 7, as that row contains only the column titles in this case, which you might not want to include in the table.)

  5. Enter the following formula in the top left cell of the table, and paste it to the remaining cells.

    =IFERROR(INDIRECT("R" & Table1[[#This Row],[INDEX]] & "C" & COLUMN(<TopLeftMostCellofArrayFormula>), FALSE), "")
    

    Note

    Replace <TopLeftMostCellofArrayFormula> in the preceding formula with the top left cell of your array formula; in the sample workbook, this is B7. (You might want to use B8 instead because the top row of the array formula contains the column titles in this case, which you might not want to include in the table.)

The formula entered in Step 5 performs the following functions:

  • Creates an R1C1 text reference to each cell in the array formula.

  • Indirectly accesses the data in those cells by the text reference that was created previously.

  • Wraps the data with an IFERROR function to convert #N/A seen in empty array formula cells to empty strings.

Writing to SQL Databases

A common request that is currently handled through custom Microsoft Visual Basic code in Excel 2007 is the ability to write to SQL databases, as well as to read from them. This ability remains just as useful on the server, if not more so. You might, for example, want to enable a scenario where different Excel Services sessions can share and manipulate the same data. Because each session has its own version of the workbook, it is not possible to simply edit a cell and have it be reflected in other sessions through shared workbooks in Excel Services. To enable this scenario, you can keep the data in a SQL database, and read/write to it through UDFs. This enables you to have data shared between the multiple sessions.

Creating a UDF to Write to SQL Databases

The code to create a UDF to write to a SQL database is not complex, and takes advantage of the built-in SQL capabilities in the .NET Framework. The following procedure shows how to create this UDF.

To create a UDF to write to a SQL database

  1. At the top of your XlUnlimitedUDFs.cs file, add the following using directive.

    using System.Data.SqlClient;
    
  2. Add the following method to your UDF class.

    Warning

    We provide the following code only as an example. Take extreme care whenever you are writing code against SQL databases because of the possibility of SQL injection attacks. When you can, restrict the following code to accept only the list of values required in your solution, as opposed to the comma-delimited strings used here. For more information, see SQL Injection. Although the following code adheres to those recommendations, it is more flexible than is required in most cases. You can help to secure it further by removing some of that flexibility.

    /// <summary>
    /// Write data to a SQL database.
    /// </summary>
    /// <param name="serverName">SQL Server name.</param>
    /// <param name="databaseName">Database name.</param>
    /// <param name="tableName">Table name.</param>
    /// <param name="columnNames">Column names (comma separated).</param>
    /// <param name="values">Values (comma separated).</param>
    /// <returns>Status string.</returns>
    [UdfMethod(IsVolatile = true, ReturnsPersonalInformation = true)]
    public string writeToSql(string serverName,
                             string databaseName,
                             string tableName,
                             string columnNames,  // Comma delimited.
                             string values)       // Comma delimited.
    {
        String[] restricted = { ";", "--", "/*", "*/", "xp_" };
        String[] checkRestriction = { tableName, columnNames, values };
        string returnVal;
    
        // Be restrictive about accepted inputs.
        foreach (String currCheck in checkRestriction)
        {    
            foreach (String currRestricted in restricted)
            {
                if (currCheck.Contains(currRestricted))
                {
                    return "An error has occurred";
                }
            }
        }
    
        // Escape quotes.
        tableName = tableName.Replace("\'", "\'\'");   
        columnNames = columnNames.Replace("\'", "\'\'"); 
        columnNames = columnNames.Replace("\"", "\"\"");
        values = values.Replace("\'", "\'\'");   
        // Impersonate current user. 
        // (see earlier section on user impersonation)
        using (WindowsImpersonationContext wiContext = impersonateUser())
        {
            SqlConnection connection = null;
            SqlCommand command = null;
    
            try
            {
                // Build connection string.
                string connectionString =
                            "Integrated Security=SSPI;" +
                            "Persist Security Info=True;Initial Catalog=" +
                            databaseName +
                            ";Data Source=" +
                            serverName +
                            ";";
                // Connect to SQL database.
                connection = new SqlConnection(connectionString);
                connection.Open();
                // Use sp_executesql to avoid SQL injection attacks.
                command = new SqlCommand("sp_executesql", connection);
                 command.CommandType =
                            System.Data.CommandType.StoredProcedure;
                // Comma-separated string of values from parameter.
                string rowValues =  "\'" +
                                    values.Replace(",", "\',\'") +
                                    "\'";
                // Comma-separated string of values from parameter.
                string columnValues =   "\"" +
                                        columnNames.Replace(",", "\",\"") +
                                        "\"";
                // Prepare statement to insert row in SQL database.
                string parameter = "insert into " +
                                    tableName +
                                    " (" +
                                    columnValues +
                                    ")" +
                                    " values (" +
                                    rowValues +
                                    ")";
                // Execute command.
                command.Parameters.AddWithValue("@statement", parameter);
                command.ExecuteNonQuery();
                // Close connection.
                connection.Close();
                returnValue = "Success!"
            }
            catch (System.Exception ex)
            {
                returnValue = "An error has occurred.";
            }
            finally
            {
                // Dispose of SQL objects instead of 
                // relying on the common language runtime.
                if (connection != null)
                    connection.Dispose();
                if (command != null)
                    command.Dispose();
            }
        } // End impersonation.
        return returnValue;
    }
    

External Workbook References

Excel Services does not support loading workbooks that reference external workbook ranges. You can overcome this limitation by using the Excel Web Services API to get the value of a particular cell of an external workbook instead of referencing that workbook directly.

To accomplish this, you must reference the Excel Web Services API, and write a UDF to call to it to retrieve the cell values from an external workbook.

Note

For more information about the Excel Web Services API, see Excel Web Services.

Referencing the Excel Web Services API

To add a reference to Excel Web Services, you must install Office SharePoint Server 2007 on the server you plan to use. Then, add a reference to the Excel Web Services API at http://server/_vti_bin/excelservice.asmx?WSDL, where server is the name of your server.

To add a Web reference to Excel Web Services

  1. On the Project menu, click Add Web Reference.

    Note

    You can also open the Add Web Reference dialog box in Solution Explorer by right-clicking References and selecting Add Web Reference.

  2. In the Add Web Reference dialog box, in the URL field, enter http://server/_vti_bin/excelservice.asmx?WSDL.

    Note

    Replace server with the name of your server.

  3. Click GO.

  4. Change the Web Reference name to ExcelServices.

  5. Click Add Reference.

Writing a UDF to Reference External Workbooks

The following code demonstrates how you can use a UDF to call the Excel Web Services API to open an external workbook stored in a trusted location, and to return the referenced value.

/// <summary>
/// Using the Excel Services user-defined functions API
/// to read data from an external workbook 
/// and return it as an object.
/// </summary>
/// <param name="workbookPath">Path to the workbook.</param>
/// <param name="sheetName">Sheet name.</param>
/// <param name="range">Sheet range.</param>
/// <returns>Object containing cell contents.</returns>
[UdfMethod(IsVolatile = true, ReturnsPersonalInformation = true)]
public object externalRef(string workbookPath, 
                          string sheetName, 
                          string range)
{
    ExcelServices.Status[] status = null;
    string sessionId = null;
    object cellValue = null;
    // Initialize Excel Web Services.
    ExcelServices.ExcelService es = new 
                                   ExcelServices.ExcelService();
    es.UseDefaultCredentials = true;

       // Impersonate the current user viewing Excel Web Access.
       using (WindowsImpersonationContext wiContext = 
                                                 impersonateUser())
       {
        // Open the workbook – this loads the workbook 
        // that was saved to the SharePoint document library
        // and returns a sessionId to use in API calls.
        try 
        {
            sessionId = es.OpenWorkbook(workbookPath, 
                                        "en-US", 
                                        "en-US", 
                                        out status);
        
            // Retrieve the data from the referenced cell.
            cellValue = es.GetCellA1(sessionId, 
                                     sheetName, 
                                     range, 
                                     true, 
                                     out status);
        }
        catch (System.Exception ex)
        {
            return "An error has occurred.";
        }
    }
    return cellValue;
}

Building and Deploying Your UDF Library

You can find a sample workbook that calls the UDFs that were created previously, as well as the complete XlUnlimitedUDFs Visual Studio project file, in the sample download. For information about how to build and deploy the UDF assemblies to Excel Services, see Excel Services User-Defined Functions.

Note

If you use the sample XlUnlimitedUDFs Visual Studio project, you must replace all references to http://<server> with the name of your server. The project also includes a Web reference. You must update the Web reference with the correct reference to your server.

Deploying a UDF Assembly on the Excel Client

It is extremely useful to be able to use your UDFs on the Excel 2007 client as well as on the server running Excel Services. Not only do you gain the functionality exposed by your UDFs on the Excel client, but it can also help you when you create your workbooks that use UDFs because you can see real data instead of the #NAME? error values usually seen when calling unknown formulas.

The code used in this article has comments such as // Client Compat and CLIENT COMPAT. SECTION. These comments point out the lines of code that are required to enable you to port your UDF code to work on the client. If you have copied the code verbatim, or used the sample XlUnlimitedUDFs Visual Studio project, you have to implement only a few remaining steps before you can run your UDFs on

Note

For more information about using Excel Services UDFs on Excel client, see Making Excel Services UDFs work on Excel 2007 - Part 1 and Making Excel Services UDFs work on Excel 2007 - Part 2.

To run UDF code on the Excel client

  1. Start with the code in this article, and make sure to include all sections marked CLIENT COMPAT.

  2. In Solution Explorer, right-click XlUnlimitedUDFs project, and then select Properties.

  3. On the Build tab, select the Register for COM interop box.

    Note

    Alternatively, if you are deploying to a computer different from your development computer, you can transfer the built XlUnlimitedUDFs.dll file and register it using the RegAsm tool included with the .NET Framework.

  4. Save your project, and then build your project.

  5. Open Excel 2007. From the Office Button, select Excel Options.

  6. On the Add-Ins tab, click Go, and then click Automation.

  7. Find and select XlUnlimitedUDFs.XlUnlimitedUDFs as the automation server, and then click OK.

  8. If you receive an alert mentioning mscoree.dll, select No, and then click OK in the Add-Ins dialog box.

    Note

    It is recommended that you use a shim with your managed-code add-in. For more information, see Isolating Microsoft Office Extensions with the COM Shim Wizard Version 2.0.

  9. Open a workbook containing your UDF calls, or create a new one.

Conclusion

In this article, you learn how to use the programmability framework in Excel Services to overcome version 1 limitations and to expand what Excel Services can do by default. You can also use the same framework to enable creation of custom solutions tailored to solve your specific business needs. The powerful Excel Services framework and API enables you to easily extend Excel Services beyond the workbook publishing scenario and into a platform for solutions.

About the Author

Bitencourt-Emilio is a software design engineer on the test team that works on Excel Services, and is the owner of the LuisBE on Services blog.

Additional Resources

For more information, see the following resources: