Interacting with the Excel Web Services API for SharePoint Server 2007

  • Article

Summary: Get a quick start with the Excel Web Services API, which enables interaction with published Microsoft Office Excel 2007 workbooks in Microsoft Office SharePoint Server 2007 from a remote application. Learn considerations around session state, security, and performance. (6 printed pages)

MVP Icon Spencer Harbar, Microsoft Certified Master, SharePoint MVP, Harbar.net

February 2010

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

Contents

  • Introduction to the Excel Web Services API

  • What the Excel Web Services API Provides

  • Session Management and Performance

  • Security Configuration

  • Exception Management

  • Quick Start Example

  • Conclusion

  • Additional Resources

Download the code samples that accompany this article:Office SharePoint Server 2007 Excel Services Quick Start.

Note

This quick start article assumes that a Microsoft Office SharePoint Server 2007 dashboard and published Microsoft Office Excel 2007 workbooks are already deployed.

Introduction to the Excel Web Services API

Excel Services within Microsoft Office SharePoint Server 2007 Enterprise Edition provides a variety of rich default capabilities to deliver business intelligence solutions via customization and configuration. In addition to these capabilities, the Excel Web Services API provides you with a broad extensibility model upon which to base custom applications, both within and from outside of Office SharePoint Server 2007.

Excel Calculation Services (ECS) is responsible for performing server-side calculations and enabling browser rendering of Excel Services content. ECS is implemented as a Web service within a shared services provider (SSP) application in Internet Information Services (IIS). This ECS Web service is private and intended for internal use by Excel Web Access. It is not intended that the ECS Web Service is used for custom development.

The Excel Web Services API is available to developers for custom solutions and is exposed by every SharePoint Web application at the following path:

