Chapter 14: Building Custom Solutions (Part 1 of 2)

  • Article

This article is an excerpt from Beginning Excel Services, by Liviu Asnash, Eran Megiddo, and Craig Thomas, from Wrox (ISBN 978-0-470-10489-7, copyright Wrox 2007, all rights reserved). No part of these chapters may be reproduced, stored in a retrieval system, or transmitted in any form or by any means—electronic, electrostatic, mechanical, photocopying, recording, or otherwise—without the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews.

Next Part: Chapter 14: Building Custom Solutions (Part 2 of 2)

Excel Services is a strong product out-of-the-box, but you can always customize it to better suit the unique needs of your enterprise. Multiple programmability surface areas exist that you can leverage individually or together to extend the capabilities of Excel Services. This chapter introduces concepts geared toward extending the feature set through custom solutions.

In this chapter, you do the following:

  • Become familiar with the Excel Services Web service methods

  • Learn how to create an application that makes Excel Services Web service calls

  • Learn how to author and deploy an Excel Services user-defined function (UDF)

  • Become familiar with calling a UDF from an Excel workbook

  • Learn about customizing xlviewer.aspx

  • Discover approaches for interacting with the EWA through JavaScript and managed code

Contents

  • Excel Services Web Service

  • Creating a Custom API Application

  • Web Service API Methods

  • API Sample

Excel Services Web Service

An important feature of Excel Services is its Web service, also referred to as the API. This is a powerful programmability feature that enables solution developers to harness the power of Excel Services. One benefit of the Web service is that developers are not tied to a specific technology when building solutions that interact with the API. The API exposes a wide range of methods that you can use to manage workbooks through Excel Services.

This chapter covers some of the terminology and good-to-know information about the API. With that information, you can then move toward creating a managed-code solution to make API calls. The chapter focuses on each of the public API methods by explaining their signatures, as well as by providing a code snippet on making calls with the methods. At the conclusion of the chapter, a comprehensive API solution is shown that ties together the API concepts.

First-Use Information

The Excel Services Web service is fairly straightforward and easy to use. Understanding some of the Excel Services concepts can help you get more out of the API methods. This section draws attention to parameters and concepts that are standard across the majority of the API methods.

Session ID

Each time Excel Services opens a workbook, a new session is created. Each created session has an associated session ID that is unique. The session ID becomes a key for the Excel Services Web service methods to perform operations on the caller’s session, and maintain state for the life of the session. To perform any operation on the workbook using an API method, a valid session ID is required, which implies that the session is still active.

A session ends when the method is called. A session can also time out after a period of inactivity, or be forced to time out prematurely under some Excel Calculation Server (ECS) error-handling conditions. Use the Excel Services administration settings to configure the session timeout associated with inactivity.

A session ID is a string that is similar to the following, which is a composite ID built with components such as the data culture and UI culture from the OpenWorkbook method call, time zone details, and an internal ECS session ID:

"64.21702262-ee93-48c6-9975-dcce27465ac940AMLTI2IPpxVF/+dMuBeZOwYeQ= 
118.22.49JgQS2MbkuI3p3gVmbSfB90.5.en-US5.en-US73.+0480#0000-11-00-
01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-0060"

Cultures

The data culture is equivalent to the configurable SharePoint site locale ID. The data culture specifies the language culture to be used when formatting numbers, currencies, and dates.

The UI culture is equivalent to the SharePoint site language ID. The UI culture specifies the language culture to be used for menus, drop-down lists, toolbars, and error messages. An Excel Services Web service method uses the UI culture for error messages being returned to the method caller.

Both the data culture and the UI culture are set on each session created by the method, and those culture settings are used for the life of the session. Excel Services supports the same set of languages that is supported by Microsoft .NET Framework version 2.0. The .NET Framework package includes the System.Globalization namespace, which contains the CultureInfo class that can provide the supported cultures. Neutral culture languages are not supported by Excel Services for data cultures. A neutral culture is one that is expressed in the form of en instead of en-us, for example. A neutral culture is handled by Excel Services as an unsupported data culture.

Excel Services defaults to a fallback culture for both the data culture and the UI culture when an unsupported or uninstalled culture is used by an API method call. If the fallback culture logic is invoked by Excel Services, the API method's out Status parameter provides the information about the culture fallback.

Status Class

Each of the Excel Services Web service methods has an out parameter or a return value of type Status. The status object is used to return alerts with a next component state of Continue and for status messages. The default Status return value is null (not a zero-length array), which occurs when there are no alerts or status. The API returns an array of status structures when there are one or more alert or status messages. The items in the array are in the order they were received from the ECS. The status array is returned along with the normal return value of the method.

Following is an example of when a status is returned. In this example, an unsupported culture is used for the OpenWorkbook method's uiCulture parameter. The status structure includes three properties: Message, Name, and Severity.

try
{
   xlSessionId = xlEcsApi.OpenWorkbook(
      @"\\TheExcelServer\files\workbook.xlsb",
      @"aa-ZZ",
      @"en-US",
      out xlStatus);
   Console.WriteLine("Message: {0}", xlStatus[0].Message);
   Console.WriteLine("Name: {0}", xlStatus[0].Name);
   Console.WriteLine("Severity: {0}", xlStatus[0].Severity);
}

This code writes the following information to the console, which shows the properties of the Status that was returned for the OpenWorkbook method call. In this case, Excel Services is an English installation (operating system and MOSS).

Message: UI culture  (aa-zz) is not supported. Excel Services is using English 
(U.S.) (en-US) instead.
Name: UnsupportedUICulture
Severity: Error

SOAP Fault Message

The ECS and WFE Excel Services components can return a second type of alert referred to as a Stop alert. This type of alert is returned when the called method must be stopped and is not allowed to finish. The out parameters of the method and the normal return value of the method are not output when a Stop alert is returned.

Unexpected exceptions can also be returned. These exceptions and the Stop alerts are returned to the caller as a SOAP Fault message. The following code is an example of a try-catch statement used to catch a SOAP Fault for an Excel Services Web service call:

try
{
   //Excel Services Web Service method called from here
}
catch (System.Web.Services.Protocols.SoapException soapE)
{
   //code to handle the SOAP exception goes here
}

A1 Notation

Many of the Excel Services Web service methods have a parameter that references one or more cells. The use of R1C1 notation to reference a cell is not supported. You can reference cells with A1 notation, object names, or a defined name.

The Excel A1 notation uses column letters and row numbers to refer to a cell. The syntax of the A1 notation can vary slightly to allow for references to cells, columns, or even rows. The following table lists some examples of supported cell references.

