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