Excel Web Services

What s all the fuss about MOSS 2007 (Microsoft OfficeSharePoint Server 2007) and Excel Web Services (EWS)? As it turns out, EWS allows SOAP clients to connect to and retrieve results from the Microsoft Excel Calculation engine. We explored a high-level overview of this in an article titled Excel Services . To recap, this type of functionality is new from the ground up, and holds tremendous promise especially at the enterprise level for companies seeking to leverage Excel in a scalable way.

There are, however, some nuances and shortcomings of which you need to be aware. We ll discuss those later. For now, let s focus more on the low-level coding details of EWS and less on the theoretical. First and foremost, EWS is a .NET Web service. For the purposes of this article, I ll assume you have MOSS 2007 installed and configured correctly. If my assumption is incorrect, follow these links to install MOSS 2007 and configure Excel Services: http://tinyurl.com/23vpzy and http://tinyurl.com/2zz72y.

Implementing an EWS Example

There are two steps to harvesting functionality from EWS. First, you must write the necessary code to address the programming model. Then, you need to configure and load the spreadsheet that will contain the formulas that drive the calculation. Let s handle the former first.

Create a .NET application based on a Web site template. Add a button and two textboxes. Set the text of the button to Calculate (or whatever description is convenient for you). When the button is clicked, the code will grab the input in one textbox, query the Web service, and return the results in the other textbox (this is a basic pattern that real-world applications follow).

Add a Web reference to the ExcelServices EWS service (see Figure 1). As a side note, it is possible that there may be more than one ExcelService URL registered on your machine, especially if you ve re-installed MOSS 2007. In that case, it s important to select the Web reference with a valid URL. Invalid URLs will generate the required proxy but the call will fail with an exception.

Figure 1: Add a Web reference.

In the Add Web Reference dialog box, enter ASPHello as the Web reference name and click the Add Reference button (see Figure 2). Make a note of the methods this service exposes to SOAP clients (you ll use these methods to invoke the Web service in your application).

Figure 2: The Add Web Reference dialog box in Visual Studio .NET.

Using ASPHello or Imports ASPHello for C# or VB, respectively, adds a reference to the service in the code-beside file. That s it for the plumbing. Now let s add the business logic (see Listing One).

Let s walk through the code. As is often the case, a using construct secures resource de-allocation while providing a reference to the Web service proxy. This is a best practice, and you must exercise the necessary discipline to use this approach consistently.

The code creates default credentials and attempts to open a workbook. If the call is successful, a valid session identifier is returned. You ll use this identifier for every subsequent call. You should note that the session identifier is guaranteed unique across the Web farm only.

For security purposes, credentials are required even if you only use the default option. There is flexibility to allow you to create credentials using a username and password for an account that is provisioned on MOSS, as well. To exercise this option, use the appropriate method on the System.Net.CredentialCache object.

The first call to EWS queries API Version information. It is not strictly necessary, but it is a good sanity check. Next, we write a value to a named range, Input, using Excel range notation. The results are read from another named range, ProductResults. Because the workbook is set to auto-calculate by default, there is no need to explicitly recalculate the worksheet. The results of the calculation are immediately available. Finally, the code displays the results of the EWS call. The code demonstrates the basics, such as creating a valid session, and reading and writing ranges via the Web service. Once you have the basics in place, you can build from there.

Provisioning EWS Spreadsheets in MOSS 2007

The second part of the exercise involves provisioning the spreadsheet. If you ran the code at this point, it would fail because there is no valid worksheet given by the path http:///Shared%20Documents/AspProWorkBook.xlsx. There is no way to create or provision an Excel Spreadsheet from a SOAP client. You must perform this exercise using Excel 2007. To create and provision a spreadsheet, open Excel 2007 (see Figure 3).

Figure 3: Excel 2007 showing formulas and named ranges.

Enter =PRODUCT(Input*Input) in cell A2. Name the range A2 ProductResults. In cell A1, name the range Input. This will allow us to mimic a typical enterprise-level spreadsheet containing formulas and named ranges. You should note that EWS also handles ranges that are not named; however, the preferred approach is to use the naming convention.

As you can see, our spreadsheet logic calculates the square of numerical input contained in cell A1. Give it a whirl in Excel 2007 by entering 10 into cell A1. Cell A2 will immediately display 100. We expect to see those same results when we use the Web service. For now, we can pretend this is a complicated, proprietary financial formula that provides a competitive advantage to our financial trading partners. Part of the business strategy involves allowing affiliates to use this proprietary financial formula to compute data. We can bill the client as needed for these services.

The implementation details of this complex formula are stored securely on our company servers using Excel Workbook security and the usual range of industrial-strength storage mechanisms available today. To the affiliates, we gate access to the computation logic by exposing it through the Excel Web service with a provisioned account. Only authorized affiliates can access this Web service. This is the reason for the credentials part of the code discussed earlier.

Let s save the spreadsheet to the path http:///Shared%20Documents/AspProWorkBook.xlsx. Remember that this file path points to a trusted location. You created the trusted location by following the details listed in the second link presented earlier. Workbooks that do not reside in a trusted location cannot be serviced through EWS.

That s really all there is to it. When you execute the application, you should see results similar to Figure 4.

Figure 4: Output after calling the Excel Web Services service.

Guidelines for Developing EWS Applications

Let s make a few notes. EWS is noticeably slower than the native workbook calculation. You can easily get a feel for how much lag is involved by timing the call through EWS and comparing it to a Microsoft Excel 2007 computation. The lag is to be expected, as there is significant overhead in any SOAP client invocation. You may also notice a lag in the initial request. EWS requires a warm up period. The initial performance hit is due to the allocation and initialization of various data structures that power EWS. However, that lag tapers off when EWS warms up.

