Excel and PDF Data Dump in SharePoint

Background

I recently had to come up with a quick solution in a week to provide both Excel and PDF downloads of datasets that I was being presented through some SharePoint web parts. I had to create some use controls that would be embedded into SharePoint that providing reporting data. The user needed the ability to down load the underlying datasets in either Excel or PDF.

My natural first inclination was to use SSRS because first it has tight integration with SharePoint. Second the report viewer control provides the ability to download the report and excel and pdf formats. Well in this case I could not use SSRS because it was not part of the solution architecture. As well, I needed to find a "free" solution.

Excel Solution

For Excel, I investigated a couple different solutions. I particularly did not want to have to mess around with the Office Excel API. First the API is not very clean and would be time consuming to code to. Second I would have to install Office onto the front end web servers. Then I thought, how about providing an XML file that opens into Excel? I though, InfoPath is just an XML file with specific processing tags embedded into it which would open InfoPath. I recalled that the same could be done with Excel. I subsequently found this blog posting (http://www.ksaelen.be/wordpress/2009/08/using-c-xml-xslt-to-create-excel-spreadsheet/) and it provided me with an extremely easy way of generating excel data dumps. All you basically do:

• Create an Excel file with all the presentation you want.
  
• Save it is as an Office 2003 XML format.
  
• Change the XML file to an *.xsl file.
  
• Make modifications to the .xsl file to merge in data from another XML file which has all the data.
  
• The use the XslCompilatedTransform class to merge it together.
  
• Then I took the results, put it into a MemoryStream and saved down into a document library.
  

The limitations of this solution are that any charting and code modules you may want to include in the generated XML file is not supported. This is really only a good solution for doing a data dump of the underlying data that may be presented to a user. However it is really simple to implement, requires no special add-ins, you can stylize your presentation and you can be provide very dynamic results. Plus many of the reports provided by SharePoint use XML Excel files so I did not feel too bad in doing this.

PDF Solution

The next thing I had to do was provide the same results in PDF format because not everyone would have Excel. Now the solution above will support both Office 2003 and 2007, however someone may not have Office installed at all. I looked into several tools that would do PDF generation from HTML, but I needed to keep cost down. I subsequently found a very mature open source PDF library called PDFsharp and MigradDoc Foundation:

• http://www.pdfsharp.net/Default.aspx
  
• http://pdfsharp.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=31456
  

The source code is very well written, commented and managed. This scored big points in my book because I usually shy away from open source solutions strictly for code maintenance reasons. These guys also provided tons of code samples and provide the ability to do basic charting too.

I was able to in an afternoon, pull in the library and generate tables of data into PDF. It was very easy for me to then save it down into a document library and provide a user a link to that generated PDF. I specifically used the MigraDoc API to save the generated PDF into a MemoryStream and then just push that right into a SPListItem.

Microsoft Application Architecture Guide 2.0

The Microsoft patterns & practices group has just released a beta version of Application Architecture Guide 2.0 (available for download). Lonnie Wall of RDA was a contributor for this book. I knew a little bit about this as Lonnie met with Colin Murphy and me to discuss workflow. We could not resist talking about K2 but third party platforms are not part of this architecture assessment.

I have just started to read over this and now I am enticed to make time to read the whole thing. Much of the content of this will help architects and decision makers during an Elaboration phase of a project to navigate the Microsoft stack. They have guidance on how to start architecting a solution, design patterns, best practices for layers, etc. They even get down into pro and cons of using things such as ADO.net vs. Entity, when to utilize LINQ, when to use WF and BizTalk, web application design considerations, SharePoint application, etc.

It seems pretty comprehensive so far and should be used as a starting place. There are still limitations on topics discussed in this book (for instance the limitations of Excel and InfoPath services for SharePoint are not really dived into too deeply). There are probably some other similar sorts of things in the book, but from what I know the purpose of this book was to again give a starting place.

Visual Studio 2008 Free MS eBooks

A colleague of mine sent out this link to get free MS Press books on LINQ, ASP.net with Ajax Extensions and Silverlight 1.0.

Introducing Microsoft LINQ by Paolo Pialorsi and Marco Russo (ISBN: 9780735623910)
This practical guide covers Language Integrated Query (LINQ) syntax fundamentals, LINQ to ADO.NET, and LINQ to XML. The e-book includes the entire contents of this printed book!

Introducing Microsoft ASP.NET AJAX by Dino Esposito (ISBN: 9780735624139)
Learn about the February 2007 release of ASP.NET AJAX Extensions 1.0, including an overview and the control toolkit.

Introducing Microsoft Silverlight 1.0 by Laurence Moroney (ISBN: 9780735625396)
Learn how to use Silverlight to simplify the way you implement compelling user experiences for the Web. Discover how to support an object-oriented program model with JavaScript.