Using the Excel Services REST API to Access Excel Data in SharePoint Server 2010

Article
07/24/2014

SharePoint Visual How To

Summary: Learn how to access Microsoft Excel workbook data on Microsoft SharePoint Server 2010 using the Excel Services REST API.

Provided by: Steve Hansen, Grid Logic (Visual Studio MVP)

The Excel Services REST API is a new feature of Excel Services that enables you to access Microsoft Excel workbook data by using a uniform resource locator (URL) address. Using the REST API, you can retrieve resources such as ranges, charts, tables, and PivotTables from workbooks stored on SharePoint Server 2010.

The REST API provides a flexible way to use Excel data. In addition to directly linking to Excel resources using the REST API, you can also make basic HTTP calls programmatically to use Excel data within external programs.

The code in this section shows how to use the Excel Services REST API using a simple console application example.

In the REST API, code is portable. There are no assemblies to distribute, no additional references are required in your project, and you do not have another object model to learn. As long as you understand the URL convention for accessing resources, you only have to build the URL and then call it with a standard HttpWebRequest object.

The following code example is from a simple console application. It demonstrates a basic pattern for retrieving resources from an Excel workbook on SharePoint Server 2010 using the REST API. The code example retrieves a range named "CustomerName" from a workbook named "Invoice.xlsx" that is stored in a document library named "Shared Documents".

using System.Net;
using System.Xml;
using System.IO;
 
static void Main(string[] args)
{
    string rangeName = "CustomerName";
    string value = GetRangeValue("Invoice.xlsx", rangeName);

    XmlDocument doc = new XmlDocument();
    doc.LoadXml(value);
    
    XmlNamespaceManager nsmgr = 
            new XmlNamespaceManager(doc.NameTable);
        
    nsmgr.AddNamespace("x", 
     "https://schemas.microsoft.com/office/2008/07/excelservices/rest");
        
    value = doc.SelectSingleNode("//x:fv", nsmgr).InnerXml;

    Console.WriteLine("The value of {0} is {1}.", rangeName, value);
    Console.WriteLine("Press ENTER to quit.");
    Console.ReadLine();
}

static string GetRangeValue(string workbookName, string rangeName)
{
    string url =
     "http://<server>/_vti_bin/ExcelRest.aspx/Shared%20Documents/" +
      workbookName + "/model/Ranges('" + rangeName + "')?$format=atom";

    HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
    request.Method = "GET";
    request.ContentLength = 0;
    request.ContentType = "text/xml";
    request.Credentials =
         System.Net.CredentialCache.DefaultCredentials;

    using (var response = (HttpWebResponse)request.GetResponse())
    {
        var responseValue = string.Empty;

        if (response.StatusCode != HttpStatusCode.OK)
        {
            string message = String.Format(
                "GET failed. Received HTTP {0}", response.StatusCode);

            throw new ApplicationException(message);
        }

        // Return the response.
        using (var responseStream = response.GetResponseStream())
        {
            using (var reader = new StreamReader(responseStream))
            {
                responseValue = reader.ReadToEnd();
            }
        }
        return responseValue;
    }
}

Be aware that the GetRangeValue() function contains a semi-hard-coded string that is used to build the URL to the resource. To use this code, replace the <server> placeholder with the address of a SharePoint environment.

This section shows you how to correctly construct a REST URL to obtain information from a resource in Excel Services.

The key to using the REST API is in understanding how to construct the correct URL to a resource. A REST URL in Excel Services consists of three required parts and one optional part.

The first part is the location of the ExcelRest.aspx page. ExcelRest.aspx is the entry point to the Excel Services REST API. The second part is the workbook location relative to the ExcelRest.aspx page. The third part is the path of the requested resource inside the workbook. As an example, if the URL to the SharePoint Server is http://intranet.contoso.com and you wanted to retrieve a range named "CustomerName" from a workbook named "Invoice.xlsx" stored in Shared Documents, the URL for this would be as follows.

http://intranet.contoso.com/_vti_bin/ExcelRest.aspx/Shared%20Documents/Invoice.xlsx/model/Ranges('CustomerName')

The optional part of the URL is a format parameter. The REST API can send range values in one of two ways. By default, the REST API returns range information as HTML code as shown in figure 1.

Figure 1. HTML fragment in an Internet Explorer window

Viewing HTML source in Internet Explorer

If you do not specify a format parameter, this is the format that is returned when you request a range resource. It is recommended that you explicitly request the format to avoid confusion. To specify the format, append ?$format=html to the end of the URL. For example, the CustomerName example earlier is as follows.

http://intranet.contoso.com/_vti_bin/ExcelRest.aspx/Shared%20Documents/Invoice.xlsx/model/Ranges('CustomerName')?$format=html

The REST API can also return range information in Atom Syndication Format. As shown in figure 2, this format is not directly accessed by Internet Explorer. However, if you right-click in Internet Explorer and View Source, you can view the data returned.

Figure 2. XML fragment in an Internet Explorer window

Although the Atom format is not immediately consumable in applications such as Internet Explorer, and because it is an XML representation of the data, the Atom format is better suited for programmatic consumption and manipulation. To request a range resource in Atom format, append ?$format=atom to the end of the URL. For example, to retrieve the CustomerName example in Atom format use the following code.

http://intranet.contoso.com/_vti_bin/ExcelRest.aspx/Shared%20Documents/Invoice.xlsx/model/Ranges('CustomerName')?$format=atom

If you are unsure what resources are available in a given format, the REST API includes a discovery mechanism. To view this, you can use a special page called the Model page. For example, to view the model page for an Invoice workbook, the URL is as follows.

http://intranet.contoso.com/_vti_bin/ExcelRest.aspx/Shared%20Documents/Invoice.xlsx/model/

Figure 3. Model page in Internet Explorer

Then, if you click Ranges, the model displays the list of range names in the workbook.

Figure 4. List of range names in a Model page in Internet Explorer

Subscribing to an RSS feed in Internet Explorer.

You can click right through to the range to inspect its value. However, the model view specifies an Atom format as you come to the range level which cannot be displayed directly in Internet Explorer. When you locate the resource to embed or use elsewhere, copy the URL in the address bar of Internet Explorer and then paste it where you want it.

Watch the video

> [!VIDEO https://www.microsoft.com/en-us/videoplayer/embed/b989266d-af0f-44a9-919d-febbfc6c6737]

Length: 00:09:03

About the Author

Steve Hansen, Grid Logic, is an MVP contributor to Microsoft Office. Steve is an Office System Architect at Grid Logic and has created many (50+) videos for Microsoft related to Office development.

Steve has authored many white papers related to Office development and two books─Mastering Excel 2000 Premium Edition and Mastering Excel 2003 Programming with VBA─both published by Sybex.

Using the Excel Services REST API to Access Excel Data in SharePoint Server 2010

Additional resources