This post is going to be pretty light-weight. It will show how to use the OpenWorkbookForEditing and SetCalculationOptions methods. I won’t create some over-arching example – the skills you have in interacting with sessions in Excel Services are the going to be the same. I will discuss the behavior and what one can expect.

OpenWorkbookForEditing

In SharePoint 2007, developers could open workbooks for transient operations – once a workbook was closed, the sessions went away and all changes to the workbook disapeared. with the introduction of the ability of editing workbooks on EWA, the Web Services API follows suit and allows the developer to apply editing to the workbook. The same methods used to change values in a view-session (that’s what we call the SharePoint 2007-type sessions) work in editing and they work the same way as they did before.

Here’s what an OpenWorkbookForEditing call looks lilke:

Of course, the first thing you notice is that there’s not much difference between the call to OpenWorkbookForEditing() and OpenWorkbook(). The important part though is the fact that once this function exists, the workbook will be saved, recalculated back into the server. A few things to know about using the WSAPI to edit workbooks:

1. Calling CloseWorkbook() does not really close the workbook – it just takes the API out of the list of people using the workbook.

2. The workbook will save regardless to whether or not you call .Save(). Because Excel Services uses an AutoSave mechanism for saving workbooks, any change is actually written to the workbook.

3. When editing a workbook, you may get an error if the server recycled by the admin or crashed (this is exactly the same as with view sessions). However, when doing editing, re-opening the workbook has a very good chance of having your changes in it (again, because of auto-save).

4. When opening a workbook via the API, the presence UI in Excel Services (the thing that shows you who is editing the workbook) will show the user under which the API is running:

In this case, xladmin is the user inside EWA, viewing the file, and shaharp is the user who is running the APIs. The little swirly icon you see will be replaced by something that indicates that  it’s an API user (I believe we chose a Robot for that).

SetCalculationOptions

An API that was requested often by user is the SetCalculationOptions API which can change whether or not a workbook calculates automatically. It is especially useful if you have a workbook that is, by default, auto-recalced and you want to disable that feature while calling multiple SetRange/SetCell calls. That way, the workbook needs not calculate each time and developers can get more perf out of it. The call is straightforward:

This call will change the calculation mode to manual, meaning that when you set values into the workbook, it will not recalculate. You can turn auto-recalc back on by passing in the Automatic enum value.

I also want to show you the SaveWorkbookCopy() API, but I will show it in one of the future Javascript examples I will post.

So far we saw how one can call into the EWA at arbitrary times and respond to events by affecting the page around the EWA. This time around we will combine the two mechanisms to make modifications to the workbook when the selection changes.

If you will recall, in part 1, we listened to events over the list of aliases in the workbook, giving the dashboard the ability to show the picture of the relevant person. This time around, we will also take the name and “write” it into another cell in the workbook. That in turn will update the EWA and show us the updated result for that person.

Step 1 – Figure out the range we want to write to

Working with ranges is an inherently asynchronous operation – before you can actually write to it, the system needs to know where it is. For that, we need to make a call to the getRangeA1Async() method of the workbook. Note that if we wanted to go by coordinates, we could call the getRange() method which is not asynchronous and thus may be more fitting for some scenarios:

The highlighted code is what we added over the existing method from the previous post. In this case, we ask for the cell named “Alias” and we tell the EWA to call into getRangeComplete() when it has the object ready. Note that we also pass in value as a parameter – we do that so that we later know what value to put into the cell (we could also have used a global variable for this if we were so inclined).

On top of that, notice that we added a condition where the code executes only in the case where the sheet name has changed. This is important because the cell-changed event also occurs when a sheet changes. This means that the second the user tries to switch back to the first sheet (where they originally clicked), since the active cell is still the same one, the event will fire, the code will run and immediately move the user again to the other sheet. Checking that lastSheet equals the sheet name that we currently are on makes sure we only run the code for actual navigation within the sheet.