A1 Notation

Description

"A1"

Cell A1

"A1:B5"

Cells A1 through B5

"A:A"

Column A

"1:1"

Row 1

"A:C"

Columns A through C

"1:5"

Rows 1 through 5

Creating a Custom API Application

This section focuses on creating a managed code application that accesses the Excel Services Web service. Creating the application has the following prerequisites:

Microsoft Office SharePoint Server 2007 must be available.

  • Excel Services must be set up and ready to load workbooks.

  • An Excel workbook must be published to a trusted location where at least view permissions are set.

  • Microsoft Visual C# and Microsoft Visual Studio 2005 must be available (to reproduce the steps noted here).

Creating the C# Console Application Project

Follow these steps to launch Visual Studio and create the C# console application:

  1. Start Visual Studio.

  2. Select File, then New, then click Project to open the New Project dialog box.

  3. In the Project Types frame, expand the Visual C# node and select Windows. In the Templates frame, select Console Application.

  4. In the Name field, enter xlWebService.

  5. Set the Location field to the location where you want to create the solution.

  6. Select OK to create the application. The New Project dialog box closes and Visual Studio opens the xlWebService solution with Program.cs opened and visible. The namespace is xlWebService.

Adding the API Web Reference

Follow these steps to add the API Web reference.

  1. Select Project, then click Add Web Reference to open the Add Web Reference dialog box.

  2. In the URL field, enter the URL to obtain the service description of the API. The URL should be in the form of http://<wfe_server>/_vti_bin/ExcelService.asmx or http://<wfe_server>/<site>/_vti_bin/ExcelService.asmx. This URL must support at least View permissions.

  3. Click Go to retrieve the available Web service. The service appears in the list as ExcelService.

  4. In the Web Reference Name field, enter ExcelWebService.

  5. Click the Add Reference button to add the ExcelService Web service as a Web reference to the solution.

Calling the API

Follow these steps to call the API:

  1. In Program.cs, add a directive for the ExcelService Web reference namespace. At the beginning of Program.cs, add the following using statement:

    using xlWebService.ExcelWebService;

  2. Add a second directive to Program.cs for handling SOAP faults:

    using System.Web.Services.Protocols;

  3. In the Main(string[] args) method, instantiate, and initialize the API with the following statement:

    ExcelService xlEcsApi = new ExcelService();

  4. Credentials must be explicitly set. Add the following statement to use default credentials:

    xlEcsApi.Credentials = System.Net.CredentialCache.DefaultCredentials;

  5. Create a status array and a string for the session ID:

    Status[] xlStatus;
string xlSessionId = "";

  6. Create a string and initialize it with the workbook to be opened by the API:

    string xlWorkbookPath = 
@"http://TheExcelServer/xlSite/Documents/workbook.xlsb";

  7. Create and initialize additional strings to capture the UI culture and the data culture:

    string uiCultureName = "en-US";
string dataCultureName = "en-US";

  8. Add the API OpenWorkbook method call to have the ECS open the workbook and return a sessionId:

    try
{
   xlSessionId = xlEcsApi.OpenWorkbook(
      xlWorkbookPath,
      uiCultureName,
      dataCultureName,
      out xlStatus);

  9. Add the API CloseWorkbook method call to close the workbook and end the session:

    xlStatus = xlEcsApi.CloseWorkbook(xlSessionId);

  10. Complete the try block by adding the following catch block for SOAP exceptions:

    }
catch (SoapException soapE)
{
   Console.WriteLine("Soap Fault: {0}", soapE.Message);
}

Following is the complete solution. This sample represents a starting point for trying the API. In the spirit of reducing the size of the sample, many good coding practices have been overlooked. Return values are not validated, error handlers are not present, and a number of values are hard-coded, to name a few of the shortcuts used here.

using System;
using System.Collections.Generic;
using System.Text;
using xlWebService.ExcelWebService;
using System.Web.Services.Protocols;
namespace xlWebService
{
   class Program
   {
      static void Main(string[] args)
      {
         ExcelService xlEcsApi = new ExcelService();
         xlEcsApi.Credentials = System.Net.CredentialCache.DefaultCredentials;
         Status[] xlStatus;
         string xlSessionId = "";
         string xlWorkbookPath = @"http://theexcelserver/testsite/shared 
documents/book1.xlsx";
         string uiCultureName = "en-US";
         string dataCultureName = "en-US";
         try
         {
            xlSessionId = xlEcsApi.OpenWorkbook(
               xlWorkbookPath,
               uiCultureName,
               dataCultureName,
               out xlStatus);
            xlStatus = xlEcsApi.CloseWorkbook(xlSessionId);
         }
         catch (SoapException soapE)
         {
            Console.WriteLine("Soap Fault {0}", soapE.Message);
         }
      }
   }
}

Local Linking

A second approach is to link the code directly to the Web service assembly and call methods in-process instead of using SOAP. For this approach to work, the Web service calling code must have Windows SharePoint Services (WSS) site context, which means the code runs within WSS.

The Excel Web service assembly is located on MOSS servers at C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\ISAPI\Microsoft.Office.Excel.Server.WebServices.dll. When you need to leverage local linking, simply add the project reference from the noted location. In Microsoft Visual Studio 2005, the assembly is available as a reference on the .NET tab of the Add Reference dialog box. If you want to use local linking, your project development environment must be on a MOSS server.

Visual Studio shows different signatures for Excel Web service methods depending on whether the assembly was added as a Web reference or as a reference (local linking). Any method with a void return and an out parameter has a different signature. When you use the Web reference, the out parameter becomes the return. The API method explanations in this chapter present signatures as Web references.

An example solution that uses local linking for accessing the API is shown in the "Managed Code Approach" section, later in this chapter.

Web Service API Methods

This section begins the coverage for each of the Web service methods. For each method, a code sample is shown that focuses strictly on the method being discussed. The code isn’t complete, but you can copy it into the previous API solution and execute it. The in, out, and return parameters are explained, as well as any intricacies for the methods.

GetApiVersion

The GetApiVersion method of the Excel Services Web service returns the version information for the Excel Services Web service. The declaration for the GetApiVersion method looks like this:

public string GetApiVersion(out Status[] status)

Parameter

The parameter for this method is Status (out). See the "Status Class" section, earlier in this chapter, for an explanation of the out parameter.

Return Value

A string is returned that is the version information for the Excel Services Web service.

Calling GetApiVersion

The return value of the following GetApiVersion method call returns the string "Excel web services (12.0)":

try
{
   string apiVersion;
   Status[] xlStatus;
   apiVersion = xlEcsApi.GetApiVersion(out xlStatus);
}

