HOW TO: Use ASP.NET to Query and Display Database Data in Excel by Using Visual Basic .NET

This step-by-step article describes how to build a tab-delimited text file dynamically from a database that can be opened in Microsoft Excel. The example in this article demonstrates how to connect to a Microsoft SQL Server database, to return a set of data from the Pubs database, and to create a tab-delimited text file with the data.

Build the Sample

This article demonstrates how to create a sample ASP.NET Visual Basic page that is named ExcelVB.asp. This page connects to the SQL Server Pubs database and returns the information to a tab-delimited text file by using the FileStream object. A link to the new .xls file then appears on the page to demonstrate the output of the code.

1. Start Microsoft Visual Studio .NET.
2. On the File menu, point to New, and then click Project.
3. In the New Project dialog box, click Visual Basic Projects under Project Types, and then click ASP.NET Web Application under Templates.
4. In the Name box, type ExcelVBTest. In the Location box, click the appropriate server. If you are using the local server, you can leave the server name as "http://localhost." Click OK.
5. In the WebForm1.aspx file, drag a HyperLink control from the toolbox to the Web form.
6. Right-click the WebForm1.aspx file, and then click View Code to display the code-behind page source.
7. Add the following code to the top of the code page:

   Imports System.Data
   Imports System.Data.Common
   Imports System.Data.SqlClient
   Imports System.IO
   Imports System.Text
   					

8. Add the following code to the Page_Load event:

   Dim i As Integer
   Dim strLine As String, filePath, fileName, fileExcel, link
   Dim objFileStream As FileStream
   Dim objStreamWriter As StreamWriter
   Dim nRandom As Random = New Random(DateTime.Now.Millisecond)
   Dim fs As Object, myFile As Object
   Dim cnn As SqlConnection = New SqlConnection("server=(local);database=pubs;" & _
                                                "Integrated Security=SSPI")
   
   'Create a pseudo-random file name.
   fileExcel = "t" & nRandom.Next().ToString() & ".xls"
   
   'Set a virtual folder to save the file.
   'Make sure that you change the application name to match your folder.
   filePath = Server.MapPath("\ExcelVBTest")
   fileName = filePath & "\" & fileExcel
   
   'Use FileStream to create the .xls file.
   objFileStream = New FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write)
   objStreamWriter = New StreamWriter(objFileStream)
   
   'Use a DataReader to connect to the Pubs database.
   cnn.Open()
   Dim sql As String = "select au_id,au_lName,au_fname,phone," & _
                       "address,city,state,zip,contract from authors"
   Dim cmd As SqlCommand = New SqlCommand(sql, cnn)
   Dim dr As SqlDataReader
   dr = cmd.ExecuteReader()
   
   'Enumerate the field names and records that are used to build the file.
   For i = 0 To dr.FieldCount - 1
      strLine = strLine & dr.GetName(i).ToString & Chr(9)
   Next
   
   'Write the field name information to file.
   objStreamWriter.WriteLine(strLine)
     
   'Reinitialize the string for data.
   strLine = ""
   
   'Enumerate the database that is used to populate the file.
   While dr.Read()
      For i = 0 To dr.FieldCount - 1
         strLine = strLine & dr.GetValue(i) & Chr(9)
      Next
      objStreamWriter.WriteLine(strLine)
      strLine = ""
   End While
   
   'Clean up.
   dr.Close()
   cnn.Close()
   objStreamWriter.Close()
   objFileStream.Close()
   
   'Show a link to the Excel file.
   HyperLink1.Text = "Open Excel"
   HyperLink1.NavigateUrl = fileExcel
   					

9. On the File menu, click Save All to save the project files.
10. On the Build menu in the Visual Studio .NET Integrated Development Environment, click Build to build the project.
11. In Visual Studio .NET Integrated Development Environment Solution Explorer, right-click the WebForm1.aspx Web form, and then click View in Browser to run the code.

Troubleshooting

• You must change the connection string in the sample code for your environment.
• You may have to increase the permissions for the aspnet_wp process to allow the file to be written.For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
  317012 INFO: Process and Request Identity in ASP.NET