Share via

Developing Custom Applications by Using Excel Web Services in SharePoint Server 2007

  • Article

Summary:  Learn to create a custom application that uses the Microsoft Office SharePoint Server 2007 Excel Web Services.

Office Visual How To

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

Joel Krist, Akona Systems

October 2007

Overview

Excel Services is a new server technology included in Microsoft Office SharePoint Server 2007. This shared service enables the ability to load, calculate, and display Microsoft Office Excel 2007 workbooks on Office SharePoint Server 2007. Excel Services offers two primary interfaces:

  • An Excel Web Access Web Part, which enables a user to view and interact with a live workbook by using a browser

  • Excel Web Services for programmatic access

This Office Visual How To article shows how to create a custom application that uses Excel Web Services.

 See It Developing Custom Application with Excel Services

Watch the Video

Length: 06:29 | Size: 6.60 MB | Type: WMV file

Code It | Read It | Explore It

Code It

Download the Code Sample

The following sections show how to create a custom application that uses the SharePoint Server 2007 Excel Web Services. The three major steps in the process are:

  1. Creating a project in Microsoft Visual Studio 2005.

  2. Adding a Web reference to the SharePoint Server Excel Web Services.

  3. Adding code to the application that uses the Excel Web Services to retrieve and display the contents of a named range of cells.

Creating a Project in Visual Studio 2005

For simplicity and brevity, this article shows how to work with the Excel Web Services creating a Console Application project—the simplest way to show a complete program.

To create a Console Application project in Visual Studio 2005

  1. Start Visual Studio.

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

  3. In the New Project dialog box, in the Project Types pane, click Visual C# or Visual Basic, and then select the Windows category.

  4. In the Templates pane, click Console Application.

  5. For the Name of the project, type ExcelSvcsApp.

  6. Specify a Location for the project, and then click OK.

    Visual Studio generates a Console Application project a single source file named Program.cs or Module1.vb, depending on the language you selected in Step 3.

Adding a Web Reference to the Excel Web Services

Web service discovery is the process by which a client locates a Web service and obtains its service description. The process of Web service discovery in Visual Studio involves interrogating a Web site to locate the service description, which is an XML document that uses the Web Services Description Language (WSDL). When you add a Web reference to a project, Visual Studio generates a proxy class that provides a local representation of the Web service, which allows the client code to interface with the Web service. You can access Web service methods by calling the methods in the proxy class. The proxy class handles the communication between the client application and the Web service.

To add a Web reference to the Excel Web Services

  1. In Solution Explorer, right-click the ExcelSvcsApp project, and then click Add Web Reference.

  2. In the Add Web Reference dialog box, in the URL box, type the URL of the Excel Web Service. The following is the default Web service location:

    http://SiteURL/Subsite/_vti_bin/excelservice.asmx

  3. Click Go.

    Visual Studio retrieves and displays the information about the Web service.

  4. Specify ExcelWebService for the Web reference name, and then click Add Reference to add the Web reference to the project.

    Visual Studio downloads the service description and generates a proxy class to interface between the application and Excel Web Services.

    Figure 1. Add Web Reference dialog box

    Add Web Reference dialog box

Adding Code to Work with the Excel Web Services

After adding the reference to the Excel Web Services, you add code that works with it.

To add code to work with the Excel Web Services

  1. Add the following Imports or using statements to the top of the Program.cs or Module1.vb source file. If you are using the Program.cs file, add the using statement after the using statements that Visual Studio generated when you created the project.

    Imports ExcelSvcsApp.ExcelWebService
Imports System.Web.Services.Protocols

    using ExcelSvcsApp.ExcelWebService;
