Developing for SharePoint 2010 Excel Services Using Web Services or the Excel Services REST API

Summary:  Learn about the new client services features that are available in Microsoft SharePoint Server 2010, including Word Automation Services and Excel Services.

Applies to: Business Connectivity Services | Office 2007 | Office 2010 | Open XML | SharePoint Designer 2010 | SharePoint Foundation 2010 | SharePoint Online | SharePoint Server 2010 | Visual Studio | Word Autmomation Services

Provided by:  John Peltonen, 3Sharp

Contents

• Microsoft Office 2010 Server Applications Overview

• Using Excel Services

• Excel Web Access Web Part

• Unsupported Client Features

• New Features in Excel Web Services

• User-Defined Functions

• ECMAScript (JScript, JavaScript) Object Model

• REST API

• Setting Values

• Conclusion

• Additional Resources

  Download Excel Services Sample for Microsoft SharePoint Server 2010 (xlrestform.zip)  

Microsoft Office 2010 Server Applications Overview

Users are spending more of their time in the Microsoft SharePoint Server environment and with good reason. As the functionality of this environment expands to include features such as rich social networking capabilities, task and calendar management, workflow, and forms (both no-code and code), SharePoint Server 2010 becomes more of a destination instead of just a data repository. Of course, SharePoint Server has been an excellent tool to store and manage documents produced by the Microsoft Office client applications. SharePoint Server 2010 expands the realm of what users can do with these documents from within the server.

Microsoft Word, Microsoft Excel, Microsoft OneNote, Microsoft Visio, Microsoft PowerPoint, Microsoft Access, Microsoft Project, and, of course, Microsoft InfoPath all have expanded functionality on the server. Word, Excel, OneNote, and PowerPoint have corresponding Web apps that enable users to open and edit documents directly within SharePoint Server without a client. Furthermore, each supports a multiuser coauthoring scenario where more than one user can be editing the same document at the same time.

Visio Services enables users to view Visio diagrams hosted in SharePoint 2010 within a browser. If Microsoft Silverlight is installed, the user will have an interactive click-through experience for panning, zooming, and following hyperlinks. If Silverlight is not installed, the diagram is rendered in PNG format. Furthermore, these diagrams can be data-driven, leading to very rich dashboard-style displays. This feature is used directly in SharePoint 2010 for visual reporting on Microsoft SharePoint Designer workflow status.

Access has always been a powerful desktop application platform. In 2010, Access also becomes a server-side application development platform, as you can now publish Access databases as SharePoint sites. Data, forms, graphics, reports, and even simple logic can all be published into the site. From that point forward, users can access the same data and functionality, either from an Access client or from a browser. This can help to reduce the headache of multiple MDB format versions on desktops and email, as each MDB file is referencing the data and logic hosted within SharePoint Server. User can also manage the look, feel, and behavior of the published site directly through the Access client without involving the IT department.

This article does not address any of these features. Instead, it focuses on very specific, mostly programmatic aspects of Excel Services. There are very powerful additions to Excel Services in SharePoint Server 2010, including two new programmatic models, the ECMAScript (JScript, JavaScript) object model and the Excel Services REST APIs. Word Automation Services is also a new feature that enables Word automation on the server without requiring the Word client application.

For information about developing using Word Automation Services, see Developing with SharePoint 2010 Word Automation Services

Using Excel Services

From the perspective of each Office application, Excel Services provides by far the widest and most varied level of functionality through its services offering. You have three object models to choose from when you develop solutions (each of which are addressed in this article). In addition, great improvements have been made to the Excel Web Access Web Part, which enables much richer user interaction with an entire workbook or a named object. Finally, you can write custom server-side user-defined functions that can be called from a workbook that is hosted on the server. Each of the following sections addresses one of these features.

Excel Web Access Web Part

The Excel Web Access Web Part first appeared in Microsoft Office SharePoint Server 2007. Its goal was to render Excel worksheets with a high degree of fidelity. The Web Part could be pointed at a worksheet that was hosted inside SharePoint Server that was published through Excel Services. Users were presented with a fixed presentation of the targeted content in the worksheet.

