One of the features gadgets posses is the ability to have a “fly-out” visual aid for “zooming in” or “drilling down” on parts of the gadget. The Excel Services gadget supports that too – for certain types of links. Generally speaking, one should not muck around with the HTML that comes back from Excel Services. If you do decide to do that, be aware that your solution may stop working because Microsoft does not guarantee the structure of the HTML. That said, lets see how we can muck around with the HTML. :)

As a reminder, this is what a Flyout looks like in Excel Services:

image

The right part is the gadget and clicking on the left column in the gadget brings up the fly-out. The normal behavior in a gadget – when a link is clicked – is to bring up a browser with that link. What we want is that for certain cases, the data will show up in a flyout. To achieve that, the HTML that wraps the content of the gadget has an onclick event associated with it. This event is fired whenever somebody clicks inside the content – and that includes clicking on links. Here’s what the code for the event looks like:

// Brings up the flyout for links. This is attached to the OnClick event that wraps

// the content.

function showFlyoutForLinks()

{

    var hideFlyout = true;

   

    // Check to see that  it's an anchor that was clicked..

    if (event.srcElement.tagName == "A")

    {

        if (event.srcElement.href) // then make sure it has a link.

        {

            if (event.srcElement.href.toUpperCase().indexOf("_VTI_BIN/EXCELREST.ASPX") != -1) // Then, make sure it's a link to a REST URL.

            {

                // If all these things are true, that means we need to show a flyout with the data.

                System.Gadget.Flyout.file = "flyout.html";

                System.Gadget.Flyout.show = true;

               

                // Tell the flyout what URL it needs to go to.

                System.Gadget.Flyout.document.restUrlNavigation = event.srcElement.href;

               

                // setting event.returnValue to false will make sure the link does not actually invoke.

                event.returnValue = false;

                hideFlyout = false;

            }

        }

    }

 

 

    if (hideFlyout && System.Gadget.Flyout.show)

    {

        System.Gadget.Flyout.show = false;

    }

 

}

 

First thing we do when processing a click, is to check to see what element got clicked – by checking the tagName property, we make sure the element is an anchor (a link). If it also has an HREF, that means we can process and check to see if we need to bring up the fly-out.

The next condition checks to see if the string “_vti_bin/ExcelRest.aspx” is part of the URL – if it is, we assume that that’s a REST URL and we will use the fly-out to display the information.

We tell the gadget to show the fly-out and use the flyout.html file to do so. We set an expando property on the document so that the fly-out will know what URL it needs to load as well. Finally calling event.returnValue = false guarantees that the gadget won’t continue processing the click event (and thus won’t execute the standard link behavior of opening the link).

The HTML in the flyout.html file contains the following body:

<body onload="setTimeout('flyoutLoadRestUrl()', 0)" style="width:40px;height:40px;">

When the fly-out completes loading, the flyoutLoadRestUrl() will execute:

// Attached to the load event of the body of the flyout. Starts the whole loading of the flyout.

function flyoutLoadRestUrl()

{

    loadRange(document.restUrlNavigation, gebid("flyout"), 0, flyoutElementComplete);

}

 

This uses the same exact method the gadget itself uses to load ranges – but it gives a different callback – flyoutElementComplete(). This method is somewhat interesting because of some of the constraints we have. Namely, we do not know what the type of the URL is – it may be a chart and it may be a range. Since I did not want to parse the URL to figure that out, I had to use a different mechanism:

// This is used as the callback for loadRange() when it appears in the flyout.

function flyoutElementComplete(element)

{

    var targetParent = element.targetParent;

 

    // With the flyout, we dont necesserily know what to do with a URL - if it's a Range, we want

    // to embed the stream that comes back. If it's an image, we want to put it in an <img> element.

    // What we do here, is detect what the data is by looking at the Content-Type http header.

    var contentType = this.getResponseHeader("Content-Type").toUpperCase();

    if (contentType.substring(0, 5) == "IMAGE")

    {

        // For images, the content type will be image/png - we detect that and make sure we add an Image tag to the

        // flyout.

        element = document.createElement("img");

        element.src = this.requestUrl;

       

        // We set up the event to fire when the image is done loading.

        element.onload = flyoutImageLoaded;

        targetParent.appendChild(element);

    }

    else // It was a range, not an image.

    {

        // Append the element to the target (inside the flyout).

        targetParent.appendChild(element);

       

        // Adjust the flyout size.

        flyoutAdjustContent();

    }

   

}

This callback is somewhat more involved. First, we check to see what the content type that got returned is (this is returned in the Content-Type HTTP response header). If the element is an image, we know we need to use a slightly different mechanism – put in an IMG tag – we also need to make sure that when the image loads, we adjust the size, so for that we have the flyoutImageLoaded callback attached to the onload event.

If, on the other hand, the content type that got returned is not an image, we will go and set the content of the fly-out to contain the returned range.

So that’s it pretty much for coding! Here’s what we saw in the past four posts:

1. The settings window of the gadget uses the REST discovery mechanism to figure out what elements exist in a workbook.
2. The gadget window uses REST calls to get charts and adjust it’s size.
3. The gadget window uses REST to get Ranges and embed them inside the gadget.
4. Override the way links work to show a fly-out for the gadget when a REST link is clicked.

There’s just one more post in this series – discussing some of the changes I want to make to the gadget and some of the new features. After that, we will take a break from REST and talk about some of the other new programmability capabilities Excel Services has with the new version.