using System.Web.Services.Protocols;

    The Imports and using statements make it possible for you to use the classes and types defined in the referenced namespaces without requiring you to use fully qualified namespace paths.

  2. Replace the code that Visual Studio generated with the following code. If you are using the Module1.vb file, replace the Module1 module; if you are using the Program.cs file, replace the Program class definition.

    Module Module1
    ' Declare variables to hold the Web service URL, 
    ' target workbook, worksheet, and named range.
    Dim serviceURL As String = String.Empty
    Dim targetWorkbookPath As String = String.Empty
    Dim sheetName As String = String.Empty
    Dim rangeName As String = String.Empty

    Sub Main()
        ' Check command-line arguments.
        If Not CheckArgs() Then
            Return
        End If

        ' Create an instance of the Web service proxy class
        ' and set the URL to use.
        Dim service As ExcelService = New ExcelService()
        service.Url = serviceURL

        ' Declare a status array.
        Dim outStatus() As Status

        ' Declare a variable to hold the session ID.
        Dim sessionId As String = String.Empty

        ' Set the credentials for the request by specifying
        ' the network credentials of the current security context.
        service.Credentials = _
            System.Net.CredentialCache.DefaultCredentials

        ' An alternative approach is to create a NetworkCredential
        ' object to connect as a specific user.
        '
        ' service.Credentials = _
        '     New System.Net.NetworkCredential("User", _
        '         "Password", "Domain")

        Try
            ' Open the workbook, specifying string.Empty for the
            ' uiCultureName and dataCultureName parameters. Doing this
            ' tells the Web service to use the server's default
            ' settings for UI- and Data-Culture for the site and user.
            sessionId = service.OpenWorkbook(targetWorkbookPath, _
                String.Empty, String.Empty, outStatus)

            ' Request the range by name, specifying true for the
            ' formatted parameter to tell the Web service to return
            ' empty cells as the Empty string. Specfying false for this
            ' parameter would tell the Web service to return empty
            ' cells as Null.
            Dim rangeResult() As Object = _
                service.GetRangeA1(sessionId, sheetName, rangeName, _
                    True, outStatus)

            ' Display the values in the range.
            Console.WriteLine("The values in the " + rangeName + _
                " range are:" + vbCrLf)

            For Each row As Object In rangeResult
                For Each cell As Object In CType(row, Object())
                    Console.Write(cell + " ")
                Next cell

                Console.WriteLine()
            Next row

        Catch se As SoapException
            Console.WriteLine("SOAP Exception Message: {0}", _
                se.Message)
        Catch ex As Exception
            Console.WriteLine("Exception Message: {0}", ex.Message)
        Finally
            ' Close the workbook. This also closes the session,
            ' proactively releasing resources on the server.
            If Not sessionId = String.Empty Then
                service.CloseWorkbook(sessionId)
            End If
        End Try

        Console.Write(vbCrLf + "Press any key to continue...")
        Console.ReadLine()

    End Sub

#Region "Commandline Argument Processing"

    Private Function CheckArgs() As Boolean
        Dim bArgsOK As Boolean = True

        ' Check the number of command-line arguments.
        If (My.Application.CommandLineArgs.Count < 4) Then
            bArgsOK = False
        Else
            ' Parse the command-line arguments.
            For Each arg As String In My.Application.CommandLineArgs
                If (arg.ToLower().StartsWith("/service=")) Then
                    serviceURL = arg.Remove(0, 9)
                End If

                If (arg.ToLower().StartsWith("/book=")) Then
                    targetWorkbookPath = arg.Remove(0, 6)
                End If

                If (arg.ToLower().StartsWith("/sheet=")) Then
                    sheetName = arg.Remove(0, 7)
                End If

                If (arg.ToLower().StartsWith("/range=")) Then
                    rangeName = arg.Remove(0, 7)
                End If
            Next arg

            ' Ensure that the URL for the Web service was specified.
            If (serviceURL = String.Empty) Then
                Console.Error.WriteLine("Please specify the URL " + _
                    "to the Web service with the /service= switch." + _
                    vbCrLf)
                bArgsOK = False
            End If

            ' Ensure that a workbook was specified.
            If (targetWorkbookPath = String.Empty) Then
                Console.Error.WriteLine("Please specify a " + _
                    "workbook with the /book= switch." + vbCrLf)
                bArgsOK = False
            End If

            ' Ensure that a worksheet was specified.
            If (sheetName = String.Empty) Then
                Console.Error.WriteLine("Please specify a " + _
                    "worksheet with the /sheet= switch." + vbCrLf)
                bArgsOK = False
            End If

            ' Ensure that a named range was specified.
            If (rangeName = String.Empty) Then
                Console.Error.WriteLine("Please specify a named " + _
                    "range with the /range= switch." + vbCrLf)
                bArgsOK = False
            End If
        End If

        If Not bArgsOK Then
            ShowUsage()
        End If

        Return bArgsOK
    End Function

    Sub ShowUsage()
        Console.Error.WriteLine("Usage:" + vbCrLf)
        Console.Error.WriteLine("ExcelSvcsApp /service=ServiceURL " + _
            "/book=WorkbookPath /sheet=WorksheetName " + _
            "/range=RangeName" + vbCrLf)
        Console.Error.WriteLine("/service=  " + _
            "The URL of the Web service,")
        Console.Error.WriteLine("           " + _
            "e.g. /service=" + _
            "http://SiteURL/Subsite/_vti_bin/excelservice.asmx" + _
            vbCrLf)
        Console.Error.WriteLine("/book=     " + _
            "The path or URL to the target workbook,")
        Console.Error.WriteLine("           " + _
          "e.g. /book=https://Server/Documents/Workbook.xlsx" + vbCrLf)
        Console.Error.WriteLine("/sheet=    " + _
            "The name of the worksheet to use in the target " + _
            "workbook,")
        Console.Error.WriteLine("           " + _
            "e.g. /sheet=Sheet1" + vbCrLf)
        Console.Error.WriteLine("/range=    " + _
            "The name of the range in the worksheet to request " + _
            "data for,")
        Console.Error.WriteLine("           " + _
            "e.g. /range=MyRange" + vbCrLf)
        Console.Error.Write("Press any key to continue...")
        Console.ReadLine()
    End Sub
