Hi All,

How can I export data from gridview to excel sheet in ASP.net?

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.

    }
}
<asp:GridView ID="grdStudentMarks" runat="server" 
    DataSourceID="dsStudentMarks">
    <EmptyDataTemplate>
        No Data Found
    </EmptyDataTemplate>
    <RowStyle CssClass="ClsOddRow" />
    <AlternatingRowStyle CssClass="ClsEvenRow" />
    <HeaderStyle CssClass="ClsHeaderRow" /> 
</asp:GridView>

<asp:SqlDataSource ID="dsStudentMarks" runat="server" ConnectionString=
  "Data Source=.;Initial Catalog=UniversityManager;Integrated Security=True;"
    SelectCommand="
    (
        SELECT *FROM STUDENT 
    ) 
    "> 
</asp:SqlDataSource>

<asp:Button ID="btnExportFromDatagrid" runat="server" 
    Text="Export From Grid" OnClick="btnExportFromDatagrid_Click" />

<asp:Button ID="btnExportFromDataset" runat="server" 
    Text="Export From Data set" />


protected void btnExportFromDatagrid_Click(object sender, EventArgs e)
    {
        ExportGridToExcel(grdStudentMarks, "StudentMarks.xls");   
    }
    public void ExportGridToExcel(GridView grdGridView, string fileName)
    {
        Response.Clear();
        Response.AddHeader("content-disposition", 
            string.Format("attachment;filename={0}.xls", fileName));
        Response.Charset = "";
        Response.ContentType = "application/vnd.xls";

        StringWriter stringWrite = new StringWriter();
        HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
        grdGridView.RenderControl(htmlWrite);
        Response.Write(stringWrite.ToString());
        Response.End();
    }

protected void btnExportFromDataset_Click(object sender, EventArgs e)
{
    ExportToExcel(dsStudentMarks, "StudentMarks");
}

public void ExportToExcel(SqlDataSource dataSrc, string fileName)
{
    //Add Response header 

    Response.Clear();
    Response.AddHeader("content-disposition", 
        string.Format("attachment;filename={0}.csv", fileName));
    Response.Charset = "";
    Response.ContentType = "application/vnd.xls";
    //GET Data From Database                

    SqlConnection cn = new SqlConnection(dataSrc.ConnectionString);
    string query = 
        dataSrc.SelectCommand.Replace("\r\n", " ").Replace("\t", " ");

    SqlCommand cmd = new SqlCommand(query, cn);

    cmd.CommandTimeout = 999999 ;
    cmd.CommandType    = CommandType.Text;
    try
    {
        cn.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        StringBuilder sb = new StringBuilder();            
        //Add Header          

        for (int count = 0; count < dr.FieldCount; count++)
        {
            if (dr.GetName(count) != null)
                sb.Append(dr.GetName(count));
            if (count < dr.FieldCount - 1)
            {
                sb.Append(",");
            }
        }
        Response.Write(sb.ToString() + "\n");
        Response.Flush();            
        //Append Data

        while (dr.Read())
        {
            sb = new StringBuilder();

            for (int col = 0; col < dr.FieldCount - 1; col++)
            {
                if (!dr.IsDBNull(col))
                    sb.Append(dr.GetValue(col).ToString().Replace(",", " "));
                    sb.Append(",");
            }
            if (!dr.IsDBNull(dr.FieldCount - 1))
                sb.Append(dr.GetValue(
                dr.FieldCount - 1).ToString().Replace(",", " "));
            Response.Write(sb.ToString() + "\n");
            Response.Flush();
        }
        dr.Dispose();
    }
    catch (Exception ex)
    {
        Response.Write(ex.Message);
    }
    finally
    {
        cmd.Connection.Close();
        cn.Close();
    }
    Response.End();
}

you can not export directly data from gridview to excel but you can export data from database to excel sheet using "Export to Excel"

Hi,

Please go through this code it can help you.

protected void lnkExport_Click(object sender, EventArgs e) 
{ 
if (gv.Rows.Count > 0) 
{ 
Response.Buffer = true; 
string attachment = "attachment; filename=Excel.xls"; 
Response.ClearContent(); 
Response.AddHeader("content-disposition", attachment); 
Response.ContentType = "application/ms-excel"; 
StringWriter sw = new StringWriter(); 
HtmlTextWriter htw = new HtmlTextWriter(sw); 
gv.RenderControl(htw); 
Response.Write(sw.ToString()); 
Response.End(); 
} 
else 
{ 
lblError.Text="No Records Found"; 
} 
} 
public override void VerifyRenderingInServerForm(System.Web.UI.Control control) 
{ 

} 
private void PrepareGridViewForExport(System.Web.UI.Control gv) 
{ 
LinkButton lb = new LinkButton(); 
Literal l = new Literal(); 
string name = String.Empty; 
for (int i = 0; i < gv.Controls.Count; i++) 
{ 
PrepareGridViewForExport(gv.Controls); 
} 
}

Thanks & regards,

Greg Christofolo