Query database, then export to Excel VB.NET

05-11-2004

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!

05-11-2004

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

6F7DCBB-38D5-45EC-8C48-...
>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!!
>
>

05-14-2004

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!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
how to export query data to excel	importantEmail@gmail.com	ASP .Net	2	02-15-2006 07:08 PM
export sql query results to Excel	S. Justin Gengo	ASP .Net	4	11-06-2005 09:30 PM
Help. SessionID is x then y then x then y	BodiKlamph@gmail.com	ASP General	0	09-03-2005 03:02 PM
Dinamt Table, cannot export sql query results to excel! Please help	IluDeR	ASP .Net Web Controls	0	01-21-2004 11:16 AM
Dinamt Table, cannot export sql query results to excel! Please help	IluDeR	ASP .Net Datagrid Control	0	01-21-2004 11:16 AM