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:

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.

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:

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.

In the past two weeks I detailed both the new REST functionality in Excel Services as well as the Excel Services Gadget (which will hopefully be available for download soon). In the previous post I showed how you can use URL parameters to REST URLs to modify the result you get back. In this post, I will show how you can create an incredibly compelling story for users by using both that and the gadget that I introduced earlier this week.

To start, I have a workbook that contains information about my team bug counts. On top of everything else inside that workbook, it also contains a cell where, when an alias (email) is entered, pars of the workbook recalculate to show the bug trend/counts for that specific person:

If you click on the image, you will see the cell called “Alias” (the one that currently has “shaharp” inside it). the green table you see contains information specific to the Alias cell (so, if you change the cell to another value, the data will update accordingly). On top of that we also have a table of links in the workbook:

For each person there are two links – one under the alias of the person and the other called “Home Site”. Here are the formulas for both:

Under the alias, the formula is: =HYPERLINK("http://tsdlt2/_vti_bin/ExcelRest.aspx/Docs/TeamBugs.xlsx/Model/Charts('PersonalBugs')?Ranges('Alias')=" & A1, A1)

Under the Home Site the formula is:=HYPERLINK("http://tsdlt2/PersonalPages/" & A1 & ".aspx", "Home Site")

As you can see, the first formula resolves to a hyperlink to a REST URL where the object shown is the same “PersonalBugs” chart but where for each person, a value is set into the “Alias” cell. In this way, a single workbook/chart can serve multiple purposes. Here, we pass in the alias to a cell and then get the chart that corresponds to that person.

Here’s how this works. First, you can see that the link show up in the gadget itself:

Now, remember, there are two links here. The Home Site one is just a run of the mill link and when clicked, will open up in your default browser:

As you can see, the link corresponds to an alias which then opens up in the browser according to the formula.

The interesting part is what we can do with the other link though. The gadget recognizes that the link is actually a REST link – and so it decides that it can actually display it as part of the gadget in a flyout:

Once the gadget loses it’s focus, or you click inside it, the flyout will collapse back into the gadget.

And that’s it for this week. This week I tried to show you some interesting ways of using the new Excel Services REST APIs and some of the new and exciting opportunities it gives developers and users. There was a post about the “essentials” of REST in Excel Services – a kind of “cheat sheet” for all the things REST can do. There were two posts about the Excel Services Gadget which showed the concept and then a post that explained the more advanced stuff REST can do by passing parameters into the workbook via the URL.

Next week will show the actual code running behind the gadget. Have a good weekend!

The last post I wrote, I showed the Excel Services gadget and how it can be a great tool for bringing your Excel data right to the desktop. In this short post, I will show some of the other capabilities it has – namely, the ability to show ranges on top of charts.

In the settings page of the gadget, you have the ability to choose not only charts, but also ranges, tables and PivotTables. The workbook I have contains a simple color coded “score card” which contains the number of bugs I have and the average number – I can choose that score-card to show when the gadget is minimized – in some cases, it can be a better indication than the chart shown in the last post. The actual workbook that contains the range is relatively simple – here’s what that specific range looks like:

Here’s what the settings page looks like – notice that I still want to see the chart showing my stats when the gadget is maximized:

Once you hit OK and the gadget refreshes, you will see the score card showing in the gadget itself:

And just like in the previous post – clicking on the “Maximize” button will show the chart.

Here’s a short video that shows exactly how this works and what it looks like:

Enjoy!!

Last week I posted a number of pieces about the various ways the new Excel Services REST APIs allows developers to leverage data and elements from within workbooks by giving direct URLs to them. This post contains details about the available capabilities of the REST APIs – essentially a list of all the entities that are available for deep-linking.

All the relative URLs presented are based off the base REST URL which I explained in the very first post – the base URL essentially follows the following template:

http://[server]/_vti_bin/ExcelRest.aspx/[DocLib]/[File.Xlsx]

