Net-informations.com
SiteMap  | About    

Gridview export to Excel

In Asp.Net, we can export the data in the Gridview control to an Excel file. Here we are going to bind the data in GridView at runtime and we create a button to allow user to export the dtata to an Excel file. In the previous lesson we saw how to bind a Gridview at runtime.

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();
}

export gridview to excel

After export to excel file, when you open the excel file, it look like the following :

gridview to excel

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







net-informations.com (C) 2017    Founded by raps mk
All Rights Reserved. All other trademarks are property of their respective owners.