Access excel workbook content through excel REST API on SharePoint 2013

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 2013.

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. 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. As a result you could use REST API though HTTP, you could call it through any program like PHP, Perl, Java, C#, and javascript.

In this blog, I’ll use a simple C# console application example to use the Excel Services REST API return all data on the PivotTable. It demonstrates a basic pattern for retrieving resources from an Excel workbook on SharePoint Server 2013 using the REST API. The code will use the user name and password to authenticate to SharePoint. The code example retrieves a PivotTable named " ProjectData" from a workbook named "QMET - Project Data.xlsx" that is stored in a Reports library named "Reports" in a ProjectBICenter.

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 for the excel book named "QMET - Project Data.xlsx".
• The forth part is the format you would like to return. The valid formats are html, atom, and json.

This is example of the the REST URL to get PivotTable data as json format.

https://webapp.mycompany.com/projectsite/ProjectBICenter/_vti_bin/ExcelRest.aspx/Sample%20Reports/QMET%20-%20Project%20Data.xlsx/Model/PivotTables('ProjectData')?$format=json

You could directly use the browser to test the URL listed above with different formats and validate the data. Then the following C# code you could use as example to port to other different languages.

using System;

using System.Net.Http;

using System.IO;

using System.Net;

namespace RestExcelClient

{

    class Program

    {

        static void Main(string[] args)

        {

            // REST to get all data from PivotTable named ProjectData

            string jsonRequest = "https://webapp.mycompany.com/projectsite/ProjectBICenter/_vti_bin/ExcelRest.aspx/Sample%20Reports/QMET%20-%20Project%20Data.xlsx/Model/PivotTables('ProjectData')?$format=json";

            // Set up HttpWebRequest call to use local user name and password

            CredentialCache credCache = new CredentialCache();

           // If run on window, you could use the login user account – uncomment the below line

           //credCache.Add(new Uri(jsonRequest), "NTLM", CredentialCache.DefaultNetworkCredentials);

            credCache.Add(new Uri(jsonRequest), "NTLM", new NetworkCredential("username", "passeord", "domain.mycompany.com"));

            HttpWebRequest spRequest = (HttpWebRequest)HttpWebRequest.Create(jsonRequest);

            spRequest.Credentials = credCache;

            spRequest.UserAgent = "Mozilla/4.0+(compatible;+MSIE+5.01;+Windows+NT+5.0";

            spRequest.Method = "GET";

            spRequest.Accept = "application/json;odata=verbose";

            HttpWebResponse endpointResponse = (HttpWebResponse)spRequest.GetResponse();

            try

            {

                // Get HttpWebResponse with Json data as string

                Stream webStream = endpointResponse.GetResponseStream();

                StreamReader responseReader = new StreamReader(webStream);

                string response = responseReader.ReadToEnd();

                Console.WriteLine(response);

                // Next task is to parse the json string result

                //DataContractJsonSerializer jsonSerializer = new DataContractJsonSerializer(typeof(Response));

                //object objResponse = jsonSerializer.ReadObject(response.GetResponseStream());

                responseReader.Close();

            }

            catch (Exception e)

            {

                Console.Out.WriteLine(e.Message);

            }      

        }

    }

}

There are several key configurations we would need to point out here.  

1. Add the Excel Services Application Trusted Data Connection Libraries
2. Add Excel Services Application Trusted File Locations 
3. Make sure the security token app user has access  to the external data
4. Enable the "Data refresh from REST enabled." for the Excel Services Application Trusted File as described in this blog

You might need to parse the result json data as described here. You could use the handy online json viewer to view the json returns.