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

Erukulla's April 03, 2015

In the world of web development, data presentation is crucial, and often, users require the ability to export data for offline analysis or sharing. One common scenario is exporting data from a GridView to an Excel file in ASP.NET. In this article, we'll explore how to achieve this using VB programming.

HTML Design for Exporting Gridview Data

Before diving into the VB code, let's set up the HTML design that includes a GridView and a button to trigger the export functionality.


<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

Now, let's dive into the VB code behind the ASP.NET page.


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

Understanding the Code

  1. HTML Design:

    The HTML structure includes a GridView (gvDetails) and a button (btnExport) to trigger the export functionality.

  2. VB Coding:

    The VB code behind the ASP.NET page handles data binding (BindGridview), Excel export (btnExport_Click), and the rendering verification function (VerifyRenderingInServerForm).

export data from gridview to excel in asp.net

Conclusion

Exporting data from a GridView to Excel is a handy feature for web applications. By combining HTML design and VB programming, we can seamlessly achieve this functionality in ASP.NET. Feel free to adapt and enhance the code based on your specific project requirements. Happy coding.

Post a Comment

0 Comments