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() .
Comments
Anonymous
October 11, 2010
can we get chart from Excel in sharepoint 2007 Excel Webservice?Anonymous
November 13, 2010
I have a problem using the new "GetPublishedItemNames"-Method. I used the ExcelService Class directly in a web part and tried to get all named ranges from a simple workbook. Unfortunately, this methd does not work for me. The result was an empty array of work items. GetRangeA1 works as expected. Using the Excel Rest API (Model/Ranges) works pretty well, just as the drop down in the excel web access web part does. Are there any known problems with this method? Did anyone run it successfully?Anonymous
March 28, 2012
Is there a limit to the number of Excel Web Access web parts that can be placed on a page. We seem to run into trouble when adding more than eight but can't find this as a limit.