Simple Access to Spreadsheet Data Using the Excel Services 2010 REST API

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

Beginning our journey on all things Excel Services 2010, I thought I’d start off with a feature that’s been getting a lot of buzz lately since its introduction at the SharePoint Conference.

What is it?

The Excel Services 2010 REST API is a new programmability framework that allows for easy discovery of and access to data and objects within a spreadsheet. “Programmability” is almost a bit of a misnomer because in its simplest incarnation it doesn’t require any knowledge of programming to use. If you can craft a URL, you can use the REST API. This makes the REST API accessible to non-developers, yet it offers capabilities that make it a very powerful tool for professional developers as well.

For example, say you have a spreadsheet saved to a SharePoint document library that looks like the one below:

Now, let’s say you are writing an internal blog post about the effect of taxes on revenue, and you’d like to embed this Excel chart into your blog post. Simple, just craft a REST URL (technically, it’s a URI, but let’s ignore that for now) that looks like the following:

http://myServer/Finance/_vti_bin/ExcelRest.aspx/Shared%20Documents/Tax.xlsx/Model/Charts(‘RevenueChart‘)

and paste this into the Insert Image function in your blog software, and out comes a chart that looks like this:

Congratulations!  You’ve just used the REST API in Excel Services 2010.  Pretty easy, right?  But wait, there’s more!

The REST API in a Nutshell

By crafting the proper URI, the REST API allows you to:

1. Discover the items that exist in a workbook, such as tables, charts and named ranges
2. Retrieve the actual items in the workbook in one of the following formats:

1. Image
2. HTML
3. ATOM feed
4. Excel workbook

• Set values in the workbook and recalculate the workbook before retrieving items

Is the Data Static?

The data, including charts, that is returned by the REST API is not static – it’s live and up-to-date.

With the REST API, any changes in the workbook are reflected in the data that is returned. This includes the latest edits made to the workbook, functions that have recalculated (including User Defined Functions), and external data that is refreshed.

The REST API can also push values into the workbook, recalculate based on those changes, and return the range or chart you requested after the effects of the change have been calculated.  For example, in the sample spreadsheet above, the chart is displaying the effects of a 10% tax rate.  With REST, you can request a chart displaying the effects of a 5% tax rate by adding a parameter to the URI that changes the value of cell B1 to 5%.

Examples

The opportunities for using REST are virtually endless. To spur your imagination, here’s a sampling of some ways to use the REST API:

• Embed a chart or range in a web page, blog post, etc.
• Embed a chart as a “linked” image into a Word document, PowerPoint presentation, or Outlook mail, and have always up-to-date information.
• Build a “mash-up” that uses Excel Services for calculation, charting, and/or as a data source.
• Build a Windows 7 gadget that displays information from an Excel workbook
• …and many more ideas I am sure our customers will think up.

Coming Up

I hope this has given you a sense of the capabilities and power of the REST API. In the next post I’ll dive into the details of the URI syntax. Stay tuned.