Restplorer – visual exploration tool for Excel Services 2010 REST capabilities


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 (10)

  1. John B says:

    Where can we download restploer

  2. Igor says:

    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

  3. Shahar Prish says:

    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?

  4. Igor says:

    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

  5. Shahar Prish says:

    Igor,

    1. I understand. I am not sure if REST will be the best solution here – some extensions to the JSOM may be a better fit. REST is intended to be used from a myrad of sources (rich apps, flash, silverlight and browser) – these guys may not support JS and so may not be able to surface the wanted funcitonality.

    2. similar to the previous answer – REST is less suited to this. In some of the REST responses, we may be able to give this information back (such as the ATOM requests for ranges) but in others, we dont really have a way of conveying this info (images). We could add a switch that will cause a request to fail if something fails in data access.. I will forward your comments to our PMs.

    Thanks for taking the time to write.

  6. Igor says:

    Shahar,

    Thanks a lot for your help.

    Igor

  7. JoeH says:

    This looks great… how do I download it?

  8. Andrew says:

    Thanks for this – but where can i get it?

    Thanks

  9. Jason says:

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

  10. Arpu says:

    This looks great. How could I download the restplorer?