How to export a data from table in SQL server to EXCEL file using C# .net

This blog explains how to export a data which is available in SQL table to EXCEL file step by step.

Sample SQL Table

Consider the following table data.

Table name : Customer

CustomerID CustomerName ProjectID CustomerRegion CustomerCountry Year
1001 AMGEN, INC. 1000001 North America Canada 2008
1002 CISCO SYSTEMS, INC. 1000002 North America Mexico 2005
1003 KIMBERLY-CLARKCORPORATION 1000003 North America Canada 2011
1004 AGILENT TECHNOLOGIES 1000004 Europe France 2012
1005 PEARSON EDUCATION INC. 1000005 APAC Japan 2015

Creating a stored procedure to fetch data from the table

Below is the sample stored procedure that is created to fetch data from the table.

Syntax:

USE Databasename

GO

/****** Object: StoredProcedure [dbo].[procedurename] Script Date: 09/26/2016 13:36:10 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE Proc [dbo].procedurename

as begin

end

GO

Example:

USE CustomerDB

GO

/****** Object: StoredProcedure [dbo].[GetCustomerDetails] Script Date: 09/26/2016 13:36:10 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE Proc [dbo].GetCustomerDetails

as begin

Select * from Customer

end

GO

Alter Procedure Syntax

After creating the procedure, make the “CREATE Proc [dbo].GetCustomerDetails” line as “Alter Proc [dbo].GetCustomerDetails”. So that if you try to change query later and execute it, error will not be thrown.

So now the table data and stored procedure is ready. All we have to do is to write the C# code to fetch data from table and export it to excel.

C# code to fetch data:

Below asp page contains the simple code with an image button to export the file.

ASP page: CustomerDetails.aspx

-- %@ Page Language="C#" CodeBehind="CustomerDetails.aspx.cs" Inherits="Customer.CustomerDetails" Title="Customer Details" % !--
-- asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server" !--
--link href="Styles/css/jquery.dataTables.min.css" rel="stylesheet" type="text/css" /!--

--/asp:Content !--
--asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server" !--
   

Customer Details

--asp:ImageButton ID="imgExport" ImageUrl="Images/excel.png" Height="35px" Width="35px" Visible="true" ToolTip="Export to excel" OnClick="btnExportToExcel_Click" runat="server" /!-- --/asp:Content !--

NOTE: Replace "--" with "<" and "!--" with ">"

Below cs file contains the c# code to fetch data from the database and export it to excel.

Cs file : CustomerDetails.aspx.cs

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Xml.Linq;

using System.Collections.Generic;

using System.Web.Services;

using System.Web.Script.Services;

using System.Data.Common;

using System.Web.Script.Serialization;

using System.Data.SqlClient;

using System.IO;

namespace Customer

{

public partial class CustomerDetails : System.Web.UI.Page

{

string con = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

protected void Page_Load(object sender, EventArgs e)

{

}

protected void btnExportToExcel_Click(object sender, EventArgs e)

{

//Get the data from database into datatable

DataTable dt = new DataTable();

SqlConnection sqlCon = new SqlConnection(con);

SqlCommand sqlCmd;

// Calling the stored procedure and executing it

sqlCmd = new SqlCommand("GetCustomerDetails", sqlCon);

sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.Connection.Open();

SqlDataReader dr;

dr = sqlCmd.ExecuteReader();

dt.Load(dr);

//Create a dummy GridView

GridView GridView1 = new GridView();

GridView1.AllowPaging = false;

GridView1.DataSource = dt;

GridView1.DataBind();

Response.Clear();

Response.Buffer = true;

Response.AddHeader("content-disposition",

"attachment;filename=CustomerDetails.xls");

Response.Charset = "";

Response.ContentType = "application/vnd.ms-excel";

StringWriter sw = new StringWriter();

HtmlTextWriter hw = new HtmlTextWriter(sw);

for (int i = 0; i < GridView1.Rows.Count; i++)

{

//Apply text style to each Row

GridView1.Rows[i].Attributes.Add("class", "textmode");

}

GridView1.RenderControl(hw);

//style to format numbers to string

string style = @"";

Response.Write(style);

Response.Output.Write(sw.ToString());

Response.Flush();

Response.End();

}

}

}

When the above code is executed, the customer details excel will be opened.

Category: 

Comments

kanagaraj (not verified)
thank ur code prefectworking