Introduction:
In this article I will explain
how to export data from SQL server database to Excel in asp.net using C#. I
will get data from database then export it into Excel File using code in
asp.net(C#).Implementation:
Create a database “Test”, Then
create one table i.e. Emp_Personal
Column Name
|
Data Type
|
EmpPer_Id
|
Int(Primary Key. So set is
identity=true)
|
EmpName
|
varchar(100)
|
Age
|
Int
|
Address
|
varchar(500)
|
create connection string in web config:
<connectionStrings>
<add name="Empcon" connectionString="Data Source=localhost;Initial
Catalog=test;Integrated Security=True"/>
</connectionStrings>
Design Section:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<fieldset style="width:300px;">
<legend><strong>Export
data from database to excel</strong></legend>
<div>
<br />
Click Here to generate Excel: <asp:Button ID="btnExport" Text="Export Data" runat="server" onclick="btnExport_Click" />
<br />
</div></fieldset>
</form>
</body>
</html>
Asp.net Code Section (Using C#):
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public partial
class excel
: System.Web.UI.Page
{
protected void Page_Load(object
sender, EventArgs e)
{
}
protected void btnExport_Click(object
sender, EventArgs e)
{
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", string.Format("attachment;
filename={0}", "Employee.xls"));
Response.ContentType = "application/ms-excel";
DataTable
dt = GetDatafromDatabase();
string
str = string.Empty;
foreach
(DataColumn dtcol in
dt.Columns)
{
Response.Write(str +
dtcol.ColumnName);
str = "\t";
}
Response.Write("\n");
foreach
(DataRow dr in
dt.Rows)
{
str = "";
for
(int j = 0; j < dt.Columns.Count; j++)
{
Response.Write(str + Convert.ToString(dr[j]));
str = "\t";
}
Response.Write("\n");
}
Response.End();
}
protected DataTable GetDatafromDatabase()
{
SqlConnection
con = new SqlConnection(ConfigurationManager.ConnectionStrings["EmpCon"].ConnectionString);
DataTable
dt = new DataTable();
SqlDataAdapter
adp = new SqlDataAdapter();
SqlCommand
cmd = new SqlCommand();
cmd = new
SqlCommand("SELECT
* FROM dbo.Emp_Personal", con);
adp.SelectCommand = cmd;
adp.Fill(dt);
con.Close();
return
dt;
}
}
Asp.net Code Section (Using Vb.NET):
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Partial Public
Class excel
Inherits
System.Web.UI.Page
Protected Sub Page_Load(ByVal
sender As Object,
ByVal e As EventArgs)
End Sub
Protected Sub btnExport_Click(ByVal
sender As Object,
ByVal e As EventArgs)
Response.ClearContent()
Response.Buffer = True
Response.AddHeader("content-disposition", String.Format("attachment;
filename={0}", "Employee.xls"))
Response.ContentType = "application/ms-excel"
Dim dt As DataTable =
GetDatafromDatabase()
Dim str
As String = String.Empty
Dim
dtcol As DataColumn
For Each dtcol In
dt.Columns
Response.Write(str + dtcol.ColumnName)
str = "\t"
Next
Response.Write("\n")
Dim dr As DataRow
For Each dr In dt.Rows
str = ""
Dim
j As Integer
For
j = 0 To dt.Columns.Count - 1 Step j + 1
Response.Write(str + Convert.ToString(dr(j)))
str = "\t"
Next
Response.Write("\n")
Next
Response.End()
End Sub
Protected Function GetDatafromDatabase() As DataTable
Dim con
As SqlConnection
= New SqlConnection(ConfigurationManager.ConnectionStrings("EmpCon").ConnectionString)
Dim dt As DataTable = New DataTable()
Dim adp
As SqlDataAdapter
= New SqlDataAdapter()
Dim cmd
As SqlCommand
= New SqlCommand()
cmd = New SqlCommand("SELECT
* FROM dbo.Emp_Personal", con)
adp.SelectCommand = cmd
adp.Fill(dt)
con.Close()
Return
dt
End Function
End Class
Final Result:
0 comments:
Post a Comment