Export Datas from SQL to Excel

Gperiyannan
Posted by Gperiyannan under ASP.NET category on | Points: 40 | Views : 3392
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("server=.;database=gopi;uid=sa;pwd=hindustan");
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;
}
}

Comments or Responses

Posted by: Akiii on: 3/9/2011 Level:Bronze | Status: [Member] | Points: 10
nice post...
I will try it.

Thanks and Regards
Akiii
Posted by: Akiii on: 3/21/2011 Level:Bronze | Status: [Member] | Points: 10
@Gperiyannan......

hi, i tried this but in the excel file its giving me System.Data.DataRow printed in all the rows. I am not getting the values.
Can you help ?

Thanks and Regards
Akiii

Posted by: Akiii on: 3/21/2011 Level:Bronze | Status: [Member] | Points: 10
the problem is dr.tostring()...

it should be dr and i in third bracket

Thanks and Regards
Akiii

Login to post response