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

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

Build the Sample Code

This example creates a sample ASP.NET Visual C# .NET page named ExcelCS.aspx. This page connects to the SQL Server Pubs database and uses the FileStream object to return the information to a tab-delimited text file. The ASP.NET page then displays a link to the .xls file that you created 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, under Project Types, click Visual C# Projects. Under Templates, click ASP.NET Web Application.
4. In the Location box, type the server name and the project name in the following format:
   http://ServerName/Project Name
   In this example, name the project
   ExcelCSTest. If you are using the local server, you can leave the server name as
   http://localhost.
   
   
5. Drag a HyperLink control from the toolbox to the WebForm1.aspx file.
6. Right-click WebForm1.aspx, and then click View Code to display the code-behind page source.
7. Add the following statements to the top of the code-behind page:

   using System.Data.SqlClient;
   using System.IO;
   using System.Text;
   

8. In the code-behind page of WebForm1.aspx, add the following code to the Page_Load event:

   //You use these variables throughout the application.
   string fileExcel, filePath, fileName, strLine, sql;
   FileStream objFileStream;
   StreamWriter objStreamWriter;
   Random nRandom = new Random(DateTime.Now.Millisecond);
   SqlConnection cnn = new SqlConnection("server=(local);database=pubs;Integrated Security=SSPI"); 
   
   //Create a random file name.
   fileExcel = "t" + nRandom.Next().ToString() + ".xls";
                       
   //Set a virtual folder to save the file.
   //Make sure to change the application name to match your folder.
   filePath = Server.MapPath("\\ExcelCSTest");
   fileName = filePath + "\\" + fileExcel;
   
   //Use FileSystem objects to create the .xls file.
   objFileStream = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write);          
   objStreamWriter = new StreamWriter(objFileStream); 
   
   //Use a DataReader object to connect to the Pubs database.
   cnn.Open();
   sql = "select au_id,au_lName,au_fname,phone,address,city,state,zip,contract from authors"; 
   SqlCommand cmd = new SqlCommand(sql, cnn); 
   SqlDataReader dr; 
   dr = cmd.ExecuteReader();
   
   //Initialize the string that is used to build the file.
   strLine = "";
                  
   //Enumerate the field names and the records that are used to build 
   //the file.
   for (int i = 0; i <= dr.FieldCount-1; i++) 
      {
         strLine = strLine + dr.GetName(i).ToString() + Convert.ToChar(9);
      }
   
   //Write the field name information to the file.
   objStreamWriter.WriteLine(strLine);
   
   //Reinitialize the string for data.
   strLine = "";
   
   //Enumerate the database that is used to populate the file.
   while (dr.Read()) 
      {
         for (int i = 0; i <= dr.FieldCount-1; i++) 
            {
            strLine = strLine + dr.GetValue(i).ToString() + Convert.ToChar(9);
         }                    
         objStreamWriter.WriteLine(strLine);
         strLine="";
      }
   
   //Clean up.
   dr.Close();
   cnn.Close();
   objStreamWriter.Close();
   objFileStream.Close();
   
   //Include a link to the Excel file.
   HyperLink1.Text="Open Excel";
   HyperLink1.NavigateUrl=fileExcel;
   

9. Modify the connection string (SqlConnection) as necessary for your environment.
10. On the File menu, click Save All to save the project files.
11. On the Build menu in the Visual Studio .NET Integrated Development Environment (IDE), click Build Solution to build the project.
12. In Visual Studio .NET Integrated Development Environment Solution Explorer, right-click WebForm1.aspx, and then click View in Browser to run the code.

Troubleshooting

• You must change the connection string in the code sample to match your environment.
• You may have to increase permissions for the aspnet_wp process (in Microsoft Windows 2000 and in Microsoft Windows XP) or the w3wp process (in Microsoft Windows Server 2003) to allow the file to be written. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
  317012 INFO: Process and Request Identity in ASP.NET