一聚教程网:一个值得你收藏的教程网站

热门教程

将SQL查询结果导入到EXCEL

时间:2022-06-29 14:26:47 编辑:袖梨 来源:一聚教程网

<%
databasename="db.mdb"
apath=server.mappath(".") & "/xmllover.xls"

set conn=server.createobject("adodb.connection")
ConStr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(databasename)
conn.open ConStr
set rs=server.createobject("adodb.recordset")
'顺序排列
sql="select * from employees order by employeeid asc"
'倒序排列
'sql="select * from employees order by employeeid desc"
rs.open sql,conn
   msg="编号" & chr(9) & "名字" & vbcrlf
do while not rs.eof
   msg=msg & rs("employeeid") & chr(9) & rs("lastname") & vbcrlf
   rs.movenext
loop
set f=server.createobject("scripting.filesystemobject")
set myfile=f.createtextfile(apath,true)
myfile.writeline msg
myfile.close
rs.close
set rs=nothing
%>

 

还有一种方法就是直接输出成csv格式的文件,再用response.进行输出,

Sub Csv()
   Response.Buffer = true
   Response.AddHeader "Content-Disposition","attachment; filename=member" &FormatDateTime(Date,2)& ".csv"
   Response.CharSet = "utf-8"
   Response.ContentType = "application/octet-stream"
   '此处添加表格列名输出代码
   Response.Flush()
   '此处添加数据输出代码
   Response.Flush()
End Sub

再来看一个生成excel文档的asp教程实例

<%dim excelstring
dim excelstr
dim excelstr2
dim head
excelstring = ""
head = "Sn" & "," & "Reason" & "," & "Empid" & "," & "Empname" & "," & "Ext" & "," & "Depname"
excel="creat"
If excel<>"" Then
     Set rs2 = Server.CreateObject ("ADODB.Recordset")
     sql2 = "Select * from value_table "
     rs2.open sql2,conn,1,3
     do while not rs2.eof
     excelstr =" " & rs2(0) & """,""" & rs2(1) & """,""" & rs2(2) & """,""" & rs2(3) & """,""" & rs2(4) & """,""" & rs2(5) & """"
     excelstr2 = excelstr2 & Chr(13) & excelstr
     rs2.movenext
     loop
     excelstring = head &Chr(13)& excelstr2
     'response.Write(excelstring)
     'rs2.close
     Set rs2=Nothing
     set fs = CreateObject("scripting.FileSystemObject")
     set exc = fs.OpenTextFile(server.MapPath("excel.csv"),2,True)
     exc.write(excelstring)
     if fs.FileExists(Server.MapPath("excel.csv")) then
         Response.Write("报表已经生成,点击查看")
         'Response.write(excelstring)
     else
         Response.Write("生成报表失败!")
     end if
End if%>

热门栏目