Gridview export to Excel
In Asp.Net, there exists a valuable capability to export data from the GridView control to an Excel file. This functionality enables users to seamlessly extract and save the displayed data for further analysis or sharing purposes.
GridView data into a downloadable Excel file
In the forthcoming discussion, we will investigate into the process of binding data to the GridView dynamically, thereby ensuring real-time data representation. Additionally, we will explore the creation of a dedicated button that empowers users to initiate the export operation, converting the GridView data into a downloadable Excel file. It is worth noting that in a prior lesson, we examined the intricacies of binding data to the GridView control dynamically, thereby laying the foundation for the subsequent steps outlined in this tutorial.
GridView at Runtime Database
In this article I have used Microsoft's Pubs database for sample data. You can download it free from the following link.
Download
Drag and Drop one GridView control and a Button control to the defailt.aspx file. In the code behind, write the code for connecting database in the page_load event and in the Button click event write the code for export to excel file.
protected void btntoExcel_Click(object sender, EventArgs e)
{
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=gvtoexcel.xls");
Response.ContentType = "application/excel";
System.IO.StringWriter sw = new System.IO.StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
GridView1.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}
After export to excel file, when you open the excel file, it look like the following :
Default.aspx
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
</div>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
<br />
<asp:Button ID="btntoExcel" runat="server" Text="GridView to Excel" onclick="btntoExcel_Click" />
</form>
</body>
</html>
C# Source Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
SqlDataAdapter adapter = new SqlDataAdapter();
DataSet ds = new DataSet();
int i = 0;
string sql = null;
string connetionString = "Data Source=.;Initial Catalog=pubs;User ID=sa;Password=*****";
sql = "select * from publishers";
SqlConnection connection = new SqlConnection(connetionString);
connection.Open();
SqlCommand command = new SqlCommand(sql, connection);
adapter.SelectCommand = command;
adapter.Fill(ds);
connection.Close();
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
}
protected void btntoExcel_Click(object sender, EventArgs e)
{
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=gvtoexcel.xls");
Response.ContentType = "application/excel";
System.IO.StringWriter sw = new System.IO.StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
GridView1.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{
/*Tell the compiler that the control is rendered
* explicitly by overriding the VerifyRenderingInServerForm event.*/
}
}
VB.NET Source Code
Imports System.Drawing
Imports System.Data.SqlClient
Imports System.Data
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim adapter As New SqlDataAdapter()
Dim ds As New DataSet()
Dim i As Integer = 0
Dim sql As String = Nothing
Dim connetionString As String = "Data Source=.;Initial Catalog=pubs;User ID=sa;Password=*****"
sql = "select * from stores"
Dim connection As New SqlConnection(connetionString)
connection.Open()
Dim command As New SqlCommand(sql, connection)
adapter.SelectCommand = command
adapter.Fill(ds)
adapter.Dispose()
command.Dispose()
connection.Close()
GridView1.DataSource = ds.Tables(0)
GridView1.DataBind()
End Sub
Protected Sub btntoExcel_Click(ByVal sender As Object, ByVal e As EventArgs)
Response.ClearContent()
Response.AddHeader("content-disposition", "attachment; filename=gvtoexcel.xls")
Response.ContentType = "application/excel"
Dim sw As New System.IO.StringWriter()
Dim htw As New HtmlTextWriter(sw)
GridView1.RenderControl(htw)
Response.Write(sw.ToString())
Response.[End]()
End Sub
Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
'Tell the compiler that the control is rendered
'explicitly by overriding the VerifyRenderingInServerForm event.
End Sub
End Class