废话少说，请看代码：
runquery.asp
1@ LANGUAGE="VBSCRIPT"
1
2'DSNless connection to Access Database
3strDSNPath = "PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("testDB.mdb")
请自己COPY这个文件
1
2server.scripttimeout=1000
3Response.Buffer = True
4
5if(Request.Form("ReturnAS") = "Content") then
6Response.ContentType = "application/msexcel"
7end if
8Response.Expires = 0
9
10dim oConn
11dim oRS
12dim strSQL
13dim strFile
14
15Set oConn = Server.CreateObject("ADODB.Connection")
16Set oRS = Server.CreateObject("ADODB.Recordset")
17strSQL = BuildSQL()
18
19oRS.Open strSQL, strDSNPath, adOpenForwardOnly, adLockReadOnly, adCmdText
1<html>
2<head>
3<title>Excel Export Demo</title>
4</head>
5<body>
if(Request.Form("ReturnAS") = "CSV") then
CreateCSVFile()
else if(Request.Form("ReturnAS") = "Excel") then
CreateXlsFile()
else if(Request.Form("ReturnAS") = "HTML") then
GenHTML()
else if(Request.Form("ReturnAS") = "Content") then
GenHTML()
end if
end if
end if
end if
Set oRS = Nothing
Set oConn = Nothing
Response.Flush
1</body>
2</html>
1<script language="vbscript" runat="Server">
2Function BuildSQL()
3dim strSQL
4dim strTemp
5
6strTemp = ""
7strSQL = "select year, region, sales_amt from sales"
8
9if(Request.Form("Year") <> "ALL") then
10strTemp = " where Year = "
11strTemp = strTemp & Request.Form("Year")
12end if
13
14if(Request.Form("Region") <> "ALL") then
15if(Len(strTemp) > 0) then
16strTemp = strTemp & " and Region = "
17else
18strTemp = strSTL & " where Region = "
19end if
20strTemp = strTemp & "'"
21strTemp = strTemp & Request.Form("Region")
22strTemp = strTemp & "'"
23end if
24
25BuildSQL = strSQL & strTemp
26End Function
27
28Function GenFileName()
29dim fname
30
31fname = "File"
32systime=now()
33fname= fname & cstr(year(systime)) & cstr(month(systime)) & cstr(day(systime))
34fname= fname & cstr(hour(systime)) & cstr(minute(systime)) & cstr(second(systime))
35GenFileName = fname
36End Function
37
38Function GenHTML()
39Response.Write("<DIV ALIGN=center><FONT SIZE=+1>Sales Reporting</FONT></DIV>")
40Response.Write("<TABLE WIDTH=100% BORDER=1 CELLSPACING=1 CELLPADDING=1>")
41Response.Write("<TR>")
42Response.Write(" <TD>Year</TD>")
43Response.Write(" <TD>Region</TD>")
44Response.Write(" <TD>Sales</TD>")
45Response.Write("</TR>")
46if(oRS.BOF = True and oRS.EOF = True) then
47Response.Write("Database Empty")
48else
49oRS.MoveFirst
50Do While Not oRS.EOF
51Response.Write("<TR>")
52Response.Write("<TD>")
53Response.Write(oRS.Fields("Year").Value)
54Response.Write("</TD>")
55Response.Write("<TD>")
56Response.Write(oRS.Fields("Region").Value)
57Response.Write("</TD>")
58Response.Write("<TD>")
59Response.Write(oRS.Fields("Sales_Amt").Value)
60Response.Write("</TD>")
61Response.Write("</TR>")
62oRS.MoveNext
63Loop
64Response.Write("</TABLE>")
65End if
66End Function
67
68Function CreateCSVFile()
69
70strFile = GenFileName()
71Set fs = Server.CreateObject("Scripting.FileSystemObject")
72Set a = fs.CreateTextFile(server.MapPath(".") & "\" & strFile & ".csv",True)
73If Not oRS.EOF Then
74strtext = chr(34) & "Year" & chr(34) & ","
75strtext = strtext & chr(34) & "Region" & chr(34) & ","
76strtext = strtext & chr(34) & "Sales" & chr(34) & ","
77a.WriteLine(strtext)
78Do Until oRS.EOF
79For i = 0 To oRS.fields.Count-1
80strtext = chr(34) & oRS.fields(i) & chr(34) & ","
81a.Write(strtext)
82Next
83a.Writeline()
84oRS.MoveNext
85Loop
86End If
87a.Close
88Set fs=Nothing
89Response.Write("Click <A HRef=" & strFile & ".csv>Here</A> to to get CSV file")
90End Function
91Function CreateXlsFile()
92Dim xlWorkSheet ' Excel Worksheet object
93Dim xlApplication
94
95Set xlApplication = CreateObject("Excel.application")
96xlApplication.Visible = False
97xlApplication.Workbooks.Add
98Set xlWorksheet = xlApplication.Worksheets(1)
99xlWorksheet.Cells(1,1).Value = "Year"
100xlWorksheet.Cells(1,1).Interior.ColorIndex = 5
101xlWorksheet.Cells(1,2).Value = "Region"
102xlWorksheet.Cells(1,2).Interior.ColorIndex = 5
103xlWorksheet.Cells(1,3).Value = "Sales"
104xlWorksheet.Cells(1,3).Interior.ColorIndex = 5
105
106iRow = 2
107If Not oRS.EOF Then
108Do Until oRS.EOF
109For i = 0 To oRS.fields.Count-1
110xlWorksheet.Cells(iRow,i + 1).Value = oRS.fields(i)
111xlWorkSheet.Cells(iRow,i + 1).Interior.ColorIndex = 4
112Next
113iRow = iRow + 1
114oRS.MoveNext
115Loop
116End If
117strFile = GenFileName()
118xlWorksheet.SaveAs Server.MapPath(".") & "\" & strFile & ".xls"
119xlApplication.Quit ' Close the Workbook
120Set xlWorksheet = Nothing
121Set xlApplication = Nothing
122Response.Write("Click <A HRef=" & strFile & ".xls>Here</A> to get XLS file")
123End Function
124</script>
%>
main.htm
1<frameset rows="20%,*">
2<frame frameborder="yes" marginheight="10" marginwidth="10" name="Request" scrolling="auto" src="request.html"/>
3<frame frameborder="yes" marginheight="10" marginwidth="10" name="Result" scrolling="auto" src="welcome.html"/>
4</frameset>
request.htm
1<html>
2<head>
3<title>Sales Report Demo</title>
4</head>
5<body>
6<div align="center"><font size="+1">Sales Reporting</font></div>
7<form action="runquery.asp" method="POST" target="Result">
8Year <select name="Year">
9<option value="ALL">ALL</option>
10<option value="1995">1995</option>
11<option value="1996">1996</option>
12<option value="1997">1997</option>
13<option value="1998">1998</option>
14<option value="1999">1999</option>
15</select>
16
17Region <select name="Region">
18<option value="ALL">ALL</option>
19<option value="North">North</option>
20<option value="East">East</option>
21<option value="South">South</option>
22<option value="West">West</option>
23</select>
24
25Return Results Using
26<select name="ReturnAS">
27<option value="HTML">HTML Table</option>
28<option value="Content">Content Type</option>
29<option value="CSV">CSV</option>
30<option value="Excel">Native Excel</option>
31</select>
32
33
34<input name="Submit" type="Submit" value="Submit"/>
35</form>
36</body>
37</html>
welcome.htm
1<html>
2<head>
3<title>Sales Report Demo</title>
4</head>
5<body>
6</body>
7</html>
数据库结构
testDB.Mdb
表sales
year 数字
Region 文本
Sales_Amt 货币
本文原始出处为国外一网站，并经过BATMAN的休正。