In this post, I will explain how to export gridview to Excel in Asp.Net?
There often is a need in a project's reporting module to show records of a GridView in an Excel sheet. In ASP.Net we can easily export data of gridview data to an excel file. I will explain how to export data of gridview to an Excel sheet with an example.In this example, we will bind gridview on the page load event and will export it to excel on "Export to Excel" button click.
ASPX Page Code(HTML Code)
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="ExportToExcel.aspx.vb" Inherits="ExportToExcel" %>
<!DOCTYPE html>
<html lang="">
<head runat="server">
<title>Gridview Export to Excel in ASP.Net</title>
<style>
body {
font-family: Georgia;
font-size: small;
}
.GridViewStyle {
border-right: 2px solid #A7A6AA;
border-bottom: 2px solid #A7A6AA;
border-left: 2px solid white;
border-top: 2px solid white;
padding: 4px;
}
.GridViewStyle a {
}
.GridViewHeaderStyle th {
border-left: 1px solid #EBE9ED;
border-right: 1px solid #EBE9ED;
}
.GridViewHeaderStyle {
font-weight: bold;
}
.GridViewFooterStyle {
font-weight: bold;
}
.GridViewRowStyle {
}
.GridViewAlternatingRowStyle {
}
.GridViewRowStyle td, .GridViewAlternatingRowStyle td {
border: 1px solid #EBE9ED;
}
.GridViewSelectedRowStyle {
font-weight: bold;
}
.GridViewPagerStyle {
}
.GridViewPagerStyle table /* to center the paging links*/ {
margin: 0 auto 0 auto;
}
</style>
</head>
<body>
<form id="form1" runat="server" style="vertical-align: top;">
<h2>Gridview Export to Excel in ASP.Net</h2>
<div>
<asp:GridView ID="gridViewEmp" runat="server" AutoGenerateColumns="false">
<FooterStyle CssClass="GridViewFooterStyle" />
<RowStyle CssClass="GridViewRowStyle" />
<SelectedRowStyle CssClass="GridViewSelectedRowStyle" />
<PagerStyle CssClass="GridViewPagerStyle" />
<AlternatingRowStyle CssClass="GridViewAlternatingRowStyle" />
<HeaderStyle CssClass="GridViewHeaderStyle" />
<Columns>
<asp:BoundField DataField="Code" HeaderText="Code" />
<asp:BoundField DataField="Name" HeaderText="Name" />
<asp:BoundField DataField="City" HeaderText="City" />
<asp:BoundField DataField="Designation" HeaderText="Designation" />
<asp:BoundField DataField="Department" HeaderText="Department" />
</Columns>
</asp:GridView>
</div>
<br />
<asp:Button ID="btnExport" runat="server" Text="Export To Excel" />
</form>
</body>
</html>
VB.Net Code
Imports System.Data
Imports System.IO
Partial Class ExportToExcel
Inherits System.Web.UI.Page
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Dim dt As New DataTable()
dt.Columns.AddRange(New DataColumn(4) {
New DataColumn("Code"),
New DataColumn("Name"),
New DataColumn("City"),
New DataColumn("Designation"),
New DataColumn("Department")
})
dt.Rows.Add("E0001", "Sachin", "Nagpur", "Software Developer", "IT")
dt.Rows.Add("E0002", "Harshal", "Mumbai", "Recruiter", "HR")
dt.Rows.Add("E0003", "Nilesh", "Pune", "Accountant", "Finance")
dt.Rows.Add("E0004", "Shrikant", "Jalgaon", "Sales Manager", "Marketing")
gridViewEmp.DataSource = dt
gridViewEmp.DataBind()
End If
End Sub
Protected Sub btnExport_Click(sender As Object, e As EventArgs) Handles btnExport.Click
Try
Dim FileName As String
FileName = "Employee List"
Response.Clear()
Response.Buffer = True
Response.AddHeader("content-disposition", "attachment;filename=" & FileName + ".xls")
Response.Charset = ""
Response.ContentType = ContentType
Dim sw As New StringWriter()
Dim HW As New HtmlTextWriter(sw)
gridViewEmp.RenderControl(HW)
Dim ExportString As String
ExportString = "<html><head><style type='text/css'>"
ExportString = ExportString & "</style></head><body>"
ExportString = ExportString & "<h2><Center>" & FileName & "</Center></h2><br>"
ExportString = ExportString & sw.ToString()
ExportString = ExportString & "</body></html>"
Response.Write(ExportString)
Response.Flush()
Response.Close()
Response.End()
Catch ex As Exception
End Try
End Sub
End Class