#End Region
End Module

    class Program
{
    // Declare variables to hold the Web service URL, 
    // target workbook, worksheet, and named range.
    static string serviceURL = string.Empty;
    static string targetWorkbookPath = string.Empty;
    static string sheetName = string.Empty;
    static string rangeName = string.Empty;

    static void Main(string[] args)
    {
        // Check command-line arguments.
        if (!CheckArgs(args))
            return;

        // Create an instance of the Web service proxy class
        // and set the URL to use.
        ExcelService service = new ExcelService();
        service.Url = serviceURL;

        // Declare a status array.
        Status[] outStatus;

        // Declare a variable to hold the session ID.
        string sessionId = string.Empty;

        // Set the credentials for the request by specifying
        // the network credentials of the current security context.
        service.Credentials =
            System.Net.CredentialCache.DefaultCredentials;

        // An alternative approach is to create a NetworkCredential
        // object to connect as a specific user.
        //
        // service.Credentials =
        //     New System.Net.NetworkCredential("User",
        //         "Password", "Domain");

        try
        {
            // Open the workbook, specifying string.Empty for the
            // uiCultureName and dataCultureName parameters. Doing this
            // tells the Web service to use the server's default
            // settings for UI- and Data-Culture for the site and user.
            sessionId = service.OpenWorkbook(targetWorkbookPath,
                string.Empty, string.Empty, out outStatus);

            // Request the range by name, specifying true for the
            // formatted parameter to tell the Web service to return
            // empty cells as the Empty string. Specfying false for
            // this parameter would tell the Web service to return 
            // empty cells as Null.
            object[] rangeResult = service.GetRangeA1(sessionId,
                sheetName, rangeName, true, out outStatus);

            // Display the values in the range.
            Console.WriteLine("The values in the " + rangeName +
                " range are:\r\n");

            foreach (object row in rangeResult)
            {
                foreach (object cell in (object[])row)
                    Console.Write(cell + " ");

                Console.WriteLine();
            }
        }
        catch (SoapException se)
        {
            Console.WriteLine("SOAP Exception Message: {0}", 
                se.Message);
        }
        catch (Exception ex)
        {
            Console.WriteLine("Exception Message: {0}", ex.Message);
        }
        finally
        {
            // Close the workbook. This also closes the session,
            // proactively releasing resources on the server.
            if (sessionId != String.Empty)
                service.CloseWorkbook(sessionId);
        }

        Console.Write("\r\nPress any key to continue...");
        Console.ReadLine();
    }

    #region Commandline Argument Processing