Step 2 – Write the new value into the cell

Once the callback gets executed, we need to call into the setValuesAsync() method:

The first step in the callback of getting the range is to get the values out of the asyncResult parameter – getReturnValue() is the standard method we use to get the result of the operation. getUserContext() is the method we use to get the user state passed as the last parameter to all asynchronous methods.

We then create an array out of the value ([[value]] creates a jagged array with a single item in it). That is passed into the setValuesAsync() method which will update the EWA. No callback is being passed to the setValuesAsync() method – this is because we don’t need to take further action. If we were to add more error handling, we would have had to use that method.

Clicking on “shaharp” and flipping to sheet2 (which is where the Alias named range is) will show us that the cell has indeed changed and with it changed the chart:

Step 3 – Navigating to Sheet2 for the user

The final step is to make sure the EWA actually shows the relevant information when the user clicks on the cells. For that we will use the range.activateAsync() method. We could have waited for the setValuesAsync()  to complete before making this call, but there’s really no reason – Excel Services can take multiple calls at the same time. This means we simply add the to the getRangeComplete() function:

And that’s it! Clicking on “danyh” for example, will cause sheet2 to get activated and the “alias” cell to change to “danyh” which in turn will cause the chart to change accordingly:

And we are done!

As you can see, the JavaScript OM gives developers the abilities to add more capabilities to their workbooks and code around them. Do not expect an OM as rich as Excel’s, however, the capabilities are nice to start with – I would love to hear from you through the comments what do you think is missing from the OM.

In the past few weeks, I wrote a bunch of posts about the new REST APIs in Excel Services. I wanted to take a bit of a breather from those APIs and start writing about the new JavaScript browser-side OM we are shipping with SharePoint 2010. This OM will allow developers to customize, automate and otherwise drive the EWA control and build more compelling, integrated solutions.

In the next few posts, I will show how developers can use the new OM to do all kinds of things to the EWA.

Step 1 – Creating the page

To begin though, we will need an EWA and a content-part. The example will revolve around a fictitious dashboard for my teams bugs – each post will add more features to the dashboard. For this, we will create a new Web-part page in our site – from the “Site actions” drop down, choose “More Options” (Note – all these steps are very similar to what you would do in SharePoint 2007 – I am just giving them for completeness):

Once chosen, you will be presented with a host of options of what to create – select web-part page:

After that, you can choose whatever layout you want – in this case we will select the “Header, Left Column, Body” and call it “TeamBugsDashboard”. Once it’s created, we can easily add an EWA to the page by clicking on the right column “Add a Web Part” area:

Selecting the Excel Web Access web part and then hitting add will place it on the web page. We will do the same with a web-part called “Content Editor” under the “Media and Content” category – this time adding it to the left column. The result should look similar to the following (the left circled area is the content element and the right one is the Excel Web Access web-part):

Next, we will select the workbook we want displayed in the EWA – to do that, we will use the dropdown that’s on the header of the control and choose “Edit Web Part”. This will present with a pane on the right where we can put the name of the workbook – in there we can type the URL to the workbook in the “workbook field”.

Hitting the “OK” button will allow the EWA to show the relevant workbook. This is where things become a little interesting – the content web-part will contain a reference to an HTML fragment that’s contained in another document library (we will show the actual content of the HTML fragment shortly):

This tells the content web part to get the file linked and embed it as part of the web-page.

Step 2 – adding the JavaScript OM skeleton code

Now, to start working with the JavaScript OM, there’s some standard code that needs to run – the following is the skeleton piece of code you need to add to your page when you want to interact with the EWA:

