Step 3: Accessing the Web Service

Article
07/24/2014

Applies to: SharePoint Server 2010

After you have added a reference to Excel Web Services to your project, the next step is to create an instance of the Web service's proxy class. You can then access the methods of the Web service by calling the methods in the proxy class. When your application calls these methods, the proxy class code generated by Visual Studio handles the communications between your application and the Web service.

First you create an instance of the Web service's proxy class, ExcelWebService. Next you call several of the Web service's methods and properties using the proxy class.

You use the calls to open a workbook, get the session ID, pass in the default credentials, define the range coordinate object, get the range that uses the range coordinate object, close the workbook, and catch the SOAP exceptions.

Accessing the Web Service

To add directives

When you added the Web reference earlier, it created an object named ExcelService in a namespace called <yourProject>.<webReferenceName>. In this example, the object is named SampleApplication.ExcelWebService. This walkthrough also shows how to catch SOAP exceptions. To do so, you use the System.Web.Services.Protocols object. The System.Web.Services.Protocols namespace consists of the classes that define the protocols used to transmit data across the wire during the communication between XML Web service clients and XML Web services created using ASP.NET.
To facilitate using these objects, you must first add the namespaces as directives to the Class1.cs file. This way, if you use these directives, you do not need to fully qualify the types in the namespace.

To add these directives, add the following code to the beginning of your code in the Class1.cs file, after using System:

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

Imports SampleApplication.ExcelWebService
Imports System.Web.Services.Protocols

To call the Web service

Instantiate and initialize the Web service proxy object by adding the following code after the opening bracket in static void Main(string[] args):
```
ExcelService es = new ExcelService();
```
```
Dim es As New ExcelService()
```

Add the following code to create a status array and range coordinate objects:

Status[] outStatus;
RangeCoordinates rangeCoordinates = new RangeCoordinates();

Dim outStatus() As Status
Dim rangeCoordinates As New RangeCoordinates()

Add the code to point to the worksheet you want to access. In this example, the worksheet is called "Sheet1". Add the following to your code:
```
string sheetName = "Sheet1";
```
```
Dim sheetName As String = "Sheet1"
```
Note

Make sure the workbook you want to open has a worksheet called "Sheet1" that contains values. Alternatively, you can change "Sheet1" in the code to the name of your worksheet.
Add the following code to point to the workbook you want to open:
```
string targetWorkbookPath = "http://myserver02/example/Shared%20Documents/Book1.xlsx";
```
```
Dim targetWorkbookPath As String = "http://myserver02/example/Shared%20Documents/Book1.xlsx"
```
Important

Change the workbook path to match the location of the workbook you are using for this walkthrough. Make sure the workbook exists and that the location where the workbook is saved is a trusted location. Using an HTTP URL to point to the location of a workbook allows you to access it remotely.

Note

You can get the path to a workbook in Microsoft SharePoint Server 2010 by right-clicking the workbook and selecting Copy Shortcut. Alternatively, you can select Properties and copy the path to the workbook from there.
Add the following code to set the credentials for the request.

Note

You have to explicitly set the credentials even if you intend to use the default credentials.
```
es.Credentials = System.Net.CredentialCache.DefaultCredentials;
```
```
es.Credentials = System.Net.CredentialCache.DefaultCredentials
```

Add the following code to open the workbook and point to the trusted location where the workbook is located. Place the code in a try block:

