Coding the Excel Services Windows 7 Gadget – Part 2 - Charts

The first part of this series showed how the code in the Settings form worked in the gadget. In this part, I will start describing how the gadget fetches the requested information from Excel Services and displays it.

As a reminder, here’s what the gadget looks like when minimized and showing a chart:


The gadget reloads itself in two cases – when the refresh interval has passed and when the settings window is OKed. In both cases, the call that gets made is to the method adjustContent which takes care both of shooting off the loading of the data and adjusting to the size of the content. The function has different processing for when the gadget is maximized and when it’s minimized which are pretty similar. Here is the function with the handling of the maximized gadget removed for brevity (since this method is relatively long, I am also adding comments with stages in them so I can refer to them):

// This method adjusts the content of the gadget to fit. It also takes care of refreshing the data if necessery.

// If the content is not yet loaded, Adjust will make a call to the loading code and exit. Once the content is loaded,

// adjust will be called AGAIN and this time adjust for the actual loaded content.

function adjustContent()


    // [[[ STAGE 1 ]]]


    var initDocking = true;


    if (isDocked()) // When the gadget is docked (minimized/thumbnail) this code will run.


        debugWrite("Adjusting for small");
// [[[ STAGE 2 ]]]

        var loaded = loadedArray[displayThumbnail];

        var tnt = config.get_resolvedThumbnailType();


        // If needed, try to load the data - depending on what we are actually showing.
// [[[ STAGE 3 ]]]

        if (tnt == config.typeChart)


            if (!loaded)




        } // [[[ STAGE 4 ]]]

        else if (tnt == config.typeRange || tnt == config.typeTable || tnt == config.typePivotTable)


            if (!loaded)


      loadRange(config.get_resolvedThumbnailUrl(), gebid("thumbnailContainerDiv"),0);





            initDocking = false;


// [[[ STAGE 5 ]]]

        // If we are loaded, adjust the size and content to properly show the data.

        if (loaded || !initDocking)


            // Figure out the zoom - this is done by taking the content size and making sure it fits in 120pixels width.

            gebid("thumbnailContainerDiv").style.zoom = 1;

            gebid("undocked").style.position = "absolute";

            var ratio = 120 / gebid("thumbnailContainerDiv").scrollWidth;

            if (ratio >= 0.95)


                ratio = 1;


            gebid("thumbnailContainerDiv").style.zoom = ratio;

   = gebid("docked").scrollHeight;

            gebid("undocked").style.position = "static";


        else // [[[ STAGE 6 ]]]


            // Show the little circle that denotes a loading gadget (the thing that goes round and round).


        } = "120px";

        debugWrite("BodyW:" +;


    else // !isDocked - If the gadget is maximized, we need to adjust it appropriately.


        // [[[ Code removed from here ]]]


    if (initDocking)





Here’s what’s happening in this method:

Stage 1:
We make sure the loading indicator (the little circly thing that goes round and round when loading) is hidden and we check to see if the gadget is docked or not (remember, a docked gadget is “minimized” or small and an undocked gadget is “maximized” or big). The processing shown here is just for handling of the minimized gadget.

Stage 2:
We have a global state that tells us whether or not the content of the gadget has been loaded – that’s stored in the variable called loadedArray[] – the first entry (displayThumbnail) is for the minimized gadget. If the content has not been loaded (or has expired), adjustContent will fire up an update – this can be seen in stages 3 and 4. The call to config.get_resolvedThumbnailType() returns the type of the content to be displayed – this can be a range, a chart, a table or a PivotTable.

Stage 3:
In the case of a chart and in the case that the gadget still has not loaded, we will call into the loadSmallChart() code (we will look into that a little later). Note that the call to config.get_resolvedThumbnailUrl() will return the URL to the content the user wants to see when the gadget is minimized.

Stage 4:
In the case of Ranges, Tables and PivotTables, the gadget will call into a different method called loadRange(). The parameters passed comprise of the URL to load, the element into which the range needs to be placed (the details of how ranges are loaded will be shown in the next post in this series).

Stage 5:
In here, if the gadget has been loaded (which it won’t be the first time you go into adjustContent()) the code will adjust the size of the minimized gadget to show the data. The code takes the size of the containing div (called thumbnailContainerDiv) and checks to see its size – if the size is too large for the minimized gadget (the width needs to be 120 pixels), it will zoom that specific div so that the HTML will fit.

Stage 6:
If the gadget still has not loaded it’s data, the adjustContent() method will show the loading indicator.

The code that was removed (handling the maximized gadget) is similar – the major difference is that instead of adjusting the content to fit in 120 pixels, the gadget will adjust it’s size to fit the content.

Before we delve into the loadSmallChart() method, it’s important to understand how the gadget update occurs. When updating, the gadget uses a hidden DIV on the HTML (called loadContainer) to anchor the new content. The reason for this is so that if the content fails to load for whatever reason, we do not lose the content that’s already on the gadget. Once an element has been successfuly loaded, it will be removed from the loadContainer element and placed in its correct location. Here’s what loadSmallChart() looks like:

// Loads a "small" chart - places an IMG element in the content (in the thumbnail)

// and sets its properties (URL etc)

// It also sets up an event so that when the chart is done loading (onload), we will know and be able to

// adjust the content to the size of the chart.

function loadSmallChart()


    debugWrite("Loading small chart");

    var imageLoader = document.createElement("img");

    gebid("loadContainer").appendChild(imageLoader); = "120px";

    imageLoader.onload = loadedElementComplete;

    imageLoader.targetParent = gebid("thumbnailContainerDiv");

    imageLoader.loadedIndex = displayThumbnail;

    imageLoader.src = config.get_resolvedThumbnailUrl();


The method creates an IMG tag and adds it to the loadContainer DIV. It then sets the parameters on the IMG and attaches a method to the onload event – this event will fire when the IMG tag is done loading the chart (at which stage we will go and update the gadget body to actually show the new chart). Note that we also that we add an expando attribute to the IMG tag we created which points to the element into which it will ultimately need to be added. This allows us to have a generic mechanism for moving the element later on. Here’s what loadedElementComplete looks like:

// Used by the gadget as a callback to when a Range is done loading.

function loadedElementComplete(element)


    if (!element)


        element = event.srcElement;


    // Figure out where we need to place the DIV - this property was set up in the

    // loadRange() method to point to the target element.

    var targetParent = element.targetParent;


    // Remove the "off screen" div from it's parent.



    // Now add it/replace the current existing child.

    if (targetParent.children.length == 1)


        targetParent.replaceChild(element, targetParent.children[0]);







    // Specify that we did indeed load the range.

    loadedArray[element.loadedIndex] = true;


    // Make sure we know when we last updated.

    lastUpdate = new Date();



    // Adjust the gadget to the content.



When loading a chart, the first part of the method will guarantee we point to the correct element that got loaded. Next, we figure out where the newly loaded element needs to go to (by accessing element.targetParent). We then remove the newly loaded element from its parent and add it to the actual location where it’s going to be viewed. Finally, we mark the element as loaded (loadedArray[element.loadedIndex] = true) and we call adjustContent to make sure the gadget is properly sized (note that in this call to adjustContent, the loaded state of the content will be set to true, and so the actual resizeing/adjusting part of the code will manifest.

That’s it for now – tomorrow I will show how ranges are fetched and displayed (the mechanism is slightly more elaborate since we do not have an element we can use to load it (as we do with the IMG element for charts).