OpenWorkbook

The OpenWorkbook method of the Excel Services Web service loads an Excel workbook in a new session by the ECS. The declaration for the OpenWorkbook method looks like this:

public string OpenWorkbook(string workbookPath, string uiCultureName, string 
dataCultureName, out Status[] status)

Parameters

Following are the parameters for this method:

  • workbookPath (in)—A string that includes the path and name of the workbook to be opened. The path must be in a UNC, HTTP, or SharePoint trusted file location. The workbook must be in an Excel 12 supported file format.

  • uiCultureName (in)—A string that is used for the UI culture. See the "Cultures" section, earlier in this chapter, for an explanation of the (in) parameter. You can use the String.Empty, null or "" instead of an actual culture string.

  • dataCultureName (in)—A string that is used for the data culture. See the "Cultures" section, earlier in this chapter, for an explanation of the (in) parameter. You can use the String.Empty, null or "" instead of an actual culture string.

  • Status (out)—See the "Status Class" section, earlier in this chapter, for an explanation of this (out) parameter.

Return Value

A string is returned that is the sessionId created by the ECS for this OpenWorkbook call. The sessionId is a parameter for other Excel Services Web service methods that may get called for this session.

Calling OpenWorkbook

The following OpenWorkbook method call is made to open a workbook from a SharePoint trusted file location using English data and UI cultures. The xlSessionId variable holds the session ID for the session created by the OpenWorkbook call.

ExcelService xlEcsApi = new ExcelService();
Status[] xlStatus;
string xlSessionId;
xlEcsApi.Url = "http://" + "TheExcelServer" + "/_vti_bin/ExcelService.asmx";
xlEcsApi.Credentials = System.Net.CredentialCache.DefaultCredentials;
try
{
   xlSessionId = xlEcsApi.OpenWorkbook(
      @"http://TheExcelServer/testsite/Shared Documents/workbook.xlsb",
      @"en-US",
      @"en-US",
      out xlStatus);
}

GetSessionInformation

The GetSessionInformation method of the Excel Services Web service returns information about the ECS session. The declaration for the GetSessionInformation method looks like this:

public string GetSessionInformation(string sessionId, out string uiCultureNameUsed, 
out string dataCultureNameUsed, out Status[] status)

Parameters

Following are the parameters for this method:

  • sessionId (in)—The sessionId string returned from a prior OpenWorkbook call. The session must still be active at the time of the GetSessionInformation call. Making a GetSessionInformation call using a sessionId that has timed out results in a SOAP exception.

  • uiCultureNameUsed (out)—A string that represents the uiCulture that was set on this session when the session was created using the OpenWorkbook method. See the "Cultures" section, earlier in this chapter, for an explanation of this (out) parameter.

  • dataCultureNameUsed (out)—A string that represents the dataCulture that was set on this session when the session was created using the OpenWorkbook method. See the "Cultures" section, earlier in this chapter, for an explanation of this (out) parameter.

  • Status (out)—See the "Status Class," section, earlier in this chapter, for an explanation of this (out) parameter.

Return Value

A string is returned that contains the version information for ECS. The return value of the GetSessionInformation method call shown next returns the string "Excel Calculation Services (12.0)".

Calling GetSessionInformation

The following example makes an OpenWorkbook call and then the GetSessionInformation method is called. The uiCulture and the dataCulture used for the OpenWorkbook call were en-US and de-DE, respectively.

try
{
   string xlSessionId, uiCultureUsed, dataCultureUsed, sessionInfo;
   Status[] xlStatus;
   xlSessionId = xlEcsApi.OpenWorkbook(
      @"http://theexcelserver/testsite/Shared Documents/workbook.xlsb",
      @"en-US",
      @"de-DE",
      out xlStatus);
   sessionInfo = xlEcsApi.GetSessionInformation(
   xlSessionId,
   out uiCultureUsed,
   out dataCultureUsed,
   out xlStatus);
   Console.WriteLine("Session Information: {0}", sessionInfo);
   Console.WriteLine("UI Culture Used: {0}", uiCultureUsed);
   Console.WriteLine("Data Culture Used: {0}", dataCultureUsed);
}

This code writes the following information to the console, which shows the uiCulture and the dataCulture properties that were set on the session:

Session Information: Excel Calculation Services (12.0)
UI Culture Used: en-US
Data Culture Used: de-DE

CloseWorkbook

The CloseWorkbook method of the Excel Services Web service closes the session. Closing the session allows any cached data associated with the session to be released. Every OpenWorkbook call should have a corresponding CloseWorkbook call. The declaration for the CloseWorkbook method looks like this:

public Status[] CloseWorkbook(string sessionId)

Parameter

The parameter for this method is sessionId (in). This is the sessionId string that was returned from a prior OpenWorkbook call. The session must still be active at the time of the CloseWorkbook call. Making a CloseWorkbook call using a sessionId that has timed out results in a SOAP exception.

Return Value

A status object is returned by default with a value of null. See the "Status Class" section, earlier in this chapter, for an explanation of this return type.

Calling CloseWorkbook

The following code makes an OpenWorkbook call and assigns the returned sessionId. The sessionId is a parameter when CloseWorkbook is called.

try
{
   string xlSessionId;
   Status[] xlStatus;
   xlSessionId = xlEcsApi.OpenWorkbook(
      @"http://theexcelserver/testsite/Shared Documents/workbook.xlsb",
      @"en-US",
      @"de-DE",
      out xlStatus);
   xlStatus = xlEcsApi.CloseWorkbook(xlSessionId);
}

CancelRequest

The CancelRequest method of the Excel Services Web service attempts to cancel any active request that is using the session. The CancelRequest method call is considered successful if there is no active request to cancel. The declaration for the CancelRequest method looks like this:

public Status[] CancelRequest(string sessionId)

Parameter

The parameter for this method is sessionId (in). This is the sessionId string that was returned from a prior OpenWorkbook call. The session must still be active at the time of the CancelRequest call. Making a CancelRequest call using a sessionId that has timed out results in a SOAP exception.

Return Value

A status object is returned by default with a value of null. See the "Status Class" section, earlier in this chapter, for an explanation of this return type.

Calling CancelRequest

You can make the following CancelRequest call when a sessionId is available after a successful OpenWorkbook call:

try
{
   string xlSessionId;
   Status[] xlStatus;
   xlSessionId = xlEcsApi.OpenWorkbook(
   @"http://theexcelserver/testsite/Shared Documents/workbook.xlsb",
      @"en-US",
      @"de-DE",
      out xlStatus);
   xlStatus = xlEcsApi.CancelRequest(xlSessionId);
}

