How to export all rows in a Gridview to excel ?

t was perfectly alright but following Article has added Advantage over the Previous Post.

    You can able to Hide Few Fields and Display desired fields in Exported Excel Sheet, bydefault all the fields which are displayed in datagrid was displayed in Excel, but let say we want to hide "Edit" and "Delete" field while exporting datagrid data to excel. (Note: You can also use datagrid1.Columns[4].Visible = false;)
    Format Datagrid Data. Full control over formatting of data as compare to previous method.
    Replace Control before exporting to excel. eg: Removing Hyperlink, dropdownlist, checkbox and other controls before exporting data to excel.
    Can Export All data @ once, bydefault you can only export page full of data.


Lets understand by example, consider following datagrid, which contain "Employee Name" and "Email" as Hyperlink, but while exporting data i want Hyperlink from email should be removed.




Utility Code: It is self explanatory

protected void btnExportToExcel_Click(object sender, EventArgs e)
{
DataTable dtOriginal = new DataTable();
dtOriginal = ReturnTable(); //Return Table consisting data

//Create Tempory Table
DataTable dtTemp = new DataTable();

//Creating Header Row
dtTemp.Columns.Add("<b>Employee Name</b>");
dtTemp.Columns.Add("<b>Email</b>");
dtTemp.Columns.Add("<b>Join Date</b>");
dtTemp.Columns.Add("<b>Salary</b>");
double dSalary;
DateTime dtDate;
DataRow drAddItem;
for (int i = 0; i < dtOriginal.Rows.Count; i++)
{
drAddItem = dtTemp.NewRow();
drAddItem[0] = dtOriginal.Rows[i][0].ToString();//Name
drAddItem[1] = dtOriginal.Rows[i][1].ToString();//Email

//Join Date
dtDate = Convert.ToDateTime(dtOriginal.Rows[i][2].ToString());
drAddItem[2] = dtDate.ToShortDateString();

//Salary
dSalary = Convert.ToDouble(dtOriginal.Rows[i][3].ToString());
drAddItem[3] = dSalary.ToString("C");

dtTemp.Rows.Add(drAddItem);
}

//Temp Grid
DataGrid dg = new DataGrid();
dg.DataSource = dtTemp;
dg.DataBind();
ExportToExcel("BudgeReport.xls", dg);
dg = null;
dg.Dispose();
}

private void ExportToExcel(string strFileName, DataGrid dg)
{
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=" + strFileName);
Response.ContentType = "application/excel";
System.IO.StringWriter sw = new System.IO.StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
dg.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}

another way

Here is the complete code to Export GridView to Excel:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class ExportGridView : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
GridView1.DataSource = BindData();
GridView1.DataBind();
}
}



private string ConnectionString
{

get { return @"Server=localhost;Database=Northwind;
Trusted_Connection=true"; }

}



private DataSet BindData()
{
// make the query
string query = "SELECT * FROM Categories";
SqlConnection myConnection = new SqlConnection(ConnectionString);
SqlDataAdapter ad = new SqlDataAdapter(query, myConnection);
DataSet ds = new DataSet();
ad.Fill(ds, "Categories");
return ds;

}




protected void Button1_Click(object sender, EventArgs e)
{
Response.Clear();

Response.AddHeader("content-disposition", "attachment;
filename=FileName.xls");

Response.Charset = "";

// If you want the option to open the Excel file without saving than

// 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);

GridView1.RenderControl(htmlWrite);

Response.Write(stringWrite.ToString());

Response.End();

}

public override void VerifyRenderingInServerForm(Control control)
{

// Confirms that an HtmlForm control is rendered for the
specified ASP.NET server control at run time.

}
}