try
{
string sessionId = es.OpenWorkbook(targetWorkbookPath, "en-US", 
    "en-US", out outStatus);

Try
Dim sessionId As String = es.OpenWorkbook(targetWorkbookPath, "en-US", "en-US", outStatus)

Add the following code to prepare an object to define range coordinates, and call the GetRange method. The code will also print the total number of rows in the range and the value in a particular range.

rangeCoordinates.Column = 3;
rangeCoordinates.Row = 9;
rangeCoordinates.Height = 18;
rangeCoordinates.Width = 12;

object[] rangeResult1 = es.GetRange(sessionId, sheetName,
    rangeCoordinates, false, out outStatus);
Console.WriteLine("Total rows in range: " + rangeResult1.Length);
Console.WriteLine("Value in range is: " + ((object[])rangeResult1[5])[2]);

rangeCoordinates.Column = 3
rangeCoordinates.Row = 9
rangeCoordinates.Height = 18
rangeCoordinates.Width = 12

Dim rangeResult1() As Object = es.GetRange(sessionId, sheetName, rangeCoordinates, False, outStatus)
Console.WriteLine("Total rows in range: " & rangeResult1.Length)
Console.WriteLine("Value in range is: " & (CType(rangeResult1(5), Object()))(2))

Add code to close the workbook and close the current session. Also add a close bracket to end the try block.

Important

It is good practice to close the workbook if you are done using the session. This will close the session and free resources.
```
es.CloseWorkbook(sessionId);
}
```
```
es.CloseWorkbook(sessionId)
```

Add a catch block to catch the SOAP exception and print the exception message:

catch (SoapException e)
{
    Console.WriteLine("SOAP Exception Message: {0}", e.Message);
}

Catch e As SoapException
    Console.WriteLine("SOAP Exception Message: {0}", e.Message)
End Try

Complete Code

The following code sample is the complete code in the Class1.cs example file described in the previous procedures.

Important

Make changes to the workbook path, sheet name, and so on, as appropriate.

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

namespace SampleApplication
{
    class Class1
    {
        [STAThread]
        static void Main(string[] args)
        {            
            // Instantiate the Web service and create a status array object and range coordinate object
            ExcelService es = new ExcelService();
            Status[] outStatus;
            RangeCoordinates rangeCoordinates = new RangeCoordinates();
            
string sheetName = "Sheet1";
            // Using workbookPath this way will allow 
            // you to call the workbook remotely.
            // TODO: change the workbook path to 
            // point to workbook in a trusted location
            // that you have access to 
            string targetWorkbookPath = "http://myserver02/example/Shared%20Documents/Book1.xlsx";
//you can also use .xlsb files, for example, //"http://myserver02/example/Shared%20Documents/Book1.xlsb";

            // Set credentials for requests
            es.Credentials = System.Net.CredentialCache.DefaultCredentials;
            //Console.WriteLine("Cred: {0}", es.Credentials);
            try
            {
                // Call open workbook, and point to the trusted   
                // location of the workbook to open.
                string sessionId = es.OpenWorkbook(targetWorkbookPath, "en-US", "en-US", out outStatus);
                // Console.WriteLine("sessionID : {0}", sessionId);

                // Prepare object to define range coordinates
                // and the GetRange method.
                rangeCoordinates.Column = 3;
                rangeCoordinates.Row = 9;
                rangeCoordinates.Height = 18;
                rangeCoordinates.Width = 12;

                object[] rangeResult1 = es.GetRange(sessionId, sheetName, rangeCoordinates, false, out outStatus);
                Console.WriteLine("Total Rows in Range: " + rangeResult1.Length);
                Console.WriteLine("Value in range is: " + ((object[])rangeResult1[5])[3]); 
        
                // Close workbook. This also closes session.
                es.CloseWorkbook(sessionId);
            }
            catch (SoapException e)
            {
                Console.WriteLine("SOAP Exception Message: {0}", e.Message);
            }
            // catch (Exception e)
//            {
//                Console.WriteLine("Exception Message: {0}", e.Message);
//            }
//            Console.ReadLine();
        }
    }
}

Imports System
Imports SampleApplication.ExcelWebService
Imports System.Web.Services.Protocols

Namespace SampleApplication
    Friend Class Class1
        <STAThread> _
        Shared Sub Main(ByVal args() As String)
            ' Instantiate the Web service and create a status array object and range coordinate object
            Dim es As New ExcelService()
            Dim outStatus() As Status
            Dim rangeCoordinates As New RangeCoordinates()

Dim sheetName As String = "Sheet1"
            ' Using workbookPath this way will allow 
            ' you to call the workbook remotely.
            ' TODO: change the workbook path to 
            ' point to workbook in a trusted location
            ' that you have access to 
            Dim targetWorkbookPath As String = "http://myserver02/example/Shared%20Documents/Book1.xlsx"
'you can also use .xlsb files, for example, //"http://myserver02/example/Shared%20Documents/Book1.xlsb";

            ' Set credentials for requests
            es.Credentials = System.Net.CredentialCache.DefaultCredentials
            'Console.WriteLine("Cred: {0}", es.Credentials);
            Try
                ' Call open workbook, and point to the trusted   
                ' location of the workbook to open.
                Dim sessionId As String = es.OpenWorkbook(targetWorkbookPath, "en-US", "en-US", outStatus)
                ' Console.WriteLine("sessionID : {0}", sessionId)

                ' Prepare object to define range coordinates
                ' and the GetRange method.
                rangeCoordinates.Column = 3
                rangeCoordinates.Row = 9
                rangeCoordinates.Height = 18
                rangeCoordinates.Width = 12

                Dim rangeResult1() As Object = es.GetRange(sessionId, sheetName, rangeCoordinates, False, outStatus)
                Console.WriteLine("Total Rows in Range: " & rangeResult1.Length)
                Console.WriteLine("Value in range is: " & (CType(rangeResult1(5), Object()))(3))

                ' Close workbook. This also closes session.
                es.CloseWorkbook(sessionId)
            Catch e As SoapException
                Console.WriteLine("SOAP Exception Message: {0}", e.Message)
            ' Catch e As Exception
            '    Console.WriteLine("Exception Message: {0}", e.Message)
            End Try
            'Console.ReadLine()
        End Sub
    End Class
End Namespace

Step 3: Accessing the Web Service

Accessing the Web Service

To add directives

To call the Web service

Complete Code

See Also

Tasks

Concepts

Other Resources

Additional resources