Excel Services in SharePoint Server 2010 has expanded on the base functionality that was provided in SharePoint Server 2007 to improve the user experience while interacting with the hosted workbook. First, more of the base-level Excel client features are supported in the Excel Web Access Web Part. The Web Part now supports scrolling. In SharePoint Server 2007, you could select a fixed grid size to expose in the Web Part, but users could not navigate to other areas. Now, if you decide to publish the entire workbook instead of a named object, the workbook is fully available to users. They can scroll within a worksheet or even navigate to other worksheets.

Figure 2 shows a sample Excel Web Access Web Part in use.

Figure 2. Sample Excel Web Access Web Part

As you probably noticed in Figure 2, graphics are also supported. Additionally, users can interact directly with the worksheet values, automatically triggering workbook recalculations. For example, a user can modify the values in the monthly projects table shown in Figure 2 to see how they would affect the chart (mostly off screen to the right). Performance has also improved because the Web Part no longer requires a page refresh to recalculate the workbook. As in SharePoint Server 2007, the Excel Web Access Web Part provides a read-only version of the worksheet, with changes stored only with the user's session.

Unsupported Client Features

In SharePoint Server 2007, if the Excel Web Access Web Part encountered any unsupported features, it would not load the workbook. This led to certain frustrating situations where, for example, a cell comment on Sheet3 prevented someone from publishing a pivot table on Sheet1 through Excel Web Access. The Excel Services team has made some solid progress in this area, by classifying the types of unsupported functionality. Now, cell comments, Microsoft Visual Basic for Applications (VBA) code, and Microsoft Office art (including SmartArt) are ignored when the workbook is open. In addition, QueryTable objects and external workbook references are ignored, but the values that were last rendered by the client application are displayed. Finally, there is still a set of unsupported client functionalities. The SharePoint Server 2007 list, provided in Differences Between Using a Workbook in Excel and Excel Services, describes the unsupported functionality, with the exception of the items mentioned earlier.

Most of the new Microsoft Excel 2010 features are supported to some extent in the Excel Web Access Web Part. This list from the Excel Services Team Blog includes many of the new Microsoft Excel 2010 client features and the level of Excel Web Access support for them:

• Sparklines in Excel

• Icon Improvements in Excel 2010

• Data Bar Improvements in Excel 2010

• Function Improvements in Excel 2010

• Easy (and Even Fun!) Data Exploration: Introducing Excel 2010 Slicers

• PivotTable Named Sets in Excel 2010

• A Few More PivotTable Improvements in Excel 2010

• Introducing PowerPivot

The original list is available on the Microsoft Excel Team Blog.

For more information about Excel Web Access, see the following articles on the Microsoft Excel Team Blog:

• Excel Services 2010 Overview

• Excel Services in SharePoint 2010 Dashboard Improvements

• Excel Services in SharePoint 2010 Administration Improvements

• Excel Services in SharePoint 2010 Feature Support

• Uncovering Publish to Excel Services in Excel 2010

New Features in Excel Web Services

Many of the programmatic Excel Services features, such as the REST API and the ECMAScript (JavaScript, JScript) object model, are new to this release of Microsoft SharePoint Server. However, Excel Services Web services first appeared in Microsoft Office SharePoint Server 2007. In SharePoint Server 2007, you could open a worksheet and then make changes, calculate, and read specific values out of the worksheet. However, you could not save changes (all your changes were transient with your session). And, you could not load charts. These two items represent the two most-requested features of Excel Services Web services. These features and others are now available in SharePoint Server 2010.

Table 1 contains the new methods that are added with this release and comes almost directly from the blog of a senior Excel Services developer, Shahar Prish.

Table 1. List of new methods

