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:
- Then craft the REST API URI to the scatter chart named 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.
- Paste the URI that you have created earlier into â€˜Addressâ€™ field.
- Finally, â€˜Stop Editingâ€™ the page
We see the clock when the wiki page reloads.
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.
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â€™.
Then select â€˜IncludePictureâ€™ and paste the REST URI for displaying the RevenueChart from the Tax.xlsx workbook
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.
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).
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.
Similarly, clicking on the city of Houston brings up the Census Rankings for the State of Texas.
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+”‘/>”;
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.
I look forward to your feedback on this feature.