Cum Grano Salis : Excel Services

So what else can OpenXML + ASPX + Excel Services can do?

Shahar — Tue, 15 Dec 2009 17:22:00 GMT

In my previous post, I ~~stole code that~~ showed how developers can write code that will generate XLSX files by using the fancy OpenXML SDK on the fly. At the end though, I explained how a workflow activity would have been much better for the scenario I presented, at least from a performance point of view.

In this post, I will show the real power behind this pattern of generating files on the fly and feeding them to Excel Services. Instead of just using the data from the document library (the list of word documents), what if we enabled user input?

If you recall, the URL I used in the previous post was this:

http://tsdlt2/_layouts/WorkbookGeneratorSample/SpecStatus.aspx

And that produced the following chart:

But what if I only wanted people from my team – say only Chris Stich and Greg Lindhorst?

Here’s the URL I can pass in:

http://tsdlt2/_layouts/WorkbookGeneratorSample/SpecStatus.aspx?pm=Chris+Stich&pm=Greg+Lindhorst

In this case, the ASPX code will know how to look at the query-string and figure out what names I am interested in. Here’s the result:

The way this works is very easy. Zayed’s original code would look through the entire list of documents in the SharePoint document library and generate a chart out of all of them. I added code, essentially, in three places. First, when loading the page, I figure out what names need to appear:

protected void Page_Load(object sender, EventArgs e)

{

HttpContext ctx = HttpContext.Current;

string[] names = ctx.Request.QueryString.GetValues("pm");

byte[] buffer = GenerateWorkbookSteam(names);

ctx.Response.Clear();

ctx.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

ctx.Response.Headers.Add("LAST-MODIFIED", DateTime.Now.ToString("R"));

ctx.Response.OutputStream.Write(buffer, 0, buffer.Length);

ctx.Response.OutputStream.Flush();

ctx.Response.End();

}

