Partager via

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

  • Article

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:

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

And that produced the following chart:

image

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:

https://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:

image

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.

Comments

  • Anonymous
    March 19, 2010
    Shahar, this functionality/ability to leverage OOXML in EWA is absolutely brilliant.  We just finished our first complete prototype on Beta 2 bits and everything is working.  In the end we decided to use .ashx (IHttpHandler) to lighten up server side processing overhead a little bit. Didn't have to do anything at all to support .ashx vs. aspx.  URL to the workbook generating ASP.NET app can be fully parameterized  using Query String filter web part with subsequent link to "Get workbook URL from" in EWA.  Do you have any more ideas on URL parameterization that you can share?  The possibilities here are limitless from what we can see. E.g., allowing users to publish their workbooks to MOSS without cleaning out incompatible with EWA features and then simply run them through OOXML filter ASP.NET "cleanser" app: remove unused styles, dead data cache, etc... to enable and speed up workbook load in EWA, dynamically collapsing rows or columns based on some dynamically calculated tags in a work column or row  (business users ask for it all the time; easy to do in VBA and managed code), and the obvious option of loading 100% programmatically generated files. Wow!  Great work guys... REST support for it is coming too some day, right?

  • Anonymous
    March 10, 2011
    Shahar, We can't  implement it basing on your example in SharePoint 2010, we always got error "The file that you selected could not be found. Check the spelling of the file name and verify that the location is correct. Make sure that the file has not been renamed, moved, or deleted." If we access the url of the given aspx file directly, we can save a correct xlsx file. But the EWA can't show correctly. Why we got this error? Did we forget to modify some SharePoint config or just the SharePoint 2010 can't support it any more? Could you give more sample codes? Thank you very much.