Method	Description
GetChartImageUrl	Generates an image based on the specified chart and hosts it for downloading through a temporary URL.
GetPublishedItemNames	Returns the names of the elements in the workbook that have been implicitly or explicitly published. Useful for investigating the structure of a workbook.
GetSheetNames	Returns the names and visibility of worksheets in the workbook.
OpenWorkbookForEditing	Allows the API to join an editing session.
SaveWorkbook	Forces a save of the workbook in editing mode.
SaveWorkbookCopy	Works in editing mode and in view mode; saves a copy of the workbook in SharePoint Server.
SetCalculationOptions	Enables you to change the way the workbook calculates—by turning auto-calc off and on.
SetParameters	Sets a parameter on the workbook. This is especially useful if you want to set page filters that have been designated as parameters, and also when you want to set multiple cells at the same time.

The example shown in Figure 3 uses the GetChartImageURL method and the OpenWorkbookForEditing method. At no point does the sample application launch an explicit Save command (SaveWorkbook). When the workbook is open for editing, changes are written directly to the worksheet, so saves are not necessary. When the workbook is open for editing, the application's credentials appear as a co-author in Excel. This means that if other people are also editing the document, they see that this application is editing it, and they also see the application's changes saved in real time.

The application requires a specific workbook that estimates factory line output based on modifications to the line speed. The application is a simple Windows Forms application that loads the worksheet in read-only mode or read/write mode, lets you adjust the modifier, and then shows the resulting chart of estimated output.

Figure 3. Sample Excel Web Services application

First, you must make a Web reference to the Excel Services Web service. To add the reference, in Microsoft Visual Studio, in Solution Explorer, right-click Service Reference, and then select Add Service Reference.

This opens the Service Reference window. You cannot use this window, because the Excel Services service is not based on Windows Communication Foundation (WCF). Instead, click Advanced to open the Service Reference Settings window, and then click Add Web Reference to load the Add Web Reference window (see Figure 4).

At this point, you can enter the Excel Services URL:

https://server name/_vti_bin/ExcelService.asmx?wsdl

You probably also want to give your reference a friendly name. I chose XLSvc, as shown in Figure 4.

Figure 4. Creating the XLSvc Web reference

The following are the using statements to add. Both of these are for the bookkeeping work of retrieving an image from a URL, which is what GetChartImageURL provides. I first tried to load it directly in the PictureBox control (PictureBox.Load(url)), but I could not find a way to pass my credentials on with the internal URL request from the control. So, instead, I had to do something different, which you will see in the GetImageFromURL helper function in the following example.

// For WebRequest to retrieve the Chart URL.
using System.Net;

// For streams (when converting the image on the end of the URL to an "Image").
using System.IO;

Use the following global variables to track the Excel Services session and, obviously, the Web service itself.

// Track the current session.
string sessionId = string.Empty;

// Excel Services reference.
XLSvc.ExcelService cli;

Open the workbook as read-only.

private void OpenWrite_Click(object sender, EventArgs e)
{
   XLSvc.Status[] status;

   // Close the open workbook.
   if (sessionId != string.Empty) 
   cli.CloseWorkbook(sessionId); 

   // Clear out the session ID.
   sessionId = string.Empty;
            
   // Open the workbook.
   sessionId = cli.OpenWorkbookForEditing(txtURL.Text, "", "", out status);

   // Initialize the form's fields.
   initFields();
}

Initialize the form fields.

private void initFields()
{
   XLSvc.Status[] status;

   // Exit function if no workbook is open.
   if (sessionId == string.Empty) return;

   // Just like in the 2007 release, retrieve the range as an array of arrays.
   object[] rows = cli.GetRangeA1(sessionId, "", "Modifier", true, out status);
   foreach (var o in rows)
   {
      // This is a single cell, so we break after the first row.
      object[] row = (object[])o;
      txtModifierValue.Text = row[0].ToString();
      break;
   }

   // Refactored this out because it is also used by "refresh".
   LoadChart("Chart 2");
}

And finally, load the chart.

