HOW TO BIND AND EXPORT GRIDVIEW DATA TO MS EXCEL FILE USING ASP.NET C#,VB.NET

In this article I will explain with example How to Bind GridView and Export Gridview data to Ms Excel file using asp.net with C# and VB.Net.

Bind and Export GridView data to Ms Excel in asp.net

Click on image to enlarge

click on image to enlarge

Implementation: Let's create an asp.net website demo to see it in action.

Create a Database e.g. "MyDataBase" and a table under that DataBase in Sql Server and name it "EMPLOYEE" as shown in figure:

Note: EMP_ID column is set to Primary key and Identity specification is set to yes with Identity increment and Identity seed equal to 1. Insert some data in this table that you want to show in the Gridview.

Now in web.config file add the connection string under <configuration> tag

</connectionStrings>

Note: Replace the Data Source and Initial Catalog(i.e. Database Name) as per your application.

Source Code:

In the design page (.aspx) place a GridView control to bind data and a Button control to Export the GridView data to MS Excel file.

<legend>Bind and Export GridView data to Ms Excel in asp.net</legend>

<table>

<tr>

<td>

<asp:GridView ID="grEmp" runat="server" AllowPaging="True" AutoGenerateColumns="False"

GridLines="None" Width="100%" CellPadding="4" ForeColor="#333333">

<asp:BoundField DataField="EMP_NAME" HeaderText="Emp Name" />

<asp:BoundField DataField="DEPT" HeaderText="Department" />

<asp:BoundField DataField="SALARY" HeaderText="salary" />

<asp:BoundField DataField="EMAIL_ID" HeaderText="Email Id" />

</Columns>

</asp:GridView>

</td>

</tr>

<tr>

<td>

<asp:Button ID="btnExportToExcel" runat="server" Text="Export To MS Excel File" OnClick="btnExportToExcel_Click" />

</td>

</tr>

</table>

</fieldset>

C#.Net Code to Bind and Export GridView data to Ms Excel file

First include the following namespaces

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using System.Net;

using System.Net.Mail;

using System.Web.UI.HtmlControls;

using System.IO;

using System.Text;

Then write the code as:

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

{

BindEmpGrid();

}

public override void VerifyRenderingInServerForm(Control control)

{

//It solves the error "Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."

}

protected void BindEmpGrid()

{

SqlCommand cmd = new SqlCommand("select * from EMPLOYEE", con);

DataTable dt = new DataTable();

SqlDataAdapter adp = new SqlDataAdapter(cmd);

adp.Fill(dt);

grEmp.DataSource = dt;

grEmp.DataBind();

}

protected void btnExportToExcel_Click(object sender, EventArgs e)

{

try

{

Response.ClearContent();

string attachment = "attachment; filename=MyExelFile.xls";

Response.ClearContent();

Response.AddHeader("content-disposition", attachment);

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

StringWriter strWrite = new StringWriter();

HtmlTextWriter htmWrite = new HtmlTextWriter(strWrite);

HtmlForm htmfrm = new HtmlForm();

grEmp.Parent.Controls.Add(htmfrm);

htmfrm.Attributes["runat"] = "server";

htmfrm.Controls.Add(grEmp);

htmfrm.RenderControl(htmWrite);

Response.Write(strWrite.ToString());

Response.Flush();

Response.End();

}

catch (Exception ex){}

}

VB.Net Code to Bind and Export GridView data to Ms Excel file

Design the form as shown above but just replace the line <asp:Button ID="btnExportToExcel" runat="server" Text="Export To MS Excel File" OnClick="btnExportToExcel_Click" /> with the <asp:Button ID="btnExportToExcel" runat="server"Text="Export To MS Excel File" />

Then in the code behind file(.aspx.vb) wrtie the code as:

First include the following namespaces

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

Imports System.Net

Imports System.Net.Mail

Imports System.Web.UI.HtmlControls

Imports System.IO

Imports System.Text

Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString)

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load

If Not Page.IsPostBack Then

BindEmpGrid()

End If

End Sub

Public Overrides Sub VerifyRenderingInServerForm(control As Control)

'It solves the error "Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."

End Sub

Protected Sub BindEmpGrid()

Dim cmd As New SqlCommand("select * from EMPLOYEE", con)

Dim dt As New DataTable()

Dim adp As New SqlDataAdapter(cmd)

adp.Fill(dt)

grEmp.DataSource = dt

grEmp.DataBind()

End Sub

Protected Sub btnExportTExcel_Click(sender As Object, e As EventArgs) Handles btnExportTExcel.Click

Try

Response.ClearContent()

Dim attachment As String = "attachment; filename=MyExelFile.xls"

Response.ClearContent()

Response.AddHeader("content-disposition", attachment)

Response.ContentType = "application/ms-excel"

Dim strWrite As New StringWriter()

Dim htmWrite As New HtmlTextWriter(strWrite)

Dim htmfrm As New HtmlForm()

grEmp.Parent.Controls.Add(htmfrm)

htmfrm.Attributes("runat") = "server"

htmfrm.Controls.Add(grEmp)

htmfrm.RenderControl(htmWrite)

Response.Write(strWrite.ToString())

Response.Flush()

Response.[End]()

Catch ex As Exception

End Try

Notice that I have added an overriding function VerifyRenderingInServerForm in the code behind. This is to resolve the error “Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server” that may occur while exporting GridView data to MS Excel file or MS Word file or PDF or CSV (Comma separated value) file.

After exporting, gridview data to Ms Excel file, will look as shown in figure:

Click on image to enlarge

KANSIRIS

HOW TO BIND AND EXPORT GRIDVIEW DATA TO MS EXCEL FILE USING ASP.NET C#,VB.NET

0 comments:

Post a Comment

Complete Tutorials

Popular Posts

Labels