Coding the Excel Services Windows 7 Gadget – Part 3 - Ranges
The first post in this series talked about how to code the settings window and the second talked about how the chart contents is displayed on the gadget itself. In this post, I will show how ranges are treated when the user requests to see them on the gadget.
Ranges (and tables and PivotTables) are different from charts because there’s no convenient mechanism that will load HTML fragments into a page. Instead, we need to use XmlHttpRequest to load the HTML and embed it into the gadget itself.
When I showed the adjustContent(), there was a call to the loadRange() in it:
else if (tnt == config.typeRange || tnt == config.typeTable || tnt == config.typePivotTable)
The first parameter we pass in is the URL to the content we want. The second parameter is the element which will contain the fetched REST HTML fragment and the last is the index of the loaded item (so we can track if it was loaded successfully or not). Here’s the content of that method:
// Loads a range from REST and puts it in a DIV. Since this an asynchronous operation, there are a number of stages that take place here:
// 1. A hidden DIV is created "off screen" and an async called made to the appropriate URL.
// 2. When the call is done, the result is taken from the DIV and placed in the actual location (thumbnail or full-sized).
// 3. Adjust content is called.
// Note that when used in the flyout, this call also knows how to load images (the callback detects the image and handles it properly)
function loadRange(url, targetElement, loadedIndex, completeCallback)
completeCallback = loadedElementComplete;
debugWrite("Loading small range");
// Create the "off screen" DIV.
var rangeLoader = document.createElement("div");
// Create an expando property to hold the loaded index - the item we are trying to load (minimized/maximized)
rangeLoader.loadedIndex = loadedIndex;
// Append the element to the hiddent div.
// Create the appropriate range request.
var rangeRequest = new XMLHttpRequest();
// Store the xmlHttpRequest on the off screen DIV.
rangeLoader.xmlHttpRequest = rangeRequest;
// Set properties/events
rangeRequest.onreadystatechange = rangeRequestReadyStateChanged;
// Also create a reference back from the XML Http Request into the DIV.
rangeRequest.divTarget = rangeLoader;
rangeRequest.loadedElementComplete = completeCallback;
rangeRequest.requestUrl = url;
// Set the targetParent on the off-screen DIV to be the element we actually want it to go into eventually.
rangeLoader.targetParent = targetElement;
// Send the async request.
As you can see from the function declaration, it actually knows how to take a fourth parameter. This will be used in a future post – for now, omitting the completeCallback parameter will default the value to the loadedElementComplete function (this was shown in the previous post).
Here’s what we do to fetch a range – just like before, we create an element which we add to the hidden DIV – this element will be used as a temporary store for the loaded data. We set up the newly created DIV and add it. We then create an XMLHttpRequest() object which we will point to the URL that will return the HTML fragment. We set up the callback (via the onreadystatechange property) and we also give a second callback (via the expando property loadedElementComplete) which is where the actual interesting stuff is going to occur. Finally, we send the the HTTP request.
The rangeRequestReadystateChanged() has one important job – it takes the result that comes back from the HTTP call and inserts it into the target DIV:
// Used by loadRange to figure out when a request is done.
if (this.readyState == 4)
if (this.status == 200)
this.divTarget.innerHTML = this.responseText;
The callback makes sure that the request is really complete, in which case it sets the inner HTML of the target DIV to be the resulting text and it then calls the callback that was set on it (that’s the expando completeCallback property discussed above).
And that’s it – from loadedElementComplete() we will make a call to adjustContent() that will resize the gadget and zoom out if needed (in the case of a minimized gadget).
With this post, we covered all the basic elements of the gadget. The next post will show how the flyout works and the one after that will discuss improvements and the like for future versions of the gadget.