That’s pretty much it for the skeleton or bootstrapping code – you can pretty much copy&paste this code whenever you want to interact with an EWA on a page. In a future post I will show how to find a specific EWA control (in the case where you have more than one). The code is relatively simple – the first block in the script will execute and add an event handler to the page so that we know when it loads (the reason we use the attachEvent/addEventListener mechanism is so that we do not override the page load event). Then, when the ewaOmPageLoad() method is called, we make sure that the OM is loaded (that’s the call to typeof(Ewa) != undefined and if it is defined, we go ahead and add another callback to an event – this time for the applicationReady event on the EWA – this is done by calling the add_applicationReady() method. This will fire once the EWA itself is up and running. When that happens, the code executing in ewaApplicationReady will store the EWA control reference in a variable so we can start using it.

Step 3 – Making the EWA recalc periodically

Now is when we start adding actual functionality. To begin, we will address one request dashboard writers sometimes express – the means to periodically recalculate the dashboard so that it shows up-to-date information w/o requiring user interaction. To do that, we will call into an OM method called recalcAsync(). Lets start simple – here’s what the ewaApplicationReady() function looks like after the call to recalcAsync:

The setTimeout() call will ensure that we do not immediately start recalculating, but instead wait for however we decide we want between. Next, this is what happens in the forceRecalc() function:

Again – a fairly simple call – we use the ewa variable to get the currently active workbook (getActiveWorkbook()) and call into the recalcAsync() method. Now, since most operations that actually have to do with the workbook that resides on Excel Services need to get to the server and back, almost all of those are actually asynchronous methods. As part of the call, you pass in the callback (recalcComplete() in our case) and an opaque user state object (null – we don’t need it). Once the recalculation is done, the EWA will call us back. Note – in some cases, you do not really care when the call comes back – in these, you can simply pass in null (or omit the parameter) and treat this call as a “fire and forget” call. The reason we care about when the call is done in this case is because we want to make sure we queue up another calculation request when the first one is done. Here’s the recalcComplete() callback:

Notice that we actually ignore the result in this case – we just make sure we issue another call to the recalcAsync() method. Since this will continue to happen over and over again, we will see the EWA recalc every RECALC_INTERVAL milliseconds.

By the way: The reason we are using setTimeout() and not setInterval() is so that in the case where the server is swamped, we do not bombard it with calls to recalc – this way we guarantee that we only tell the EWA to recalculate after it has actually finished the last time we asked it to recalculate.

That’s it for this post – you now know how to start working with the new JavasScript OM Excel Services is exposing – in the next few posts, we will explore some of the other functionality exposed and how it is used.

We just went through two or three weeks worth of posts and I tried to show you some of the new stuff we can do with Excel Services. It’s cool, it’s exciting, it’s great. But here comes the caveat post!!

There are a number of things that do not currently work in REST – the following is a partial list, but it contains the more painful missing parts:

1. No floating charts – If a range contains a chart on it and you request the range via REST, you will get just the range – where the chart is, you will actually see the stuff that’s under it.

2. No sparklines, no icon conditional formatting – due to resource constraints, we do not do either of these. We know it’s a shame, and we want it to work too, but it’s just going to have to be one of those things we add later on.

3. Not pixel-perfect with EWA – even though the HTML that REST produces is very close to the HTML the EWA produces, it’s not perfect. The fact that we do not have access to all the CSS elements that EWA has is part of it (remember, we return an HTML snippet – it needs to be self contained as much as possible).

4. No distinction in tables (when requested as ATOM) about whether or not something is a column head or a total.

5. URL size is limited to around 2000 characters. That means that if you have a large number of parameters, you may not be able to set all of them (especially if the workbook itself is pretty deep in folders).

There are probably a number of other limitations and caveats – I will add them here as you guys complain about them.

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:

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:

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:

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

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 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.

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):

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:

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:

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).

One of the fun things about working on the programmability aspects of Excel Services is realizing just how many interesting scenarios exist that you never thought of originally. I got an IM from Dany Hoter asking for help on an issue he ran into with one of his customers. I have been working with Dany for about 10 years now and he always has interesting questions and ideas. This time around, the problem revolved around a complicated word document that contained links to Excel data – the word document would update and grab data from the workbook via automation (OLE embedding essentially). For a number of reasons, Dany wanted to remove the dependency on the Excel client and was wondering how to go about it. Eran from the Excel Services team suggested looking into fields and that’s how Dany landed in my IM window.

