asp开发学校OA项目中常用到知识点（5）数据导出

以下代码为数据导出为EXCEL表格格式的代码
<% on error resume next'如果有错误继续执行下面的代码

  dim grade,fb
    grade = request.querystring("grade")
    fb = request.querystring("fb")

Server.ScriptTimeOut=360000'防止超时
set conn = server.CreateObject("ADODB.connection")
conn.connectionstring = "driver={SQL Server}; server=192.168.100.207;uid=sa;pwd=szweb05;database=shizhu_2009"
conn.open        
set rs=server.createobject("adodb.recordset")             
sql="select * from student where grade = '"&grade&"' and fb = '"&fb&"' "'根据此SQL语句导出至Excel
rs.Open sql,conn,3,3
for Createtablei=0 to rs.Fields.Count-1
Createtable=Createtable&rs.fields(Createtablei).name&" text ,"
next
Createtablesql="Create table Sheet1("&left(Createtable,len(Createtable)-1)&")"
ExcelFile="Excel.xls"
set fso=Server.CreateObject ("Scripting.FileSystemObject")
fpath=Server.MapPath(ExcelFile)      
if fso.FileExists(fpath) then
whichfile=Server.MapPath(ExcelFile)
                  Set fs = CreateObject("Scripting.FileSystemObject")
                  Set thisfile = fs.GetFile(whichfile)
                  thisfile.delete true
dim excelfile,tbname
end if                   
Dim Driver,DBPath
Set conn = Server.CreateObject("ADODB.Connection")
Driver = "Driver={Microsoft Excel Driver (*.xls)};Readonly=0;"
DBPath = "DBQ=" & Server.MapPath(excelfile)
conn.Open Driver & DBPath
conn.Execute(Createtablesql)
for ii=0 to rs.recordcount-1
for i=0 to rs.Fields.Count-1
Inserttablename=Inserttablename&rs.fields(i).name&","
Inserttable=Inserttable&"'"&Rs(i)&"',"
Next
Insertintosql="Insert into Sheet1("&left(Inserttablename,len(Inserttablename)-1)&")values("&left(Inserttable,len(Inserttable)-1)&")"
'显示错误信息开始
if err.number<>0 then
response.write "






"
response.write " "
response.write "





"
response.end
end if
'显示错误信息结束
conn.Execute(Insertintosql)
'显示错误信息开始
if err.number<>0 then
response.write "






"
response.write " "
response.write "





"
response.end
end if
'显示错误信息结束
Insertintosql =""
Inserttable=""
Inserttablename=""
rs.MoveNext
Next
'显示错误信息开始
if err.number<>0 then
response.write "






"
response.write " "
response.write "





"
response.end
end if
'显示错误信息结束
Response.Redirect (ExcelFile)
%>

上一篇: CSS 图片重叠
下一篇: asp开发学校OA项目中常用到知识点（6）邮件通知
文章来自: 本站原创
引用通告: 查看所有引用 | 我要引用此文章
Tags:
相关日志: