Excel Services REST API in SharePoint 2010–simple sample

I started a new SharePoint 2010 project several weeks ago and one of our first assignments was to create a quick demo to highlight some of the new 2010 functionality.  In our discussions about 2010, my Sogeti colleague mentioned that far and away his favorite new 2010 feature was the Excel REST API.  I wasn’t that familiar with it, so I offered to set up that part of the demo.

I have a SharePoint 2010 server running in a virtual machine, with Excel Services already configured. Please note: if you are having issues with Excel Services, one thing to double check is that the Excel Services “service account” has access to your content database.  If this isn’t set correctly, you’ll receive an access denied error message.

The first thing I did was create an Excel file with some sample data in it.  I also added a chart:

image

The next step is to upload the Excel file to a document library in SharePoint 2010.  I created a new Team site and then just uploaded my file to the Shared Documents library.

Next, I needed the Excel Rest API URL for my document:

http://sp2010devone/_vti_bin/excelrest.aspx/sales/Shared%20Documents/QuarterlySalesDemo.xlsx/model

This link is specific to my server, so don’t try this one at home.  Instead, try putting together your URL using this formula:

http://{SERVER}/_vti_bin/excelrest.aspx/{SITE}/{DOCUMENT_LIBRARY}/{document.xslx}/model

Just replace any part of the above URL that is in {} with the correct name/link for your site. (For further information about the Excel Services REST API, check out: http://msdn.microsoft.com/en-us/library/ff394624.aspx)

Once you have this URL, you should see something along these lines in your browser (this is from IE9):

image

Next, using the links above, you can get a portion of the data from your Excel document.  I wanted to display my chart on my site’s homepage, so I followed the Charts link, and found the URL for my “Quarterly Sales Goals Achieved” chart (see the image of the Excel file at the start of this article).

Now that I had the URL, all I had to do was add an image to my site homepage.  Just add an Image Viewer webpart to the page, and put the URL in the “Image Link” property for the web part:

image

Save your changes and now view your page:

image

Ok, so you think that’s handy?  Try changing the values in your spreadsheet (be sure to save your changed document to SharePoint). 

Now, refresh the page where you put the chart – UPDATED values! How cool is that?  You can use this to easily build reports/dashboards based on the data that is already in your Excel files.

Print | posted @ Sunday, October 24, 2010 4:47 PM