Generating a Tabdelimited File on the Web Server

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

In your first ASP script (see the code in the AccessTbl.asp file earlier in this chapter), you found out that to retrieve data from a table in a Microsoft Access database and display it in an Internet browser as an Excel spreadsheet, you must use the following directive in your ASP script:

<% Response.ContentType = "Application/" %>

If you comment out the above statement in the AccessTbl.asp file, you will see a static table of data upon requesting this .asp page.

The next example ASP script demonstrates another way to generate an Excel file on the server. This script creates a tab-delimited file on the web server with an .xls extension. The user can open the generated Excel file in the browser or download it. After the session ends, the file is automatically deleted by the server. This example uses two files. The first one is named AccessTbl_2.asp and contains the code to generate the Excel file on the server. The second one is the Global.asa file that changes the session timeout when the session starts and deletes the file when the session ends.

| http: //I ocalhost/accessD B /AccessT bl. asp


1 1 File Edü View Insert Foimat Tools Data 1

Go To Favorites Help F*; j

J Bock • ■+

J ¿3 ¿¿Search [¿J Favorites .^History ijl »

Address |iä] http:

//localhost/accaa ;D8 /AcciisT blasp

jJ (>3o


¡1 f* Speedy Express



□ 1 E FT



(«' Phone



1 llSpeedy Express 1(503) 555-3831 I



United Package (503) 555-3199




Federal Shipping (503) 555-9931



H 4

> >l . AccessTb!/ | i


Step 1: Creating the Active Server Page That Generates an Excel File on the Server

1. Open Notepad and type the ASP code shown below.

2. Save the file in your virtual folder as C:\Inetpub\wwwroot\Excel-WithASP\AccessTbl_2.asp.

3. Close Notepad when you are finished. AccessTbl_2.asp code

' declare variables

Dim accessDB

Dim conn

Dim rst

Dim sql

Dim strFileN

Dim fso

Dim excelFile

Dim strLine

' name of the database accessDB="Northwind"

' connection string to the database conn="DRIVER={Microsoft Access Driver (*.mdb)};" conn=conn & "DBQ=" & Server.Mappath(accessDB)

' create a Recordset

Set rst = Server.CreateObject("ADODB.Recordset")

' select some records from Products table sql = "SELECT * FROM Products WHERE Categoryld = 2"

' Open Recordset (and execute SQL statement above) ' using the connection to the database rst.Open sql, conn

' use the Session ID as the name for the Excel file ' get the complete path to the file (needed for the hyperlink) strFileN=Server.MapPath("./") & "\" & Session.SessionID & ".xls"

' create a text file

Set fso = Server.CreateObject("Scripting.FileSystemObject") Set excelFile=fso.CreateTextFile(strFileN, True)

'read and write table headings For Each fld in rst.Fields strLine = strLine & fld.Name & vbTab Next excelFile.WriteLine strLine

'read and write records Do While Not rst.EOF strLine = ""

For Each fld in rst.Fields strLine = strLine & fld.Value & vbTab Next excelFile.WriteLine strLine rst.MoveNext Loop

' store the filename in the Session object for cleanup by the server Session("strExcelFile") = strFileN

' close and release the variables rst.Close

Set rst = Nothing

Set fso = Nothing set excelFile = Nothing

' write file information and link to the browser

Response.Write "Excel file from Products table was generated as " & _

Session.SessionID & ".xls<BR>" Response.Write "<A href=" & strFileN & ">Click here to open " & _ "this file in Excel</A>"

The above ASP script shows how to generate an Excel file from scratch. The script connects to the Access database on the server and retrieves data from the Products table. This time, however, we are not retrieving all table data but only a subset of data that belongs to the product category 2. Instead of creating a table using HTML tags, the ASP script stores retrieved data to the strLine variable. This variable is then used as a parameter to the WriteLine method that actually writes the data to a file (see the "Modern Methods of Working with Files and Folders" section in Chapter 8). Notice that the vbTab constant is used to separate columns. In this ASP script example, the file name is generated using the SessionID property of the Session object:

strFileN = Server.MapPath("./") & "/" & Session.SessionID & ".xls"

The above statement guarantees that each time the ASP page is requested, a unique file name is created. The SessionID property returns the session identifier, a unique identifier that is generated by the server when the session is created. The session ID is returned as a Long data type.

Note: You can also generate a unique file name by using the GetTempName method of the FileSystemObject. Dim fso, strFileN

Set fso = Server.CreateObject("Scripting.FileSystemObject") strFileN = fso.GetBaseName(fso.GetTempName()) & ".xls"

The GetBaseName method of the FileSystemObject will remove the .tmp file extension from the file name returned by the GetTempName method.

Next, the code stores the filename in the Session object for cleanup by the server:

Session("strExcelFile") = strFileN

The above statement stores the variable strFileN in the Session object named strExcelFile. You can use the Session object to store information needed for a particular user session. Variables stored in the Session object persist for the entire user session. The server destroys the Session object when the session expires or is abandoned.

After retrieving and writing all data to the text file, you will want to close and release the variables, notify the user that the file was generated, and provide a hyperlink to download or display the file:

Response.Write "Your Excel file was generated as " & strFileN & "<BR>" Response.Write "<A href=" & strFileN & ">Click here to open " & _ "this file in Excel</A>"

In the above statements, the Response.Write statement writes the text to the browser. The <BR> tag indicates a line break. This way, two statements that you write to the browser will appear on separate lines. The second line includes a link that the user can click to download the file or open it in the browser. The link begins with the <A href=" & strFileN & "> tag and ends with the </A> tag. Between these tags, you should enter the text for the user to click on.

Step 2: Creating a Global.asa File

To ensure that the server is not burdened with many files created by users requesting your ASP script, you need to somehow remove these files when they are no longer needed. The technique commonly used is to perform the cleanup in the Global.asa file when the user session ends. The Global.asa file is an optional file that contains Application events, Session events, object declarations, and type library declarations. An application can only have one Global.asa file. Before you can try out your AccessTbl_2.asp script, use these steps to create the Global.asa file:

1. Open Notepad and type the Global.asa code shown on the following page.

2. Save the file in your virtual folder as C:\ExcelWithASP\Global.asa.

3. Close Notepad when you are finished. Global.asa code

<SCRIPT LANGUAGE=VBScript RUNAT="Server"> Sub Session_OnStart

' make the session expire after 1 minute ' (for demonstration purpose only)

Session.Timeout = 1 End Sub

Sub Session_OnEnd

' delete the file created during the session set Session("fso") = CreateObject("Scripting.FileSystemObject") Session("fso").DeleteFile Session("strExcelFile"), True End Sub

Notice that the Global.asa file presented above has two event procedures. The Session_On start procedure will run when the user requests a page from your ASP application. A session automatically ends if a user has not requested or refreshed a page in an application for a specified period of time. This value is 20 minutes by default. Because we don't want to wait that long to see if the Excel file actually gets deleted by the server, we will change the default by setting the Session Timeout property of the Session object to 1 minute:

Session.Timeout = 1

When the session ends, the Session_OnEnd event will fire. Here we will use the DeleteFile method of the FileSystemObject to remove the file created during the session from the server directory:

set Session("fso") = CreateObject("Scripting.FileSystemObject") Session("fso").DeleteFile Session("strExcelFile"), True

Notice that the name of the file to be deleted is stored in the Session object named strExcelFile. Recall that you created this object in your ASP script. The second, optional argument of the DeleteFile method with the value of True indicates that the files with the read-only attribute set should be deleted.

Step 3: Running the ASP Script—AccessTbl_2.asp

Now that your ASP script file (AccessTbl_2.asp) and the Global.asa file are ready, it's time to try out our script.

1. Open your Internet browser.

2. Enter the following address: http://localhost/accessDB/Access-Tbl_2.asp.

You should see the page shown in Figure 16-32.

File Edit View Favorites Tools Help

File Edit View Favorites Tools Help

on the server on the server

3. Click on the provided hyperlink. You should see the File Download dialog box, as shown earlier in Figure 16-30. Notice that with the provided options, you can either open the file in the browser or download it to your computer.

4. Activate Windows Explorer and navigate to your virtual directory named C:/ExcelWithASP If you stay inactive for a minute or so, you will notice that the .xls file disappears after the set timeout interval. You may need to press F5 to refresh the window. If you switch back to your browser after the file has been removed from the virtual directory and click the provided link, you will get the "Page cannot be displayed" screen.

Was this article helpful?

0 0

Post a comment