Excel Web Services

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

There are, however, some nuances and shortcomings of whichyou need to be aware. We ll discuss those later. For now, let s focus more onthe low-level coding details of EWS and less on the theoretical. First andforemost, EWS is a .NET Web service. For the purposes of this article, I llassume you have MOSS 2007 installed and configured correctly. If my assumptionis incorrect, follow these links to install MOSS 2007 and configure ExcelServices: http://tinyurl.com/23vpzy andhttp://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 formulasthat 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 (orwhatever description is convenient for you). When the button is clicked, thecode will grab the input in one textbox, query the Web service, and return the resultsin the other textbox (this is a basic pattern that real-world applicationsfollow).

Add a Web reference to the ExcelServices EWS service (see Figure1). As a side note, it is possible that there may be more than one ExcelServiceURL registered on your machine, especially if you ve re-installed MOSS 2007. Inthat case, it s important to select the Web reference with a valid URL. InvalidURLs 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 theWeb reference name and click the Add Reference button (see Figure 2). Make anote of the methods this service exposes to SOAP clients (you ll use thesemethods to invoke the Web service in your application).

Figure 2: The Add Web Referencedialog 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 sit for the plumbing. Now let s add the business logic (see ListingOne).

Let s walk through the code. As is often the case, a usingconstruct secures resource de-allocation while providing a reference to the Webservice proxy. This is a best practice, and you must exercise the necessarydiscipline to use this approach consistently.

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

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

The first call to EWS queries API Version information. Itis not strictly necessary, but it is a good sanity check. Next, we write avalue to a named range, Input, using Excel range notation. The results are readfrom another named range, ProductResults. Because the workbook is set toauto-calculate by default, there is no need to explicitly recalculate theworksheet. 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 Webservice. 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 thespreadsheet. If you ran the code at this point, it would fail because there isno 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 aspreadsheet, open Excel 2007 (see Figure 3).

Figure 3: Excel 2007 showingformulas and named ranges.

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

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

The implementation details of this complex formula arestored securely on our company servers using Excel Workbook security and theusual range of industrial-strength storage mechanisms available today. To theaffiliates, we gate access to the computation logic by exposing it through theExcel Web service with a provisioned account. Only authorized affiliates canaccess this Web service. This is the reason for the credentials part of thecode 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 thetrusted location by following the details listed in the second link presentedearlier. Workbooks that do not reside in a trusted location cannot be servicedthrough EWS.

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

Figure 4: Output after calling theExcel Web Services service.

Guidelines for Developing EWS Applications

Let s make a few notes. EWS is noticeably slower than thenative workbook calculation. You can easily get a feel for how much lag isinvolved by timing the call through EWS and comparing it to a Microsoft Excel2007 computation. The lag is to be expected, as there is significant overheadin any SOAP client invocation. You may also notice a lag in the initialrequest. EWS requires a warm up period. The initial performance hit is due tothe 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 theSOAP client altogether and connecting to the Web service locally. Thistechnique is called Direct-Linking and is best suited for real-timecalculations that service financial applications or game programmingapplications. The performance of a direct-linked implementation is equivalentto the calculation response of Excel 2007.

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

We assumed that auto-calculation was turned on. This maynot always be the case in real-world applications especially if thespreadsheet contains a lot of data, formulas, and embedded externalconnections. In this case, you d need to force a calculation using thecalculation method before retrieving the results. Recall that you made a noteof these functions earlier when you added a reference to the ExcelServices Webservice.

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

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

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

Finally, you may be a bit disappointed that EWS does notallow authoring of workbooks. That was never the design intent of the product.Further, legal issues related to SOX compliance impose limitations on the waydata can be created and maintained at the enterprise level. With the currentdesign, 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 spreadsheetfunctionality requires minimal coding effort. The architecture is looselycoupled in a way that allows applications based on Excel Services to scale wellin the enterprise. And while we only endeavored to present one possiblescenario, there are certainly many, many more industry services that canbenefit from this type of architecture.

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

Begin Listing One Accessing data throughthe 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 BasicImports SystemImports System.WebImports ASPHelloPartial 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 SubEnd Class

End Listing One