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

[eileenc87]

Hi All,

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

[zslgreen]

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.

    }
}

[eileenc87]

Thanks..it helpsss

[crishlay]

<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();
}

[aspproject]

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

[softwareskill]

You can export the data of dataset or datatable, that is used to bind the gridview to excel, rather than the gridview itself. Try this. Export Gridview to excel in c# as well as vb
http://www.freedotnetapps.com/asp-net/how-to-export-data-from-asp-net-gridview-control-to-excel-spreadsheet

[Shekhar_1]

YOU CAN EXPORT THE DATAFROM EXCEL SHEET.....CHECK THE BELOW LINK.....HERE I EXPLAIN STEP BY STEP THE COMPLETE PROCESS....

http://chauhanshekhar.blogspot.in/p/how-to-export-gridview-data.html

I HOPE THIS WILL HELPS YOU....!

[dreamztechusa]

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

[Stanley36]

The custom DataGridView is able to export data from GridView to excel, CSV, and PDF.

[bonaved]

Export to excel source code in c# and vb.net Asp.net Gridview to Excel

Bona

[jacksmithcommons]

This is a wonderful component to export datatable or gridview to excel 2007/2010 files.

http://www.zubedu.com/products.htmlClick Here