Refresh

The Refresh method of the Excel Services Web service requests updated query results for one or more external data sources used by the workbook associated with the session. The declaration for the Refresh method looks like this:

public Status[] Refresh(string sessionId, string connectionName)

Parameters

Following are the parameters for this method:

  • sessionId (in)—The sessionId string that was returned from a prior OpenWorkbook call. The session must still be active at the time of the Refresh call. Making a Refresh call using a sessionId that has timed out results in a SOAP exception.

  • connectionName (in)—Use this string to specify one connection that is to be refreshed, or to indicate that all connections in the workbook are to be refreshed. Specify the specific connection name to refresh a single connection. Use values of String.Empty, null or "" to refresh all workbook connections. Specifying a nonexistent connection name results in a Status return that contains a message indicating that there was a problem refreshing the data.

Return Value

A status object is returned by default with a value of null. See the "Status Class" section, earlier in this chapter, for an explanation of this return type.

Calling Refresh

The following example makes two Refresh calls. The first call refreshes all external data sources in the workbook. The second call refreshes only the connection with a name of "oneSource", if it exists in the workbook.

try
{
   string xlSessionId;
   Status[] xlStatus;
   xlSessionId = xlEcsApi.OpenWorkbook(
      @"http://theexcelserver/testsite/Shared Documents/workbook.xlsb",
      @"en-US",
      @"de-DE",
      out xlStatus);
   xlStatus = xlEcsApi.Refresh(xlSessionId, "");
   xlStatus = xlEcsApi.Refresh(xlSessionId, "oneSource");
}

CalculateA1

The CalculateA1 method of the Excel Services Web service calculates the entire workbook, a single sheet, a named range, a named object, or a range that is specified with A1 notation. The declaration for the CalculateA1 method looks like this:

public Status[] CalculateA1(string sessionId, string sheetName, string rangeName)

Parameters

Following are the parameters for this method:

  • sessionId (in)—The sessionId string that was returned from a prior OpenWorkbook call. The session must still be active at the time of the CalculateA1 call. Making a CalculateA1 call using a sessionId that has timed out results in a SOAP exception.

  • sheetName (in)—Use this string to specify the name of the sheet containing the rangeName that is to be calculated.

  • rangeName (in)—Use this string to specify the single cell or range to be calculated. You can specify the rangeName using a defined name that represents the range to be calculated, or an A1 notation. The name of an object (such as "Table1") is also a valid value.

The behavior of CalculateA1 is dependent on the values provided for both the sheetName and the rangeName parameters. Following are common uses for the CalculateA1 method:

  • Calculate workbook—The sheetName is either String.Empty or "", and the rangeName is either String.Empty or "".

  • Calculate sheet—The sheetName is the name of a sheet, and the rangeName is either String.Empty or "".

  • Calculate a range—The sheetName is the name of a sheet, and the rangeName is a defined name on the sheet used for sheetName. rangeName can reference a single cell or a contiguous range.

  • Calculate a range—The sheetName is the name of a sheet, and the rangeName is the A1 notation that references a single cell (such as "B2") or a contiguous range (such as "C4:F10").

Return Value

A status object is returned by default with a value of null. See the "Status Class" section, earlier in this chapter, for an explanation of this return type.

Calling CalculateA1

The following example makes four CalculateA1 calls. Comments are provided prior to each CalculateA1 call to describe what is going to be calculated.

try
{
   string xlSessionId;
   Status[] xlStatus;
   xlSessionId = xlEcsApi.OpenWorkbook(
      @"http://theexcelserver/testsite/Shared Documents/workbook.xlsb",
      @"en-US",
      @"de-DE",
      out xlStatus);
   //calculate the entire workbook
   xlStatus = xlEcsApi.CalculateA1(xlSessionId, "", "");
   //calculate only Sheet3
   xlStatus = xlEcsApi.CalculateA1(xlSessionId, "Sheet3", string.Empty);
   //calculate the RangeOne named range on Sheet3
   xlStatus = xlEcsApi.CalculateA1(xlSessionId, "Sheet3", "RangeOne");
   //calculate range A1:B3 on Sheet1
   xlStatus = xlEcsApi.CalculateA1(xlSessionId, "Sheet1", "A1:B3");
}

Calculate

The Calculate method of the Excel Services Web service calculates a single sheet or a range using range coordinates. The declaration for the Calculate method looks like this:

public Status[] Calculate(string sessionId, string sheetName, RangeCoordinates 
rangeCoordinates)

Parameters

Following are the parameters for this method:

  • sessionId (in)—The sessionId string that was returned from a prior OpenWorkbook call. The session must still be active at the time of the Calculate call. Making a Calculate call using a sessionId that has timed out results in a SOAP exception.

  • sheetName (in)—Use this string to specify the name of the sheet containing the range coordinates that are to be calculated.

  • rangeCoordinates (in)—Use this RangeCoordinates type to specify the range that is to be calculated. The range can be a single cell or a contiguous range. The rangeCoordinates type has the following four properties:

    • Column—The zero-based integer index, where column A is 0.

    • Row—The zero-based integer row index, where row 1 is 0.

    • Height—An integer greater than 0, which represents the row height of the range.

    • Width—An integer greater than 0, which represents the column width of the range.

The behavior of Calculate is dependant on the values provided for the rangeCoordinates. Following are common uses for the Calculate method:

  • Calculate sheet—The sheetName is the name of a sheet, and the rangeCoordinates are initialized to 0.

  • Calculate cell A1—The sheetName is the name of a sheet, and the rangeCoordinates are initialized as Column=0, Row=0, Height=1, and Width=1. This calculates cell A1.

  • Calculate cell B1—The sheetName is the name of a sheet, and the rangeCoordinates are initialized as Column=1, Row=0, Height=1, and Width=1. This calculates cell B1.

  • Calculate cells A1:A2—The sheetName is the name of a sheet, and the rangeCoordinates are initialized as Column=0, Row=0, Height=2, and Width=1. This calculates cells A1:A2.

  • Calculate cell A1:B1—The sheetName is the name of a sheet, and the rangeCoordinates are initialized as Column=0, Row=0, Height=1, and Width=2. The result is that cells A1:B1 are calculated.

Return Value

A status object is returned by default with a value of null. See the "Status Class" section, earlier in this chapter, for an explanation of this return type.

Calling Calculate

The following example makes a Calculate call. The range is defined using the RangeCoordinates object, and then the Calculate method is called to calculate range A1:A2 on Sheet3.

