What’s new in Excel Web Services in SharePoint 2010

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("https://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() .