Categories: Asp.net , C#.Net , ExcelSheet , ExportGridviewData , Gridview
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#.
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;
}
}
Posts
No comments:
New comments are not allowed.