Discovery via the Excel Services REST APIs

In my previous post, I showed what embedding a chart from Excel Services inside a blog looks like. In this post, we will take a step back and explore the discovery mechanisms built into the Excel Services REST APIs.

Discovery allows developers and users to explore the content of the workbook either manually or programmatically by supplying ATOM feeds that contain information the elements that reside a specific workbook. Starting out with discovery is pretty easy. If you will recall, the link we used to access the chart in the previous post looked a like this:

https://tssrva/_vti_bin/ExcelRest.aspx/sites/sp2009/shared documents/Bugs.xlsx/Model/Charts(‘PersonalBugs’)

To start seeing how discovery works, simply chop off all that Charts business and you are left with:

https://tssrva/_vti_bin/ExcelRest.aspx/sites/sp2009/shared documents/Bugs.xlsx/Model

This is your anchor for starting discovery. Pointing your browser to this URL will show up the four resource collections we currently support:

image

You will notice that you get access to four resource collections: Ranges, Charts, Tables and PivotTables.

Clicking on the Charts link will bring to yet another ATOM feed – this one listing all the charts available in the workbook:

image

As you can see, there are two charts on the workbook – one called “PersonalBugs” (which was used in the previous post) and one called “TeamBugs”. Clicking on “TeamBugs” will navigate to the actual chart URL:

image

Using the ATOM feed provided by the REST APIs allows for an incredibly easy way of getting to the data you care about. The flip side of this is that since these are just ATOM feeds, they are readable by machine as well! So, in the previous screenshot, if you ask FireFox to give you the source of the document, you get a bunch of XML:

 

<?xml version="1.0" encoding="utf-8" standalone="yes"?>

<feed xmlns="https://www.w3.org/2005/Atom" xmlns:x="https://schemas.microsoft.com/office/2008/07/excelservices/rest" xmlns:d="https://schemas.microsoft.com/ado/2007/08/dataservice" xmlns:m="https://schemas.microsoft.com/ado/2007/08/dataservices/metadata">

  <title type="text">Charts</title>

  <id>https://tssrva/_vti_bin/ExcelRest.aspx/sites/sp2009/shared%20documents/Bugs.xlsx/Model/Charts</id>

  <updated>2009-10-28T08:15:13Z</updated>

  <author>

    <name />

  </author>

  <link rel="self" href="https://tssrva/_vti_bin/ExcelRest.aspx/sites/sp2009/shared%20documents/Bugs.xlsx/Model/Charts?$format=atom" mce_href="https://tssrva/_vti_bin/ExcelRest.aspx/sites/sp2009/shared%20documents/Bugs.xlsx/Model/Charts?$format=atom" title="Charts" />

  <entry>

    <category term="ExcelServices.Chart" scheme="https://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />

    <title>PersonalBugs</title>

    <id>https://tssrva/_vti_bin/ExcelRest.aspx/sites/sp2009/shared%20documents/Bugs.xlsx/Model/Charts('PersonalBugs')</id>

    <updated>2009-10-28T08:15:13Z</updated>

    <author>

      <name />

    </author>

    <link rel="alternate" title="PersonalBugs" href="https://tssrva/_vti_bin/ExcelRest.aspx/sites/sp2009/shared%20documents/Bugs.xlsx/Model/Charts('PersonalBugs')?$format=image" mce_href="https://tssrva/_vti_bin/ExcelRest.aspx/sites/sp2009/shared%20documents/Bugs.xlsx/Model/Charts('PersonalBugs')?$format=image" />

    <content type="image/png" src="https://tssrva/_vti_bin/ExcelRest.aspx/sites/sp2009/shared%20documents/Bugs.xlsx/Model/Charts('PersonalBugs')?$format=image" mce_src="https://tssrva/_vti_bin/ExcelRest.aspx/sites/sp2009/shared%20documents/Bugs.xlsx/Model/Charts('PersonalBugs')?$format=image" />

  </entry>

…  [Removed the rest of the feed]

As you can see, the feed contains easily traversable elements that will allow any piece of code to go through and discover what elements exist in the workbook – each ATOM entry corresponds to a Chart that can be accessed.

This same mechanism applies to discovering PivotTables, Tables and Ranges. In the next post, I will show some of the capabilities revolving around those.