The following table shows all the accessible resources off this base URL:

As you can see, there are a number of elements inside workbooks that are addressable via a URL. For example, the table I pasted in here actually resides within Excel Services. This means that it is addressable via the /Model/Tables(‘[Name]’) rows. The following example shows this very same table accessed in IE by a direct link:

The name of the table is “Table1” – because the HTML format is the default one when accessing tables, that means that one does not need to specify it to get HTML back.

That’s it for now. The next post will show you a solution written using REST which will allow end users to put refreshable data from Excel Services on their desktop via a relatively simple Windows 7 Gadget.

Margins. In script.

To set? Use element.style.marginTop.

To get in runtime? Use element.topMargin.

Arghhhhhhh! It's like DHTML is forcefuly trying to make me hate it.

In my previous post, I showed what embedding a chart from Excel Services inside a blog looks like. In this post, we will take a step back and explore the discovery mechanisms built into the Excel Services REST APIs.

Discovery allows developers and users to explore the content of the workbook either manually or programmatically by supplying ATOM feeds that contain information the elements that reside a specific workbook. Starting out with discovery is pretty easy. If you will recall, the link we used to access the chart in the previous post looked a like this:

http://tssrva/_vti_bin/ExcelRest.aspx/sites/sp2009/shared documents/Bugs.xlsx/Model/Charts(‘PersonalBugs’)

To start seeing how discovery works, simply chop off all that Charts business and you are left with:

http://tssrva/_vti_bin/ExcelRest.aspx/sites/sp2009/shared documents/Bugs.xlsx/Model

This is your anchor for starting discovery. Pointing your browser to this URL will show up the four resource collections we currently support:

You will notice that you get access to four resource collections: Ranges, Charts, Tables and PivotTables.

Clicking on the Charts link will bring to yet another ATOM feed – this one listing all the charts available in the workbook:

As you can see, there are two charts on the workbook – one called “PersonalBugs” (which was used in the previous post) and one called “TeamBugs”. Clicking on “TeamBugs” will navigate to the actual chart URL:

Using the ATOM feed provided by the REST APIs allows for an incredibly easy way of getting to the data you care about. The flip side of this is that since these are just ATOM feeds, they are readable by machine as well! So, in the previous screenshot, if you ask FireFox to give you the source of the document, you get a bunch of XML:

As you can see, the feed contains easily traversable elements that will allow any piece of code to go through and discover what elements exist in the workbook – each ATOM entry corresponds to a Chart that can be accessed.

This same mechanism applies to discovering PivotTables, Tables and Ranges. In the next post, I will show some of the capabilities revolving around those.

Now that Microsoft SharePoint Conference 2009 is done, I have some more time to start posting about all the nice things we have done with SharePoint 2010 – especially with Excel Services. In the coming months, I will post about Excel Services, concentrating on some of the new capabilities we enabled via our new extensibility mechanisms.

Since the new REST capabilities we built into Excel Services are close to my heart, I will start discussing them first.

Now, if you know what REST is, you will probably aching to comment that what I am going to discuss is not really REST. There are quite a few parts missing and some things that may not be exactly up to the “standard” (as much as it is one, which is not much). If it’s easier for you, you can also call it “URL facilitated access to spreadsheet component elements”. I find REST quicker to say.

The new API allows you to access parts of the workbook directly through a URL. The first part of the URL is a representation of the workbook location. The second part is the path to the requested element inside the workbook. In practice, there’s also a “marker” which is our entrypoint (an aspx page).

To start off with a simple example, say there’s a workbook called bugs.xlsx which lives in a document library called “Shared Documents”. Say that workbook contains a chart called “PersonalBugChart”. You can get direct access to the chart via a simple URL:

http://server/_vti_bin/ExcelRest.aspx/Shared Documents/Bugs.xlsx/Model/Charts(‘PersonalBugChart’)