try
{
   string xlSessionId;
   Status[] xlStatus;
   xlSessionId = xlEcsApi.OpenWorkbook(
      @"http://theexcelserver/testsite/Shared Documents/workbook.xlsb",
      @"en-US",
      @"de-DE",
      out xlStatus);
   RangeCoordinates xlRange = new RangeCoordinates();
   xlRange.Column = 0;
   xlRange.Row = 0;
   xlRange.Height = 2;
   xlRange.Width = 1;
   //calculate range A1:A2 on Sheet3
   xlStatus = xlEcsApi.Calculate(xlSessionId, "Sheet3", xlRange);
}

CalculateWorkbook

The CalculateWorkbook method of the Excel Services Web service calculates the entire workbook using one of the two supported types of calculation. The declaration for the CalculateWorkbook method looks like this:

public Status[] CalculateWorkbook(string sessionId, CalculateType calculateType)

Parameters

Following are the parameters for this method:

  • sessionId (in)—The sessionId string that was returned from a prior OpenWorkbook call. The session must still be active at the time of the CalculateWorkbook call. Making a CalculateWorkbook call using a sessionId that has timed out results in a SOAP exception.

  • CalculateType (in)—Use this CalculateType type to specify the type of calculation to be performed. There are two supported enumerators for CalculateType:

    • CalculateFull—All formulas are calculated, but the dependency tree is not rebuilt.

    • Recalculate—Only dirty formulas are calculated.

Return Value

A status object is returned by default with a value of null. See the "Status Class" section, earlier in this chapter, for an explanation of this return type.

Calling CalculateWorkbook

The following example makes a CalculateWorkbook call using the CalculateFull enumerator:

try
{
   string xlSessionId;
   Status[] xlStatus;
   xlSessionId = xlEcsApi.OpenWorkbook(
      @"http://theexcelserver/testsite/Shared Documents/workbook.xlsb",
      @"en-US",
      @"de-DE",
      out xlStatus);
   xlStatus = xlEcsApi.CalculateWorkbook(
      xlSessionId,
      CalculateType.CalculateFull);
}

GetRangeA1

The GetRangeA1 method of the Excel Services Web service returns the calculated values for a single cell, a named range, a named object, or a range that is specified with A1 notation. The declaration for the GetRangeA1 method looks like this:

public object[] GetRangeA1(string sessionId, string sheetName, string rangeName, 
bool formatted, out Status[] status)

Parameters

Following are the parameters for this method:

  • sessionId (in)—The sessionId string that was returned from a prior OpenWorkbook call. The session must still be active at the time of the GetRangeA1 call. Making a GetRangeA1 call using a sessionId that has timed out results in a SOAP exception.

  • sheetName (in)—Use this string to specify the name of the sheet containing the rangeName. You can pass the sheetName as String.Empty or "" when you use a defined name or the name of an object for the rangeName.

  • rangeName (in)—Use this string to specify the single cell or range to be retrieved. You can specify the rangeName using a defined name or an object name that represents the range to be retrieved. You can also use A1 notation.

  • formatted (in)—Use this Boolean to specify whether formatted strings (true) or raw values (false) are to be returned.

  • Status (out)—See the "Status Class" section, earlier in this chapter, for an explanation of this (out) parameter.

The behavior of GetRangeA1 is dependent on the values that you provide for the sheetName and the rangeName parameters. Following are common uses for the GetRangeA1 method:

  • Retrieve a single cell—Specify a sheetName. Set the rangeName to a defined name that references a single cell or set the rangeName using A1 notation. For example, set the rangeName to "B1:B1" to retrieve the contents of cell B1.

  • Retrieve a range—Specify a sheetName. Set the rangeName to a defined name that references a range or set the rangeName using A1 notation For example, set the rangeName to "B1:C10" to retrieve the contents of the cells in the B1:C10 contiguous range.

  • Retrieve a range associated with an object—Specify a sheetName. Set the rangeName to the name of an object. For example, set the rangeName to "Table1" to retrieve the range of data contained within Table1. (The column header row for Table1 is not returned.)

Return Value

An array of values for the requested range is returned. The returned array has the same dimensions as the requested range. When the formatted parameter is true, String.Empty is returned for empty cells. When the formatted parameter is false, null is returned for empty cells.

GetRangeA1 returns the values from cells in hidden rows and columns. If column A is hidden and the rangeName is "A1:B1", then two objects would be returned: one for cell A1 and one for cell B1.

Calling GetRangeA1

The following example makes three GetRangeA1 calls. Comments are provided prior to each GetRangeA1 call to describe what is going to be retrieved.

try
{
   string xlSessionId;
   Status[] xlStatus;
   object[] rangeResult;
   xlSessionId = xlEcsApi.OpenWorkbook(
      @"http://theexcelserver/testsite/Shared Documents/workbook.xlsb",
      @"en-US",
      @"de-DE",
      out xlStatus);
   //get the unformatted value for cell B1 on Sheet1
   rangeResult = xlEcsApi.GetRangeA1(
      xlSessionId,
      "Sheet1",
      "B1:B1",
      false,
      out xlStatus);
   //get the formatted value for the range associated with Table1 on Sheet1
   rangeResult = xlEcsApi.GetRangeA1(
      xlSessionId,
      "Sheet1",
      "Table1",
      true,
      out xlStatus);
   //get the unformatted value for the range H5:L15 on Sheet1
   rangeResult = xlEcsApi.GetRangeA1(
      xlSessionId,
      "Sheet1",
      "H5:L15",
      false,
      out xlStatus);

}

GetRange

The GetRange method of the Excel Services Web service returns the calculated values for a single cell or a range that is specified using range coordinates. The declaration for the GetRange method looks like this:

public object[] GetRange(string sessionId, string sheetName, RangeCoordinates 
rangeCoordinates, bool formatted, out Status[] status)

Parameters

Following are the parameters for this method:

  • sessionId (in) — The sessionId string that was returned from a prior OpenWorkbook call. The session must still be active at the time of the GetRange call. Making a GetRange call using a sessionId that has timed out results in a SOAP exception.

  • sheetName (in)—Use this string to specify the name of the sheet containing the rangeCoordinates.

  • rangeCoordinates (in)—Use this RangeCoordinates type to specify the range that is to be retrieved. The range can be a single cell or a contiguous range. The rangeCoordinates type has four properties:

    • Column—The zero-based integer index, where column A is 0.

    • Row—The zero-based integer row index, where row 1 is 0.

    • Height—An integer greater than 0, which represents the row height of the range.

    • Width—An integer greater than 0, which represents the column width of the range.

  • formatted (in)—Use this Boolean to specify whether formatted strings (true) or raw values (false) are to be returned.

  • Status (out)—See the "Status Class" section, earlier in this chapter, for an explanation of this (out) parameter.

