快精灵印艺坊 您身边的文印专家 广州名片 深圳名片 会员卡 贵宾卡 印刷 设计教程
产品展示 在线订购 会员中心 产品模板 设计指南 在线编辑
 首页 名片设计   CorelDRAW   Illustrator   AuotoCAD   Painter   其他软件   Photoshop   Fireworks   Flash  

 » 彩色名片
 » PVC卡
 » 彩色磁性卡
 » 彩页/画册
 » 个性印务
 » 彩色不干胶
 » 明信片
   » 明信片
   » 彩色书签
   » 门挂
 » 其他产品与服务
   » 创业锦囊
   » 办公用品
     » 信封、信纸
     » 便签纸、斜面纸砖
     » 无碳复印纸
   » 海报
   » 大篇幅印刷
     » KT板
     » 海报
     » 横幅

利用ASP制作EXECL报表方式(二)

废话少说，请看代码：
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("</TABLE>")
End if
End Function

Function CreateCSVFile()

strFile = GenFileName()
Set fs = Server.CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile(server.MapPath(".") & "" & strFile & ".csv",True)
If Not oRS.EOF Then
strtext = chr(34) & "Year" & chr(34) & ","
strtext = strtext & chr(34) & "Region" & chr(34) & ","
strtext = strtext & chr(34) & "Sales" & chr(34) & ","
a.WriteLine(strtext)
Do Until oRS.EOF
For i = 0 To oRS.fields.Count-1
strtext = chr(34) & oRS.fields(i) & chr(34) & ","
a.Write(strtext)
Next
a.Writeline()
oRS.MoveNext
Loop
End If
a.Close
Set fs=Nothing
Response.Write("Click <A HRef=" & strFile & ".csv>Here</A> to to get CSV file")
End Function
Function CreateXlsFile()
Dim xlWorkSheet \\\' Excel Worksheet object
Dim xlApplication

Set xlApplication = CreateObject("Excel.application")
xlApplication.Visible = False
xlApplication.Workbooks.Add
Set xlWorksheet = xlApplication.Worksheets(1)
xlWorksheet.Cells(1,1).Value = "Year"
xlWorksheet.Cells(1,1).Interior.ColorIndex = 5
xlWorksheet.Cells(1,2).Value = "Region"
xlWorksheet.Cells(1,2).Interior.ColorIndex = 5
xlWorksheet.Cells(1,3).Value = "Sales"
xlWorksheet.Cells(1,3).Interior.ColorIndex = 5

iRow = 2
If Not oRS.EOF Then
Do Until oRS.EOF
For i = 0 To oRS.fields.Count-1
xlWorksheet.Cells(iRow,i + 1).Value = oRS.fields(i)
xlWorkSheet.Cells(iRow,i + 1).Interior.ColorIndex = 4
Next
iRow = iRow + 1
oRS.MoveNext
Loop
End If
strFile = GenFileName()
xlWorksheet.SaveAs Server.MapPath(".") & "" & strFile & ".xls"
xlApplication.Quit \\\' Close the Workbook
Set xlWorksheet = Nothing
Set xlApplication = Nothing
Response.Write("Click <A HRef=" & strFile & ".xls>Here</A> to get XLS file")
End Function
</script>
%>

main.htm

<!-- frames -->
<FRAMESET ROWS="20%,*">
<FRAME NAME="Request" SRC="request.html" MARGINWIDTH="10" MARGINHEIGHT="10" SCROLLING="auto" FRAMEBORDER="yes">
<FRAME NAME="Result" SRC="welcome.html" MARGINWIDTH="10" MARGINHEIGHT="10" SCROLLING="auto" FRAMEBORDER="yes">
</FRAMESET>

request.htm

<html>
<head>
<title>Sales Report Demo</title>
</head>

<body>

<DIV ALIGN="center"><FONT SIZE="+1">Sales Reporting</FONT></DIV>
<FORM ACTION="runquery.asp" METHOD="POST" target=Result>
Year <SELECT NAME="Year">
<OPTION VALUE="ALL">ALL</OPTION>
<OPTION VALUE="1995">1995</OPTION>
<OPTION VALUE="1996">1996</OPTION>
<OPTION VALUE="1997">1997</OPTION>
<OPTION VALUE="1998">1998</OPTION>
<OPTION VALUE="1999">1999</OPTION>
</SELECT>
&nbsp;
Region <SELECT NAME="Region">
<OPTION VALUE="ALL">ALL</OPTION>
<OPTION VALUE="North">North</OPTION>
<OPTION VALUE="East">East</OPTION>
<OPTION VALUE="South">South</OPTION>
<OPTION VALUE="West">West</OPTION>
</SELECT>
&nbsp;
Return Results Using
<SELECT NAME="ReturnAS">
<OPTION VALUE="HTML">HTML Table</OPTION>
<OPTION VALUE="Content">Content Type</OPTION>
<OPTION VALUE="CSV">CSV</OPTION>
<OPTION VALUE="Excel">Native Excel</OPTION>
</SELECT>
&nbsp;
&nbsp;
<INPUT TYPE="Submit" NAME="Submit" VALUE="Submit">
</FORM>
</body>
</html>

welcome.htm
<html>
<head>
<title>Sales Report Demo</title>
</head>

<body>



</body>
</html>

数据库结构
testDB.Mdb
表sales
year 数字
Region 文本
Sales_Amt 货币
返回类别: 教程
上一教程: 用ASP执行DTC
下一教程: 用纯ASP代码实现图片上传并存入数据库中(一)

您可以阅读与"利用ASP制作EXECL报表方式(二)"相关的教程:
· 利用ASP制作EXECL报表方式(一)
· 利用ASP和ACCESS数据库制作局域网网上答题系统
· 利用ASP生成EXECL文档
· 使用INSTALLSHIELD制作ASP安装程序(1)
· 词语搭配游戏的制作(ASP)二
    微笑服务 优质保证 索取样品  
  快精灵印艺坊 版权所有 首页会员中心在线印刷在线编辑付款方式索取样品设计指南连锁门店
网站地图关于我们友情链接