REST API in SharePoint 2010 for Excel Services: Part 1

Introduction:

The REST API in Excel Services is new in Microsoft SharePoint Server 2010. REST API is used to access workbook parts or elements directly through a URL. This will also enable developers and users to explore the content of the workbook manually or programmatically by supplying an Atom feed that contains information about the elements that reside in a specific workbook. Atom is an XML-based document format that describes lists of related information known as "feeds". Feeds are composed of a number of items, known as "entries", each with an extensible set of attached metadata. For more information on Atom refer http://tools.ietf.org/html/rfc4287. A REST API for Excel Services enables operations against Excel workbooks by using operations specified in the HTTP standard. This allows for a flexible, secure, and simpler mechanism to access and manipulate Excel Services content.

Excel Workbook:

I have an excel workbook "ExcelChart" which contains the following

Sample ("Sheet1" is renamed as Sample) Sheet:

Named Range (Names):

1. Drag select the cell or range of cells to be named, such as A1 to B8
2. Click in the Name box, to the left of the formula bar.
3. Type a name for the list, e.g. Names.
4. Press the Enter key on the keyboard.
5. The name appears in the Name box.

Chart:

Table:

Pivot Table:

Pivot Chart:



Discovering Items in a workbook:

I have uploaded the excel workbook to the "Shared Documents".



Now using REST API I am going to access the excel workbook.

URL Structure:

We can access resources like charts, PivotTables, tables, and named ranges in a workbook directly through a URL using REST API. Each REST URL in Excel Services is built of three parts.

1. REST aspx Page URI The entry point to an .aspx page
2. Workbook Location The path to the workbook
3. Resource Location The path to the requested resource inside the workbook

Example:

http://servername:1111/sites/VJTesting/_vti_bin/excelrest.aspx/Shared%20Documents/ExcelChart.xlsx/model
REST aspx Page URI - http://servername:1111/sites/VJTesting/_vti_bin/excelrest.aspx

Workbook Location - /Shared%20Documents/ExcelChart.xlsx

Resource Location- /model

We can use the resource location to get the particular element from the excel workbook.



Ranges:

In this section we will be seeing how to retrieve the items from named range using the REST URL.

REST URL:

http://serverName:1111/sites/VJTesting/_vti_bin/excelrest.aspx/Shared%20Documents/ExcelChart.xlsx/model/Ranges

http://serverName:1111/sites/VJTesting/_vti_bin/excelrest.aspx/Shared%20Documents/ExcelChart.xlsx/model/Ranges('Names')

REST aspx Page URI - http://servername:1111/sites/VJTesting/_vti_bin/excelrest.aspx

Workbook Location - /Shared%20Documents/ExcelChart.xlsx

Resource Location- /model/Ranges('Names')

The output will be as shown in the following



We can also access the ranges using the following REST URL
http://serverName:1111/sites/VJTesting/_vti_bin/excelrest.aspx/Shared%20Documents/ExcelChart.xlsx/model/Ranges('A1|B8')

I have created one more sheet in my excel workbook and I made Sheet2 as default as shown in the following



I have uploaded the same in the "Shared Documents".

Now if I try to retrieve the items with the same REST URL

http://serverName:1111/sites/VJTesting/_vti_bin/excelrest.aspx/Shared%20Documents/ExcelChart.xlsx/model/Ranges('A1|B8') the output will come from the Sheet2 only as shown in the following( because Sheet2 is the default sheet in the excel workbook).



How to retrieve the items from the particular range using REST URL from different Sheets:

In my Excel workbook Sheet1 is named as "Sample" and Sheet2 as "Sheet2". Sheet2 is the default one. Now I am going to retrieve the items from "Sample" (Sheet1) sheet.

REST URL:

http://serverName:1111/sites/VJTesting/_vti_bin/excelrest.aspx/Shared%20Documents/ExcelChart.xlsx/model/Ranges('Sample!A1|B8')

The output looks like the following: