Sql database to Excel

public void ExportToExcel(DataTable data, string fileName)
{
    HttpContext context = HttpContext.Current;
    context.Response.Clear();

    context.Response.ContentType = "text/csv";
    context.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + ".csv");

    //iterate column header names
    for (int i = 0; i < data.Columns.Count; i++)
    {
        if (i > 0)
        {
            context.Response.Write(",");
        }
        context.Response.Write(data.Columns[i].ColumnName);
    }
    context.Response.Write(Environment.NewLine);

    //prepare data
    foreach (DataRow row in data.Rows)
    {

        for (int i = 0; i < data.Columns.Count; i++)
        {
            if (i > 0)
            {
                context.Response.Write(",");
            }
            context.Response.Write(row.ItemArray[i].ToString());
        }
        context.Response.Write(Environment.NewLine);
    }
    context.Response.End();
}

private DataTable getData()
{
    //return datatable - that you get from database
}

protected void Button1_Click(object sender, EventArgs e)
{
    ExportToExcel(getData(), "[customefilename]");
}

Quote:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data;
using System.Web.Configuration;

public partial class DbtoExcel : System.Web.UI.Page
{
SqlConnection cn;
DataGrid dg = null;
static SqlCommand cmd;
SqlDataAdapter da = null;
string cs,excelfile;
SqlDataReader dr;
DataSet ds=null;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
try
{
cs = WebConfigurationManager.ConnectionStrings["Mydbcon"].ConnectionString;
cn = new SqlConnection(cs);
cn.Open();
cmd = new SqlCommand("select table_name from INFORMATION_SCHEMA.Tables", cn);
dr = cmd.ExecuteReader();
ds = new DataSet();
while (dr.Read())
{
ddtable.Items.Add(dr[0].ToString());
}
dr.Dispose();
cn.Close();
}
catch(Exception ex)
{
lerror.Text = ex.Message;
}
}

}

protected void ddtable_SelectedIndexChanged(object sender, EventArgs e)
{
lerror.Text = ddtable.SelectedItem.Text;

}

protected void bdownload_Click(object sender, EventArgs e)
{
da=new SqlDataAdapter("select * from (" + ddtable.SelectedItem.Text + ")", cn);
da.Fill(ds,"tab");
dg = new DataGrid();
dg.DataSource = ds;
dg.DataBind();
Response.Clear();
Response.Buffer = true;
Response.ContentType="application/vnd.ms_excel";
Response.Charset = "";
this.EnableViewState = false;
System.IO.StringWriter sqlstringwriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter sqlhtmltextwriter = new System.Web.UI.HtmlTextWriter(sqlstringwriter);
dg.RenderControl(sqlhtmltextwriter);
Response.Write(sqlstringwriter.ToString());
Response.End();
}