THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Exploring the Excel Services REST API

Over the last few years Analysis Services guru Chris Webb and I have been on something of a crusade to enable better access to data that is locked up in countless Excel workbooks that litter the hard drives of enterprise PCs. The most prominent manifestation of that crusade up to now has been a forum thread that Chris began on Microsoft Answers entitled Excel Web App API? Chris began that thread with:

I was wondering whether there was an API for the Excel Web App? Specifically, I was wondering if it was possible (or if it will be possible in the future) to expose data in a spreadsheet in the Excel Web App as an OData feed, in the way that it is possible with Excel Services?

Up to recently the last 10 words of that paragraph "in the way that it is possible with Excel Services" had completely washed over me however a comment on my recent blog post Thoughts on ExcelMashup.com (and a rant) by Josh Booker in which Josh said:

Excel Services is a service application built for sharepoint 2010 which exposes a REST API for excel documents.
We're looking forward to pros like you giving it a try now that Office365 makes sharepoint more easily accessible.  Can't wait for your future blog about using REST API to load data from Excel on Offce 365 in SSIS.

made me think that perhaps the Excel Services REST API is something I should be looking into and indeed that is what I have been doing over the past few days. And you know what? I'm rather impressed with some of what Excel Services' REST API has to offer.

Unfortunately Excel Services' REST API also has one debilitating aspect that renders this blog post much less useful than it otherwise would be; namely that it is not publicly available from the Excel Web App on SkyDrive. Therefore all I can do in this blog post is show you screenshots of what the REST API provides in Sharepoint rather than linking you directly to those REST resources; that's a great shame because one of the benefits of a REST API is that it is easily and ubiquitously demonstrable from a web browser. Instead I am hosting a workbook on Sharepoint in Office 365 because that does include Excel Services' REST API but, again, all I can do is show you screenshots.

N.B. If anyone out there knows how to make Office-365-hosted spreadsheets publicly-accessible (i.e. without requiring a username/password) please do let me know (because knowing which forum on which to ask the question is an exercise in futility).

In order to demonstrate Excel Services' REST API I needed some decent data and for that I used the World Tourism Organization Statistics Database and Yearbook - United Nations World Tourism Organization dataset hosted on Azure Datamarket (its free, by the way); this dataset "provides comprehensive information on international tourism worldwide and offers a selection of the latest available statistics on international tourist arrivals, tourism receipts and expenditure" and you can explore the data for yourself here. If you want to play along at home by viewing the data as it exists in Excel then it can be viewed here. Let's dive in.

 

The root of Excel Services' REST API is the model resource which resides at:

  • http://server/_vti_bin/ExcelRest.aspx/Documents/TourismExpenditureInMillionsOfUSD.xlsx/model

Note that this is true for every workbook hosted in a Sharepoint document library - each Excel workbook is a RESTful resource. (Update: Mark Stacey on Twitter tells me that "It's turned off by default in onpremise Sharepoint (1 tickbox to turn on though)". Thanks Mark!)

The data is provided as an ATOM feed but I have Firefox's feed reading ability turned on so you don't see the underlying XML goo. As you can see there are four top level resources, Ranges, Charts, Tables and PivotTables; exploring one of those resources is where things get interesting. Let's take a look at the Tables Resource:

  • http://server/_vti_bin/ExcelRest.aspx/Documents/TourismExpenditureInMillionsOfUSD.xlsx/model/Tables

image

Our workbook contains only one table, called ‘Table1’ (to reiterate, you can explore this table yourself here). Viewing that table via the REST API is pretty easy, we simply append the name of the table onto our previous URI:

  • http://server/_vti_bin/ExcelRest.aspx/Documents/TourismExpenditureInMillionsOfUSD.xlsx/model/Tables('Table1')

image

As you can see, that quite simply gives us a representation of the data in that table. What you cannot see from this screenshot is that this is pure HTML that is being served up; that is all well and good but actually we can do more interesting things. If we specify that the data should be returned not as HTML but as:

  • http://server/_vti_bin/ExcelRest.aspx/Documents/TourismExpenditureInMillionsOfUSD.xlsx/model/Tables('Table1')?$format=image

then that data comes back as a pure image and can be used in any web page where you would ordinarily use images. This is the thing that I really like about Excel Services’ REST API – we can embed an image in any web page but instead of being a copy of the data, that image is actually live – if the underlying data in the workbook were to change then hitting refresh will show a new image. Pretty cool, no? The same is true of any Charts or Pivot Tables in your workbook - those can be embedded as images too and if the underlying data changes, boom, the image in your web page changes too.

There is a lot of data in the workbook so the image returned by that previous URI is too large to show here so instead let’s take a look at a different resource, this time a range:

  • http://server/_vti_bin/ExcelRest.aspx/Documents/TourismExpenditureInMillionsOfUSD.xlsx/model/Ranges('Data!A1|C15')

That URI returns cells A1 to C15 from a worksheet called “Data”:

image

And if we ask for that as an image again:

  • http://server/_vti_bin/ExcelRest.aspx/Documents/TourismExpenditureInMillionsOfUSD.xlsx/model/Ranges('Data!A1|C15')?$format=image

image

