Office 2007 Download Excel Services in SharePoint

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:

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

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

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

becomes…

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.

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

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:


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




PivotTables:




Charts:




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:
htmlatomimageworkbook
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,Office 2010 Key, /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,Office 2010 Product Key, 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,Office 2010 Key, 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.

is equivalent to

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.

What About Normal Ranges?
The example above showed how to access a named range. It is also possible to access arbitrary, non-named,Office 2007 Professional, 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:

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

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,Office 2007 Download, 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%.

results in the original chart being returned

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

results in an updated chart being returned:

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

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

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

and

and

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.