This is where the fun part begins. In Word, one can insert fields into the document that have certain functionality. The most used ones are probably the document Index, page numbers and Date/Time. One of those fields is an IncludeText field which can take text from various sources and put it inside the field. To add such a field, you use the “Quick Parts” drop-down in the “Insert” ribbon tab:

Once selected, you are presented with a slew of options – the one you want is the “IncludeText” field:

When the IncludeText field is selected, the user is presented with two more panes – the file name or URL from which to take the data (that’s in the middle) and the options of the field (that’s on the right).

In the URL field, we supply an Excel REST URL that points to our desired cell – this should be a REST URL that’s presented in the Atom format – that way we can traverse the data and get back what we want. In my post about getting range data from Excel Services, there’s an example for such a link. Here’s what the link looks like for our case here:

http://tsdlt2/_vti_bin/ExcelRest.aspx/docs/Bugs.xlsx/Model/Ranges('PersonalBugCount')?$format=atom

As you can see, we are going to a spreadsheet called “Bugs.xlsx” and asking for the range called “PersonalBugCount”. The parameter $format is set to atom to instruct Excel Services to return an XML representing the data. The result (removed some elements for brevity) looks like this:

We now need to set two more fields in the dialog – the namespace mappings and the XPath expression – luckily, for the case where you want a single value, both of these are the same and require no modification.

Namespace Mapping is used in conjunction with the XPath expression – the XPath expression needs to know what aliases to use for what namespaces. In this case, we have two namespaces we need to use:

http://www.w3.org/2005/Atom – this is the namespace for the ATOM scheme.

http://schemas.microsoft.com/office/2008/07/excelservices/rest – this is the namespace for the Excel REST data scheme.

Both of these will need to have aliases associated with them (the aliases are not really that important). The setting that should be entered in the “Namespace mapping” field is then:

The XPath needs to point to the “fv” element that’s in the XML and so it should be set to:

And that’s pretty much it! Once you hit OK, the dialog will close and you should see the value from the workbook. Here’s text from a word document with fields toggled to see the codes:

And here’s the text as it appears when the fields are shown as text:

The highlighted areas are the values that came from Excel Services. The cool part is that when the workbook updates and the word document is updated, this data will update as well, showing the new values!

Now, another nifty aspect of this is that the same mechanism can be used to embed richer data – like HTML. If you will recall, at the bottom of this post I showed how one can access Ranges from Excel and get back HTML fragments. Guess what – Word knows how to embed those as well. Using the same mechanism, we can enter a field and point it to a REST URL returning an HTML fragment. In this case, we do not need the namespace mappings or the XPath expression – just the URL (in this example a URL to a range that looks like a score-card):

http://tsdlt2/_vti_bin/ExcelRest.aspx/Docs/Bugs.xlsx/Model/Ranges('PersonalScoreCard')

The URL resolves to the following HTML:

When embedded in the workbook by using the IncludeText field, this is what it looks like:

The same thing applies as before – if the data of the workbook changes and the scorecard data and formatting changes, the next time the Word document will be updated, this text and formatting will also be updated.

Finally, if you look just above the IncludeText field, you will see an IncludePicture field – using that allows you to embed live images from Excel Services within the workbook – in this example, the URL we will embed is the chart that shows the progress made on the bugs and the goals:

To summarize – as you can see, you can create richer and self-updating documents in word by using fields that connect to Excel data and bring back plain text (the first example with the Atom) or formatted text (the second example with the HTML fragment) or charts (last example). These can be updated at will just like any other field in Excel and guarantee that your document will contain data that is up to date and is synchronized with the source – no more checking to see if the data was copied properly etc.