Then, when deciding on whether or not to add rows to the workbook, I make sure we only add the rows if the PMs are on the URL (or if there’s nothing on the URL (note that most of the code is in Zeyad’s original post – I am just showing what I modified here):

//Go through our spec hashtable so we can add the data to workbook

IDictionaryEnumerator enumerator = specTable.GetEnumerator();

while (enumerator.MoveNext())

{

string pmName = (string)enumerator.Key;

if (names != null && names.Length > 0 && !names.Contains(pmName, StringComparer.OrdinalIgnoreCase))

{

continue;

}

The highlighted area is the one that was added – it simply looking at the names that were passed in as URL parameters, and if the name currently being procesed (pmName) is not one of them, it ignores the set of documents and does not add the row to Excel.

Finally, Zayed’s FixChartData() method had to be tweaked to make sure the chart has the correct amount of elements:

void FixChartData(WorksheetPart worksheetPart, Hashtable specTable, string[] names)

{

int totalCount = specTable.Count + 1;

if (names != null && names.Length > 0)

{

totalCount = names.Length + 1;

}

And that’s about it! All in all, we added about 12 lines of code to the original code developed by Zeyad and we have a solution that allows us to target exactly which PMs we want to look at.

In the posts to come, I will show more tricks and uses for this idea of generating Excel workbooks on the fly.

Zeyad Rajabi just got served – Extending the reach of Excel Services with the Open XML SDK – now with more awesomness

Shahar — Mon, 14 Dec 2009 16:49:00 GMT

A few days ago, Zeyad Rajabi posted a really nice post about using OpenXML with Excel Services. His solution showed an Excel chart that you use to update a workbook and generate a chart showing, in the case of his example, the status of specs in his group.

When I saw this post, I decided to ‘one up’ it and give my spin on it, and by that introduce one of the less known new features in Excel Services on SharePoint 2010 – the ability to point Excel Services at any URL that returns a workbook – not specifically ones that are extensioned as such. More on the general case in a later post. This post will show how we can take Zeyad’s solution and turn it into a dynamic solution.

To summarize Zeyad’s solution – the idea is to have a chart in Excel represent how many “open issues” a list of specs have for each PM in the team, furthermore broken out by what stage the document is in. The owner of the document is deduced from document properties and the amount of “open issues” is deduced from looking at the Word file and searching for a specific style applied to paragraphs (we have a special style for marking opened issues). If you read Zeyad’s great post, you will see the work done there – essentially, you have a web-part where you click on an “Update” button and that generates the new workbook (with the updated chart) and saves it into SharePoint. Here’s a screen-shot I pilfered from Zeyad’s post:

You can see the little buttons above the chart – you click the left one to cause the chart to get regenerated and then on the right one to cause the chart on the page to display.

Here’s what my solution looks like (I have less specs in my library, so my graph is less impressive):

Note the missing “Update” button. So how does the workbook get updated? To understand that, it’s important to see what the URL is that the EWA is looking at:

As you can see, the workbook URL is pointing to an aspx page. “That’s tom-foolery!” I can hear you crying. Not quite. Here’s the code in the ASPX page:

protected void Page_Load(object sender, EventArgs e)

{

HttpContext ctx = HttpContext.Current;

string[] names = null;

byte[] buffer = GenerateWorkbookSteam(names);

ctx.Response.Clear();

ctx.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

ctx.Response.Headers.Add("LAST-MODIFIED", DateTime.Now.ToString("R"));

ctx.Response.OutputStream.Write(buffer, 0, buffer.Length);

ctx.Response.OutputStream.Flush();

ctx.Response.End();

}

The call to GenerateWorkbookStream() is essentially the method called btnSubmit_Click() in Zeyad's post with very little change – instead of saving the binary stream back into SharePoint, GenerateWorkbookStream() returns the byte array to the caller.

What we do with that byte array is write it as the response to the ASPX page (that’s where we make the call to ctx.Response.OutputStream.Write()). The other important parts are the headers we set and the call to Response.End() which finishes the call and returns the result to the caller.

The other two lines are where we set the header for the response. First, we set the content-type to be that long string which is the content type of an OpenXML Spreadsheet file. The second one we set is the “LAST-MODIFIED” which is required for Excel Services to process the file (the file would not be loaded if this header is not set).

That’s it – the rest of the code is pretty much the same. Here’s a diagram of what happens:

When I told Zeyad I was going to post about this, we talked a bit about the solution, and he pointed out that, much better than my solution or his, it would have been nice to have a workflow that actually updated the workbook whenever a word document was updated in the doc lib. That way, we only regenerate the workbook when we need. The next post I will make will show how the dynamic workbook solution can do so much more.

“Something interesting” Sample app – Coding – Part 4 – Summary

Shahar — Fri, 11 Dec 2009 17:22:00 GMT

This post shows how to code the sample application shown on this blog. This continues part 3 of this coding series.

In this series, I tried to show how you can play with multiple technologies to achieve your goal. In this case, this “app” uses the following SharePoint 2010 technologies:

1. Excel Web Services: Because the EWA JavaScript OM does not support saving of workbook copies, we needed to use this API to save the workbook.

2. Excel Web Access (EWA) JavaScript OM: We used this to figure out what the user who publishes is looking at and to select that region when other users open up the “interesting” link.

3. SharePoint 2010 REST List APIs: ListData.svc is a powerful new mechanism in SharePoint 2010 that you can use to browse and updated the various lists (Document Libraries, list of links etc).

4. EWA: To show the workbook and allow users to interact with it.

I also wanted to spend some time on what’s missing, or what could be done better in the app. Obviously, when writing samples (and when you are as lazy as me), corners get cut:

1. There is virtually no error handling or even checking in the code.

2. Instead of passing the actual title in the link, it would have been better to pass the list-item ID and use that with another REST request to get the data from SharePoint (that way, you can also fill in the notes in the text-box area).

3. The visuals could be made to look a little less appalling by somebody with even meager skills in HTML.

“Something interesting” Sample app – Coding – Part 3 – Showing the interesting stuff

Shahar — Thu, 10 Dec 2009 17:00:00 GMT

This post shows how to code the sample application shown on this blog. This continues part 2 of this coding series.

In this stage, I will show how the link we generated in the previous link causes the page to show correctly to the user.

In the previous post, we created a link that looks like this:

/Docs/SomethingInteresting.aspx?url=[url to saved workbook]#[range]|[title]

In part 1 of this series, we took care of the url query string parameter (right after the “?”). The part-to-part mechanism of SharePoint will make sure that the link specified in the url parameter will open up in the EWA. The Javascript code is left to handle everything that’s after the hash (#) symbol. Here’s the code that runs when the EWA control says it’s ready:

function ewaApplicationReady()

{

ewa = Ewa.EwaControl.getInstances().getItem(0);

ewa.add_activeSelectionChanged(selectionChanged);

var hash = window.location.hash;

if (hash != "" && hash)

{

hash = hash.substring(1);

var splat = hash.split("|", 2);

if (splat.length >= 1)

{

ewa.getActiveWorkbook().getRangeA1Async(splat[0], hashGetRangeComplete);

}

if (splat.length >= 2)

{

var el = document.getElementById("headerTitle");

el.innerHTML = splat[1];

}

We take the string that comes after the hash and split it around the pipe (|) symbols. We then look at the two parts – the first one is the range we need to select in the EWA and the second is the title of the find. We take the title and put it at the top of the page (remember, in the part 1 post we gave the title we create an ID – we use that here).

The first element of the array (the range) we take and pass into a call to getRangeA1Async() which I already showed in a previous post. We also pass a callback that will run when EWA returns the requested range (hashGetRangeComplete()).

function hashGetRangeComplete(asyncResult)

{

var range = asyncResult.getReturnValue();

range.activateAsync();

}

This method takes the range and activates it by calling the activateAsync() method. Note that we do not pass a callback.

So here’s, in a nut shell, what happens when this page loads.

1. If the URL is specified, the page will load the workbook it points to. If it’s not, it will load the default workbook as set in the web-part filter.

2. If there’s a hash, the page will look at it and select the range that’s in that hash, making sure the selection that was originally saved is selected in the EWA.

3. The title the person who did the publish is then also placed at the top.

The next post will summarize everything the app does, from a technology point of view and will discuss places where the “app” could be made better.

“Something interesting” Sample app – Coding – Part 2 – coding the publish functionality

Shahar — Wed, 09 Dec 2009 17:00:00 GMT

This post shows how to code the sample application shown on this blog. This continues part 1 of this coding series.

Before we go into the code of the sample app, I will explain the flow of the web part page. The previous post showed the building of the page. The two really interesting parts in there are the content web-part that hosts the panel at the left and the Query String filter which tells EWA which workbook to load. The flow of the application can be separated into two distinct functionalities – what happens when the “I found something interesting” is clicked and what happens when the page loads (that’s when the page decides if it needs to show “something interesting” or go into the default state.

This part of the series will show what happens when the “I found something interesting” button is clicked – it is the more elaborate piece of the code.

Stage 1: The HTML

First off, here’s the HTML for the panel where the user types what they found:

Here’s the HTML:

<div>

<a href="/Lists/InterestingWorkbookLinks">See what others found.</a>

<br />

or...

<br />

Describe what you found:<br />

Notes:<br />

I found something interesting!!</button>

</div>

Saving...

</div>

Done saving!!

</div>

<div>

<a style="display:none" id="sharedLink">Link to what you found</a>

</div>

The only part that’s really interesting is the button – that’s the guy who initiates the operation of saving the data (it runs the method foundSomething()). Above that piece are the text boxes used to enter the relevant information (title + notes) and below it are a bunch of divs I use to notify the user about what’s happening as well as a link (that’s initially hidden) to the link we just created.

Stage 2: The onclick method

(Note that the full script contains the JSOM skeleton OM described in my EWA JavaScript OM posts)

When clicking on the button, the following code runs:

function foundSomething()

{

var d = new Date();

var name = "InterestingWB" + d.getYear().toString() + d.getMonth().toString() + d.getDay().toString() + d.getHours().toString() + d.getMinutes().toString() + d.getSeconds().toString() + d.getMilliseconds().toString() + ".xlsx";

var sid = ewa.getActiveWorkbook().getSessionId();

var url = "http://tsdlt2/InterestingWorkbooks/" + name;

var client = new ExcelServices(url);

client.saveWorkbookCopy(sid, url, saveComplete, name);

showElement("savingElement", true);

showElement("saveDoneElement", false);

enableElement("saveButton", false);

}

First, we create a “unique” name for the file (local variable name). We then get the sessionId the EWA is currently working with from the control. The session id is the opaque id on the back-end that is used to identify the instance of the workbook the user has opened. It is used widely when using Excel Web Services. The method for getting the session id (getSessionId()) is the replacement for the old ewaGetSessionId() method we had in SharePoint 2007. Once we have the session id inside the sid local variable, we also create a new proxy for the SOAP interface exposed by Excel Services. I am using a modified version of the Excel Web Services AJAX library I published on the blog a few years ago – to this version, I added the new saveWorkbookCopy() method introduced in Excel Services in SharePoint 2010.

With the proxy created, we make a call to the saveWorkbookCopy() method – passing in the callback saveComplete() which will, as the name implies, be called when save is complete. The Excel Web Services saveWorkbookCopy() function will save the workbook into the appropriate URL (the unique name in a document library created especially for this application called “InterestingWorkbooks.xlsx”).

Finally, the function shuffles around some of the elements so that the user gets an indication that the workbook is being published.

Stage 3: Adding the proper link to the SharePoint list of links we use as the repository

When the call to SaveWorkbookCopy() is done, we are ready to create the link that will be saved into the SharePoint link library.

This method is divided into three parts – the first part figures out the URL to the workbook we just saved, the second builds the XML we will send SharePoint to add a link to the list and the last does the actual sending of the link:

function saveComplete(call)

{

// Part 1 - Create the link.

var sel = ewa.getActiveWorkbook().getActiveSelection();

var range = sel.getAddressA1();

var url = call.excelServices.userState;

var link = "/Docs/SomethingInteresting.aspx?url=" + encodeURIComponent(url) + "#" + range + "|" + encodeURIComponent(document.getElementById("title").value);

// Part 2 - Build the XML we will send ListData.svc

var data = document.getElementById("restInsertRequest").xml;

data = data.replace("{title}", xmlEncode(document.getElementById("title").value));

data = data.replace("{link}", xmlEncode(link));

data = data.replace("{notes}", xmlEncode(document.getElementById("notes").value));

// Add to the list of links.

var req = new XMLHttpRequest();

req.onreadystatechange = listAddDone;

req.open("POST", "/_vti_bin/ListData.svc/InterestingWorkbookLinks");

req.setRequestHeader("Content-type", "application/atom+xml");

req.setRequestHeader("Content-length", data.length);

data.replace("{title}", document.getElementById("title").value);

req.send(data);

document.getElementById("sharedLink").href = link;

}

Part 1 – we get the current selection from the EWA by using the getActiveSelection()method on the workbook. We then get the address of the selection (i.e. Sheet1!A1:Z26) and the URL to the saved workbook (we set it as the user-state when we made the saveWorkbookCopy() call). The link takes the following form:

/Docs/SomethingInteresting.aspx?url=[url to saved workbook]#[range]|[title]

In the previous post, I showed how we have a QueryString filter web part associated with the “url” parameter – this is how we make the EWA show the correct workbook when opening from the list of links (the url parameter on the link we create). We then have two items after the hash of the link – these will be used to figure out what to do and I will show that code in the next post.

Part 2 – Here we take the XML from an XML part that’s on the page – this XML is a template we will use to do a REST call into SharePoint and tell it to add a link to the list. Here’s the XML:

<entry xml:base="http://tsdlt2/_vti_bin/ListData.svc/"

xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices"

xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"

xmlns="http://www.w3.org/2005/Atom">

<id />

</author>

<m:properties>

<d:URL>{link}, {title}</d:URL>

<d:Notes>{notes}</d:Notes>

</m:properties>

</content>

</entry>

</xml>

Most of it is goo needed to make SharePoint’s ListData.svc work, the interesting part is at the bottom where the properties element is – we have two elements there – the link (<URL>) and the notes (<Notes>).

Once we take the XML content, we replace the various tokens (link, title and notes) with their respective values.

Part 3 – Finally we make the call to SharePoint to add the link. We use an XMLHttpRequest() object to post the data to the appropriate link (/_vti_bin/ListData.svc/InterestingWorkbookLinks in this case – the REST entrypoint for our list of interesting links). We pass in the function listAddDone() function as a callback.

At the end, we also change the sharedLink element to point to the link we just created – that way users can copy it and send via email.

Stage 4 (Last): Update the UI to show the user that the save is done.

Finally, we just do some UI clean up, showing the relevant elements to the user so that they know we are done saving.

function listAddDone()

{

showElement("savingElement", false);

showElement("saveDoneElement", true);

enableElement("saveButton", true);

showElement("sharedLink", true);

}

And that’s it pretty much – once this sequence has occurred, the following steps have taken place:

1. We save a copy of the currently used workbook to a special document library (so that any changes the user made are retained).

2. We create a link back to our page with a url query string parameter pointing to the new workbook and with some more info tacked at the end saying what the title is and what the range selected is.

3. We add the link to a SharePoint link list so that it can be found by other users.

“Something interesting” Sample app – Coding – Part 1 – Building the page

Shahar — Tue, 08 Dec 2009 17:00:00 GMT

This post shows how to code the sample application shown on this blog.

In my previous post, I showed the “I found something interesting” app which allows users to analyze workbooks and publish their insights. With the following few posts, I will show how this solution is coded.

The first element of the solution is the page that hosts it – a web-part page that you can create – in this case, the page template is “header, left column, body”

Creating the page

Once created, we are going to add 5 web-parts to the page:

1. Title page (at the top). This will be used both for the name of the app (when you first land on the page) and for the title of the published interesting element.

2. EWA (on the right – the “body” section). This is the EWA the users are going to use to navigate and analyze the workbook.

3. Query String filter (on the left - “Left column” section). This will be used to modify the workbook the page is looking at when an “interesting” link is clicked.

4. Two content editors (on the left as well). These will contain the Excel Services AJAX library and the code/html for the solution to work.

Setting up the page - title

There are some things that need to be done before we go into writing script. In this stage, we will set up these various pieces. First, the title – we will write the relevant text (“I found something interesting!") in the content editor – we will also go in and name the element where the text is so that we can change it later on (see how to edit a content part and it’s HTML here):

Setting up the page – Query String filter

Once that’s set up, we need to connect the Query String filter to our EWA component. This is done in a similar fashion to how it was done in 2007. In this case, we will set up the Query String filter in the following manner:

As you can see, the query string parameter name will be “URL” and the default value is the workbook we generally want to analyze. We will then connect the part via part to part communication:

Once you select to send the filter value to the EWA control, you need to choose what to do with it:

Setting up the page – Excel Services AJAX content part

Once that’s done, we need to set up one of the content parts we placed on the left column to contain our AJAX library. We will do that by editing the content part properties and pointing it to the AJAX library we have in one of our libraries:

This will cause the content part to load the Excel Services AJAX library (next post will show why we need it).

That’s it for this post! Next posts will show the flow of the application and the code that runs behind it.

Sample application “I found something interesting”

Shahar — Mon, 07 Dec 2009 17:02:00 GMT

Another week, another app (and I use “app” loosely – it’s a SharePoint web-part page).

The following sample application is used to allow people to analyze a certain workbook and add insight. The insight may be as simple as marking a certain area in the workbook and pointing people’s attention to it and it may be more complicated- a user may do some analysis in a workbook (drill down, add calculations etc) and publish this modified workbook for others to see. Note that this is a sample application and as such I am mostly just trying to give people ideas and expand what people think can be done with Excel Services on SharePoint 2010.

The first post here will show the application and explain the user experience – the posts after that will revolve around the coding of the solution.

Starting up – a simple viewer

The initial screen of the application shows the user two interesting areas – the right where the workbook is (EWA) and the left where the panel for adding information is:

The idea is that the user navigates/analyses the workbook and when they find something interesting, the use the selection (in the workbook) and the text at the left (title of the find and notes. So, say that the user found an interesting area in one of the sheets (this workbook tracks team bug stats):

In this case, we found an instance where the bug count went up significantly between two months. We select the area in question, then move to the right side to type in our findings:

Great! We then hit the “I found something interesting!” button and get an indication that the link is ready:

The user can now take the link to what they just created and copy it into email to send to someone, or they can go to a link library that contains the interesting things that were found:

Clicking on this, bring the user directly to the selected area in the workbook that is interesting:

The two marked areas are the difference between landing on the page from the “links of interest” and the initial view on the page (shown at the top). You can see that the title was changed to the title the user who published the workbook created and that the EWA navigated to the correct location in the workbook (the two months with the drop).

Finally, changes in the workbook get saved too – so say the user added an analysis to the bug table presented at the top screenshot:

The line with the average was not there – we added it. If we were to click “I found something interesting”, and the link produced was to be opened, the workbook would contain this further analysis.

That’s it for today – here’s a video showing the elements of the application one by one:

Summary – new programmability capabilities in Excel Services

Shahar — Tue, 01 Dec 2009 17:00:00 GMT

The last 20 posts or so on the blog discussed the new Excel Services programmability capabilities – I went into detail on most of them. I hope I was able to convey some of the new and exciting (I am allowed to use this word when I really believe it!) capabilities we built into Excel Services 2010.

Here’s a short summary of the recent posts by category:

REST

Introduction

Workbook content discovery with REST

Ranges + More discovery

REST basics

Advanced REST capabilities – parameterization

How to seamlessly embed data from Excel Services spreadsheets into Word

REST Limitations

Excel Services gadget: Parts One, Two and Three

Coding the Excel Services Gadget: Parts One, Two, Three, Four and Five

JavaScript OM

Introduction: Parts One, Two and Three

Excel Web Services

What’s new

Opening a workbook for editing (+collab)

Setting parameters in a workbook

What’s next for the blog..

I have a few more interesting tid-bits (mostly examples on how to use the technology that was so far described) that I want to share, but some of them are not yet ready. The next few posts will be about an “application” called “Something Interesting” which allows users to specify interesting locations/views on their workbooks and publish those for others to consume.

After that, I will post about the “Restplorer” – a user-friendly mechanism for getting and embedding REST URLs and data. Stay tuned.

Using SetParameters with the Excel Web Services APIs

Shahar — Mon, 30 Nov 2009 17:00:00 GMT

One of the new methods I listed in the post about new features is the SetParameters() SOAP call. This allows callers to set multiple parameters at the same time in Excel Services. In this example I have a simple list containing items and sales of those items. I then use a PivotTable to aggregate the sales and get an average.

Here’s the workbook snippet:

On the right there’s the raw table and on the right the PivotTable, showing only the average of sales with a page field acting as a parameter of the workbook. The parameter was named ItemFilter and the average of Sales cell was named AverageSales. The following piece of code shows how to set the parameter and get the value back:

ES.ExcelService client = new ES.ExcelService();

client.Credentials = System.Net.CredentialCache.DefaultCredentials;

ES.Status[] status;

string sessionId = client.OpenWorkbook("http://tsdlt2/docs/ParamPivot.xlsx", "", "", out status);

ES.ParameterInfo[] infos = new ES.ParameterInfo[1];

ES.ParameterInfo param = new ES.ParameterInfo();

infos[0] = param;

param.Name = "ItemFilter";

param.Values = new String[1];

param.Values[0] = "TV";

client.SetParameters(sessionId, infos);

string val = (string)client.GetCellA1(sessionId, "", "AverageSales", true, out status);

Console.WriteLine("Average Sales are: " + val);

As you can see, first we go and set up the parameters objects – we create a parameter array (you can pass in multiple parameters with one call). In our instance, it has a single item in it. We then create the parameter we want to set up (called ItemFilter") and the value we want to put in (via the Value property).

Finally, we call SetParameters() with the array – when that call comes back, it will have filtered the PivotTable. Here’s the result:

Note that if you are using the more recent versions of C#, the code can look a little less verbose:

ES.ExcelService client = new ES.ExcelService();

client.Credentials = System.Net.CredentialCache.DefaultCredentials;

ES.Status[] status;

string sessionId = client.OpenWorkbook("http://tsdlt2/docs/ParamPivot.xlsx", "", "", out status);

ES.ParameterInfo[] infos = new ES.ParameterInfo[]

{ new ES.ParameterInfo() { Name = "ItemFilter", Values = new string[] { "TV" } }};

client.SetParameters(sessionId, infos);

string val = (string)client.GetCellA1(sessionId, "", "AverageSales", true, out status);

Console.WriteLine("Average Sales are: " + val);

Instead of instantiating each element in its own line, we do the initialization inline – makes the code look less complicated (though it does essentially the same thing as we did in the first sample).

Using the Web Services APIs to open a workbook for editing and set calculation options

Shahar — Wed, 25 Nov 2009 17:00:00 GMT

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:

static void Main(string[] args)

{

ES.ExcelService client = new ES.ExcelService();

client.Credentials = System.Net.CredentialCache.DefaultCredentials;

ES.Status[] status;

string sessionId = client.OpenWorkbookForEditing("http://tsdlt2/docs/bugs.xlsx", "", "", out status);

client.CalculateWorkbook(sessionId, ES.CalculateType.Recalculate);

client.CloseWorkbook(sessionId);

}

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:

client.SetCalculationOptions(sessionId, ES.WorkbookCalculation.Manual);

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.

What’s new in Excel Web Services in SharePoint 2010

Shahar — Tue, 24 Nov 2009 17:00:00 GMT

So far I talked about the new extensibility features in Excel Services 2010 – REST and the new JavaScript OM. I thought I would spend some time talking about the existing extensibility mechanism we have – Excel Web Services. With the new release we have added some new functionality to this SOAP interface to the server. Here’s a short list of the added functionality and their descriptions:

Here’s the list of of methods that have been added with this release:

New method	Description
GetChartImageUrl	This is probably the most frequently requested feature – the ability to get at charts from workbooks via the WSAPI.
GetPublishedItemNames	This returns the names of the elements in the workbook that have been implicitly or explicitly published. Useful for investigating the structure of a workbook.
GetSheetNames	Returns the names and visibility of sheets on the workbook.
OpenWorkbookForEditing	Allows the API to join an editing session.
SaveWorkbook	Forces a save of the workbook when in editing mode.
SaveWorkbookCopy	Works both in Editing mode and in view mode – saves a copy of the workbook in SharePoint. Another feature that has been requested frequently.
SetCalculationOptions	Allows the developer to change the way the workbook calculates – turn auto-calc off and on.
SetParameters	Sets parameter on the workbook. This is especially useful if you wish to set page filters that have been designated as parameters but also when you want to set multiple cells at the same time.

This first post will show how to use the GetChartImageUrl() call to get an image from Excel Services. I will use the same workbooks we have been using so far, but show how to work with them with the Web Services APIs. Our solution will open the TeamBugs.xlsx file, iterate over the people who are reported on in the workbook and generate a chart for each, saving it to local disk.

Step 1 – Adding a reference to the Excel Services SOAP APIs + Skeleton code

In Visual Studio (inside your project – in my case I am using a console project), right-click the project and select “Add Service Reference”. This will bring up the WCF client proxy generator dialog. In it go to “Advanced” and then “Add Web Service” - you will be presented with the usual UI for adding SOAP proxies (see my older posts about this).

Next we add the skeleton code - this code is almost always the same whenever you write Excel Services code – it creates the client and sets it up:In here, we also open up the workbook called teamBugs.xlsx.

static void Main(string[] args)

{

ES.ExcelService client = new ES.ExcelService()

client.Credentials = System.Net.CredentialCache.DefaultCredentials;

ES.Status[] status;

string sessionId = client.OpenWorkbook("http://tsdlt2/docs/teambugs.xlsx", "", "", out status);

}

That’s it – we have a session ID – we can start partying with the server.

Step 2 – Getting the range containing the relevant aliases we want to generate the charts out of

This is nothing new – I have it here for completeness mostly. The code will grab the range called AliasList:

object[] rows = client.GetRangeA1(sessionId, "", "AliasList", true, out status);

foreach (var o in rows)

{

object[] row = (object[])o;

Console.WriteLine("Generating chart for " + row[0]);

}

The result (placed in rows) is a jagged array – or an array of arrays. In this case, we get it from the server and then iterate on each element in the top-level array – row. Each item in that array is an array by itself – an array of cells. Since we only care about the first one, this is what we will use.

Step 3 – Setting the value of the alias into the workbook

Again – nothing new – by using the SetCellA1 method, we take each of the values we read and set them in the cell called Alias:

object[] row = (object[])o;

Console.WriteLine("Generating chart for " + row[0]);

client.SetCellA1(sessionId, "", "Alias", row[0]);

Step 4 – Getting the chart image

Finally we can use the new shiny method! GetChartImageUrl() returns a URL to the requested chart. Here’s an example of the call:

Console.WriteLine("Generating chart for " + row[0]);

client.SetCellA1(sessionId, "", "Alias", row[0]);

string url = client.GetChartImageUrl(sessionId, null, "PersonalBugs", out status);

The first parameter, as always, is the session id. The second parameter is the size of the chart requested – in this case we pass in null which means we want the default size. We could pass an instance of the Size class which contains Width and Height. The third parameter is the name of the chart and the final one is the usual status out parameter.

What do we now do with the URL then? We read it of course, and write it into disk:

WebRequest req = WebRequest.Create(url);

req.Credentials = System.Net.CredentialCache.DefaultCredentials;

using(FileStream output = File.Create("c:\\temp\\" + row[0] + ".png"))

using(WebResponse response = req.GetResponse())

using (Stream input = response.GetResponseStream())

{

byte[] buffer = new byte[1024];

int read;

while ((read = input.Read(buffer, 0, buffer.Length)) > 0)

{

output.Write(buffer, 0, read);

}

The process is relatively simple, using .NETs classes.

We create a WebRequest class based on the charts URL. We then create a new file (in this case, named after the alias and located in the temp folder) and get the response from the web request (that’s the two calls to GetResponse() and GetResponseStream(). Finally, we read the chart stream block by block and write it into the file.

Here’s what the final program shows in the console:

And here’s Explorer looking at the temp folder after the program is done running:

As you can see, we generated a number of charts, each calculated specifically for each of the aliases supplied.

Here’s one of these charts in full size:

That’s it! As you can see, it’s fairly simple to get a chart from Excel Services. You can use the stream that comes back in the CLR’s Image class to load the image directly and show it, say, on a Form. Remember that the chart is valid only for as long as the session is valid! Closing the workbook and trying to access the URL will result in failure.

Next post I will talk a bit about OpenWorkbookForEditing() and SetCalculationOptions().

Introduction to the Excel Services JavaScript OM - Part 3 – writing to the workbook

Shahar — Mon, 23 Nov 2009 17:00:00 GMT

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:

function cellchanged(rangeArgs)

{

var sheetName = rangeArgs.getRange().getSheet().getName();

if (lastSheet == sheetName)

{

var col = rangeArgs.getRange().getColumn();

var row = rangeArgs.getRange().getRow();

var value = rangeArgs.getFormattedValues()[0][0];

if (sheetName == "Sheet1" && col == 1 && row > 1 && value && value != "") {

var img = document.getElementById("dashboardPersonPicture");

img.src = "/PersonalPages/" + encodeURIComponent(value) + ".jpg";

ewa.getActiveWorkbook().getRangeA1Async("Sheet2!Alias", getRangeComplete, value);

}

lastSheet = sheetName;

writelog('Address:' + rangeArgs.getRange().getAddressA1(), 1);

writelog('Value:' + rangeArgs.getFormattedValues(), 1);

writelog('Cell changed event fired', 0);

}

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:

function getRangeComplete(asyncResult)

{

var range = asyncResult.getReturnValue();

var value = asyncResult.getUserContext();

var values = [[value]];

range.setValuesAsync(values);

}

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:

function getRangeComplete(asyncResult)

{

var range = asyncResult.getReturnValue();

var value = asyncResult.getUserContext();

var values = [[value]];

range.setValuesAsync(values);

range.activateAsync();

}

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.

Introduction to the Excel Services JavaScript OM - Part 2 – listening to events

Shahar — Fri, 20 Nov 2009 17:00:00 GMT

The previous post showed how to start using the Excel Services JavaScript OM to automate the EWA web-part. The example given showed how to cause periodic recalculation on a workbook that’s on a SharePoint web-page.

In this post, we will add our first event to the EWA and see how we can get an even more integrated experience. The feature we are going to code in this step is to give users the ability to click in our dashboard on a specific person alias in one of the sheets and use that value to show a picture of that person on the side of the web-part page.

Step 1 – Listening to an event

But before we can do all this image handling, first thing’s first – we need to listen to events. Hooking up event is typically done in the ewaApplicationReady() method described before – that’s where the EWA is ready and able:

function ewaApplicationReady()

{

ewa = Ewa.EwaControl.getInstances().getItem(0);

// Start recalcing the workbook every 5 minutes.

setTimeout(forceRecalc, RECALC_INTERVAL);

ewa.add_activeCellChanged(cellChanged);

}

Note the line that was added to the method – ewa.add_activeCellChanged() – in this case we are hooking up to get notification whenever the active cell changes on the workbook. Now, each time a cell is selected, the EWA will call this function. Lets take a look at what the prototype of this function looks like:

function cellchanged(rangeArgs)

{

}

The rangeArgs parameter in the function will contain information on the range clicked. Specifically, it will contain the following three methods:

getRange() – This returns the Range object that’s behind the cell that was selected.

getWorkbook() – The workbook object that is currently loaded in the EWA.

getFormattedValues() – The value that’s in the cell.

In our case, we will use the getFormattedValues() to figure out what the text is in the cell and use that to display the image.

Step 2 – Adding the image tag so that we can display an image

In the first post, the HTML fragment we are using inside the content-part contains a single <script> tag containing our code. It is obviously quite possible to also have any other HTML in there – in this case, we will add an IMG tag:

Step 3 – Setting the image due to cell change

We will modify the cellchanged() function in the following manner:

function cellchanged(rangeArgs)

{

var img = document.getElementById("dashboardPersonPicture");

img.src = "/PersonalPages/" + encodeURIComponent(rangeArgs.getFormattedValues()) + ".jpg";

}

What this does is take the formatted value of the cell that was selected and uses it in a URL that is set into the img tag – in this case, the URL will be /personalpages/shaharp.jpg when my alias is picked. And here’s how this looks in the browser:

That’s great! But watch what happens when a different random cell is selected (say, the one that says “Alias”):

The problem is that we are indiscriminately setting the image. Instead, we need to make sure that the range where the user clicked makes sense.

Step 4 – Looking at the Range object

To do this, we will take the Range object that comes back as part of the rangeArgs parameter and use it to check the coordinates of the cell that was selected. If it falls under our scenario (the column in question), only then will we actually set the image:

function cellchanged(rangeArgs)

{

var sheetName = rangeArgs.getRange().getSheet().getName();

var col = rangeArgs.getRange().getColumn();

var row = rangeArgs.getRange().getRow();

var value = rangeArgs.getFormattedValues();

if (sheetName == "Sheet1" && col == 1 && row > 1 && value && value != "")

{

var img = document.getElementById("dashboardPersonPicture");

img.src = "/PersonalPages/" + encodeURIComponent(value) + ".jpg";

}

The code uses the getSheet().getName(), getColumn() and getRow() calls on the range to figure out exactly where the selection is, and if it falls under the range we are interested in, only then will we go and set the URL for the image.

And that’s it for this post – we saw how to hook up events and then we saw how to use the argument in the event to figure out where the user clicked in the document and what the content of the selection is. Next up, we will see how we can write back values into the workbook.

Introduction to the Excel Services JavaScript OM - Part 1 – Humble beginnings

Shahar — Thu, 19 Nov 2009 17:00:00 GMT

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:

///<reference path="http://tsdlt2/_layouts/EwaJsom.ashx?v=1"/>

var ewa = null;

//set page event handlers for onload and unload

if (window.attachEvent)

{

window.attachEvent("onload", ewaOmPageLoad);

}

else

{

window.addEventListener("DOMContentLoaded", Page_Load, false);

}

//load map

function ewaOmPageLoad()

{

if (typeof (Ewa) != "undefined")

{

Ewa.EwaControl.add_applicationReady(ewaApplicationReady);

}

else

{

// Error - the EWA JS is not loaded.

}

function ewaApplicationReady()

{

ewa = Ewa.EwaControl.getInstances().getItem(0);

}

</script>

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:

function ewaApplicationReady()

{

ewa = Ewa.EwaControl.getInstances().getItem(0);

// Start recalcing the workbook every 5 minutes.

setTimeout(forceRecalc, RECALC_INTERVAL);

}

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:

function forceRecalc()

{

ewa.getActiveWorkbook().recalcAsync(recalcComplete, null);

}

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:

function recalcComplete(result)

{

setTimeout(forceRecalc, RECALC_INTERVAL);

}

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.

Creative workarounds – Windings edition

Shahar — Wed, 18 Nov 2009 17:00:00 GMT

11/24/09 - Correction - Sergei contacted me to explain that I got it all wrong - Windings does not give all the required shapes (Circle, Triangle and Square).Instead, Sergei had to hunt until he found the perfect font that supported all that perfectly - Good ol' "Arial Unicode MS".

In older posts, I have talked about a developer working on internal Microsoft application who really gives Excel Services a run for its money. Sergei has “starred” in two other posts and after playing for a bit with REST, has come up with a really nice workaround to the limitation of the REST API that does not allow for icon conditional formatting. Sergei instead creatively uses Windings and formatting conditional formatting to convey the same idea. Here’s one of his score cards inside a gadget, minimized:

As you can see, there’s a yellow triangle showing that whatever-it-is we are watching is not doing too badly or too well. Here’s the score-card maximized in the gadget:

This is similar to the Excel mechanism that shows icons for conditional formatting:

I love crazy people!!