Introduction:
Here I will explain how to export data from sql server to excel in asp.net using c# or export data from sql server database to excel in asp.net using c#.
Description:
In previous articles I explained sql injection attacks prevention in asp.net, ExecuteReader example in asp.net, import data from excel to sql database in asp.net, export gridview data to excel in asp.net using c# , export gridview data to pdf in asp.net using c# and many articles relating to asp.net, c#,vb.net and jQuery. Now I will explain how to export data from sql server to excel in asp.net using c#.
In previous articles I explained sql injection attacks prevention in asp.net, ExecuteReader example in asp.net, import data from excel to sql database in asp.net, export gridview data to excel in asp.net using c# , export gridview data to pdf in asp.net using c# and many articles relating to asp.net, c#,vb.net and jQuery. Now I will explain how to export data from sql server to excel in asp.net using c#.
Before
implement this example first design one table UserInformation in your database as shown below
Column Name
|
Data Type
|
Allow Nulls
|
UserId
|
int
|
Yes
|
UserName
|
varchar(50)
|
Yes
|
Location
|
varchar(50)
|
Yes
|
Once
table created in database enter some dummy data to test application after that
write the following code in your aspx page
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Export data from sql server database to excel in asp.net
using c#</title>
</head>
<body>
<form id="form1"
runat="server">
<div>
<asp:Button ID="btnExport"
Text="Export
Data" runat="server" onclick="btnExport_Click" />
</div>
</form>
</body>
</html>
|
Now open code behind file and write the following
code
C#
Code
using System;
using System.Data;
using System.Data.SqlClient;
public partial class _Default :
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}", "Customers.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()
{
DataTable dt = new DataTable();
using (SqlConnection
con = new SqlConnection("Data Source=SureshDasari;Integrated
Security=true;Initial Catalog=MySampleDB"))
{
con.Open();
SqlCommand cmd = new SqlCommand("Select TOP 10 UserName,LastName,Location FROM
UserInformation", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
con.Close();
}
return dt;
}
}
|
VB.NET
Imports System.Data
Imports System.Data.SqlClient
Partial Class VBCode
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}", "Customers.xls"))
Response.ContentType = "application/ms-excel"
Dim dt As
DataTable = GetDatafromDatabase()
Dim str As
String = String.Empty
For Each
dtcol As DataColumn In
dt.Columns
Response.Write(str + dtcol.ColumnName)
str = vbTab
Next
Response.Write(vbLf)
For Each
dr As DataRow In
dt.Rows
str = ""
For j As
Integer = 0 To
dt.Columns.Count - 1
Response.Write(str &
Convert.ToString(dr(j)))
str = vbTab
Next
Response.Write(vbLf)
Next
Response.[End]()
End Sub
Protected Function
GetDatafromDatabase() As DataTable
Dim dt As
New DataTable()
Using con As
New SqlConnection("Data
Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB")
con.Open()
Dim cmd As
New SqlCommand("Select
TOP 10 UserName,LastName,Location FROM UserInformation", con)
Dim da As
New SqlDataAdapter(cmd)
da.Fill(dt)
con.Close()
End Using
Return dt
End Function
End Class
|
Demo
 |
|
If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email. |
|||
Subscribe by RSS
Subscribe by Email
|
|||
8 comments :
I have got an issue when column have large amount of data then it not working fine and excel column and rows becomes misplaced..Please help
How to save a excel sheet in particular destination/path
Data is not Coming in SqlDataAdapter .. and its generating Blank Excel Sheet...please Help me .. its urgent...
Using this method , how to make the font of header column BOLD or change its background color?
do this in .net mvc with sql server + without ef.
Note: Only a member of this blog may post a comment.