Recently I have answered a number of questions regarding an easy way to export data grid contents to excel format. In this blog post, I will share a static class that I have created called "ExportHelper" which I use quite often to generate quick Excel exports of Datagrid data. I will outline the process and the code below. It should be noted that this solution is NOT a DotNetNuke specific solution, I use this for both DotNetNuke and standard ASP.NET solutions.
First of all please note that this solution is a great utility to help individuals export data to Excel it is NOT a 100% solution, there are specific configurations, multi-row headers for example, that will break this process. However, in general, this process works very well to export content with minimal code and can be reused between multiple applications.
For the ease of use, I created the export process as a static class with a single static member. This allows me to quickly export a grid with just a few lines of code.
As you can see in this example the actual export code is pretty simple. First we set up the response object by cleaning anything that has already been written to the response, we then set up the content type and attachment to let the browser know that we are sending a file. A parameter to the "ExportGrid" method is used to allow a specific file name to be used when sending the file to the user. After this we set up a string and HtmlWriter to take the rendering from the gird, then we clean the controls in the grid, ensuing we only have literals. Lastly, we render the grid to the writer then send the resultant code to the user.
This uses a little known feature of Excel which allows it to open HTML documents as Excel documents. Since the DataGrid renders itself as an HTML table it displays very well when opened by Excel.
As noted in the XML Comments for the method it is very important that if you have paging enabled in your datagrid, that you disable it and re-bind your grid PRIOR to calling the export. Since the grid is rendered to an HtmlWriter and not the response stream, changes to the grid configuration will not be saved in ViewState and will not affect the user's display. Below is an example of the code I use to export a grid. Please note that the "BindGrid()" method performs standard grid binding.
In this example code, I disable paging, hide the first column which was an edit column, then bind the grid and export. with 4 simple lines of code, I can export any datagrid from my application with ease.
Due to the nature of the operations completed inside the ExportGrid method, you may only call the ExportGrid method from inside of a postback to the server. If your application is AJAX-enabled, you CANNOT export a grid during an AJAX update as the modifications to the response header is not allowed. If inside DotNetNuke just be sure to register your export button as a postback control using the DotNetNuke.Framework.AJAX.RegisterPostBackControl() method.
This should provide many people with a great starting point for exporting grid data in a web application. As always your feedback is appreciated!