.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Code Snippets >> ASP.NET Controls >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

Export database values to Excel

Posted By:satyapriyanayak       Posted Date: March 25, 2013    Points: 40    Category: ASP.NET Controls    URL: http://www.dotnetspark.com  

Export database values to Excel
 

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 Page</title>

</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