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.