May 22, 2008

Exporting a DataGrid to Excel The Easy Way

Recently I have answered a number of questions regarding an easy way to export datagrid 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.

Preface/Notice

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.

The Code

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.

/// <summary>
/// This static class is used to export items to excel format
/// </summary>
public static class ExportHelper
{
    
/// <summary>
    /// This method will export a datagrid to MS Excel format, it will remove all header and footer
    /// styles from the grid
    /// </summary>
    /// <remarks>NOTE: Grid must be bound without paging PRIOR to being passed to this method
    /// for exporting to Excel.  Failure to do so will result in incorrect results</remarks>
    /// <param name="oGrid">The datagrid that we are to export</param>
    /// <param name="exportFile">The name of the file that should be shown to the user</param>
    /// <example>
    /// <code>
    /// ExportHelper.ExportGrid(dgrListing, "MyExport.xls");
    /// </code>
    /// </example>
    /// <exception cref="IndexOutOfRangeException">Thrown when more than 65,100 Records exist</exception>
    
public static void ExportGrid(DataGrid oGrid, string exportFile)
    {
        
//Clear the response, and set the content type and mark as attachment
        
HttpContext.Current.Response.Clear();
        
HttpContext.Current.Response.Buffer = true;
        
HttpContext.Current.Response.ContentType "application/vnd.ms-excel";
        
HttpContext.Current.Response.AddHeader("Content-Disposition""attachment;filename=\"" + exportFile + "\"");
        
        
//Clear the character set
        
HttpContext.Current.Response.Charset "";
        
        
//Create a string and Html writer needed for output
        
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
        
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

        
//Clear the controls from the pased grid
        
ClearControls(oGrid);

        
//Show grid lines
        
oGrid.GridLines GridLines.Both;

        
//Color header
        
oGrid.HeaderStyle.BackColor System.Drawing.Color.LightGray;

        
//Render the grid to the writer
        
oGrid.RenderControl(oHtmlTextWriter);

        
//Write out the response (file), then end the response
        
HttpContext.Current.Response.Write(oStringWriter.ToString());
        
HttpContext.Current.Response.End();
    
}

    
private static void ClearControls(Control control)
    {
        
//Recursively loop through the controls, calling this method
        
for (int control.Controls.Count - 1i >0i--)
        {
            ClearControls(control.Controls[i])
;
        
}

        
//If we have a control that is anything other than a table cell
        
if (!(control is TableCell))
        {
            
if (control.GetType().GetProperty("SelectedItem") != null)
            {
                LiteralControl literal 
= new LiteralControl();
                
control.Parent.Controls.Add(literal);
                try
                
{
                    literal.Text 
(string)control.GetType().GetProperty("SelectedItem").GetValue(control, null);
                
}
                
catch
                
{
                }
                control.Parent.Controls.Remove(control)
;
            
}
            
else
                if 
(control.GetType().GetProperty("Text") != null)
                {
                    LiteralControl literal 
= new LiteralControl();
                    
control.Parent.Controls.Add(literal);
                    
literal.Text (string)control.GetType().GetProperty("Text").GetValue(control, null);
                    
control.Parent.Controls.Remove(control);
                
}
        }
        
return;
    
}
}

As you can see in this example the actual export code is pretty simple.  First we setup the respone object by clearning anything that has already been written to the response, we then setup 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 setup 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 a HTML table it displays very well when opened by Excel.

Example Usage

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 users display.  Below is an example of the code I use to export a grid.  Please note that the "BindGrid()" method performs standard grid binding.

//Disable paging and viewstate
dgrExample.AllowPaging = false;

//Hide any columns you don't want exported
dgrExample.Columns[0].Visible = false;

//Rebind
BindGrid();

//Export
ExportHelper.ExportGrid(dgrExample, "ExampleExport.xls")

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. 

Considerations

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. 

Conclusion

This should provide many people a great starting point for exporting grid data in a web application. As always your feedback is appreciated!

tags: DNN, Tutorials, ASP.NET, .NET 1.1, .NET 2.0, .NET 3.5, C#
comments powered by Disqus

Content provided in this blog is provided "AS-IS" and the information should be used at your own discretion.  The thoughts and opinions expressed are the personal thoughts of Mitchel Sellers and do not reflect the opinions of his employer.

Content Copyright

Content in this blog is copyright protected.  Re-publishing on other websites is allowed as long as proper credit and backlink to the article is provided.  Any other re-publishing or distribution of this content is prohibited without written permission from Mitchel Sellers.