Back
Excel

Excel Services in SharePoint 2010 REST API Syntax

Thanks to Christian Stich for putting together this series on the REST API. 

As we saw in the previous post, using the Excel Services REST API is as simple as specifying a URI (Uniform Resource Identifier) in your web browser.  In this post we’ll walk through the details of the URI syntax.

We start out with a simple Excel workbook stored on a SharePoint 2010 server at the following URL:

http://contoso/Finance/Shared Documents/Tax.xlsx

This example workbook contains two named ranges, a chart, a table and a PivotTable.

In order to create a URI that can be used to access the REST API we insert _vti_bin/ExcelRest.aspx/ in the above URL behind the site part (in this case /Finance).

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared Documents/Tax.xlsx

Discovering Items in a Workbook

Next we add /Model behind the filename (in this case /Tax.xlsx)

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared Documents/Tax.xlsx/Model

We now have a URI that can be used to discover the items in the workbook – the REST API returns an ATOM feed that contains the types of items that are accessible – currently ranges, charts, tables and PivotTables are supported.

image

Note: In the case where the workbook resides in the root site of the SharePoint server then you would insert the _vti_bin/ExcelRest.aspx/ behind the server name.

http://contoso/Shared Documents/Tax.xlsx

becomes…

http://contoso/_vti_bin/ExcelRest.aspx/Shared Documents/Tax.xlsx/Model

Retrieving Items in a Workbook

We can get a list of the named ranges in the workbook by simply appending /Ranges to the previous URI or you can click the link for Ranges that was returned in the previous example.

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared Documents/Tax.xlsx/Model/Ranges

The REST API returns an ATOM feed that contains the named ranges in the workbook – this workbook contains two named ranges: TaxRate and Revenue.

image

We can also get also get an HTML representation of a range by specifying the range using /Ranges(‘range name’) and by adding ?$format=HTML.

To get the HTML representation of the name range Revenue we specify the following URI:

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared Documents/Tax.xlsx/Model/Ranges(‘Revenue’)?$format=HTML

image

Discovering and accessing tables, PivotTables and charts is similar to discovering and accessing ranges as shown above.

Tables:

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared Documents/Tax.xlsx/Model/Tables

image

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared Documents/Tax.xlsx/Model/Tables(‘RevenueTable’)?$format=html

image

PivotTables:

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared Documents/Tax.xlsx/Model/PivotTables

image

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared Documents/Tax.xlsx/Model/PivotTables(‘PivotTable’)?$format=html

image

Charts:

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared Documents/Tax.xlsx/Model/Charts

image 

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared Documents/Tax.xlsx/Model/Charts(‘RevenueChart’)?$format=image

image

Supported REST API Return Formats

Notice in the previous paragraph that we added a query string parameter to the URI in the previous examples where we were accessing the actual items. This query string parameter is used to specify the format that the REST API should return. The currently supported formats are:

  • html
  • atom
  • image
  • workbook

When ?$format=html is specified the REST API returns an HTML representation of the item that can be displayed by the web browser.

When ?$format=atom is specified the REST API returns an ATOM feed representation of the item.

When ?$format=image is specified the REST API returns an image file (at this time the format is always PNG).

I’ll cover the ?$format=atom return format in a future post on the REST API. At that time we will also address the differences between the ATOM feeds for /Ranges, /Tables and /PivotTables.

When ?$format=workbook is specified the REST API returns the workbook which can then be opened in Excel or saved. The format of the workbook being returned matches the format of the original file – if the original file is in .xlsx format, then the workbook being returned will be in .xlsx format; if the original file is in .xlsb format, then the workbook being returned will be in .xlsb format. Same for .xlsm format.

Which Requests Support Which Formats?

Not all formats are supported with all items. Moreover, different types of REST API requests have different default values. For example /Charts(‘chart name’) default return format is image – this means that you don’t have to specify ?$format=image as part of the URI. Similarly, with /Ranges(‘range name’) you can omit ?$format=html if that is the format you would like to be returned.

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared Documents/Tax.xlsx/Model/Charts(‘RevenueChart’)

is equivalent to

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared Documents/Tax.xlsx/Model/Charts(‘RevenueChart’)?$format=image

Both URIs return an image in PNG format.

Below is a matrix that shows the supported formats for different types of REST API requests. Specifying ?$format=… allow you to override the default return format with another supported format.

 image

What About Normal Ranges?

The example above showed how to access a named range. It is also possible to access arbitrary, non-named, ranges using A1-style notation. The following URIs show how it’s done for accessing single cell ranges and multiple cell ranges.

To get an HTML representation of the content of cell A1 on Sheet1:

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared Documents/Tax.xlsx/Model/Ranges(‘Sheet1!A1’)?$format=HTML

To get an HTML representation of the content of the range A1:D4 on Sheet2:

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared Documents/Tax.xlsx/Model/Ranges(‘Sheet2!A1|D4’)?$format=HTML

NOTE: you need to replace the colon in the A1 style notation for ranges with a pipe character when used with the REST API -> the range A1:D4 is entered as A1|D4

Inserting Values

The Excel Services 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.

Setting values is simple and can be done by specifying single cells using A1 style notation, or by specifying a single cell named range or a parameter.

In the following example, we have a workbook with a named range called “Tax Rateâ€, which is currently set to 10%.

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared Documents/Tax.xlsx/Model/Charts(‘RevenueChart’)

results in the original chart being returned

image

Changing the Tax Rate to 50% in the workbook by adding ?Ranges(‘TaxRate’)=0.50

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared Documents/Tax.xlsx/Model/Charts(‘RevenueChart’)?Ranges(‘TaxRate’)=0.50

results in an updated chart being returned:

image

We could also have used A1 style notation to specify the cell to be changed (‘TaxRate’ has the A1 style address ‘Sheet1!B1’)

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared Documents/Tax.xlsx/Model/Charts(‘RevenueChart’)?Ranges(‘Sheet1!B1’)=0.50

It is also possible to set multiple values into different cells. Additional query string parameters are separated by the ampersand & character.

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared Documents/Tax.xlsx/Model/Charts(‘RevenueChart’)?Ranges(‘TaxRate’)=0.50&Ranges(‘FiscalYear’)=2008

And you can combine this with specifying the return format – the order of the query string parameters does not matter. For example:

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared Documents/Tax.xlsx/Model/Charts(‘RevenueChart’)?Ranges(‘TaxRate’)=0.50&Ranges(‘FiscalYear’)=2008&$format=html

and

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared Documents/Tax.xlsx/Model/Charts(‘RevenueChart’)?Ranges(‘FiscalYear’)=2008&$format=html&Ranges(‘TaxRate’)=0.50

and

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared Documents/Tax.xlsx/Model/Charts(‘RevenueChart’)?$format=html&Ranges(‘FiscalYear’)=2008&Ranges(‘TaxRate’)=0.50

are all equivalent and return the same result.

More To Come

This concludes our walk through the REST API syntax.  You should now have enough knowledge to start building REST-based solutions with Excel Services.  In my next post I’ll showcase a few simple scenarios that you can quickly build yourself.