Using the Excel Services 2010 REST API

  • Article

SharePoint QuickStart Banner

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: Excel Services | Office 2010 | SharePoint Foundation 2010 | SharePoint Server 2010 | Visual Studio 2010

In this article
Discover Items in a Workbook
Retrieve Various Items in Different Formats
Change Data Values
Next Steps

Published:  September 2010

Provided by:   Frank Rice, Microsoft Corporation

Click to view video   Watch the Video

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:

  • Discover Items in a Workbook

  • Retrieve Various Items in Different Formats

  • Change Data Values

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

  1. Open the website containing the workbook in a browser.

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

    http://myserver/mywebsite/_vti_bin/ExcelRest.aspx/Shared%20Documents/Sales_Report.xlsx

  3. After the name of the workbook, add the word Model. The URL should look like the following example.

    http://myserver/mywebsite/_vti_bin/ExcelRest.aspx/Shared%20Documents/Sales_Report.xlsx/Model

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

    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:

  • Image

  • HTML

  • ATOM feed

  • Excel workbook

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

  1. Open a web browser and in the address box, type the following URL and then press ENTER.

    http://myserver/mywebsite/_vti_bin/ExcelRest.aspx/Shared%20Documents/Sales_Report.xlsx/Model/Ranges

    You will see the webpage shown in Figure 2. Notice that the names of four named ranges are displayed.

    Figure 2. Range listings

    Range listings

  2. 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).

    ('RegionalSalesSummary')?$format=HTML

    Figure 3. Regional Sales Summary data

    Regional Sales Summary data

  3. 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).

    http://myserver/mywebsite/_vti_bin/ExcelRest.aspx/Sales%20Reports/Sales_Report.xlsx/Model/Charts('ResellerSalesChart')?$format=image

    Figure 4. ResellersSalesChart chart

    ResellersSalesChart chart

  4. In addition to named ranges, you can also return regular ranges. The following URL returns the range from A1 to D4 on Sheet2.

    http://myserver/mywebsite/_vti_bin/ExcelRest.aspx/Shared%20Documents/Sales_Report.xlsx/Model/Ranges(‘Sheet2!A1|D4’)?$format=HTML

    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

Reseller Sales Chart before the change

To change data values in a chart

  1. Change the URL in the browser to the following, press ENTER, and observe the change to the Retail Chain value in Figure 6.

    http://myserver/mywebsite/_vti_bin/ExcelRest.aspx/Shared%20Documents/Sales_Report.xlsx/Model/Charts('ResellerSalesChart')?Ranges(‘Sheet2!D7')=0.0

    Figure 6. Reseller Sales Chart after the change

    Reseller Sales Chart after the change

  2. You can also place values in different cells by using the following URL.

    http://myserver/mywebsite/_vti_bin/ExcelRest.aspx/Shared%20Documents/Sales_Report.xlsx/Model/Charts('ResellersSalesChart')?Ranges(‘Sheet2!D7')=0.0&Ranges('FiscalYear')=2011

  3. Additionally, you can place values in different cells and specify formatting for each or both by using the following URLs, respectively

    http://myserver/mywebsite/_vti_bin/ExcelRest.aspx/Shared%20Documents/Sales_Report.xlsx/Model/Charts('ResellerSalesChart')?Ranges(‘Sheet2!D7')=0.0&Ranges('FiscalYear')=2011?$format=HTML

    http://myserver/mywebsite/_vti_bin/ExcelRest.aspx/Shared%20Documents/Sales_Report.xlsx/Model/Charts('ResellerSalesChart')?Ranges(‘Sheet2!D7')=0.0&?format=HTML&Ranges('FiscalYear')=2011

    http://myserver/mywebsite/_vti_bin/ExcelRest.aspx/Shared%20Documents/Sales_Report.xlsx/Model/Charts('ResellerSalesChart')?$format=HTML&Ranges(‘Sheet2!D7')=0.0&Ranges('FiscalYear')=2011

Next Steps