Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

How to Export Data from SQL Server to Excel in Asp.net using C#

Nov 6, 2014
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#.


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


Export Data from SQL Server to Excel using Asp.net in c#

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 RSS subscribe by email Subscribe by Email

8 comments :

Unknown said...
This comment has been removed by a blog administrator.
Unknown said...
This comment has been removed by a blog administrator.
Anonymous said...
This comment has been removed by a blog administrator.
Pankaj Chaudhary said...

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

Gowtham said...

How to save a excel sheet in particular destination/path

sachin bhatt said...

Data is not Coming in SqlDataAdapter .. and its generating Blank Excel Sheet...please Help me .. its urgent...

Anonymous said...

Using this method , how to make the font of header column BOLD or change its background color?

Anonymous said...

do this in .net mvc with sql server + without ef.

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.