Following are common uses for the GetRange method:

  • Retrieve a single cell—The sheetName is the name of a sheet, and the rangeCoordinates are initialized as Column=0, Row=0, Height=1, and Width=1. This retrieves the value of cell A1.

  • Retrieve a range—sheetName is the name of a sheet and the rangeCoordinates are initialized as: Column=0, Row=0, Height=1, and Width=2. This retrieves the values in the range A1:B1.

Return Value

An array of values for the requested range is returned. The returned array has the same dimensions as the requested range. When the formatted parameter is true, String.Empty is returned for empty cells. When the formatted parameter is false, null is returned for empty cells.

GetRange returns the values from cells in hidden rows and columns. If column A is hidden and the rangeName was "A1:B1", then two objects would be returned: one for cell A1 and one for cell B1.

Calling GetRange

The following example makes a GetRange call. The range is defined using the RangeCoordinates object, and then the GetRange method is called to retrieve the value in range E5:J9 on Sheet2. This represents a range that is 5 rows by 6 columns.

try
{
   string xlSessionId;
   Status[] xlStatus;
   object[] rangeResult;
   xlSessionId = xlEcsApi.OpenWorkbook(
      @"http://theexcelserver/testsite/Shared Documents/workbook.xlsb",
      @"en-US",
      @"de-DE",
      out xlStatus);
   RangeCoordinates xlRange = new RangeCoordinates();
   xlRange.Column = 4;
   xlRange.Row = 4;
   xlRange.Height = 5;
   xlRange.Width = 6;
   rangeResult = xlEcsApi.GetRange(
      xlSessionId,
      "Sheet2",
      xlRange,
      true,
      out xlStatus);
}

GetCellA1

The GetCellA1 method of the Excel Services Web service returns the calculated values for a single cell that you specify with A1 notation, or a single cell that you reference by a defined name or object name. The declaration for the GetCellA1 method looks like this:

public object GetCellA1(string sessionId, string sheetName, string rangeName, bool 
formatted, out Status[] status)

Parameters

Following are the parameters for this method:

  • sessionId (in)—The sessionId string that was returned from a prior OpenWorkbook call. The session must still be active at the time of the GetCellA1 call. Making a GetCellA1 call using a sessionId that has timed out results in a SOAP exception.

  • sheetName (in)—Use this string to specify the name of the sheet containing the rangeName. You can pass the sheetName as String.Empty or "" when you use a defined name or the name of an object for the rangeName.

  • rangeName (in)—Use this string to specify a single cell to be retrieved. Specify the rangeName using a defined name or an object name that represents the cell to be retrieved. You can also use A1 notation.

  • formatted (in)—Use this Boolean to specify whether formatted strings (true) or raw values (false) are to be returned.

  • Status (out)—See the "Status Class" section, earlier in this chapter, for an explanation of this (out) parameter.

Return Value

An object is returned that is the value of the requested cell. Empty cells are represented as null. If the returned range is not a 1x1 range, a SOAP exception occurs.

Calling GetCellA1

The following example makes two GetCellA1 calls. Comments are provided prior to each GetCellA1 call to describe what is going to be retrieved.

try
{
   string xlSessionId;
   Status[] xlStatus;
   object cellResult;
   xlSessionId = xlEcsApi.OpenWorkbook(
      @"http://theexcelserver/testsite/Shared Documents/workbook.xlsb",
      @"en-US",
      @"de-DE",
      out xlStatus);
   //get the formatted value for cell A1 on Sheet1
   cellResult = xlEcsApi.GetCellA1(
      xlSessionId,
      "Sheet1",
      "A1",
      true,
      out xlStatus);
   //get the unformatted value for the cell with the OneCell defined name
   cellResult = xlEcsApi.GetCellA1(
      xlSessionId,
      "",
      "OneCell",
      false,
      out xlStatus);
}

GetCell

The GetCell method of the Excel Services Web service returns the calculated values for a single cell that you specify using range coordinates. The declaration for the GetCell method looks like this:

public object GetCell(string sessionId, string sheetName, int row, int column, bool 
formatted, out Status[] status)

Parameters

Following are the parameters for this method:

  • sessionId (in)—The sessionId string that was returned from a prior OpenWorkbook call. The session must still be active at the time of the GetCell call. Making a GetCell call using a sessionId that has timed out results in a SOAP exception.

  • sheetName (in)—Use this string to specify the name of the sheet containing the cell that is the focus of the GetCell call.

  • row (in)—Use this integer to specify the zero-based row containing the target cell that is to be retrieved.

  • column (in)—Use this integer to specify the zero-based column containing the target cell that is to be retrieved.

  • formatted (in)—Use this Boolean to specify whether a formatted string (true) or a raw value (false) is to be returned.

  • Status (out)—See the "Status Class" section, earlier in this chapter, for an explanation of this (out) parameter.

Return Value

An object is returned that is the value of the requested cell. An empty cell returns an object containing a zero-length string. GetCell returns the value from a cell in a hidden row or hidden column.

Calling GetCell

The following example makes a GetCell call. The GetCell call is made against Sheet1 to return the formatted value from cell E7.

try
{
   string xlSessionId;
   Status[] xlStatus;
   object cellResult;
   xlSessionId = xlEcsApi.OpenWorkbook(
      @"http://theexcelserver/testsite/Shared Documents/workbook.xlsb",
      @"en-US",
      @"de-DE",
      out xlStatus);
   cellResult = xlEcsApi.GetCell(
      xlSessionId,
      "Sheet1",
      6,
      4,
      true,
      out xlStatus);
}

SetRangeA1

The SetRangeA1 method of the Excel Services Web service sets values into a range that is specified using the A1 notation, a named range, or a named object. The declaration for the SetRangeA1 method looks like this:

public Status[] SetRangeA1(string sessionId, string sheetName, string rangeName, 
object[] rangeValues)

Parameters

Following are the parameters for this method:

  • sessionId (in)—The sessionId string that was returned from a prior OpenWorkbook call. The session must still be active at the time of the SetRangeA1 call. Making a SetRangeA1 call using a sessionId that has timed out results in a SOAP exception.

  • sheetName (in)—Use this string to specify the name of the sheet containing the rangeName. You can pass the sheetName as String.Empty or "" when you use a defined name or the name of an object for the rangeName.

  • rangeName (in)—Use this string to specify the range where the values are to be set. Specify the rangeName using a defined name or an object name that represents the range where the values are to be set. You can also use A1 notation.

  • rangeValues (in)—This is the array of values that are to be set in the specified rangeName. Empty cells are represented in the array as null.