private void LoadChart(string chartName)
 {
    XLSvc.Status[] status;
   if (sessionId == string.Empty) return;

   // This call will return a URL.  At the other end of the URL is our image.
   string url = cli.GetChartImageUrl(sessionId, null, chartName, out status);

   // Unfortunately, we cannot just call picturebox.Load because credentials will not be passed.
   pictChart.Image = GetImageFromURL(url);
}

private Image GetImageFromURL(string url)
{
   WebRequest r = WebRequest.Create(url);
   r.Credentials = System.Net.CredentialCache.DefaultCredentials;
   WebResponse response = r.GetResponse();
   Stream s = response.GetResponseStream();
   Image img = Image.FromStream(s);
   return img;
}

If you download the code example (Excel Services Sample for SharePoint Server 2010), you will see that the function to open a file as read-only instead of read/write is almost identical. The only difference is the call to OpenWorkbook instead of OpenWorkbookForEditing.

Download the rest of the code and worksheet to try this functionality (see Excel Services Sample for SharePoint Server 2010). The only piece of functionality that is left is to set the value of the Modifier named range and reload the chart.

cli.SetCellA1(sessionId, "", "Modifier", (object)txtModifierValue.Text);
LoadChart("Chart 2");

This code is exactly the same regardless of whether the workbook has been opened for editing. The only difference is that in the default read-only mode, the modification happens on a transient session-based copy of the workbook, whereas in edit mode, the change is written back to the main copy in SharePoint Server.

As you can see, the Web services capabilities are expanded quite a bit to allow for even more powerful scenarios. For more information about this feature, read three of Shahar Prish's current blog posts:

• What's new in Excel Web Services in SharePoint 2010

• Using the Web Services APIs to open a workbook for editing and set calculation options

• Using SetParameters with the Excel Web Services APIs

In addition, read Excel Web Services in the SharePoint 2010 SDK.

User-Defined Functions

Occasionally, Excel users may have to use functions that are not native to Excel. The Excel client application has been extensible in this way for many releases, allowing developers to create an XLL add-in to surface custom functions in the Excel client. In SharePoint Server 2007, Excel Services also provided support for server-side user-defined functions. This support has continued in SharePoint Server 2010. Using managed code, you can build a server-side user-defined function to expose custom functions that users can use the same way that they can use built-in functions, such as =SUM() or RAND().

These functions are available only on the server, so workbooks that are edited in the client do not have access to them. They can be deployed through SharePoint solutions and can be quite powerful in that they can access third-party data sources and Web services.

For a detailed walkthrough of creating and deploying a user-defined function, see Excel Services User-Defined Functions in the SharePoint 2010 SDK.

ECMAScript (JScript, JavaScript) Object Model

The JavaScript object model is another new feature of Excel Web Services for SharePoint Server 2010. It is meant to enable developers to programmatically interact with the Excel Web Services Web Parts that are hosted on a given SharePoint page. By writing JavaScript that is hosted on the SharePoint page (either directly or through a Content Editor Web Part), developers can interact with the Excel Web Access Web Parts and monitor the user's interaction with them.

Using the JavaScript object model, you can set and retrieve values of cells, either through addressed or named ranges, and navigate users to different locations within the hosted workbook. You can also monitor events that occur when users add content to or edit cells.

The following example walks you through an JavaScript file that sets the value of a cell (Sheet1!B3) with the value of the cell that the user selects.

To begin, you must take care of some general Excel Web Access maintenance. Specifically, you must retrieve a reference to the Excel Web Access Web Parts on the page. Of course, first you must let the page load and the Web Parts initialize. So, you capture the OnLoad event, initialize it, and watch for the Excel Web AccessapplicationReady event. When you know that Excel Web Access is ready, enumerate the controls collection for all control instances. In this case, you are finding only the first instance.

