Step 3: Accessing the Web Service
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.DefaultCredentialsAdd 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
See Also
Tasks
Step 1: Creating the Web Service Client Project
Step 2: Adding a Web Reference
Step 4: Building and Testing the Application
Walkthrough: Developing a Custom Application Using Excel Web Services