A SOAP exception occurs when the dimensions of the rangeValues object do not match the dimensions of the rangeName.

Return Value

A status object is returned by default with a value of null. See the "Status Class" section, earlier in this chapter, for an explanation of this return type.

Calling SetRangeA1

The following example makes two SetRangeA1 calls. Comments are provided prior to each SetRangeA1 call to describe what is going to be set.

try
{
   string xlSessionId;
   Status[] xlStatus;
   xlSessionId = xlEcsApi.OpenWorkbook(
      @"http://theexcelserver/testsite/Shared Documents/workbook.xlsb",
      @"en-US",
      @"de-DE",
      out xlStatus);
   //define and create the data for the rangeValues
   int height = 2, width = 3;
   object[] rangeValues = new object[height];
   object[] firstRow = new object[width];
   object[] secondRow = new object[width];
   for (int x = 0; x < width; x++)
   {
      firstRow[x] = x + x;
      secondRow[x] = x + x + 1;
   }
   rangeValues[0] = firstRow;
   rangeValues[1] = secondRow;
   //set values for the cells in the K5:M6 range on Sheet1
   xlStatus = xlEcsApi.SetRangeA1(
      xlSessionId,
      "Sheet1",
      "K5:M6",
      rangeValues);
   //set values for the cells in Table2
   xlStatus = xlEcsApi.SetRangeA1(
      xlSessionId,
      string.Empty,
      "Table2",
      rangeValues);
}

SetRange

The SetRange method of the Excel Services Web service sets values into a range that you specify using range coordinates. The declaration for the SetRange method looks like this:

public Status[] SetRange(string sessionId, string sheetName, RangeCoordinates 
rangeCoordinates, object[] rangeValues)

Parameters

Following are the parameters for this method:

  • sessionId (in)—The sessionId string that was returned from a prior OpenWorkbook call. The session must still be active at the time of the SetRange call. Making a SetRange call using a sessionId that has timed out results in a SOAP exception.

  • sheetName (in)—Use this string to specify the name of the sheet containing the rangeCoordinates.

  • rangeCoordinates (in)—Use the RangeCoordinates type to specify the range that is to be set. The range can be a single cell or a contiguous range. The RangeCoordinates type has four properties:

    • Column—The zero-based integer index, where column A is 0.

    • Row—The zero-based integer row index, where row 1 is 0.

    • Height—An integer greater than 0, which represents the row height of the range.

    • Width—An integer greater than 0, which represents the column width of the range.

  • rangeValues (in)—This is an array of array of objects that is the array of values to be set in the specified rangeCoordinates. Empty cells are represented in the array as null.

A SOAP exception occurs when the dimensions of the rangeValues object do not match the dimensions of the rangeCoordinates.

Following are common uses for the GetRange method:

  • Set a single cell—The sheetName is the name of a sheet, and the rangeCoordinates are initialized as Column=0, Row=0, Height=1, and Width=1. This sets the value of cell A1 with rangeValues.

  • Set a range—The sheetName is the name of a sheet, and the rangeCoordinates are initialized as Column=0, Row=0, Height=1, and Width=2. This sets the values in the range A1:B1 with rangeValues.

Return Value

A status object is returned by default with a value of null. See the "Status Class" section, earlier in this chapter, for an explanation of this return type.

Calling SetRange

The following example makes a SetRange call using a range that is 2 rows by 6 columns. The range to be set is E7:J8.

try
{
   string xlSessionId;
   Status[] xlStatus;
   xlSessionId = xlEcsApi.OpenWorkbook(
      @"http://theexcelserver/testsite/Shared Documents/workbook.xlsb",
      @"en-US",
      @"de-DE",
      out xlStatus);
   //define and create the data for the rangeValues
   int height = 2, width = 6;
   object[] rangeValues = new object[height];
   object[] firstRow = new object[width];
   object[] secondRow = new object[width];
   for (int x = 0; x < width; x++)
   {
      firstRow[x] = x + x;
      secondRow[x] = x + x + 1;
   }
   rangeValues[0] = firstRow;
   rangeValues[1] = secondRow;
   //define range for E7:J8
   RangeCoordinates xlRange = new RangeCoordinates();
   xlRange.Column = 4;
   xlRange.Row = 6;
   xlRange.Height = height;
   xlRange.Width = width;
   //set values for E7:J8 on Sheet1
   xlStatus = xlEcsApi.SetRange(
      xlSessionId,
      "Sheet1",
      xlRange,
      rangeValues);
}

SetCellA1

The SetCellA1 method of the Excel Services Web service sets a value into a single cell that you specify using the A1 notation, a named range, or a named object. The declaration for the SetCellA1 method looks like this:

public Status[] SetCellA1(string sessionId, string sheetName, string rangeName, 
object cellValue)

Parameters

Following are the parameters for this method:

  • sessionId (in)—The sessionId string that was returned from a prior OpenWorkbook call. The session must still be active at the time of the SetCellA1 call. Making a SetCellA1 call using a sessionId that has timed out results in a SOAP exception.

  • sheetName (in)—Use this string to specify the name of the sheet containing the rangeName. You can pass the sheetName as String.Empty or "" when you use a defined name or the name of an object for the rangeName.

  • rangeName (in)—Use this string to specify the range where the value is to be set. Specify the rangeName using a defined name, or an object name that represents the cell where the value is to be set. You can also use A1 notation.

  • cellValue (in)—This is an object that is the value to be set in the specified rangeName. An empty cell is represented in the object as null.

If the rangeName is not a 1x1 range, a SOAP exception occurs.

Return Value

A status object is returned by default with a value of null. See the "Status Class" section, earlier in this chapter, for an explanation of this return type.

Calling SetCellA1

The following example makes two SetCellA1 calls. Comments are provided prior to each SetCellA1 call to describe what is going to be set.

try
{
   string xlSessionId;
   Status[] xlStatus;
   object cellValue;
   xlSessionId = xlEcsApi.OpenWorkbook(
      @"http://theexcelserver/testsite/Shared Documents/workbook.xlsb",
      @"en-US",
      @"de-DE",
      out xlStatus);
   \\set a cell value in a 1x1 named object
   cellValue = "some text";
   xlStatus = xlEcsApi.SetCellA1(
      xlSessionId,
      string.Empty,
      "Table2",
      cellValue);
   \\set cell C2 on Sheet1 to a date
   cellValue = "5/30/2008";
   xlStatus = xlEcsApi.SetCellA1(
      xlSessionId,
      "Sheet1",
      "C2",
      cellValue);
}

