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):
- Drag select the cell or range of cells to
be named, such as A1 to B8
- Click in the Name box, to the left of the
formula bar.
- Type a name for the list, e.g. Names.
- Press the Enter key on the keyboard.
- 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.
- REST aspx Page URI The entry point to an .aspx
page
- Workbook Location The path to the workbook
- 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: