Using the Excel Services 2010 REST API
Getting Started with Web Development in SharePoint 2010: Learn how to use the Excel Services REST API to access items and work with data in a spreadsheet.
Applies to: Microsoft SharePoint Foundation 2010 | Microsoft SharePoint Server 2010
Published: September 2010
Provided by: Frank Rice, Microsoft Corporation
The Excel Services 2010 REST API is a new programmability framework that allows for easy discovery of and access to data and objects within a spreadsheet. If you can write a URL, you can use the REST API. This makes the REST API accessible to non-developers, yet it offers capabilities that make it a very powerful tool for professional developers. In this scenario, you will see several examples of using the REST API to access objects and work with data in a workbook that exists on a SharePoint 2010 site. To complete this scenario, do the following:
This scenario assumes that there is a workbook named Sales_Report.xlsx saved in a document library on a SharePoint 2010 site. The workbook contains two ranges, a chart, a table, and a PivotTable.
Discover Items in a Workbook
In this task, you use the REST API to discover various objects in the sample workbook. Assume that the workbook is located at http://myserver/mywebsite/shared%20documents/Sales_Report.xlsx.
To discover items in the workbook
Open the website containing the workbook in a browser.
In the URL in the address box of the browser, add _vti_bin/ExcelRest.aspx/ after the website name so that the URL looks similar the following example.
After the name of the workbook, add the word Model. The URL should look like the following example.
Press ENTER. After a few moments, the browser returns a page similar to that in Figure 1 in an ATOM feed format.
Figure 1. REST API returns this webpage
Currently, the REST API can access ranges, charts, tables, and PivotTables. The REST API returns items in the following formats:
Retrieve Various Items in Different Formats
In this task, you write URLs with REST API notation that return various items. You can use the returned items in webpages, blogs, wikis; embed live charts as a linked image into a Word 2010 document, PowerPoint 2010 presentation, or Outlook 2010 mail; create mash-ups that use Excel Services for calculation, charting, and as a data source.
To retrieve items from the workbook
Open a web browser and in the address box, type the following URL and then press ENTER.
You will see the webpage shown in Figure 2. Notice that the names of four named ranges are displayed.
Figure 2. Range listings
In the browser's address box, append the following to the URL, just after the word Ranges, and then press ENTER. This displays the RegionalSalesSummary range data as HTML (see Figure 3).
Figure 3. Regional Sales Summary data
Discovering and accessing charts and tables is just as simple. Type the following URL into the browser and observe that the ResellerSalesChart chart is displayed as an image (see Figure 4).
Figure 4. ResellersSalesChart chart
In addition to named ranges, you can also return regular ranges. The following URL returns the range from A1 to D4 on Sheet2.
Notice that you need to replace the colon in the A1 style notation for ranges with a pipe character.
Change Data Values
The REST API provides a means to change values in the workbook. These changes only affect the values, HTML rendering info, or charts returned by the REST API for that particular request. These changes are not saved back to the workbook, nor do they affect other REST API or Excel Web Access browser sessions. In this task, you write a URL that will change the sales data for the Retail Chain reseller type from $167,690 to $0 to see the effect on the Reseller Sales Chart. Before the change, the chart (and Retail Chain value) looks like Figure 5.
Figure 5. Reseller Sales Chart before the change
To change data values in a chart
Change the URL in the browser to the following, press ENTER, and observe the change to the Retail Chain value in Figure 6.
Figure 6. Reseller Sales Chart after the change
You can also place values in different cells by using the following URL.
Additionally, you can place values in different cells and specify formatting for each or both by using the following URLs, respectively