<script>
    var ewa = null;

    // Set page event handlers for onload and unload.
    if (window.attachEvent) {
        window.attachEvent("onload", ewaOmPageLoad);
    }
    else {
        window.addEventListener("DOMContentLoaded", Page_Load, false);
    }
 
    // Load map.
    function ewaOmPageLoad() {
        if (typeof (Ewa) != "undefined") {
            Ewa.EwaControl.add_applicationReady(ewaApplicationReady);
        }
        else {

            // Error - the Excel Services Web Access ECMAScript is not loaded.
        }
    }

Now, you can capture the various Excel Web Access Web Parts on the page. You can also set event handlers for whatever events you want to start watching on that particular control. In this case, you watch the activeCellChanged event.

function ewaApplicationReady() {
        
        // Find the first Excel Web Access control on the page.
        ewa = Ewa.EwaControl.getInstances().getItem(0);

        // Add a cell changed event handler to the script.
        ewa.add_activeCellChanged(cellChanged);

    }

When the cell changes, you must first determine where its coordinates are (sheet, row, and cell). This can come in handy when you are trying to determine if the user has selected important data that you must respond to, or random cells that do not matter. In this case, you take the value of any cell that they select and insert it into Sheet1!A3. This code does not assume that the user has selected only a single value. Of course, the user could also select a range.

When you have the details (coordinates and range), you call into the workbook to asynchronously set the value of the target range. The getRangeA1Async event expects the target range, the callback function, and the value that you want to put in the range. When the callback is executed, it is passed an asyncResult variable that contains the target range and the value. This means that you can call this function many times without having to worry about tracking what is going where.

    function cellChanged(rangeArgs) {

        // Find the sheet, column, and row the range starts in.
        var sheetName = rangeArgs.getRange().getSheet().getName();
        var col = rangeArgs.getRange().getColumn();
        var row = rangeArgs.getRange().getRow();
        
        // Making the assumption that this is a single cell, but it does not have to be.
        var value = rangeArgs.getFormattedValues();

        // Async call to set ranges.  We pass the range, the call-back function, and the value we want to set.
        ewa.getActiveWorkbook().getRangeA1Async("Sheet1!B3", getRangeComplete, value);
    }

The following is the asynchronous callback. You just pull out the range and value from asyncResult and convert the value into an array (remember, I am making an assumption that the user will only select a single cell and not a range of cells).

function getRangeComplete(asyncResult) {
    
        // Find the range we are going to set.
        var range = asyncResult.getReturnValue();

        // Find the value we will put in the range.
        var value = asyncResult.getUserContext();

        // Assuming it is a single item (convert to a double array).
        var values = [[value]];

        // Set the value (again, asynchronously).
        range.setValuesAsync(values, setValuesCallBack, null);
    }

Finally, you catch the callback from the set values call. There are not many interesting things for you to do, but this can come in handy if you are chaining actions.

function setValuesCallBack(returnValues) {
        // Nothing really interesting here. Just notifying the user that something happened.
        window.status = 'Values Set';
    } 
</script>

That may have been a good walkthrough, but it still is not enough to get you going. First, you need to determine how to get the script inside the SharePoint page. Here is one way to do it: Save the script to an HTML file (after you edit it in Microsoft Visual Studio), upload it to a document library (my very creative choice of Shared Documents should not surprise you), and then add a Content Editor Web Part to the page and point it to the HTML file in Shared Documents, as shown in Figure 5. You can also put the script directly in the Content Editor Web Part.

Figure 5. Content Editor Web Part properties

Also, remember that Windows Internet Explorer now includes a good developer tool that you can use to debug JavaScript on the page, including adding watches and putting break points in (see Figure 6). To load the tool, in Internet Explorer, press F12, or select Developer Tools on the Tools menu.

Figure 6. Developer Tools script debugger

Although this code was added by using the Content Editor Web Part, it is run as part of the page. Here you can see that the user has selected the cell on Sheet1, row 8, and column 3.

REST API

