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.