How to Export Data from Gridview to Excel in Asp.net Using VB Programing


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


HTML Design Export Gridview Data to Excel in Asp.net

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Export Gridview Data to Excel in Asp.net</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:gridview autogeneratecolumns="false" cellpadding="5" id="gvDetails" runat="server">
<columns>
<asp:boundfield datafield="UserId" headertext="UserId">
<asp:boundfield datafield="UserName" headertext="UserName">
<asp:boundfield datafield="Education" headertext="Education">
<asp:boundfield datafield="Location" headertext="Location">
</asp:boundfield></asp:boundfield></asp:boundfield></asp:boundfield></columns>
<headerstyle backcolor="#df5015" font-bold="true" forecolor="White">
</headerstyle></asp:gridview>
</div>
<asp:button id="btnExport" onclick="btnExport_Click" runat="server" text="Export to Excel">
</asp:button></form>
</body>
</html>

VB Coding

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") 
Next
  gvDetails.RenderControl(htw)
  Response.Write(sw.ToString())
  Response.[End]() 
End Sub
End Class

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 setVerifyRenderingInServerForm function then compiler will think that controls rendered before exporting and our functionality will work perfectly.
Once we click on Export to Excel button we will see data in excel file like as shown below
export data from gridview to excel in asp.net
0 Komentar untuk "How to Export Data from Gridview to Excel in Asp.net Using VB Programing"

Back To Top