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.
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.
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.
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();
// Get HttpWebResponse with Json data as string
Stream webStream = endpointResponse.GetResponseStream();
StreamReader responseReader = new StreamReader(webStream);
string response = responseReader.ReadToEnd();
// Next task is to parse the json string result
//DataContractJsonSerializer jsonSerializer = new DataContractJsonSerializer(typeof(Response));
//object objResponse = jsonSerializer.ReadObject(response.GetResponseStream());
catch (Exception e)
There are several key configurations we would need to point out here.
- Add the Excel Services Application Trusted Data Connection Libraries
- Add Excel Services Application Trusted File Locations
- Make sure the security token app user has access to the external data
- Enable the "blog Excel Services Application Trusted File as described in this
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.