For real-time calculation results, consider by-passing the SOAP client altogether and connecting to the Web service locally. This technique is called Direct-Linking and is best suited for real-time calculations that service financial applications or game programming applications. The performance of a direct-linked implementation is equivalent to the calculation response of Excel 2007.

You should be aware that the call to EWS can result in an exception. To test this, enter non-numeric input in both Excel 2007 and the SOAP client. In both cases, you should see a #VALUE error being returned. For EWS, the error is caught and re-wrapped as a serializable SOAP exception that is propagated to the client. In Excel 2007, the #VALUE error is displayed in the cell.

We assumed that auto-calculation was turned on. This may not always be the case in real-world applications especially if the spreadsheet contains a lot of data, formulas, and embedded external connections. In this case, you d need to force a calculation using the calculation method before retrieving the results. Recall that you made a note of these functions earlier when you added a reference to the ExcelServices Web service.

Some of the code to extract data from the spreadsheet was unsightly compared to .NET standards. The reason for this is that the spreadsheet uses a jagged array as its primary data structure. A jagged array is the most suitable container for storing spreadsheet data because it allows the rows and column information in the spreadsheet to be represented efficiently. However, the choice of storage and the type used to hold row and column information implies that we will need to use ugly casts for the object arrays and a looping construct to probe and retrieve data. EWS has not yet matured to the point of exposing strongly-typed variable access.

If you care to place a break point after the line of code to call SetCellA1, you ll find that when you open the spreadsheet in Microsoft Excel 2007, the data from the SOAP client is not recorded in the Excel workbook. Excel Services first loads the workbook in a user session. From this point on, changes are made to the in-memory copy of the workbook. The original workbook on disk remains untouched. This is by design and is done for performance reasons.

The in-memory copy may be shared among clients with the same security credentials similar to object pooling mechanisms used today. This optimization provides a significant performance boost when compared to disk-based access. It also allows spreadsheets to be updated and swapped in real-time, without impact to the executing application. However, it leaves the in-memory version susceptible to server hiccups and application restarts, much like the intrinsic session objects in ASP.NET.

Finally, you may be a bit disappointed that EWS does not allow authoring of workbooks. That was never the design intent of the product. Further, legal issues related to SOX compliance impose limitations on the way data can be created and maintained at the enterprise level. With the current design, spreadsheets and workbooks now have a single point of entry for authorship; EWS simply allows access to these forms of data in a way that is safe, secure, and scalable.

Conclusion

I m sure you ll agree that harvesting spreadsheet functionality requires minimal coding effort. The architecture is loosely coupled in a way that allows applications based on Excel Services to scale well in the enterprise. And while we only endeavored to present one possible scenario, there are certainly many, many more industry services that can benefit from this type of architecture.

The VB.NET and C# source code accompanying this article is available for download.

Begin Listing One Accessing data through the EWS service

C#

using System;

using System.Web;

using ASPHello;

public partial class _Default : System.Web.UI.Page

{

 protected void Calculate_Click(object sender, EventArgs e)

 {

   using (ExcelService es = new ExcelService())

   {

     //error handling options

     Status[] outStatus;

     //target workbook residing in a trusted location

     string targetWorkbookPath = "http:///

      Shared%20Documents/AspProWorkBook.xlsx";

     es.Credentials =

       System.Net.CredentialCache.DefaultNetworkCredentials;

     //open the workbook

     string sessionId = es.OpenWorkbook(targetWorkbookPath,

       "en-US", "en-US", out outStatus);

     //if the workbook is opened, proceed with the call

     if (!String.IsNullOrEmpty(sessionId))

     {

       if (es.GetApiVersion(out outStatus) ==

         "Excel Web Services (12.0)")

       {

         outStatus = es.SetCellA1(sessionId, "sheet1",

           "Input", TextBox1.Text);

        //if all is well, read the value

        if (outStatus == null)

        {

           object[] product = es.GetRangeA1(sessionId,

             "sheet1", "ProductResults", false,

            out outStatus);

            if (product != null)

           {

             TextBox2.Text =

               ((object[])product[0])[0].ToString();

           }

         }

       }

     }

   }

 }

}

 

 

Visual Basic

Imports System

Imports System.Web

Imports ASPHello

Partial Class _Default

 Inherits System.Web.UI.Page

 Protected Sub Calculate_Click(ByVal sender As Object,

   ByVal e As EventArgs) Handles Button1.Click

   Using es As ExcelService = New ExcelService()

     'error handling options

     Dim outStatus As Status()

     'target workbook residing in a trusted location

     Dim targetWorkbookPath As String =

       "http://servername:port>/Shared%20Documents/

       AspProWorkBook.xlsx"

       es.Credentials =

         System.Net.CredentialCache.DefaultNetworkCredentials

     'open the workbook

     Dim sessionId As String = es.OpenWorkbook(

      targetWorkbookPath, "en-US", "en-US", outStatus)

     'if the workbook is opened, proceed with the call

     If (Not String.IsNullOrEmpty(sessionId)) Then

         If es.GetApiVersion(outStatus) = "Excel Web

           Services (12.0)" Then

             outStatus = es.SetCellA1(sessionId, "sheet1",

               "Input", TextBox1.Text)

             'if all is well, read the value

             If outStatus Is Nothing Then

               Dim product As Object() = es.GetRangeA1(

                sessionId, "sheet1", "ProductResults",

                 False, outStatus)

               If Not product Is Nothing Then

                 TextBox2.Text = (CType(product(0),

                   Object()))(0).ToString()

               End If

             End If

         End If

     End If

   End Using

 End Sub

End Class

Comments

Plain text