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

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.