Coding the Excel Services Windows 7 Gadget – Part 1 - Settings
In the next few days, my posts will revolve around the mechanisms that make the Excel Services gadget tick. I won’t talk a whole lot about Gadget development – that could easily take up 4 or 5 posts – I will give a very brief overview though on what a gadget is and how it works. You can read more about gadgets on msdn and on blogs around the tubes.
Windows 7 (and vista) gadgets come in various flavors. By far the most popular are the ones based on HTML and Javascript because they are very easy to write and deploy. When writing a gadget in HTML, there are 3 basic parts that you need to code:
1. What appears in the actual gadget: That’s the guy that’s usually visible on the desktop. It can have 2 “modes” – big/small, maximized/minimized or docked/undocked (in Vista you used to be able to dock it). The actual content is represented by an HTML that’s in the gadget package – the HTML gets loaded and from that point you can consider it the gadget “application”.
2. The settings window: Gadgets have little wrench icons attached to them – clicking on that icon will bring up a form you can use to edit your settings. The infrastructure gives you the ability to save settings via special methods supplied off the Gadget object. The settings window is also essentially an HTML page that the Gadget HTML sets up as part of it’s initialization.
3. The flyout window: Some gadgets have functionality where by you can click on some UI element and a “flyout” view will shoot out of the side of the gadget, allowing it to supply more information. The flyout appearance and disappearance is governed by the Gadget HTML (again, via special methods off the Gadget object available in the javascript OM).
Each post will tackle one of these aspects of the gadget. I am not going to go too deeply into DHTML and the like (because DHTML and JS make me bleed from my eyes) – I will only go into the areas that I think are critical or especially interesting.
This first post will talk about the settings window – this is what it looks like:
The settings page has two important parts to it. The first is the bit that knows how to take a workbook, extract the various items that are available in it and put them in the combo boxes for the user to select from. The second is actually writing the information to the gadget settings store so that it can be later extracted and used to display the appropriate information. By far the more interesting part here is the way we do the discovery. So lets delve into that. All the code for the Settings screen is encapsulated in the settings.js file in the gadget and the HTML that is displayed is the one contained in Settings.html (I am pretty imaginative with names!)
When a user clicks on the “Refresh” button, the following JS code runs:
// This call issues 4 async requests to the Excel Services REST API to discover what elements exist in the workbook.
// An async call is made each to the Charts, Ranges, Tables and PivotTables.
// when each call is done, it will call back into the onAtomRequestComplete function which will tally the results and take care
// of updating the UI once done.
// Each call takes the base URL (which is essentially https://[server]/_vti_bin/ExcelRest.aspx/[Path to workbook]) and appends the
// appropriate URL for it.
function refreshFromWorkbook()
{
feedsReturned = 0;
var refreshReq = null;
refreshHadErrror = false;
refreshReq = new atomRequest(get_uiBaseDocument() + "/Model/Charts?___nocache___=" + new Date().getTime(), chartsIndex, onAtomRequestComplete);
refreshReq.send();
refreshReq = new atomRequest(get_uiBaseDocument() + "/Model/Ranges?___nocache___=" + new Date().getTime(), rangesIndex, onAtomRequestComplete);
refreshReq.send();
refreshReq = new atomRequest(get_uiBaseDocument() + "/Model/Tables?___nocache___=" + new Date().getTime(), tablesIndex, onAtomRequestComplete);
refreshReq.send();
refreshReq = new atomRequest(get_uiBaseDocument() + "/Model/PivotTables?___nocache___=" + new Date().getTime(), pivotTablesIndex, onAtomRequestComplete);
refreshReq.send();
// Update the UI to show that we are querying.
setRefreshStatus("Connecting to server...");
showRefreshIndicator(true);
clearLists();
}
When the user asks to refresh from the workbook, we will issue four different discovery requests – one for each of the four resources we know we can represent on the gadget: Charts, ranges, tables and PivotTables. The gadget is using a class called “atomRequest” which is fairly straightforward wrapper around the XmlHttpRequest which is the class used to issue asynchronous HTTP requests via script. Because these are async requests, two things are apparent:
1. They both need callbacks to be specified (so that we know when they are done running) and
2. We can issue multiple requests at the same time, running them in parallel*.
The get_uiBaseDocument() method call takes the name of the workbook specified in the text-box on the settings window and turns it into the REST URL base that we need. For each request we append the appropriate resource relative URL (/Modek/Charts for charts for example) and we also add another URL parameter called __nocache__ which is not used by REST, but will cause the system to not try and use cached versions of the returned data. Here’s the actual code behind get_uiBaseDocument() – as you can see it’s a fairly straightforward string manipulation method that sticks “_vti_bin/ExcelRest.aspx” in the middle of the URL. Could probably have been written more efficiently with some regex magic.
// Takes a document URL (https://server/doclib/file.xlsx) and turns it into a REST URL
// (https://server/_vti_bin/ExcelRest.aspx/doclib/file.xlsx)
function baseRestFromDoc(doc)
{
if (doc.toLowerCase().indexOf("_vti_bin/ExcelRest.aspx") > -1)
{
return doc;
}
var newUri = new String();
var startIndex;
if (doc.substr(0, 7).toLowerCase() == "https://")
{
newUri = doc.substr(0, 7).toLowerCase();
startIndex = 7;
}
else if (doc.substr(0, 8).toLowerCase() == "https://")
{
newUri = doc.substr(0, 8).toLowerCase();
startIndex = 8;
}
else
{
return "";
}
doc = doc.substr(startIndex);
var server = doc.substr(0, doc.indexOf("/"));
newUri += server;
newUri += "/_vti_bin/ExcelRest.aspx";
newUri += doc.substr(server.length);
return newUri;
}
All four async requests have the same callback. That callback will track which one return and once all are done, will continue to the next step:
// This is the callback that gets invoked when each of the ATOM request is done.
// The atomRequest is the instance of the JS object we use to make calls - it is defined in utils.cs/
// This method keeps tally on how many calls came back. Once all 4 come back, it updates the UI appropriatley.
function onAtomRequestComplete(atomRequest)
{
if (atomRequest.hasError || refreshHadErrror)
{
// If we errored out, mark the global as such.
refreshHadErrror = true;
}
else
{
// Otherwise retain the result - we will use it once all 4 requests are done.
refreshResult[atomRequest.userState] = atomRequest;
}
// Tally how many came back.
feedsReturned++;
// Once all feeds came back, update the UI.
if (feedsReturned == feedCount)
{
showRefreshIndicator(false);
if (refreshHadErrror)
{
// We had an error - set the status line to say that.
setRefreshStatus("There was an error trying to fetch charts.", "red");
}
else
{
// Need to fill the selection.
fillSelect(refreshResult[chartsIndex], config.typeChart, "Charts");
fillSelect(refreshResult[rangesIndex], config.typeRange, "Ranges");
fillSelect(refreshResult[tablesIndex], config.typeTable, "Tables");
fillSelect(refreshResult[pivotTablesIndex], config.typePivotTable, "PivotTables");
setRefreshStatus("You can now select elements from the workbook.");
}
}
}
For each of the async calls that comes back, the callback checks on the error status and stores the result in a global array (refreshResult). It also counts how many requests have come back already. When the number that comes back is the number we issued, the code will take the entire result set and update the UI with it.
The fillSelect() method simply takes the ATOM feed that is returned, iterates over the elements that are there and adds them to the combo-boxes under the appropriate header so that you get the following result in the combo-box:
And that’s about it for the REST interaction in here. Once the user hits “OK”, the gadget uses a class called config (defined in config.js) to store the data into the settings repository of the gadget. The Gadget is also listening to the OK button and will update once it’s pressed (showing the data the user requested).
As you can see, the code needed for doing discovery is relatively simple. The missing part is the atomRequest class which in itself is fairly simple and the code that adds elements into the combo boxes which is not Excel Services specific.
Next up, updating the gadget to show the actual data/charts!
* Well, semi-parallel – the gadget infrastructure may or may not limit how many opened connections we can have concurrently.
Comments
Anonymous
October 28, 2010
Hi - sorry for the stupid question. But where can i download the gadget from? Thanks for an excellent series of blog posts on this. Cheers AndrewAnonymous
November 02, 2010
Great question Andrew, did you get an answer?Anonymous
November 02, 2010
Great question Andrew, did you get an answer?Anonymous
September 19, 2011
where do i download the gadget fromAnonymous
January 19, 2012
yeah ... another four months later and not a working download in sight . . .Anonymous
May 28, 2013
More than one year later... and not a working download in sight.... :(Anonymous
February 04, 2014
The comment has been removedAnonymous
April 03, 2014
I know its very very late request. Can someone post a working gadget download link? After I download the gadget from above mentioned link, i do not see it in my gadgets list. Thanks