Export Datas from SQL to Excel

Just Create One button in ASPX Page to export Data from SQL to MS-Excel.

In Aspx page

<asp:Button ID="but1" runat="server" OnClick="click_excel" />

In Cs Page:

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 Sqltoexcel : System.Web.UI.Page

{

    SqlConnection conn = new SqlConnection(connectionstring="Datasource=192.168.100.30\sqlserver;Database=IvinSample;User id=sa;password=IIT;")

 

 

 

 

DataTable dt = new DataTable();

    protected void Page_Load(object sender, EventArgs e)

    {

 

    }

    protected void click_excel(object sender, EventArgs e)

    {

        String str = "select * from Emp1";

        SqlDataAdapter sda = new SqlDataAdapter(str, conn);

        sda.Fill(dt);

        exporttosql(dt);

    }

    public void exporttosql(DataTable dtdata)

    {

        HttpContext context = HttpContext.Current;

        string attach = "attachment;filename=example.xls";

        context.Response.ClearContent();

        context.Response.AddHeader("content-disposition", attach);

        context.Response.ContentType = "application/ms-excel";

        string sep = "";

        if (dtdata != null)

        {

            foreach (DataColumn dc in dtdata.Columns)

            {

                context.Response.Write(sep + dc.ColumnName);

                sep = "\t";

            }

            context.Response.Write(System.Environment.NewLine);

            foreach (DataRow dr in dtdata.Rows)

            {

                sep = "";

                for (int i = 0; i < dtdata.Columns.Count; i++)

                {

                    context.Response.Write(sep + "\"" + dr[i].ToString() + "\"");

                    sep = "\t";

                }

                context.Response.Write(System.Environment.NewLine);

            }

            context.Response.End();

        }

        labmesg.Text = "datatransfered";

        labmesg.Visible = true;

    }

}