If you develop a web-application for a client that involves creating and viewing reports, it’s very likely that they’re going to want to be able to export that data to excel very readily. Fortunately, you won’t need any special libraries or hundreds of lines of code to make that happen in ASP.NET. All you need is a GridView with some data in it and a few lines of code to make that happen.

Here’s how to export data from a GridView in ASP.NET:

(1) Create a Grid View

First, we’ll need a GridView control on the page that we can load our report or table into.

<asp:GridView ID=”GridView1″ runat=”server”></asp:GridView>

(2) Disable Event Validation

Because we will be creating a fresh HTTP response, ASP.NET’s event validator will likely run into an error. Specifically, the message you would receive is “Invalid postback or callback argument.” In order to get around this, we need to disable event validation for the page that is used to export to excel. To do that, add the following tag to the top of your .ASPX page:

<%@Page EnableEventValidation=”true” %>

(3) Add data to your GridView

At this point, you’ve got everything you need done on the HTML side of things, and it’s time to write some C# (or VB or J#) if you prefer. The first thing we need to do in our server-side code is to add data to the gridview. In the code below, I’m using a function from the database utilities library mentioned earlier this month to get the data and return it as a DataTable. You’re more than welcome to use whatever data-access code you would like to pull the resultset you would like to get from the database. This code will most likely end up in your Page_Load() function.

DataTable dtResults= SQLSelect(“Select * from Users”);
GridView1.DataSource = dtResults;
GridView1.DataBind();

(4) Building your export-to-excel function

You’ll need about 10 lines of code to make the actual export happen. Essentially, it creates a new HTTP response, re-renders the gridview and puts it in a downloadable XLS format. You can either put this in your Page_Load() function or in the event handle for a button if you would like to preview the gridview before exporting it.

Here’s the export code:

Response.Clear();
Response.ContentType = “application/ms-excel”;
Response.Charset = “”;
Page.EnableViewState = false;
Response.AddHeader(“Content-Disposition”, “inline;filename=report.xls”);

System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);

GridView1.RenderControl(hw);

Response.Write(tw.ToString());
Response.End();

(5) Override the VerifyRenderingInServerForm Method

Add this function to your C# class for the page to prevent a rendering error:

public override void VerifyRenderingInServerForm(System.Web.UI.Control control)
{
//confirms that an HtmlForm control is rendered for the
//specified ASP.NET server control at run time.
}

(6) You’re done!

That’s really all it takes. If you’d like a good example file, you can download one from ASPAlliance.

Other Resources:

You might also be interested in reading these articles about exporting files to excel:

• How to Export Data to Excel from an ASP.NET Application + Avoid the File Format Differ Prompt (MSDN Blogs)
• CodeSnip: Exporting GridView to Excel (CodeSnip)
• ASP.Net 2.0: Export GridView to Excel (C-Sharp Corner)