Each Excel Services REST URL is built out of three parts:

Marker path: That’s the “http://server/_vti_bin/ExcelRest.aspx”. To access REST you always need to preface it with this.

File path: That’s the “/Shared Documents/Bugs.xlsx” part. This is the file that contains the element you are interested in.

Element path: That’s “/Model/Charts(‘PersonalBugChart’)” part. This is the path inside the workbook to the element you request.

That’s it. The result of this URL is a PNG stream which would be the chart that is named PersonalBugChart. When the workbook updates, the REST path will return the new chart – this is a completely live URL. Placing this URL inside an img tag on a page will place the image on the page:

<img src=”http://server/_vti_bin/ExcelRest.aspx/Shared Documents/Bugs.xlsx/Model/Charts(‘PersonalBugChart’)”>

If you have SharePoint 2010 Enterprise, try it with your own Excel workbooks! It’s as easy as that! Change the chart type, the contents, anything, and the next time the chart is requested, you will see the new content.

In the next post, we will discuss how you can access ranges both as data (via XML) and as HTML fragments.

I was going to get coffee today at work, minding my own business, when someone from the test-team asked me if the order I was doing things when preparing coffee was intentional. Absentmindedly, I explained the reasoning behind the order of things and how I reached this way of doing things and belatedly realized that I may have gone too far in my explanation – I think it was the look of pity in his eyes that clued me in.

To explain, first you need to understand what our kitchenette looks like and the various steps in making coffee:

Here are the various things we are seeing here:

1) Access – that’s where I typically come to the kitchenette from.
2) The coffee machine
3) Counter
4) Cups
5) Lids
6) Fridge (Where milk is).

Our coffee machines basically give you an Americano – no milk. The way I take my cup’o’joe is to do 12 oz of machine generated coffee (into a 16 oz cup) and fill another 2-3 oz of milk – and then the  cup needs to be lidded as well. Of course, there are a number of variations on how to make such a coffee with this layout.

Making Coffee – the naive way:

Step 1: Go to position (4) – grab a cup.
Step 2: Go to position (1) – place cup at machine.
Step 3: Press buttons - Wait for coffee to be made.
Step 4: Take milk from fridge (6)
Step 5: Fill cup with milk (possibly at position (3))
Step 6: Discard milk carton or, if still full, place back in fridge.
Step 7: Go to (5) and grab a lid.
Step 8: Place lid on cup.

And we are done. To understand how this can be optimized we first need to figure out how much each part of the coffee making process takes:

Making coffee (the machine phase): ~2 minutes (constant).
Getting milk (from position (1)): On average, takes about 20 seconds. Depending on traffic, can easily take as long as 1:00 minutes if there is a lot of traffic and even longer if you consider the possibility of people talking to you while you are making the coffee.
Getting a cup: Usually takes about 5 seconds from position 1 – can take longer due to traffic and chat. Rarely takes more than 30 seconds and requires little concentration.
Filling cup with milk: Takes about 5 secodns.
Discarding/Returning milk: Takes about 10 seconds. Worst case (traffic etc) can take up to a minute.
Grabbing a lid: 5 seconds from position (1).
Lidding the cup: 5 seconds at position (3).

So.. Translating this into pseudo code, this is what we get:

function NaiveCoffeeMaking()
{
WalkTo(4);
GetCup(); // 10s
WalkTo(2);
PlaceCupInMachine(); // 1s
MakeCoffee(); // 120s
GrabCupFromMachine(); // 1s
WalkTo(6);
GetMilk(); // 20s
PourMilk(); // 5s
DiscardOrReturnMilk(); // 10s
WalkTo(5);
GrabLid(); // 5s (probably closer to 3s in this case)
WalkTo(3);
LidCup(); // 5s
}

Phew.. Okay.. So we have that. Now lets calculate how much time this takes (best case):177 seconds!