    private static bool CheckArgs(string[] args)
    {
        bool bArgsOK = true;

        // Check the number of command-line arguments.
        if (args.Length < 4)
        {
            bArgsOK = false;
        }
        else
        {
            // Parse the command-line arguments.
            foreach (string arg in args)
            {
                if (arg.ToLower().StartsWith("/service="))
                    serviceURL = arg.Remove(0, 9);

                if (arg.ToLower().StartsWith("/book="))
                    targetWorkbookPath = arg.Remove(0, 6);

                if (arg.ToLower().StartsWith("/sheet="))
                    sheetName = arg.Remove(0, 7);

                if (arg.ToLower().StartsWith("/range="))
                    rangeName = arg.Remove(0, 7);
            }

            // Ensure that the URL for the Web service was specified.
            if (serviceURL == string.Empty)
            {
                Console.Error.WriteLine("Please specify the URL to " +
                    "the Web service with the /service= switch.\r\n");
                bArgsOK = false;
            }

            // Ensure that a workbook was specified.
            if (targetWorkbookPath == string.Empty)
            {
                Console.Error.WriteLine("Please specify a workbook " +
                    "with the /book= switch.\r\n");
                bArgsOK = false;
            }

            // Ensure that a worksheet was specified.
            if (sheetName == string.Empty)
            {
                Console.Error.WriteLine("Please specify a worksheet " +
                    "with the /sheet= switch.\r\n");
                bArgsOK = false;
            }

            // Ensure that a named range was specified.
            if (rangeName == string.Empty)
            {
                Console.Error.WriteLine("Please specify a named " +
                    "range with the /range= switch.\r\n");
                bArgsOK = false;
            }
        }

        if (!bArgsOK)
            ShowUsage();

        return bArgsOK;
    }

    private static void ShowUsage()
    {
        Console.Error.WriteLine("Usage:\r\n");
        Console.Error.WriteLine("ExcelSvcsApp /service=ServiceURL " +
            "/book=WorkbookPath /sheet=WorksheetName " +
            "/range=RangeName\r\n");
        Console.Error.WriteLine("/service=  " +
            "The URL of the Web service,");
        Console.Error.WriteLine("           " +
            "e.g. /service=" +
        "http://SiteURL/Subsite/_vti_bin/excelservice.asmx\r\n");
        Console.Error.WriteLine("/book=     " +
            "The path or URL to the target workbook,");
        Console.Error.WriteLine("           " +
            "e.g. /book=https://Server/Documents/Workbook.xlsx\r\n");
        Console.Error.WriteLine("/sheet=    " +
            "The name of the worksheet to use in the target " +
            "workbook,");
        Console.Error.WriteLine("           " +
            "e.g. /sheet=Sheet1\r\n");
        Console.Error.WriteLine("/range=    " +
            "The name of the range in the worksheet to request " +
            "data for,");
        Console.Error.WriteLine("           " +
            "e.g. /range=MyRange\r\n");
        Console.Error.Write("Press any key to continue...");
        Console.ReadLine();
    }

    #endregion
}

  3. Build and run the ExcelSvcsApp application.

    The ExcelSvcsApp application accepts the required parameters described in Table 1.

    Table 1. Required parameters for the ExcelSvcsApp application

    Switch Meaning

    /service=

    URL of Excel Web Services.

    Example:

    /service=http://SiteURL/SubSite/_vti_bin/excelservice.asmx

    /book=

    Path or URL to the target workbook.

    Example:

    /book=http://Server/Documents/Workbook.xlsx

    /sheet=

    Name of worksheet to use in the target workbook.

    Example:

    /sheet=Sheet1

    /range=

    Name of range in worksheet to request data for.

    Example:

    /range=MyNamedRange

    Note

    The target workbook must reside in a trusted location or the OpenWorkbook call will fail. For more information about how to trust a location by using the SharePoint 3.0 Central Administration site, see How to: Trust a Location. You can determine the path to a workbook in Office SharePoint Server 2007 by right-clicking the workbook, and then clicking Copy Shortcut. Alternatively, you can determine the path by right-clicking the workbook, and then clicking Properties. The property page displays the address (URL) of the workbook.

Read It

This article demonstrates how to create an application that uses the SharePoint Server 2007 Excel Web Services. The steps are:

  1. Creating a project in Visual Studio 2005.

  2. Adding a Web reference to the Excel Web Services.

  3. Adding code to the application that uses the Excel Web Services to retrieve and display the contents of a named range of cells.

Explore It