SharePoint 2010 Excel REST Services

The ability to access Excel Services via the REST protocol is a really welcome addition that can be used in a whole range of applications on SharePoint.

Essentially the REST protocol allows you to access and interact with items within an Excel spreadsheet using simple URL strings. The reason that this is so powerful within SharePoint is that you can easily create these URL strings either in InfoPath or through configuring web parts in SharePoint.
Not only that it is a great way to expose real time information to users on pages, you simply insert the URL of the REST spreadsheet you are trying to view and you have dynamic information at your fingertips!

Possible Uses

So what use can this be? Actually there is a range of applications that can leverage REST:

• Embedding a Chart or calculated values from a spreadsheet directly on a SharePoint page
• Using REST services to perform intelligent calculations on data passed in from a InfoPath form
• Embedding Charts on the front page of a document set from a Excel spreadsheet stored within the set

Using REST

Lets assume that we have a document located at http://mpsp2010/ES/Shared%20Documents/Calculation.xlsx.

Basically to access a spreadsheet you need to add a /_vti_bin/ExcelRest.aspx/ after the location of the site and a /model at the end of the URL to access the spreadsheet.

So for our example above the string to access our spreadsheet via rest would be:

http://mpsp2010/ES/_vti_bin/ExcelREST.aspx/Shared%20Documents/Calculation.xlsx/model

The REST API will allow you to access the following elements but remember that to access any of these things that they have to be named in the spreadsheet (this does not apply to cell ranges however):

• Ranges ( A single or range of cells, these do not have to be named)
• Charts
• Tables
• PivotTables

Retrieving Items

Items can be easily retrieved depending on the type of item and the name associated with them.

• Ranges: /Model/Ranges(‘Range Name’)
• Charts: /Model/Charts(‘Chart Name’)
• Tables: /Model/Tables(‘Table Name’)
• PivotTables: /Model/PivotTables(‘Pivot Table Name’)

For named items simply add (‘item name’) to the appropriate element that you are looking for. Lets try to access our items from our base URL:

Item Type	Item Name	Generic URL	URL
Range	Principal	/Model/Ranges(‘Range Name’)	http://mpsp2010/ES/_vti_bin/ExcelREST.aspx/Shared%20Documents/Calculation.xlsx/Model/Ranges(‘Principal’)?$format=html
Chart	Chart 3	/Model/Charts(‘Chart Name’)	http://mpsp2010/ES/_vti_bin/ExcelREST.aspx/Shared%20Documents/Calculation.xlsx/Model/Charts(‘Chart%203′)?$format=image
Tables	Table1	/Model/Tables(‘Table Name’)	http://mpsp2010/ES/_vti_bin/ExcelREST.aspx/Shared%20Documents/Calculation.xlsx/model/Tables?$format=atom
PivotTables	PivotTable1	/Model/PivotTables(‘PivotTiable Name’)	http://mpsp2010/ES/_vti_bin/ExcelREST.aspx/Shared%20Documents/Calculation.xlsx/model/PivotTables?$format=atom

However in the case of the Range element you don’t necessarily need to have a named range but can use the traditional

Retrieving Ranges

An exception to having a named item in a workbook is for ranges. For ranges you can use similar notation as for Excel.

To get a single cell value at A3 you would use: /Model/Ranges(‘A3’)

To get a range in Excel of A3:A6 you would use: /Model/Ranges(‘A3|A6)
Note: You have to use the | character instead of the : character

Formats

The REST API can retrieve items in different formats that include:

• Image: Always a PNG image is returned
• HTML: HTML representation that can be viewed in a browser
• ATOM feed: ATOM feed representations
• Excel workbook: Returns the workbook

By default REST returns items by ATOM Feed, to change the format that it returns add the following to the end of your URL:

• ?$format=image
• ?$format=atom
• ?$format=html
• ?$format=workbook

Arguments to REST

One of the best things about REST however is that you can also send arguments within the URL, let the spreadsheet do the calculations and then get your value back! This can allow you to do some complex calculations using a spreadsheet hosted on SharePoint and then get the value back.

The default syntax for inserting arguments is to add your item type, item name and equals value as query string parameters.

?Ranges(‘Range Name’)=value

So if I wanted to retrieve the range named total, whilst passing in a range called Principal to a value of 5000 I would construct the following string:

http://mpsp2010/ES/_vti_bin/ExcelREST.aspx/Shared%20Documents/Calculation.xlsx/Model/Ranges(‘Total’)?Ranges(‘Principal’)=50000

This would return:

If I wanted to pass in 100000 to the principal and set the months the 10 and return the total my query string would be:

http://mpsp2010/ES/_vti_bin/ExcelREST.aspx/Shared%20Documents/Calculation.xlsx/Model/Ranges(‘Total’)?Ranges(‘Principal’)=100000&Ranges(‘Months’)=10

Now what if I wanted to pass in these values yet see my Chart (named Chart 3). In this case I want keep my arguments but change what I am retrieving to Chart 3:

http://mpsp2010/ES/_vti_bin/ExcelREST.aspx/Shared%20Documents/Calculation.xlsx/Model/Charts(‘Chart%203′)?Ranges(‘Principal’)=100000&Ranges(‘Months’)=10

Pretty cool! You can quickly see how powerful REST can become!

Laymans Terms

Basically I remember REST API calls this way:

• Anything after /Model/ and before the ? character is what you want to get back
• Anything after the ? character are arguments that you are passing in
• Separate arguments with the & operator

So there we go a quick tour of REST.

Tags: Excel, post-wide, REST Services

Category: General