The Excel SOAP-based Web services are a very powerful way to programmatically interact with Excel worksheets. However, they offer absolutely no way for power users to consume Excel objects. For example, it would be nice for a user to be able to embed a chart in an internal blog post or wiki and have that Web page automatically update whenever it is loaded or refreshed. Likewise, it would be even better if that power user could update worksheet parameters when referring to that chart, to have a specific what-if scenario generated and hosted directly in a Word document—to be refreshed whenever the document is opened or saved.

From a developer's perspective, one can argue that the Excel Services REST API is not a true REST implementation. However, regardless of the true conformance of the protocol, it is a powerful implementation of an API that gives developers and power users access to the objects in the workbook through nothing more than a URL.

For example, the following URL returns all discoverable components of the Book1.xlsx workbook that is stored in the Shared Document library of the top-level site at http://intranet.contoso.com.

http://intranet.contoso.com/\_vti\_bin/ExcelRest.aspx/Shared%20Documents/book1.xlsx/model $format=atom

This URL may seem overwhelming, but when you break it into its composite parts, it is quite simple.

Table 2. URL components

URL Component	Description
http://intranet.contoso.com	The path to the site or subsite where the workbook resides.
/_vti_bin/excelrest.aspx	The relative path to the Web service.
/shared%20documents/book1.xlsx	The relative path to the workbook.
/model$format=atom	The command or commands for the Web service. In this case, you are requesting a "model" of the workbook in an ATOM feed.

In addition to just listing the items in a workbook, you can use the REST service to retrieve raw data or images of tables and charts. You can also interact with the data in a workbook, setting values of specific ranges that can affect the tables and charts that you are retrieving. Table 3 lists all the commands that you can send to the REST service and the formats that work with each.

Table 3. Lists of commands

Command	Atom	Workbook	Image	HTML	Action
/model	Default	Yes			Returns all discoverable items in the workbook, or returns the workbook itself.
/model/Charts	Default			Default	Lists all charts.
/model/Charts('name')	Yes				Returns the specified chart.
/model/Ranges	Default				Lists all named ranges.
/model/Ranges('name')	Yes			Default	Returns the specified range. This can be a named range or an arbitrary range.
/model/Tables	Default				Lists all tables.
/model/Tables('name')	Yes			Default	Returns the specified table.
/model/PivotTables	Default				Lists all pivot tables.
/model/PivotTables('name')	Yes			Default	Returns the specified pivot table.

Because the colon (":") character is a special character in a URL, the normal range declarations, such as Sheet2!A1:B3, are not valid. The Excel REST API expects a pipe symbol ("|") instead of the colon, such as "Sheet2!A1|B3"

Setting Values

Although the REST API does not let you modify the saved instance of the worksheet on the server, you can modify ranges in the in-memory instance that is being used by the query. For example, you can set the value of a range that specifically modifies a chart that you are querying. The following URL sets the value of a range and requests an image of a chart.

http://intranet.contoso.com/\_vti\_bin/ExcelRest.aspx/Shared%20Documents/book1.xlsx/model/Charts('sales')?Ranges('SalesYear')=2009?$format=image

Users can host this content in any environment that can show text or images from a Web source. Other than the obvious blogs and wikis, one very relevant source in the context of this article is Microsoft Word.

Although you may not know what a Microsoft Word field code is, you have used one if you have ever inserted a table of contents or a hyperlink into a document. A field code is a way of representing any number of dynamic types of content in a document's body. To see the entire list of field codes, on the Insert tab on the ribbon, select Quick Parts, and then select Field, as shown in Figure 7.

Figure 7. Accessing the entire list of field codes

There are two special field codes, IncludePicture and IncludeText, that embed text or images from a file or URL. Word keeps this content up to date by periodically refreshing it (on Open, Save, and so on).

To include an up-to-date image of a chart in a workbook

1. Create the REST URL, such as the following:

   http://intranet.contoso.com/\_vti\_bin/ExcelRest.aspx/Shared%20Documents/book1.xlsx/model/Charts('Chart%205')?$format=image

2. On the Insert menu, select Quick Parts, and then select Field.

