how to export gridview to excel in vb.net

Hi Sraj,

Default.aspx code

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<title>Untitled Pagetitle>

head>

<body>

<div>

div>

<asp:GridView ID="g1" runat="server">

asp:GridView>

<br />

<asp:Label ID="Label1" runat="server" Text="Export data to Excel"

BackColor="Yellow" Font-Bold="True" ForeColor="#FF3300">asp:Label><br />

<asp:ImageButton ID="ImageButton1" runat="server" ImageUrl= "~/images/Excel2.JPG" />

form>

body>

html>

Default.aspx.vb code

Imports System.Data

Imports System.Data.SqlClient

Imports System.IO

Partial Class _Default

Inherits System.Web.UI.Page

Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString").ToString()

Dim con As New SqlConnection(strConnString)

Dim str As String

Dim com As SqlCommand

Dim sqlda As SqlDataAdapter

Dim ds As DataSet

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

bindgrid()

g1.Visible = False

End Sub

Sub bindgrid()

con.Open()

str = "select * from SampleCustomer"

com = New SqlCommand(str, con)

sqlda = New SqlDataAdapter(com)

ds = New DataSet

sqlda.Fill(ds, "SampleCustomer")

g1.DataSource = ds

g1.DataMember = "SampleCustomer"

g1.DataBind()

con.Close()

End Sub

Private Sub ExportToExcel(ByVal strFileName As String, ByVal dg As GridView)

Response.Clear()

Response.Buffer = True

Response.ContentType = "application/vnd.ms-excel"

Response.Charset = ""

Me.EnableViewState = False

Dim oStringWriter As New System.IO.StringWriter

Dim oHtmlTextWriter As New System.Web.UI.HtmlTextWriter(oStringWriter)

g1.RenderControl(oHtmlTextWriter)

Response.Write(oStringWriter.ToString())

Response.[End]()

End Sub

Protected Sub ImageButton1_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles ImageButton1.Click

g1.Visible = True

ExportToExcel("Report.xls", g1)

End Sub

Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)

End Sub

End Class

Thanks
If this post helps you mark it as answer

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Satyapriya Nayak14y ago
Hi Sraj,

Default.aspx code

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

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>Untitled Pagetitle>

head>

<body>

    <form id="form1" runat="server">

    <div>



    div>

    <asp:GridView ID="g1" runat="server">

    asp:GridView>

    <br />

    <br />

    <asp:Label ID="Label1" runat="server" Text="Export data to Excel"

        BackColor="Yellow" Font-Bold="True" ForeColor="#FF3300">asp:Label><br />



    <asp:ImageButton ID="ImageButton1" runat="server" ImageUrl= "~/images/Excel2.JPG" />

    form>

body>

html>

Default.aspx.vb code

Imports System.Data

Imports System.Data.SqlClient

Imports System.IO

Partial Class _Default

    Inherits System.Web.UI.Page

    Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString").ToString()

    Dim con As New SqlConnection(strConnString)

    Dim str As String

    Dim com As SqlCommand

    Dim sqlda As SqlDataAdapter

    Dim ds As DataSet

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        bindgrid()

        g1.Visible = False

    End Sub

    Sub bindgrid()

        con.Open()

        str = "select * from SampleCustomer"
        com = New SqlCommand(str, con)

        sqlda = New SqlDataAdapter(com)

        ds = New DataSet

        sqlda.Fill(ds, "SampleCustomer")

        g1.DataSource = ds

        g1.DataMember = "SampleCustomer"

        g1.DataBind()

        con.Close()

    End Sub

    Private Sub ExportToExcel(ByVal strFileName As String, ByVal dg As GridView)

        Response.Clear()

        Response.Buffer = True

        Response.ContentType = "application/vnd.ms-excel"

        Response.Charset = ""

        Me.EnableViewState = False

        Dim oStringWriter As New System.IO.StringWriter

        Dim oHtmlTextWriter As New System.Web.UI.HtmlTextWriter(oStringWriter)

        g1.RenderControl(oHtmlTextWriter)

        Response.Write(oStringWriter.ToString())

        Response.[End]()

    End Sub

    Protected Sub ImageButton1_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles ImageButton1.Click

        g1.Visible = True

        ExportToExcel("Report.xls", g1)
    End Sub

    Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)

    End Sub

End Class

Thanks
If this post helps you mark it as answer
0