The thing to consider though is that the machine phase can be asynchronous – that is to say – while the machine is making coffee, the CPU (me) is waiting Idle. The question is, how can we use that to optimize the time? For our pseudo code then, we will now separate MakeCoffee into two functions: BeginMakeCoffee() which essentially means pressing the buttons on the machine and immediately return to the caller and EndMakeCoffee() which completes the operation (and if not done, blocks until done). And so, the most optimized version of how to make coffee is:

function FullyOptimizedCoffeeMaking()
{
WalkTo(2);
BeginMakeCoffee(); // 120s – but returns immediately
WalkTo(4);
GrabCup(); // 5s
WalkTo(6);
GetMilk(); // 20s
PourMilk(); // 5s
DiscardOrReturnMilk(); // 10s
WalkTo(5);
GrabLid(); // 5s (probably closer to 3s in this case)
WalkTo(2);
PlaceCupInMachine();
EndMakeCoffee();
LidCup(); // 5s
}

Notice that when we call BeginMakeCoffee(), we immediately return and continue processing – going through the phases of getting the milk, preparing the cup and only then, when done, placing it in the machine. So, in this case, best case time would be: 125 seconds. That’s 120 seconds that it takes to make the coffee plus the 5 that it takes to lid the cup. The rest of the time is taken up in parallel to making the coffee.

But there’s a problem! BeginMakeCoffee() and EndMakeCoffee() do not work exactly like a machine async operation. Generally speaking, when you issue an async operation, the sub-system will take care to store the result for you when it’s done and wait patiently for you to call the second part of the operation (EndMakeCoffee) before it returns the result. A coffee machine does not work that way. When the coffee is ready, it’s coming out – whether there’s something to store it or not. That presents some problems for the FullyOptimizedCoffeeMaking() function! What happens if instead of talking about Best case, we were to talk about worst case. The GetCup() can sometimes take 30 seconds. GetMilk can sometimes take 120 seconds. As well as some of the other things that make take longer under high-stress cases. What happens then? Well, what happens is that the coffee starts pouring and there’s nowhere for it to go, so it gets wasted. Sacrilege!!

In essence, we need something that’s both optimized AND safe. Since life does not have convenient temporary memory (where the coffee would have been stored) or sync objects (where the act of pouring the coffee could have been locked and deferred until a cup is to be placed underneath the nozzle), it means we need to work around those limitations:

function OptimizedAndSafeCoffeeMaking()
{
WalkTo(2);
BeginMakeCoffee(); // 120s – but returns immediately
WalkTo(4);
GrabCup(); // 5s
WalkTo(2);
PlaceCupInMachine();
WalkTo(6);
GetMilk(); // 20s
WalkTo(2);
PourMilk(); // While cup is in the machine – 10s
WalkTo(6)
DiscardOrReturnMilk(); // 15s
WalkTo(5);
GrabLid(); // 5s (probably closer to 3s in this case)
WalkTo(2);
EndMakeCoffee();
LidCup(); // 5s
}

The CPU (me) now needs to work more – you will notice that there is more walking around to accommodate the new placement of the cup (it can no longer be carried around – one needs to walk to it to pour the milk and then back to the fridge to discard or put back). The time this now takes is still 125 seconds - 120s to make the coffee and prepare the cup and another 5s at the end when the cup is lidded. Switching the lines around and doing more leg work though has bought us a lot more safeness.

Note that this is still not perfect – if the 5s it takes to get a cup and place it in the machine would balloon from 5s to 120s, we would be right back in our original position of losing our coffee. However, that is far less likely to happen in this case. If you want to eliminate risk completely, simply switch BeginMakeCoffee() with GrabCup() and PlaceCupInMachine(). You will lose 5 seconds (making the total time 130 seconds), but you are then completely safe.

So that’s what I was trying to explain to the tester when that look came to his face. The thing is, I am sure everyone does this. But people probably do this unconsciously or at least in a less obsessive compulsive analytic manner than a software developer would.