A few weeks ago I presented at CincySpug on a pattern to integrate SharePoint 2007 and the Open XML SDK 2.0.  This is a moderately advanced developer topic (read: plenty of Visual Studio involved).  One of the things I hate about SharePoint’s export list to excel functionality is that it provides inconsistent results: Sorting, Filtering etc isn’t reflected in the export, AND it chucks (technical term) some columns in there that my users don’t want to see.  Further, I could never get this functionality to work in an FBA environment (though I think others have) because it calls out to owssvr.dll to do the export.  Unpleasant.

So I set out on my integration example to basically recreate this functionality using the Open XML SDK for the document creation.  You can take a look at the solution (entire project linked below), but essentially what it does is create an HttpHandler that gets deployed to the _layouts folder.  The Handler takes a series of arguments for sorting, filtering etc.—the exact same ones that the ListView pages take, actually—and sends the fabricated Excel document down to the response stream for download by the user.  In order to wire this up to the User Interface I created a Menu Item  for all lists (it goes into the Actions menu) with a feature.

  Its one of the “more complex” customactions you can create, because it is backed by an actual control class.  All the menu item does is create a url that links to the httphandler with the given list’s ID and the appropriate sort/filter query string parameters.

So, my caveat is I haven’t 100% completed/codified this solution.  Paging isn’t fully functional yet, and there’s probably bugs.  What I was really trying to do was 1) provide an example of the Open XML SDK code (which there is plenty of in here and 2) show an example of the httphandler/_layouts pattern that I think more developers should know about.  So now I at least have a reference to a sorta-baked solution when questions surrounding these things come up.

Oh, if you want to check out the easter egg, take a look at the Operand.cs class I have in there.  This is a library I take from project to project.  Allows me to build CAML queries (I tend to modify/enhance it every time I use it) using strongly typed objects/collections instead of raw XML.

Here’s a link to the code on my skydrive: SharePoint.ExportListToExcel.zip