server| data 1, ASP file:
<%@ language= "VBSCRIPT"%>
<%option explicit%>
<%
' EXAMPLE as: Put a 24-hour online number in the database into an Excel file
' AUTHOR: Steel Workers
' Email:hello_hhb@21cn.com
' Date:2001-3-25
' Test: Testing through in Nt4,sp6,sql SERVER 7.0,excel2000
%>
<HTML>
<HEAD>
<meta content= "text/html; charset=gb2312 "http-equiv=" Content-type ">
<TITLE> Generate Excel File </TITLE>
</HEAD>
<body>
<a href= "Dbtoexcel.asp?act=make" > Generation of online population excel</a>
<HR size=1 Align=left width=300px>
<%
If Request ("act") = "" Then
Else
Dim conn
Set Conn=server. CreateObject ("Adodb.connection")
Conn. Open "Test", "sa", ""
' Conn. Open application ("ConnStr")
Dim Rs,sql,filename,fs,myfile,x,link
Set fs = server. CreateObject ("Scripting.FileSystemObject")
'--suppose you want the resulting Excel file to be stored as follows
filename = "C:\online.xls"
'--delete it if the original Excel file exists
If Fs. FileExists (filename) Then
Fs. DeleteFile (filename)
End If
'--Create Excel file
Set myfile = fs. CreateTextFile (Filename,true)
Set rs = Server.CreateObject ("ADODB.") Recordset ")
'--Find out from the database the data you want to put in Excel
sql = "Select Population,hourpos,datepos from Populationperhour ORDER by datepos,hourpos ASC"
Rs. Open Sql,conn
If Rs. EOF and Rs. BOF Then
Else
Dim strline,responsestr
Strline= ""
For each x in Rs.fields
Strline= StrLine & X.name & Chr (9)
Next
'--Writes the table's column name first to Excel
MyFile.WriteLine StrLine
Do but not Rs. Eof
Strline= ""
For each x in Rs. Fields
Strline= StrLine & X.value & Chr (9)
Next
'--Writes the table's data to Excel
MyFile.WriteLine StrLine
Rs. MoveNext
Loop
End If
Rs. Close
Set rs = Nothing
Conn.close
Set conn = Nothing
Set myfile = Nothing
Set fs=nothing
link= "<a href=" & filename & ">open the Excel file</a>"
Response.Write Link
End If
%>
</BODY>
</HTML>
2, Database Related:
CREATE TABLE [Populationperhour] (
[Population] [INT] Not NULL,
[Hourpos] [INT] Not NULL,
[Datepos] [DateTime] Not NULL
);
INSERT into populationperhour values (' 936 ', ' 1 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 636 ', ' 2 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 106 ', ' 3 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 177 ', ' 4 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 140 ', ' 5 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 114 ', ' 6 ', ' 2001-1-11 ');
INSERT into Populationperhour values (' 94 ', ' 7 ', ' 2001-1-11 ');
INSERT into Populationperhour values (' 49 ', ' 8 ', ' 2001-1-11 ');
INSERT into Populationperhour values (' 88 ', ' 9 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 215 ', ' 10 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 370 ', ' 11 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 550 ', ' 12 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 629 ', ' 13 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 756 ', ' 14 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 833 ', ' 15 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 923 ', ' 16 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 980 ', ' 17 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 957 ', ' 18 ', ' 2001-1-11 ');
INSERT into Populationperhour values (' 812 ', ' 19 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 952 ', ' 20 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 1379 ', ' 21 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 1516 ', ' 22 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 1476 ', ' 23 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 1291 ', ' 24 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 1028 ', ' 1 ', ' 2001-1-12 ');
INSERT into populationperhour values (' 687 ', ' 2 ', ' 2001-1-12 ');
INSERT into populationperhour values (' 462 ', ' 3 ', ' 2001-1-12 ');
INSERT into populationperhour values (' 317 ', ' 4 ', ' 2001-1-12 ');
INSERT into Populationperhour values (' 221 ', ' 5 ', ' 2001-1-12 ');
INSERT into populationperhour values (' 158 ', ' 6 ', ' 2001-1-12 ');
INSERT INTO PO