SetCell

The SetCell method of the Excel Services Web service sets a cell value into a range that you specify using range coordinates. The declaration for the SetCell method looks like this:

public Status[] SetCell(string sessionId, string sheetName, int row, int column, 
object cellValue)

Parameters

Following are the parameters for this method:

  • sessionId (in)—The sessionId string that was returned from a prior OpenWorkbook call. The session must still be active at the time of the SetCell call. Making a SetCell call using a sessionId that has timed out results in a SOAP exception.

  • sheetName (in)—Use this string to specify the name of the sheet containing the cell that is the focus of the SetCell call.

  • row (in)—Use this integer to specify the zero-based row containing the target cell that is to be set.

  • column (in)—Use this integer to specify the zero-based column containing the target cell that is to be set.

  • cellValue (in)—This is an object that is the value to be set in the specified cell. An empty cell is represented in the object as null.

Return Value

A status object is returned by default with a value of null. See the "Status Class" section, earlier in this chapter, for an explanation of this return type.

Calling SetCell

The following example makes a SetCell call. The SetCell call is made against Sheet1 to set a date value into cell K6.

try
{
   string xlSessionId;
   Status[] xlStatus;
   object cellValue;
   xlSessionId = xlEcsApi.OpenWorkbook(
      @"http://theexcelserver/testsite/Shared Documents/workbook.xlsb",
      @"en-US",
      @"de-DE",
      out xlStatus);
   cellValue = "5/30/2008";
   xlStatus = xlEcsApi.SetCell(
      xlSessionId,
      "Sheet1",
      5,
      10,
      cellValue);
}

GetWorkbook

The GetWorkbook method of the Excel Services Web service returns the workbook using one of the three supported modes. The declaration for the GetWorkbook method looks like this:

public byte[] GetWorkbook(string sessionId, WorkbookType workbookType, out Status[] status)

Parameters

Following are the parameters for this method:

  • sessionId (in)—The sessionId string that was returned from a prior OpenWorkbook call. The session must still be active at the time of the GetWorkbook call. Making a GetWorkbook call using a sessionId that has timed out results in a SOAP exception.

  • workbookType (in)—Use this WorkbookType type to specify the mode to be used when retrieving the workbook. There are three supported enumerators for WorkbookType:

    • FullSnapshot—Maps to the entire workbook that is being used, and returns the workbook in its current state, including the private state view if one exists.

    • FullWorkbook—Maps to the entire workbook that was published to the server, taking into account the caller’s permissions. This does not return the private state view of the workbook.

    • PublishedItemsSnapshot—Maps to the items that were marked as viewable when the workbook was published, and returns those items in their current state, including the private state view if one exists.

  • Status (out)—See the "Status Class" section, earlier in this chapter, for an explanation of this (out) parameter.

Return Value

A byte array is returned that represents the workbook content based on the specified WorkbookType. The file format loaded into the session is used.

Calling GetWorkbook

The following example makes a GetWorkbook call using the PublishedItemsSnapshot enumerator:

try
{
   string xlSessionId;
   Status[] xlStatus;
   byte[] bits;
   xlSessionId = xlEcsApi.OpenWorkbook(
      wkbk2,
      @"en-US",
      @"en-US",
      out xlStatus);
   bits = xlEcsApi.GetWorkbook(
      xlSessionId,
      WorkbookType.PublishedItemsSnapshot,
      out xlStatus);
}

API Sample

Chapter 13 described how you can leverage Excel Services to offload calculations from the client. This method is used primarily for workbooks that take a significant amount of time to calculate. Because there is no Excel Services out-of-the-box functionality for offloading such workbooks to the server, a custom solution is needed.

This section provides a sample application that enables the scenario described at the end of Chapter 13. It makes use of the Excel Services Web service presented in this chapter.

The sample illustrates how you can use Excel Services to offload calculations from the client to the server. With this program, users of calculation-intensive workbooks can save those workbooks to the target folder, the workbooks are calculated on the server, and the updated workbooks are made available to the users in an output folder.

The sample creates a program that monitors the addition of workbooks in a target file location (provided by the second argument). Excel Services opens workbooks that get added to the target location, force a calculation of the workbook, and then save the calculated workbook to an output file location (provided by the first argument). As far as the usage of Excel Services API is concerned, this is a straightforward example. The program calls on the OpenWorkbook method, the CalculateWorkbook method, and the GetWorkbook method to complete the steps described previously.

Here is the sample code:

namespace ExcelServicesCalcDemo
{
   using System;
   using System.Collections.Generic;
   using System.IO;
   using System.Text;
   using System.Threading;
   using ExcelServices;
   class Program
   {
      // The destination directory holds the location
      // to which the calculated workbooks will be saved
      private static string DestinationDirectory;
      static void Main(string[] args)
      {
         DestinationDirectory = args[0];
         // Listen for any new *.xlsb files created in a given folder.
         // Call the OnFileCreated method for every such file
         FileSystemWatcher fileWatcher = new FileSystemWatcher(
            args[1],
            "*.xlsb");
         fileWatcher.Created += new FileSystemEventHandler(OnFileCreated);
         fileWatcher.EnableRaisingEvents = true;
         Console.ReadLine();
      }
      private static void OnFileCreated(object source, FileSystemEventArgs e)
      {
         // Create the Excel Service web service caller
         ExcelService service = new ExcelService();
         service.Credentials = System.Net.CredentialCache.DefaultCredentials;
         // Open the workbook, calculate it, get the workbook
         // into a byte array and close the session
         Status[] status;
         string sessionId = service.OpenWorkbook(
            e.FullPath,
            "en-US",
            "en-US",
            out status);
         service.CalculateWorkbook(sessionId, CalculateType.CalculateFull);
         byte[] savedWorkbook = service.GetWorkbook(
            sessionId,
            WorkbookType.FullWorkbook,
            out status);
         service.CloseWorkbook(sessionId);
         // Save the workbook to the destination folder
         string newFileName = Path.Combine(DestinationDirectory, e.Name);
         BinaryWriter writer = new BinaryWriter(File.Open(
            newFileName,
            FileMode.CreateNew));
         writer.Write(savedWorkbook);
         writer.Close();
      }
   }
}

This sample is very rudimentary. In a real-world application, additional functionality is needed to provide notifications or SharePoint alerts as steps in the process are completed. In addition, such a real-world application sends multiple requests in parallel to Excel Services. The formula for calculating the number of parallel requests is the number of ECS machines available in the farm multiplied by the number of processors on each ECS machine.

Next Part: Chapter 14: Building Custom Solutions (Part 2 of 2)