Restplorer – visual exploration tool for Excel Services 2010 REST capabilities

  • Article

At the beginning of the year, I wrote a little web app that lets you get embed code for REST URLs/elements in Excel Services in a very easy way – instead of crafting the URL yourself, or navigating through ATOM feeds, you get a nice UI to do that.

Well.. Not really “nice” as much as an “eye sore” – the HTML was pretty awful. I elicited the help of one of our PMs (Manpratap Suri) to make the application look and behave like somebody a sane person would want to use. Once installed, the first step is to select the workbook you want the embed code for:

image

The “Publish to web” option will show up, allowing the user to automatically navigate to the application page. Once clicked, the user is immediately dropped the application page:

image

There are five parts to this screen – at the top, there’s a huge “Publish to the web” title that should probably be made smaller. Then, below that, there are four elements you can choose from – charts, tables, PivotTables or ranges. To the left, below that, there’s a list of the available items (three items in the example above) and to the right is a preview image. Below the preview image, there are the available embed options – URL, HTML, Raw Data and DHTML.

The user can now easily navigate between the type of elements the workbook exposes and between the actual elements exposed and easily pick what they want to use in their site.

Clicking on “Tables” for example, will give the list of tables in the workbook:

image

The interesting part though is the “Embed” code at the bottom. There are four options:

* URL – this is the direct URL to the entity. Pasting this in the browser address box will directly navigate to the entity.

* HTML – this is the HTML that represents the element. For charts, this is an <img> tag with the appropriate link (which means it will be refreshed every time the page containing it is loaded). For ranges/PivotTables and Tables, this is the actual HTML for the object (which means that it is “static” – it will not change).

* DHTML – Only works for Ranges/PivotTables/Tables – this is is a piece of script and HTML that can be embedded – it will return the HTML for the requested element. Because this is brought in via AJAX every time the page loads, the data will always be current.

* Raw Data – Only works for Ranges/PivotTables/Tables – this is the link to the raw data (values) of the requested element.

In the next post, I am going to show how to use the various Embed options in SharePoint.

Comments

  • Anonymous
    February 27, 2010
    Where can we download restploer

  • Anonymous
    May 05, 2010
    Excel Services REST looks very good but there are a couple of things: Looks like Excel Services REST does not support workbook interactivity, currently supported in EWA web part. Do you have any plans to add this functionality in a future? Also, when workbook uses external data and data connection is broken, REST does not return any warning about workbook not being able to refresh external data. Did I miss some settings? Igor

  • Anonymous
    May 05, 2010
    Igor:

  1. I would like to hear more about your expectation - how do you see REST supporting workbook interactivity?
  2. REST is supposed to be a lightweight way of accessing Excel Services - again - what would you see as a good solution here - a. fail requests if Data access fails or b. Return header-information in the result saying that there was a data refresh problem?
  • Anonymous
    May 05, 2010
    Shahar, Thank you for response.
  1. For example, to be able to change filter and sorting settings and to drill down on rows and columns in PivotTable. As I understand it requires extensive DHTM to be returned by REST. Currently it returns just a simple HTML chunk (until I missed some settings). So, it will be good as a lightweight preview of some parts of Excel workbook but REST is not supposed to be a replacement for EWA web part. Am I correct?
  2. Let say, if I have Excel PivotTable connected to the OLAP cube and connection fails for any reason, user will not see any warning that something is going wrong. So, some JavaScript pop up message showing warning would be good. Pretty much like it is implemented in the EWA.  Also, returning error description in a header will be a great idea either, so it will be possible to display some customized error message. It would be great if this behaviour will be controllable over URI parameters as well as "Stop When Refresh on Open Fails" checkbox on the "Central Administration>Excel Services Application>Edit Trusted File Location" page. Thank you. Igor

  • Anonymous
    May 05, 2010
    The comment has been removed

  • Anonymous
    May 06, 2010
    Shahar, Thanks a lot for your help. Igor

  • Anonymous
    September 13, 2010
    This looks great... how do I download it?

  • Anonymous
    November 07, 2010
    Thanks for this - but where can i get it? Thanks

  • Anonymous
    November 19, 2010
    Igor, you should try doing some research on rest architecture.  It's not meant to be used in the way you are envisioning.

  • Anonymous
    July 01, 2012
    This looks great. How could I download the restplorer?