Knowledge Point

Here I will explain how to export data from gridview to excel in asp.net using c#, vb.net or bind data to gridview and export asp.net gridview data to excel in asp.net using c#, vb.net.

Description:

In previous articles I explained export gridview data to csv file in asp.net, export gridview data to pdf file in asp.net, export webpage with images to pdf in asp.net, group columns in asp.net gridview header row and many articles relating gridview,asp.net, c#, vb.net. Now I will explain how to export data from gridview to excel in asp.net using c#, vb.net.

To export gridview data to excel we need to write the code like as shown below

<title>Export Gridview Data to Excel in Asp.net</title>

</head>

<body>

<div>

<asp:GridView ID="gvDetails" AutoGenerateColumns="false" CellPadding="5" runat="server">

<asp:BoundField HeaderText="UserId" DataField="UserId" />

<asp:BoundField HeaderText="UserName" DataField="UserName" />

<asp:BoundField HeaderText="Education" DataField="Education" />

<asp:BoundField HeaderText="Location" DataField="Location" />

</Columns>

</asp:GridView>

</div>

<asp:Button ID="btnExport" runat="server" Text="Export to Excel"

onclick="btnExport_Click" />

</form>

</body>

</html>

Now in code behind add following namespaces

C# Code

using System;

using System.Data;

using System.IO;

using System.Web.UI;

After that add following code in code behind like as shown below

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

BindGridview();

}

protected void BindGridview()

{

DataTable dt = new DataTable();

dt.Columns.Add("UserId", typeof(Int32));

dt.Columns.Add("UserName", typeof(string));

dt.Columns.Add("Education", typeof(string));

dt.Columns.Add("Location", typeof(string));

dt.Rows.Add(1, "SureshDasari", "B.Tech", "Chennai");

dt.Rows.Add(2, "MadhavSai", "MBA", "Nagpur");

dt.Rows.Add(3, "MaheshDasari", "B.Tech", "Nuzividu");

dt.Rows.Add(4, "Rohini", "MSC", "Chennai");

dt.Rows.Add(5, "Mahendra", "CA", "Guntur");

dt.Rows.Add(6, "Honey", "B.Tech", "Nagpur");

gvDetails.DataSource = dt;

gvDetails.DataBind();

}

public override void VerifyRenderingInServerForm(Control control)

{

/* Verifies that the control is rendered */

}

protected void btnExport_Click(object sender, EventArgs e)

{

Response.ClearContent();

Response.Buffer = true;

Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Customers.xls"));

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

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

gvDetails.AllowPaging = false;

BindGridview();

//Change the Header Row back to white color

gvDetails.HeaderRow.Style.Add("background-color", "#FFFFFF");

//Applying stlye to gridview header cells

for (int i = 0; i < gvDetails.HeaderRow.Cells.Count; i++)

{

gvDetails.HeaderRow.Cells[i].Style.Add("background-color", "#df5015");

}

gvDetails.RenderControl(htw);

Response.Write(sw.ToString());

Response.End();

}

If you observe above code I added one function that is VerifyRenderingInServerForm this function is used to avoid the error like “control must be placed in inside of form tag”. If we set VerifyRenderingInServerForm function then compiler will think that controls rendered before exporting and our functionality will work perfectly.

VB.NET Code

Imports System.Data

Imports System.IO

Imports System.Web.UI

Partial Class ExportGridviewDatainVB

Inherits System.Web.UI.Page

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

If Not IsPostBack Then

BindGridview()

End If

End Sub

Protected Sub BindGridview()

Dim dt As New DataTable()

dt.Columns.Add("UserId", GetType(Int32))

dt.Columns.Add("UserName", GetType(String))

dt.Columns.Add("Education", GetType(String))

dt.Columns.Add("Location", GetType(String))

dt.Rows.Add(1, "SureshDasari", "B.Tech", "Chennai")

dt.Rows.Add(2, "MadhavSai", "MBA", "Nagpur")

dt.Rows.Add(3, "MaheshDasari", "B.Tech", "Nuzividu")

dt.Rows.Add(4, "Rohini", "MSC", "Chennai")

dt.Rows.Add(5, "Mahendra", "CA", "Guntur")

dt.Rows.Add(6, "Honey", "B.Tech", "Nagpur")

gvDetails.DataSource = dt

gvDetails.DataBind()

End Sub

Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)

' Verifies that the control is rendered

End Sub

Protected Sub btnExport_Click(ByVal sender As Object, ByVal e As EventArgs)

Response.ClearContent()

Response.Buffer = True

Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", "Customers.xls"))

Response.ContentType = "application/ms-excel"

Dim sw As New StringWriter()

Dim htw As New HtmlTextWriter(sw)

gvDetails.AllowPaging = False

BindGridview()

'Change the Header Row back to white color

gvDetails.HeaderRow.Style.Add("background-color", "#FFFFFF")

'Applying stlye to gridview header cells

For i As Integer = 0 To gvDetails.HeaderRow.Cells.Count - 1

gvDetails.HeaderRow.Cells(i).Style.Add("background-color", "#df5015")

gvDetails.RenderControl(htw)

Response.Write(sw.ToString())

Response.[End]()

End Sub

End Class

Demo

Whenever we run application we will see the screen like as shown below

Once we click on Export to Excel button we will see data in excel file like as shown below

Knowledge Point

Sunday, 19 January 2014

No comments:

Post a Comment