/vti_bin/ExcelService.asmx (for example, http://intranet/_vti_bin/ExcelService.asmx)

The Excel Web Services API is essentially a bridge to ECS, enabling developers to interact with ECS without using the Excel Web Access components. One common scenario for the Excel Web Services API is enabling developers to build server-side applications that avoid the need to recode logic contained within an Excel workbook.

To inherit site context and security, the Excel Web Services API should be consumed from the site being worked with (for example, http://intranet/Reports/_vti_bin/ExcelService.asmx).

The Excel Web Services API can be consumed by custom applications like any other Web service, but it is important to consider the reliance upon a "user session" when interacting with its methods. The Excel Web Services API makes this easier by exposing a session identifier, which is used in subsequent calls and manages the session state transparently in the background.

What the Excel Web Services API Provides

The Excel Web Services API enables developers to initiate an ECS session and interact with Excel workbooks, including performing calculations in workbook cells. The methods provided are summarized in Table 1.

Table 1. Summary of Excel Web Services API methods

Method Description Comment

GetApiVersion

Gets the installed ExcelService API build.

OpenWorkbook

Opens a server-side calculation session. The method takes a workbook path, user interface (UI) culture name, and data culture name and returns a sessionId.

GetSessionInformation

Gets properties of the server session, including the language of the session.

SetCell

Sets a value into a cell on one sheet of the workbook.

This method has two addressing mechanisms: one takes a cell address ("A22") or a named range ("Cost"), and the other accepts integer coordinates.

SetRange

Same as SetCell (sets a value into a cell on one sheet of the workbook), but used for setting values into an entire contiguous range.

This method has two addressing mechanisms: one takes a cell address ("A22") or a named range ("Cost"), and the other accepts integer coordinates.

Refresh

Reads data from external data connections and refreshes the values in relevant cells.

Calculate

Recalculates the formulas in a specific range or in the entire workbook. Useful when the workbook author has turned off automatic calculation.

This method has two addressing mechanisms: one takes a cell address ("A22") or a named range ("Cost"), and the other accepts integer coordinates.

CalculateWorkbook

Calculates the entire workbook, by using one of two calculation methods:

Recalculate: Calculate only formulas that have dependencies that changed.

CalculateFull: Calculate all formulas, regardless of dependency changes.

GetCell

Gets a value out of a cell. Can return formatted string values or raw binary values.

This method has two addressing mechanisms: one takes a cell address ("A22") or a named range ("Cost"), and the other accepts integer coordinates.

GetRange

Gets a set of values out of a contiguous range of cells.

This method has two addressing mechanisms: one takes a cell address ("A22") or a named range ("Cost"), and the other accepts integer coordinates.

GetWorkbook

Gets the entire calculated workbook into memory as a byte array. Can return a live result or a snapshot.

A snapshot is a workbook that has had its formulas and conditional formatting flattened and external data connections removed.

CancelRequest

Aborts a long-running server request.

CloseWorkbook

Closes the workbook opened for this session, allowing the server to release all associated resources.

Session Management and Performance

ECS maintains server-side sessions for performance reasons. This prevents having to recalculate the entire workbook each time a user chooses to interact with the workbook (for example, by changing an input parameter). Similarly, if a custom application interacts with a workbook by changing a cell value, only the formulas that depend upon that cell need to be recalculated, instead of the entire workbook. The context is kept in memory on the server as a session. Custom applications need to tell ECS which session they are working with by including the sessionId parameter.

You can configure ECS session settings and performance settings via the Shared Service Provider Excel Services Settings page, and also by using the Stsadm.exe command-line tool. If you are building custom applications, you must consider these settings. By default, the maximum number of sessions per user is 25, although you can configure this value with no limit or as any positive integer. By default, the memory allocated to the ECS process is 50 percent of the physical memory on the machine running ECS, but this value is frequently reconfigured.

Security Configuration

ECS includes a File Access Method configuration setting, which governs how workbooks are retrieved when they are not stored within a SharePoint library. By default, Impersonation is used, meaning that the workbook is opened under the context of the current user credentials. This setting can be changed to Process account, which uses the application pool identity of the SSP to open the workbook.

ECS can also be configured to use Kerberos authentication for access to all workbook storage locations. This is often necessary in complex topologies where delegation is required, or when workbooks access data stored in Microsoft SQL Server Analysis Services.

It is essential that developers consider the intended production security configuration, to ensure that errors do not occur when opening workbooks from custom applications.

Exception Management

ECS raises three categories of exceptions to custom applications:

  • Errors that prevent ExcelService API methods from completing raise a SOAP exception, which can be caught and handled in the custom application code.

  • Noncritical errors that do not prevent the return of normal results are returned as output arguments.

  • Calculation errors are returned just as they are in Excel (for example, #VALUE!).

Quick Start Example

The following example demonstrates how to use the Excel Web Services API to interact with a workbook that performs a simple loan calculation. The workbook is shown in Figure 1, as rendered by Excel Web Access.

Figure 1. Loan calculation workbook in Excel Web Access

Loan calculation workbook in Excel Web Access

In this example, a simple Windows Form application is created to demonstrate the interaction with ECS via the Excel Web Services API. The application performs the following steps when Calculate is clicked:

  1. Creates an instance of the ExcelService object.

  2. Sets the URL to the Web service (within the context of the hosting SharePoint site).

  3. Sets credentials to be used.

  4. Calls the OpenWorkbook method, passing the path to the workbook.

    A sessionId is returned.

  5. Calls SetCellA1 to set the three parameter cells to the values that the user typed into the form.

    Named ranges are used, instead of direct cell references. This makes the code more robust, and less sensitive to layout changes in the workbook.

  6. Calls GetCellA1 to get the calculation result, the "Payment" named range.

    The sample workbook is set to be automatically recalculated; as soon as all of the parameters are set, the result is available. In some cases, automatic recalculation is turned off while authoring the Excel workbook; it is then necessary to call Calculate at the point in the code where we want to tell ECS to explicitly calculate formulas.

  7. Calls CloseWorkbook to end the session.

    This call tells ECS that we are finished with this session, and all the associated resources can be released.

To get started, we need a new Windows Form application that includes a Web reference to ExcelService.asmx.

To add a Web reference to ExcelService.asmx

  1. Right-click the project, and then click Add Service Reference.

  2. Click Advanced.

  3. Click Add Web Reference.

  4. Type the URL (for example, http://intranet/reports/_vti_bin/ExcelService.asmx), and then click Go.

  5. Type a Web reference name, and then click Add Reference.

The Windows Form also requires three labels and text boxes for the input values Amount, Interest, and Length. A button to initiate the calculation and a text box to display the result is also needed.

Two using statements are also required in the form's code.

using System.Net;
using ExcelServiceAPISample.intranet;

Add the following code to the Click event handler of the button.

Status[] status;
string sessionId = null;

ExcelService es = new ExcelService();
es.Url = "http://intranet/Reports/_vti_bin/ExcelService.asmx";
es.Credentials = System.Net.CredentialCache.DefaultCredentials;

try {
sessionId = es.OpenWorkbook("http://intranet/Reports/ReportsLibrary/ExcelSample.xlsx",String.Empty, String.Empty, out status);
      }
catch {
      sessionId = null;
}
if (sessionId == null){
MessageBox.Show("Error opening workbook.");
}
es.SetCellA1(sessionId, "Sheet1", "B3", txtAmount.Text.Trim());
es.SetCellA1(sessionId, "Sheet1", "B4", txtInterest.Text.Trim());
es.SetCellA1(sessionId, "Sheet1", "B5", txtLength.Text.Trim());
            
object o = es.GetCellA1(sessionId, "Sheet1", "B7", true, out status);
if (o != null){
txtPayment.Text = Convert.ToString(o);
}
else{
    txtPayment.Text = "Error getting total value from workbook.";
}

status = es.CloseWorkbook(sessionId);

After the application is built and run and the user enters values, the calculation is performed and the result is returned. There is one caveat with the use of the SetCell method and the GetCell methods: If the author of the workbook changes the location of the cells, the application breaks. A much better approach is to use named ranges within the workbook, which can then be used within the two methods.

es.SetCellA1(sessionId, "", "Amount", txtAmount.Text.Trim());
es.SetCellA1(sessionId, "", "Length", txtLength.Text.Trim());
es.SetCellA1(sessionId, "", "Interest", txtInterest.Text.Trim());

object o = es.GetCellA1(sessionId, "Sheet1", "Payment", true, out
                       status);

Conclusion

This extremely simple quick start application demonstrates the power of the Excel Web Services API for performing server-side calculations by using Excel Calculation Services (ECS) while securing the calculation logic on the server. By using the Excel Web Services API, you can implement powerful applications that consume the services offered by ECS.

Additional Resources

For more information, see the following resources: