«

»

Mar 30

how to export data from sqlserver database to excel in asp.net c#

Hi,

In this article I will explain you how to export data directly from sql server database table to excel,

Assume there is some data in the sql server database as below:

DB

I will export this data into excel file to excel by using C#.net coding as below:

1.Add a new file TabletoExcel.aspx in the solution explorer

Copy and paste below code in TabletoExcel.aspx page

[code language=”html”]

<body>

<form id="form1" runat="server">

<div>

<asp:Button ID="btnGenaerateToExcel" runat="server" onclick="btnGenerateToExcel_Click" Text="Button" />

</div>

</form>

</body>

[/code]

Copy and paste below code in TabletoExcel.cs  page:

[code language=”csharp”]

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Configuration;

using System.Data.SqlClient;

using System.Data;

public partial class TableToExcel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}

protected void btnGenerateToExcel_Click(object sender, EventArgs e)
{
PopulatExcelEntry();
}

protected void PopulatExcelEntry()
{
SqlConnection Connection = new SqlConnection("Server=Naseer-PC;Database=Naseer;Uid=sa;Pwd=123");
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter("select * from Tbl_Mst_Employee", Connection);

try
{
adapter.Fill(ds);
ExportToExcel(ds);
}

catch (Exception ex)
{
Connection.Close();
}
}

public static void ExportToExcel(System.ComponentModel.MarshalByValueComponent DataSource)

{
try
{
System.IO.StringWriter objStringWriter = new System.IO.StringWriter();
System.Web.UI.WebControls.DataGrid tempDataGrid = new System.Web.UI.WebControls.DataGrid();
System.Web.UI.HtmlTextWriter objHtmlTextWriter = new System.Web.UI.HtmlTextWriter(objStringWriter);
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.Charset = "";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=TableToExcel.xls");
tempDataGrid.DataSource = DataSource;
tempDataGrid.DataBind();
tempDataGrid.HeaderStyle.Font.Bold = true;
tempDataGrid.RenderControl(objHtmlTextWriter);
DataSource.Dispose();
HttpContext.Current.Response.Write(objStringWriter.ToString());
HttpContext.Current.Response.End();
HttpContext.Current.ApplicationInstance.CompleteRequest();
}

catch (Exception ex)
{
throw ex;
}
}
}
[/code]

Execute the code you will get this below screen after clicking Button

PopUp

Click on Ok and you will get your required output as:

ExcelData

Happy Coding!!!!

Admin.

6 comments

Skip to comment form

  1. Anonymous

    super

  2. rekha

    super coding thanlks

    Regards

    rekha

    1. Peggy

      You really found a way to make this whole prsecos easier.

  3. Prasant

    thank you sir

    1. Keyon

      I never thought I would find such an everyday topic so enghlarlint!

  4. Will

    Whoever edits and pubeishls these articles really knows what they’re doing.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>