Krishan Dutt Sharma

Web Developer

Export SQL Table data to Excel File with Column Headers in asp.net and VB.Net.

In this article I will explain, how to export SQL Table data to Excel file with Column Headers in C#.

 

First of all, we fetch the SQL Server database recodes into DataTable .net Object and then the DataTable will be exported to Excel file using the ClosedXml library in  asp.net and VB.Net.

 

DATABASE 

I have made a SQL table which name is A_RBG_SBG_MASTER.  Below i am Show the Design part of the table and its data.

Now, WE create a SQL Procedure for Fetching Data from above table to our Asp.net page. Show Below is the Procedure :

CREATE PROCEDURE [DBO].[PROC_ROLE_RBG_SBG_MASTER ]          
(@PARAM1 VARCHAR(10),
 @PARAM2 VARCHAR(10),
 @OPERATIONTYPE INT
 )
 AS
 DECLARE @DATE DATETIME
 BEGIN
 IF @OPERATIONTYPE =1
 BEGIN
 SELECT * FROM A_ROLE_RBG_SBG_MASTER
 END
 END

Here is the HTML Code:

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">
 
 <div class="service-bill-wrap-search">
 <fieldset>
 <legend><b>EXPORT DATA IN EXCEL</b></legend>
 <table width="100%" cellspacing="3" cellpadding="1" border="0">
 
 <tr>
 <td style="vertical-align: middle; text-align: center;">
 &nbsp;
 </td>
 <td>
 <asp:Button ID="btn_save" Text="Submit" runat="server" Width="100px" Height="25px"
 CssClass="button" />
 </td>
 </tr>
 
 </table>
 </fieldset>
 </div>
</asp:Content>

and  finally  Code-behind Code for export the DataTable/ DataSet into Excel File:

Protected Sub btn_save_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_save.Click
 Dim sttt As String = "EXEC PROC_ROLE_RBG_SBG_MASTER " + Session("Zone").ToString() + "," + Session("Project").ToString() + "," + rblist1.SelectedValue + ""
 Dim dss As DataSet = DataAccessLayer.ExecuteDataset(sttt.ToString)
 If dss.Tables.Count > 0 Then

Dim dt As DataTable = dss.Tables(0)

//Create a dummy GridView
 Dim GridView1 As New GridView()
 GridView1.AllowPaging = False
 GridView1.DataSource = dt
 GridView1.DataBind()

Response.Clear()
 Response.Buffer = True
 Response.AddHeader("content-disposition", _
 "attachment;filename=DataTable.xls")
 Response.Charset = ""
 Response.ContentType = "application/vnd.ms-excel"
 Dim sw As New StringWriter()
 Dim hw As New HtmlTextWriter(sw)

For i As Integer = 0 To GridView1.Rows.Count - 1
 //Apply text style to each Row
 GridView1.Rows(i).Attributes.Add("class", "textmode")
 Next
 GridView1.RenderControl(hw)

//style to format numbers to string
 Dim style As String = "<style> .textmode{mso-number-format:\@;}</style>"
 Response.Write(style)
 Response.Output.Write(sw.ToString())
 Response.Flush()
 Response.End()

End If

End Sub