3. Select the IncludePicture field, paste your URL in the Filename or URL field, and then click OK (see Figure 8).

   Figure 8. Adding the IncludePicture field

   

Inserting text is similar, but it is slightly more complicated because the data is not the raw data that you are probably looking for. It is either in an ATOM feed or formatted as HTML. Neither are very good options for a Word document. Fortunately, this field code lets you include either an XSL transform or an XPath query as part of the field code. By writing either an XSLT transformation or XPath query to query the ATOM feed presented, you can select the relevant pieces of data and include them in the document.

This sample ATOM feed represents the results of the following query.

http://intranet.contoso.com/_vti_bin/ExcelRest.aspx/Shared%20Documents/book1.xlsx/model/Ranges('Sheet1!F1')?$format=atom
<?xml version="1.0" encoding="utf-8"?>
<entry xml:base="http://excel.live.com/REST" xmlns:x="https://schemas.microsoft.com/office/2008/07/excelservices/rest" xmlns:d="https://schemas.microsoft.com/ado/2007/08/dataservice" xmlns:m="https://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://www.w3.org/2005/Atom">
    <title type="text">Sheet1!F1</title>
    <id>http://intranet.contoso.com/_vti_bin/ExcelRest.aspx/Shared%20Documents/book1.xlsx/model/Ranges('Sheet1!F1')</id>
    <updated>2009-12-12T22:23:39Z</updated>
    <author>
        <name />
    </author>
    <link rel="self" href="http://intranet.contoso.com/_vti_bin/ExcelRest.aspx/Shared%20Documents/book1.xlsx/model/Ranges('Sheet1!F1')?$format=atom" title="Sheet1!F1" />
    <category term="ExcelServices.Range" scheme="https://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
    <content type="application/xml">
        <x:range name="Sheet1!F1">
            <x:row>
                <x:c>
                    <x:v>0.05</x:v>
                    <x:fv>5%</x:fv>
                </x:c>
            </x:row>
        </x:range>
    </content>
</entry>

In addition to the Filename or URL field property of the InsertText field, you must update two field options:

• Namespace Mappings

  There are two namespaces used in the XML you will be querying: the ATOM feed namespace and the Excel Services REST namespace. The following string will define those and create namespace mappings that the XPath query will reference.

  xmlns:a="http://www.w3.org/2005/Atom"

  xmlns:x="https://schemas.microsoft.com/office/2008/07/excelservices/rest"

• XPath

  Fortunately, the XPath is relatively simple. The <fv> element contains the formatted value of the cell. The following XPath retrieves it.

  /a:entry/a:content/x:range/x:row/x:c/x:fv

  Figure 9. Adding the InsertText field

  

Clearly this is not something that you would ever expect anyone but the most experienced users to do. However, it can be very powerful when you consider a server-side document-generation scenario with the Office Open XML formats (where you can manipulate these field codes programmatically), or a client-side Microsoft Office development tools in Microsoft Visual Studio add-in, where you are programmatically adding the field code while the application is running.

Naturally, you can also programmatically access and manipulate the ATOM feeds. The following code example is part of the sample application that is available to download (Excel Services Sample for SharePoint Server 2010). The sample application queries a worksheet that is located on a top-level site, enumerates its named ranges, and then shows the value of a selected range.

Figure 10. Sample application to list named ranges in a worksheet

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

// Need this to process the ATOM Feed.
using System.Xml.Linq;

// Specifically for the XMLUrlResolver to pass credentials to the Web service.
using System.Xml;

