ASP.NET Tutorial – How to Export Data from SQL Server to Excel in ASP.NET using c# |

CheapASPNETHostingReview.com | Best and cheap ASP.NET Hosting. 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:

Now I will explain to you, 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
Once table created in database enter some dummy data to test application after that write the following code in your aspx page

Column Name	Data Type	Allow Nulls
UserId	int	Yes
UserName	varchar(50)	Yes
Location	varchar(50)	Yes

<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>

<title>Export data from sql server database to excel in asp.net using c#</title>

</head>

<body>

<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 :

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;
    }
}

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

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

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

Response.Write(vbLf)

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