Hi All,

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

Re: How to export data from gridview to excel in ASP.net? 80 80
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.

    }
}
Re: How to export data from gridview to excel in ASP.net? 80 80

Thanks..it helpsss

Re: How to export data from gridview to excel in ASP.net? 80 80
<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();
}
Re: How to export data from gridview to excel in ASP.net? 80 80

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

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

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

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

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....!

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

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

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

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

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

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

Bona

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

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.