// There are streams ahead.
using System.IO;    

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {

        // Declare all my namespaces up top so that I can just reference a short variable name down below :)
        const string atomNameSpace = "http://www.w3.org/2005/Atom";
        const string xlsvcNameSpace = "https://schemas.microsoft.com/office/2008/07/excelservices/rest";



        private void button1_Click(object sender, EventArgs e)
        {

            // As long as we have a server name and relative path to the worksheet, call the Web service.
            if(txtSite.Text != string.Empty & txtSpreadsheet.Text != string.Empty)
                LoadRanges();
        }
private void LoadRanges()
        {
            string relativeUri;
            XNamespace a = atomNameSpace;
            Stream s;

            // Build the relative URL for the Excel REST Web service.
            relativeUri = "/_vti_bin/ExcelRest.aspx/" + txtSpreadsheet.Text + "/model/Ranges?$format=atom";

            // Pass the server portion of the URL and the relative URL down
            // and receive a stream with the ATOM feed results.
            s = GetAtomResultsStream(txtSite.Text, relativeUri);

            // Load the stream into an XDocument.
            XDocument atomResults = XDocument.Load(s);

            // Query the XDocument for all title elements that are child elements of an entry element.
            IEnumerable<XElement> ranges =//= atomResults.Root.Descendants(a + "title")
                from t in atomResults.Descendants(a + "title")
                where t.Parent.Name == a+"entry"
                select t;
                

            // Add all the elements that we found to the listbox.
            foreach (XElement r in ranges)
            {
                listBox1.Items.Add((string)r);
            }
                     
        }

            private Stream GetAtomResultsStream(string serverName, string relativeUri)
            {
                
                XNamespace a = atomNameSpace;

                // I use the XMLUrlResolver to pass credentials to the Web service.
                XmlUrlResolver resolver = new XmlUrlResolver();
                resolver.Credentials = System.Net.CredentialCache.DefaultCredentials;

                // Build the URI to pass the resolver.
                Uri baseUri = new Uri("http://" + serverName);
                Uri fullUri = resolver.ResolveUri(baseUri, relativeUri);

                // Mostly for debugging, display where we are going.
                lblUrl.Text = fullUri.ToString();

                // Call the resolver and receive the ATOM feed as a result.
                Stream s = (Stream)resolver.GetEntity(fullUri,null,typeof(Stream));

                return s;
            }

    }
}

The Excel REST Web service is deceptively powerful. It can span a large range of user needs and solution scenarios—from the very basic task of embedding a URL into a Web page that shows the most up-to-date data available to a custom Microsoft .NET Framework solution.

For more information about the Excel REST API, see the following resources:

• Shahar Prish's blog, Cum Grano Salis

  • Welcome to the new Excel Services! (the first part of a multipart series about REST)

  • Coding the Excel Services Windows 7 Gadget – Part 1 - Settings (the first part of a seven-part series about coding a desktop gadget that consumes data from Excel Services)

• Microsoft Excel Team Blog

  • Simple Access to Spreadsheet Data Using the Excel Services 2010 REST API

  • Excel Services in SharePoint 2010 REST API Syntax

  • Excel Services in SharePoint 2010 REST API Examples

• Excel Services REST API in the SharePoint 2010 SDK

Conclusion

This article presents an intense review of some very powerful new features of the client services provided in Microsoft SharePoint Server 2010. You have many options when thinking about the architecture of your next application—the ability to programmatically render, print, and convert Word documents without the Word client; the ability to easily embedding live Excel charts in a Web page or document; and multiple ways of programmatically manipulating Excel workbooks. Hopefully, you will be ready to try them and your choice of which to use will be easier.

Of course, do not think that one feature must be used independently from another or from other client and server-side features. Nothing is stopping you from creating a document generation solution that programmatically inserts live Excel charts from their ATOM feeds, renders the document by using Word Automation Services, and then converts the document to a PDF. In fact, that sounds exciting to me. I think I know what my next project is going to be.

Additional Resources

For more information, see the following resources:

• Excel Services Sample

• Word Automation Services in SharePoint Server 2010

• Developing with SharePoint 2010 Word Automation Services

• Word Team Blog: Introducing Word Automation Services

• Excel Team Blog: Excel Services 2010 Overview

• Shahar Prish's Blog: What's new in Excel Web Services in SharePoint 2010

• SharePoint Developer Center

• Welcome to the Microsoft SharePoint 2010 SDK

• Office Developer Center