Back
Excel

Excel Services in SharePoint 2010 REST API Examples

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

In this final installment on the REST API, we’ll walk through a couple examples of using the REST API to embed spreadsheet data in new and interesting ways.

Embedding a ‘Live’ Chart on a SharePoint Wiki Page.

For this example we are embedding an Excel scatter chart on a SharePoint wiki page. The scatter chart works like an analog clock, so that you can see that the data returned via REST is dynamic, and the workbook containing the chart and the logic is attached to this blog post.

  • First upload the Clock.xlsx workbook to a SharePoint document library. (the workbook is included in the archive folder that is attached to this post)

    Let’s assume that the workbook has been uploaded to the following location:

    http://contoso/Shared Documents/Clock.xlsx.

  • Then craft the REST API URI to the scatter chart named Clock

    http://contoso/_vti_bin/ExcelRest.aspx/Shared Documents/Clock.xlsx/Model/Charts(‘Clock’)

    You might want to post the URI into a browser’s address bar to verify that you can access the chart before proceeding with the next steps.

  • Create a SharePoint Wiki page.
  • Next, start editing the wiki page by clicking on ‘Edit Page’. Since we are inserting a chart which the REST API returns as a PNG image file click on the ‘Insert’ tab on the ribbon, then click on the ‘Image’ icon and then select ‘From Address’ from the dropdown box.
    image 
  • Paste the URI that you have created earlier into ‘Address’ field.
    image
  • Finally, ‘Stop Editing’ the page

We see the clock when the wiki page reloads.

image

And when we reload the wiki page again, the scatter chart representing the analog clock will be retrieved again using the REST API – and will have been updated with the new time – showcasing the dynamic and live nature of Excel Services REST API calls.

image

A more business focused scenario could include a chart on a wiki page that shows the latest sales figures for a division of a company which are retrieved by Excel Services from a SQL Server database or Analysis Services cube when the wiki page is opened (or refreshed) by a user.

Inserting a Chart in a Word Document Using the Excel Services REST API.

Similarly, we can use the Excel Services REST API to insert live charts in Word and PowerPoint documents.

In Microsoft Word, select the ‘Insert’ tab on the ribbon, then click on the ‘Quick Parts’ icon, followed by selecting ‘Field’.

image

Then select ‘IncludePicture’ and paste the REST URI for displaying the RevenueChart from the Tax.xlsx workbook

http://contoso/Finance/_vti_bin/ExcelRest.aspx/Shared Documents/Tax.xlsx/Model/Charts(‘RevenueChart’)

into the ‘Filename or URL field’. Also, select ‘Data not stored with document’ in the ‘Field options’ column – this results in Word refreshing the chart (reflecting potentially updated numbers) each time the document is opened.

image

Thus, we have created a Word document that contains a live and up-to-date Excel Services chart. If the data in the workbook (which itself can come from an external source, such as a SQL data base or Analysis Services Cube) is updated, so will the chart in the Word document each time the document is re-opened or when the document is refreshed (by pressing the F9 function key).

image

Building a Mash Up with Bing Maps and the Excel Services REST API.

Bing Maps provides an SDK that enables developers to determine the location of a mouse click on the map and to get the address corresponding to this location. In this demo, the (U.S.) state corresponding to the click location is extracted from the address. Then an Excel Services REST URI is created that requests a chart with the census rankings of that state by setting the state’s name/abbreviation into the Excel Workbook.

Clicking on the city of Seattle brings up the Census Rankings for Washington State.

image

Similarly, clicking on the city of Houston brings up the Census Rankings for the State of Texas.

image

The entire code required to load the Excel Services Chart with the Census Rankings for each state is:

document.getElementById(‘resultDiv’).innerHTML = “<img src=’http://contoso/_vti_bin/ExcelRest.aspx/Shared Documents/StateRankings.xlsx/Model/Charts(%27Report%27)?Ranges(%27State%27)=”+s+”‘/>”;

We assemble the REST API URI string by taking the URL to the workbook’s location and setting the range ‘State’ to the JavaScript variable ‘s’ which contains the state’s name/abbreviation.

The entire JavaScript code (including the code to get the location from Bing Maps) and the workbook for this demo are included in the archive folder that is attached to this article.

Summary

The Excel Services REST API is a powerful yet very easy to use means of embedding Excel Services content on web pages and for creating mash-up applications. It enables users to retrieve image, HTML and Atom feed representations of items in Excel workbooks and modifying those by setting values into the workbook. The REST API also leverages the other capabilities of Excel Services, such as external data connections and user defined functions which can be used to fetch up-to-date values from external sources, which can then be retrieved, displayed or otherwise consumed by simply specifying a REST URI. 

If you’d like to read more about REST, Shahar Prish, a developer on the Excel Services team who worked on the REST feature, has written a series of blog posts about REST on his blog.

I look forward to your feedback on this feature.

Notice: Array to string conversion in D:\home\site\wwwroot\wp-includes\taxonomy.php on line 3255 Notice: Array to string conversion in D:\home\site\wwwroot\wp-includes\category-template.php on line 1245

Tags