Exporting data to Excel from a GridView in ASP.net

Hate or like it, regardless how pretty your GridView looks like, how easy it’s to print, customers just want to export everything to excel to do their own manipulation. So willy-nilly we must find a good solution to handle this. In asp.net, it’s actually very easy to do, but there are a few things we need to be aware of. Let’s have a look at the function first.

private void Export2Excel(GridView gv, string strFileName)
{
 Response.Clear();
 Response.AddHeader("content-disposition", "attachment; filename=" + strFileName);
 Response.Charset = "";

 // If you want the option to open the Excel file without saving then
 // comment out the line below
 // Response.Cache.SetCacheability(HttpCacheability.NoCache);

 Response.ContentType = "application/vnd.xls";
 System.IO.StringWriter stringWrite = new System.IO.StringWriter();
 System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
 gv.RenderControl(htmlWrite);

 Response.Write(stringWrite.ToString());

 Response.End();
}

By calling above method in button click event, we can export any GridView to excel.

protected void imgExp2Excel_Click(object sender, ImageClickEventArgs e)
{
    Export2Excel(GridView1, "demo.xls");
}

However there are a few things you need be aware of:

1. If you receive a HttpException regarding to type 'GridView' must be placed inside a form tag with runat=server. You need to overide VerifyRenderingInServerForm Method as below:

public override void VerifyRenderingInServerForm(Control control) { }

2. In Ajax enabled environment. You will very likely to receive an exception error as: Exception is Sys.WebForms.PageRequestParserErrorException .The message received from the server could not be parsed. This is because modified Response object cannot work in Ajax Framework. The workaround is to use asynchronous post back through the trigger in asp:UpdatePanel. Here are the code:

3. We must turn off AllowPaging to false before we are doing the export.

protected void imgExp2Excel_Click(object sender, ImageClickEventArgs e)
{
 GridView1.AllowPaging = false;  
 Export2Excel(GridView1, "Overview.xls");
 GridView1.AllowPaging = true;
}

4. If you see this exception Exception RegisterForEventValidation can only be called during Render().You need to notify ASP.Net that not to validate the event by setting the EnableEventValidation flag to FALSE.

Page Title="" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
CodeFile="demo.aspx.cs" Inherits="demo" EnableEventValidation = "false"

That has covered all errors I have seen in the past. Please let me know if there are any problems or questions. Another very good post I found on aspsnippet, is definitely worth a read.

Create a master and detail relationship report in ASP.net

A very common requirement I received from customers is to display a summary report in the main page, while user click on each row of the grid view, a new window will popup and show the detail information of the selected row. One scenario is that we have a list of customer orders in the main page, when user click on each order inside the gridview, a window will popup to show the order details.

We can easily achieve this in .net with a little bit JavaScript, the technique we discussed in my previous post Calling a JavaScript function from server side code. In today’s demo I am going to use Orders and Order Details tables from Northwind database. Let’s start.

1. Create our main page orders.asp. In the design view, drag a data source and select all from table orders.





2. In the source view, add 2 JavaScript functions after the body tag. The first function is to highlight the row on mouse hover. The second function is to open a popup window and pass the order no parameter OrderID to the second page orderdtl.asp which I am going to create in step 4.

3. Create a RowDataBound event handler as below.

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
 e.Row.Attributes["onmouseover"] = "highlight(this, true);this.style.cursor = 'pointer'";
 e.Row.Attributes["onmouseout"] = "highlight(this, false);";

 e.Row.Attributes["onclick"] = "PopUpWin(this, 'OrderDtl.aspx', 'name',150,200,410,315)";
}

4. Create a new webpage called orderdtl.asp.
5. In the page design view, drag a GridView on to the page and configure the data source selecting from Order Details



In where clause, set the parameter value from Request.QueryString(“OrdrID”).



6. Save all and run the page. If everything works well, you should see the result as: