1. Create a Web Page as below (Default.aspx):
<%@ Page Language="C#" AutoEventWireup="true"
CodeFile="Default.aspx.cs"
Inherits="_Default"
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD
XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="Button1"
runat="server"
onclick="Button1_Click"
Text="Export
Excel" />
</div>
</form>
</body>
</html>
2. Add below code in web.config for Database connection:
<appSettings>
<add key="ConnString" value="server=ServerIP;uid=id;password=password;database=dbName;pooling=no"/>
</appSettings>
Note: Update all the above highlighted values.
3. Add below code in
Default.aspx.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
public partial class _Default :
System.Web.UI.Page
{
SqlConnection conn = null;
DataTable dt = new
DataTable();
string strConn = (string)ConfigurationSettings.AppSettings["ConnString"];
protected void
Page_Load(object sender, EventArgs e)
{
conn = new SqlConnection(strConn);
}
protected void
Button1_Click(object sender, EventArgs e)
{
string query = "select
* from table";
conn.Open();
SqlDataAdapter sda = new
SqlDataAdapter(query, conn);
sda.Fill(dt);
ExportDataTableIntoExcel(dt);
conn.Close();
conn.Dispose();
}
public void
ExportDataTableIntoExcel(DataTable data)
{
string attach = "attachment;filename=Data.xls";
Response.ClearContent();
Response.AddHeader("content-disposition",
attach);
Response.ContentType = "application/ms-excel";
if (data != null)
{
foreach (DataColumn
dc in data.Columns)
{
Response.Write(dc.ColumnName + "\t");
//sep = ";";
}
Response.Write(System.Environment.NewLine);
foreach (DataRow
dr in data.Rows)
{
for (int
i = 0; i < data.Columns.Count; i++)
{
Response.Write(dr[i].ToString()
+ "\t");
}
Response.Write("\n");
}
Response.End();
}
}
}
4. Execute the code and click on "Export
Excel" Button. After clicking on button you will see pop-up
as below for download file.
After click on OK button file will save on user system at default download location.
No comments:
Post a Comment