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

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.