Were this image resource not behind a username/password then this would be a live image of the data in the workbook as opposed to one that I had to copy and upload elsewhere. Nonetheless I hope this little wrinkle doesn't detract from the inate value of what I am trying to articulate here; that an existing image in a web page can be changed on-the-fly simply by inserting some data into an Excel workbook. I for one think that that is very cool indeed!

I think that's enough in the way of demo for now as this shows what is possible using Excel Services' REST API. Of course, not all features work quite how I would like and here is a bulleted list of some of my more negative feedback:

  • The URIs are pig-ugly. Are "_vti_bin" & "ExcelRest.aspx" really necessary as part of the URI? Would this not be better:
http://server/Documents/TourismExpenditureInMillionsOfUSD.xlsx/Model/Tables(‘Table1’)
That URI provides the necessary addressability and is a lot easier to remember.
  • Discoverability of these resources is not easy, we essentially have to handcrank a URI ourselves. Take the example of embedding a chart into a blog post - would it not be better if I could browse first through the document library to an Excel workbook and THEN through the workbook to the chart/range/table that I am interested in? Call it a wizard if you like. That would be really cool and would, I am sure, promote this feature and cut down on the copy-and-paste disease that the REST API is meant to alleviate.
  • The resources that I demonstrated can be returned as feeds as well as images or HTML simply by changing the format parameter to ?$format=atom however for some inexplicable reason they don't return OData and no-one on the Excel Services team can tell me why (believe me, I have asked).
  • $format is an OData parameter however other useful parameters such as $top and $filter are not supported. It would be nice if they were.
  • Although I haven't demonstrated it here Excel Services' REST API does provide a makeshift way of altering the data by changing the value of specific cells however what it does not allow you to do is add new data into the workbook. Google Docs allows this and was one of the motivating factors for Chris Webb's forum post that I linked to above.
  • None of this works for Excel workbooks hosted on SkyDrive

This blog post is as long as it needs to be for a short introduction so I'll stop now. If you want to know more than I recommend checking out a few links:

Any thoughts? Let's hear them in the comments section below!

@Jamiet 

P.S. If you agree with me that having the same functionality in SQL Server Reporting Services (i.e. rendering a report as an image) would be useful then please vote for it here.

Published Tuesday, March 13, 2012 3:59 PM by jamiet
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

jbooker said:

Nice Article Jaime.  There is an unsuported way to make Office365 libraries anonymously accessible, like so:

http://joshuabooker.com/_vti_bin/ExcelRest.aspx/PDF/PDF/TourismExpenditureInMillionsOfUSD.xlsx/model

This may only be possible on the public website after highly cutomizing.

The supported method allows you to invite up to 25 (may have been increased to 50(?) external users to login to your site using their LiveID.

Josh

March 13, 2012 10:43 AM
 

jamiet said:

Thanks Josh. In order to make the images embeddable I need anonymous access so the LiveID option isn't feasible.

Can you tell me more about this unsupported method?

March 13, 2012 10:57 AM
 

jbooker said:

Jamie,

Replied via email Re: Anon access.  

It involves this solution:

http://www.fiechter.eu/blog/Lists/Posts/Post.aspx?ID=10

See if you can view this image anonymously:

http://joshuabooker.com/_vti_bin/ExcelRest.aspx/PDF/PDF/TourismExpenditureInMillionsOfUSD.xlsx/model/Charts('Chart%201')?$format=image

I think the biggest shortcomming to Excel Web Services is the lack of ability to publish a document programatically.  Perhaps there is a way, but I havn't found it.  The method to filter data in REST API is set the value of a parameter (named cell).  However the parameter has to be setup at the time of publishing otherwise it's not available to Excel Services.  This means your

'crusade to enable better access to data that is locked up in countless Excel workbooks that litter the hard drives of enterprise PCs'

would involve a lot of manual effort publishing each doc with valuable parameters.  I'm not a big excel user, but it seems to me MS is spending a great deal of effort to make excel more powerful for large datasets.  We have named cell parameters, slicers, data connections, powerpivot all serving a similar purpose but usable for only a certain circumstance.  What's missing is a single easy way to programatically filter, sort and group.  One that's applicable to a sheet of columns and rows, pivot table, charts, External data and Excel Services.  My 2 cents.

Cheers,

Josh

March 13, 2012 12:12 PM
 

jbooker said:

"•Discoverability of these resources is not easy, we essentially have to handcrank a URI ourselves. Take the example of embedding a chart into a blog post - would it not be better if I could browse first through the document library to an Excel workbook and THEN through the workbook to the chart/range/table that I am interested in? Call it a wizard if you like. That would be really cool and would, I am sure, promote this feature and cut down on the copy-and-paste disease that the REST API is meant to alleviate.

"

See if you can track this guy down and get them to release this tool:

http://blogs.msdn.com/b/cumgranosalis/archive/2010/02/17/restplorer-visual-exploration-tool-for-excel-services-2010-rest-capabilities.aspx

March 13, 2012 12:16 PM
 

SSIS Junkie said:

Sean Boon has begun an interesting blog series where he is analysing data from the Olympics using using

July 30, 2012 10:07 AM
 

Jamie Thomson said:

I’m a frequent user of OneNote and so was delighted with today’s news that there is now a public API

March 17, 2014 4:58 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement