最新下载
热门教程
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
将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%>