Query database, then export to Excel VB.NET

Discussion in 'ASP .Net Web Controls' started by Andy, May 11, 2004.

  1. Andy

    Andy Guest

    I am working on a web form and I need to create a report for a user and would like to query the database and then send the data set to Excel. Could somebody please help me with some code to do this

    Thanks a lot!
     
    Andy, May 11, 2004
    #1
    1. Advertising

  2. Hi Andy,

    Here's some code that should get you going. It grabs data from the SQL
    database as a datareader and pushes it out as a CSV. No storage of the file
    required. You should see Excel open with the data.

    Does this help?

    Ken
    Microsoft MVP [ASP.NET]


    Imports System.Data.SqlClient
    Imports System.IO
    Public Class csv
    Inherits System.Web.UI.Page
    Protected WithEvents Button1 As _
    System.Web.UI.WebControls.Button
    Protected WithEvents SqlConnection1 As _
    System.Data.SqlClient.SqlConnection
    #Region " Web Form Designer Generated Code "
    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> _
    Private Sub InitializeComponent()
    Me.SqlConnection1 = _
    New System.Data.SqlClient.SqlConnection
    '
    'SqlConnection1
    '
    Me.SqlConnection1.ConnectionString = _
    "data source=P4320;initial catalog=" & _
    "Northwind;password="""";persist security info=Tru" & _
    "e;user id=sa;workstation id=P4320;packet size=4096"
    End Sub
    Private Sub Page_Init _
    (ByVal sender As System.Object, _
    ByVal e As System.EventArgs) _
    Handles MyBase.Init
    'CODEGEN: This method call is
    'required by the Web Form Designer
    'Do not modify it using the code editor.
    InitializeComponent()
    End Sub
    #End Region
    Private Sub Button1_Click _
    (ByVal sender As System.Object, _
    ByVal e As System.EventArgs) _
    Handles Button1.Click
    'Set the appropriate ContentType.
    Dim filename As String = "orderdetails.csv"
    Dim myCommand As New SqlCommand _
    ("select * from [order details] ", SqlConnection1)
    myCommand.Connection.Open()
    Dim myReader As SqlDataReader = _
    myCommand.ExecuteReader _
    (CommandBehavior.CloseConnection)
    Dim i As Integer
    Dim sb As New System.Text.StringBuilder
    For i = 0 To myReader.FieldCount - 1
    If i < (myReader.FieldCount - 1) Then
    sb.Append(Chr(34) & myReader.GetName(i) & _
    Chr(34) & ",")
    Else
    sb.Append(Chr(34) & myReader.GetName(i) & _
    Chr(34) & vbCrLf)
    End If
    Next
    While myReader.Read()
    For i = 0 To myReader.FieldCount - 1
    If i < (myReader.FieldCount - 1) Then
    sb.Append(Chr(34) & _
    myReader.GetValue(i).ToString & Chr(34) & ",")
    Else
    sb.Append(Chr(34) & _
    myReader.GetValue(i).ToString & Chr(34) & vbCrLf)
    End If
    Next
    End While
    myReader.Close()
    SqlConnection1.Close()
    Response.ContentType = "Application/x-msexcel"
    Response.AddHeader _
    ("content-disposition", "attachment; filename=""" & _
    filename & """")
    'Write the file directly to the HTTP output stream.
    Response.Write(sb.ToString)
    Response.End()
    End Sub
    End Class



    <%@ Page Language="vb" AutoEventWireup="false" Codebehind="csv.aspx.vb"
    Inherits="p4320work.csv"%>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
    <HTML>
    <HEAD>
    <title>csv</title>
    <meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
    <meta name="CODE_LANGUAGE" content="Visual Basic .NET 7.1">
    <meta name="vs_defaultClientScript" content="JavaScript">
    <meta name="vs_targetSchema"
    content="http://schemas.microsoft.com/intellisense/ie5">
    </HEAD>
    <body MS_POSITIONING="FlowLayout">
    <form id="Form1" method="post" runat="server">
    <asp:Button id="Button1" runat="server" Text="Launch"></asp:Button>
    </form>
    </body>
    </HTML>


    "Andy" <> wrote in message
    news:D...
    >I am working on a web form and I need to create a report for a user and
    >would like to query the database and then send the data set to Excel.
    >Could somebody please help me with some code to do this?
    >
    > Thanks a lot!!
    >
    >
     
    Ken Cox [Microsoft MVP], May 11, 2004
    #2
    1. Advertising

  3. Andy

    Andy Bolk Guest

    Thanks. How would I modify this code to access an existing excel file?
    The file I am working with has a specific format and I just want to fill
    in the fields using .NET.

    Thanks



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    Andy Bolk, May 14, 2004
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Luis Esteban Valencia
    Replies:
    1
    Views:
    1,405
    Carl Prothman [MVP]
    Jan 12, 2005
  2. Replies:
    3
    Views:
    5,886
  3. Grey
    Replies:
    4
    Views:
    2,048
    Mark Rae [MVP]
    Oct 17, 2007
  4. Replies:
    0
    Views:
    432
  5. Replies:
    0
    Views:
    313
Loading...

Share This Page