利用ASP制作EXECL报表方法（二）
日期：2013-04-25 浏览次数：20860 次
废话少说，请看代码：
runquery.asp
<%@ LANGUAGE="VBSCRIPT" %>
<%
'DSNless connection to Access Database
strDSNPath = "PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("testDB.mdb")
%>
<!--#include file="adovbs.inc" --> 请自己COPY这个文件
<%
server.scripttimeout=1000
Response.Buffer = True
if(Request.Form("ReturnAS") = "Content") then
Response.ContentType = "application/msexcel"
end if
Response.Expires = 0
dim oConn
dim oRS
dim strSQL
dim strFile
Set oConn = Server.CreateObject("ADODB.Connection")
Set oRS = Server.CreateObject("ADODB.Recordset")
strSQL = BuildSQL()
oRS.Open strSQL, strDSNPath, adOpenForwardOnly, adLockReadOnly, adCmdText
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Excel Export Demo</title>
</head>
<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
%>
</body>
</html>
<SCRIPT LANGUAGE=vbscript RUNAT=Server>
Function BuildSQL()
dim strSQL
dim strTemp
strTemp = ""
strSQL = "select year, region, sales_amt from sales"
if(Request.Form("Year") <> "ALL") then
strTemp = " where Year = "
strTemp = strTemp & Request.Form("Year")
end if
if(Request.Form("Region") <> "ALL") then
if(Len(strTemp) > 0) then
strTemp = strTemp & " and Region = "
else
strTemp = strSTL & " where Region = "
end if
strTemp = strTemp & "'"
strTemp = strTemp & Request.Form("Region")
strTemp = strTemp & "'"
end if
BuildSQL = strSQL & strTemp
End Function
Function GenFileName()
dim fname
fname = "File"
systime=now()
fname= fname & cstr(year(systime)) & cstr(month(systime)) & cstr(day(systime))
fname= fname & cstr(hour(systime)) & cstr(minute(systime)) & cstr(second(systime))
GenFileName = fname
End Function
Function GenHTML()
Response.Write("<DIV ALIGN=center><FONT SIZE=+1>Sales Reporting</FONT></DIV>")
Response.Write("<TABLE WIDTH=100% BORDER=1 CELLSPACING=1 CELLPADDING=1>")
Response.Write("<TR>")
Response.Write(" <TD>Year</TD>")
Response.Write(" <TD>Region</TD>")
Response.Write(" <TD>Sales</TD>")
Response.Write("</TR>")
if(oRS.BOF = True and oRS.EOF = True) then
Response.Write("Database Empty")
else
oRS.MoveFirst
Do While Not oRS.EOF
Response.Write("<TR>")
Response.Write("<TD>")
Response.Write(oRS.Fields("Year").Value)
Response.Write("</TD>")
Response.Write("<TD>")
Response.Write(oRS.Fields("Region").Value)
Response.Write("</TD>")
Response.Write("<TD>")
Response.Write(oRS.Fields("Sales_Amt").Value)
Response.Write("</TD>")
Response.Write("</TR>")
oRS.MoveNext
Loop
Response.Write("</TABL
runquery.asp
<%@ LANGUAGE="VBSCRIPT" %>
<%
'DSNless connection to Access Database
strDSNPath = "PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("testDB.mdb")
%>
<!--#include file="adovbs.inc" --> 请自己COPY这个文件
<%
server.scripttimeout=1000
Response.Buffer = True
if(Request.Form("ReturnAS") = "Content") then
Response.ContentType = "application/msexcel"
end if
Response.Expires = 0
dim oConn
dim oRS
dim strSQL
dim strFile
Set oConn = Server.CreateObject("ADODB.Connection")
Set oRS = Server.CreateObject("ADODB.Recordset")
strSQL = BuildSQL()
oRS.Open strSQL, strDSNPath, adOpenForwardOnly, adLockReadOnly, adCmdText
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Excel Export Demo</title>
</head>
<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
%>
</body>
</html>
<SCRIPT LANGUAGE=vbscript RUNAT=Server>
Function BuildSQL()
dim strSQL
dim strTemp
strTemp = ""
strSQL = "select year, region, sales_amt from sales"
if(Request.Form("Year") <> "ALL") then
strTemp = " where Year = "
strTemp = strTemp & Request.Form("Year")
end if
if(Request.Form("Region") <> "ALL") then
if(Len(strTemp) > 0) then
strTemp = strTemp & " and Region = "
else
strTemp = strSTL & " where Region = "
end if
strTemp = strTemp & "'"
strTemp = strTemp & Request.Form("Region")
strTemp = strTemp & "'"
end if
BuildSQL = strSQL & strTemp
End Function
Function GenFileName()
dim fname
fname = "File"
systime=now()
fname= fname & cstr(year(systime)) & cstr(month(systime)) & cstr(day(systime))
fname= fname & cstr(hour(systime)) & cstr(minute(systime)) & cstr(second(systime))
GenFileName = fname
End Function
Function GenHTML()
Response.Write("<DIV ALIGN=center><FONT SIZE=+1>Sales Reporting</FONT></DIV>")
Response.Write("<TABLE WIDTH=100% BORDER=1 CELLSPACING=1 CELLPADDING=1>")
Response.Write("<TR>")
Response.Write(" <TD>Year</TD>")
Response.Write(" <TD>Region</TD>")
Response.Write(" <TD>Sales</TD>")
Response.Write("</TR>")
if(oRS.BOF = True and oRS.EOF = True) then
Response.Write("Database Empty")
else
oRS.MoveFirst
Do While Not oRS.EOF
Response.Write("<TR>")
Response.Write("<TD>")
Response.Write(oRS.Fields("Year").Value)
Response.Write("</TD>")
Response.Write("<TD>")
Response.Write(oRS.Fields("Region").Value)
Response.Write("</TD>")
Response.Write("<TD>")
Response.Write(oRS.Fields("Sales_Amt").Value)
Response.Write("</TD>")
Response.Write("</TR>")
oRS.MoveNext
Loop
Response.Write("</TABL
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
