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.

How to call a Server Side Function from Client Side Code using JavaScript in ASP.net Ajax

You cannot call server-side code ‘directly’ from client-side code. That is because by design, the server side code executes at server side and client side code at the client. However there are some workarounds. To call serverside code from javascript, you will need to use AJAX. ASP.Net AJAX introduced a technique called PageMethods using which we can call server side methods from JavaScript. Try to imagine a scenario, in a user registration form, when user type a user name in a textbox, a server side process is triggered to find whether this is an exiting user or not in our user table in the database. In this example I am using the SQL Server 2005 as backend.

1.Create a demo table on Northwind db and insert 3 users into the table.

CREATE TABLE demo(user_id varchar(10) PRIMARY KEY);
insert into demo values ('user1');
insert into demo values ('user2');
insert into demo values ('user3');

2.Every server-side method that is called from the client-side, must be declared as “static”, and also has to be decorated with the [System.Web.Services.WebMethod] tag. Let’s create a simple function as below:

[System.Web.Services.WebMethod]
public static bool IsUserExist(string strUserID)
{
    bool blUserExist = false;
    string connectionString = "Data Source=localhost;Initial Catalog=Northwind; " + 
        "Integrated Security=True";
    SqlConnection conn = new SqlConnection(connectionString);
    conn.Open();            
    string strSqlStatement = "select * from demo where user_id = '" + strUserID + "'";
    SqlCommand cmd = new SqlCommand(strSqlStatement, conn);
    SqlDataReader reader = cmd.ExecuteReader();
   
    if (reader.HasRows)
        blUserExist = true;            

    reader.Close();
    conn.Close();

    return blUserExist;
}

3. Go to the Markup page and drag a ScriptManager to the page. The “EnablePageMethods” attribute has to be added on the ScriptManager tag and set it to “true”.

4. Adding a simple HTML TextBox as:

5. Adding the JavaScript code as below:

The “OnSuccess” is the name of the JavaScript function that will be called if the request is successful. Whereas the “OnFailure” will be called if an exception is thrown.

6. Running the application. Will see the screenshot as below: