How to Write XLS Data in ASP
Writing web content that opens up in Excel can be a fairly simple process involving two key actions. The first action is that the initial content that your ASP outputs must tell the browser that Excel data, not HTML or plain text, is coming. Second, your ASP script need only follow up that header data with a simple HTML table, from which Excel will be able to create distinct columns and rows of data.
Other People Are Reading
Instructions
-
-
1
Create the MySQL database table from which the ASP code will fetch data. Logon to your server's MySQL administration page. Create a database called "myDB." Contact your server's technical support if you need help in creating the database.
Select the SQL tab and enter the following command to create the table:
CREATE TABLE movies (title VARCHAR(30), director VARCHAR(30), year INT );
Return to the SQL tab and enter this command to create data for the table:
INSERT INTO movies VALUES ( "star wars", "lucas", 1977), ("excalibur", "boorman", 1980);
-
2
Open up Notepad and enter the following ASP program code:
<%
dim td
dim etd
dim br
dim tr
dim etr
dim tab
dim etabtd = "<td>"
etd = "<etd>"
br = "<br>"
tr = "<tr>"
etr = "</tr>"
tab = "<table>"
etab = "</table>"'dbase stuff
Dim Connection
Dim RS
Dim SQLStmt
Dim SSN, Firstname, Lastname
Dim ConnectionString'Query the database
Set Connection = Server.CreateObject("ADODB.Connection")
ConnectionString="DRIVER={MySQL ODBC 3.51 Driver}; SERVER=*username*.readyhostingmysql.com; PORT=3306;" &_
"DATABASE=myDB; USER=*username*; PASSWORD=*password*; OPTION=3;"Connection.Open ConnectionString
SQLStmt = "SELECT * FROM movies;"
Set recSet = Connection.Execute(SQLStmt)''''''''''''''''''''''''''''''''''''''''
'Write the HTTP header that tells the browser that the output is an Excel file
WriteHeader()'Create an HTML table from the db table rows
Response.Write tab
Do While Not recSet.EOF
Response.Write tr
Response.Write td & recSet.Fields("title") & etd & br
Response.Write td & recSet.Fields("director") & etd & br
Response.Write td & recSet.Fields("year") & etd & br
Response.Write etr
recSet.MoveNext
LoopResponse.Write etab
Connection.Close
WriteFooter()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
public sub WriteHeader()
'tell the Browser that content is an Excel file
Response.ContentType = "application/vnd.ms-excel"
%>
<HTML>
<HEAD>
</HEAD>
<BODY>
<%
end sub 'WriteHeaderpublic sub WriteFooter()
%>
</BODY>
<%
end sub 'WriteHeader
%> -
-
3
In the statement that contains the ConnectionString, change the string to the one required to connect to the myDB database you created. The string in this article is a template. Request the correct connection string from your site's technical support.
-
4
Save the script as "WriteExcelExamp.asp" and upload it to your server.
-
5
Enter the URL for the script in your browser. Notice the message box that appears, verifying that you want to open the file in Excel. Indicate that you do want to open it. If Excel then displays a message box to confirm that you want to open the file, click "Yes."
Excel will open to display all the rows you entered in the movies table.
-
1
