Getting Excel data over HTTP has never been so easy.  The idea behind REST web services is that you can use simple web requests to get and set data that you need.  Instead of having to have complex SOAP headers and lots of code which makes consuming web services, particularly in AJAX solutions difficult, is eliminated in RESTful web services.   I am a bit picky when it comes to exact specifications of interfaces, which is easy to miss in RESTful services, but we’ll save that for another blog post because right now we are just focusing on the benefits.

REST BASICS

Nouns

RESTful web services use “Nouns” to describe how data is organized.  These nouns are included right a URI (URL).

http://mysite/Players.html returns a list of all players as an HTML table

http://mysite/Players/Willie-Veasley returns details about a particular player.

(My girlfriend is mildly obsessed with Butler Basketball so I’ll use their players as examples).

The idea behind this is that it is easier to find information if it is intuitively categorized, maybe even using a taxonomy.  This hierarchy “Players” followed by player name are each nouns in a RESTful web service.

With RESTful web services are are exposing data based on a hierarchy. This is a full fledged taxonomy for the data in your system.

Verbs

The information is only so great until you can do something with it.  This is where verbs come in.   There are four verbs – based off of the 4 types of HTTP requests GET, POST, PUT, DELETE.  These, on the back end, map to the CRUD functions of an ADO object.  (Read, Create, Update, Delete).

The Point is to Keep it Simple

RESTful web services can return data in multiple formats meaning that it can be both human and computer readable.  It doesn’t have to be pretty, it just has to be easy and GET THE DATA OUT THERE.  The web is designed to share information, so use it to share the data so other people can use it (another blog on web data mining is coming).

Another idea is to use RESTful web services with AJAX controls.  REST is stateless and designed to retrieve and set data, kind of like RPC but over HTTP.   To retrieve data, a HTTP request sent using a javascript object like HttpRequest, using “GET” to the specific URL, pick the right url for the right format (xml, html, json) and you have your data.  There is no constructing complex XML requests to pass and just a quick hit to get and retrieve your data that is ready to be bound to your favorite AJAX control.

RESTful Excel Services in 2010

Excel REST can be used to get and set cells or ranges in an excel workbook that has been published to SharePoint.  It can also return charts and graphs as images that can be imbedded in any web site with a simple <img src=”URL”> tag.

The Excel REST web service is located

http://myserver/_vti_bin/ExcelRest.aspx/

To see an Rss feed of all of your excel objects just go to

http://myserver/_vti_bin/ExcelRest.aspx/model

Click around and check out the url.

The following url is used to both get and set the data.

Ranges(‘[Sheet]![TOPLEFT]%7C[BOTTOMRIGHT]’)?Ranage(‘[SHEET]![CELL]’)=[VALUE])

Range would be appended after the ExcelRest.aspx/model

Charts

Getting and setting data is really cool, but what blows my mind is the ability to use the Excel Web Services to get graphs as images.  The images are cached which makes the REST request very efficient and suitable to be included in public facing web sites.  The images are updated automatically when the data in the excel file.

/model/Charts(‘<CHART NAME>’)

This is so powerful because it enables excel to serve as a back end for user driven business intelligence.   One thing just about every businessperson loves and knows how to use is excel.  Excel Services allows the business user to make the charts and slice and dice the data the way they know how to and then share it by publishing the data on the server.  IT can use this data in their application development as a way to do more complex computations and graphic that is otherwise annoying to do with complex and costly APIs or SSRS.

Slide Deck

More Resources

http://www.techbubbles.com/softwarearchitecture/rest-overview/

http://msdn.microsoft.com/en-us/magazine/dd943053.aspx

http://msdn.microsoft.com/en-us/library/ee556820(office.14).aspx

Excel Services, nSPIN, REST, SharePoint