ที่มา : https://social.msdn.microsoft.com/Forums/en-US/812b55a1-73e1-47cd-beb2-06c49d1187d1/export-datatable-to-excel?forum=aspgettingstarted
By : User-2119480821 posted
ใส่ในปุ่ม Export หน้า ExportToExcel.aspx ได้เลย
//*********************
string constr = @"Server=xxxx;Database=xxxxxx;UID=xxxx;PWD=xxxxxxxx;Application
Name=exportExcel";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("SELECT * FROM [dbo].[tblCustomers]
with(nolock)"))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt); // select data to datatable
GridView tmpGv = new GridView();
tmpGv.DataSource = dt;
tmpGv.DataBind();
System.IO.StringWriter sw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
tmpGv.RenderControl(htw);
Response.Clear();
Response.Charset = "";
Response.ContentEncoding
= System.Text.Encoding.UTF8;
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.xls";
Response.AddHeader("content-disposition", "attachment;filename=" + "test.xls");
Response.Write(sw.ToString());
sw = null;
htw = null;
Response.Flush();
Response.End();
}
}
}
}
//***************
/* asp.net VB.net
Protected Sub Button1_Click(ByVal
sender As Object, ByVal e As EventArgs)
Dim constr As String =
"Server=xxx;Database=xxx;UID=xxxxxx;PWD=xxxxxxx;Application
Name=testexportExcel"
Using con As SqlConnection =
New SqlConnection(constr)
Using cmd As SqlCommand =
New SqlCommand("SELECT * FROM customers")
Using sda As
SqlDataAdapter = New SqlDataAdapter()
cmd.Connection =
con
sda.SelectCommand =
cmd
Using dt As
DataTable = New DataTable()
sda.Fill(dt)
Dim tmpGv As
GridView = New GridView()
tmpGv.DataSource = dt
tmpGv.DataBind()
Dim sw As
System.IO.StringWriter = New System.IO.StringWriter()
Dim htw As
System.Web.UI.HtmlTextWriter = New System.Web.UI.HtmlTextWriter(sw)
tmpGv.RenderControl(htw)
Response.Clear()
Response.Charset
= ""
Response.ContentEncoding = System.Text.Encoding.UTF8
Response.Cache.SetCacheability(HttpCacheability.NoCache)
Response.ContentType = "application/vnd.xls"
Response.AddHeader("content-disposition",
"attachment;filename=" & "test.xls")
Response.Write(sw.ToString())
sw = Nothing
htw = Nothing
Response.Flush()
Response.[End]()
End Using
End Using
End Using
End Using
End Sub
*/
