asp导出数据库表为excel

网上答案很多的啦,基本都是那两种方法,现在贴出其中一种asp导出数据库为excel的详细代码以作备忘(里面整合了一些其他多余的部分,懒得精简了)。这个导出页面其实没必要显示内容,直接后台调用就行啦!另外一种方法好像是直接写txt文件不过后缀名是.xls而已,有人说后面这种方法效率高,但是实际我在测试的时候发现,可能会导出后导致文本显示不正常,比如18位的身份证如果是全数字的话,asp导出的excel表里的内容就变成3.12105E+17类似的形式,目前还没有找到解决方法,所以最后还是选择了下面这种导出方式,只不过里面的一些东西需要预先设定,肯定可以做成全自动导表的,时间有限还是先将就用着吧!

<%
set conn=server.createobject("adodb.connection")
conn.open "driver={microsoft access driver (*.mdb)};dbq="&server.mappath("test.mdb")
%>
<% 

year1=year(now())  '时间
mouth1=month(now())
d1=day(now())
T1=hour(now())
m1=minute(now())
s1=second(now())
'timenow=year1&"年"&mouth1&"月"&d1&"日"&t1&"时"&m1&"分"&s1&"秒"
timenow=year1&"年"&mouth1&"月"&d1&"日"&t1&"时"&m1&"分"&s1&"秒"

dim filename,fs,fn,table,time
table="lingjiang"
time=timenow
fn=table&"_"&time&"_back_up.xls"
Set fs = server.CreateObject("scripting.filesystemobject")  
'filename = Server.MapPath("back_up.xls") 
filename = Server.MapPath("./"&fn) 
'--如果原来的EXCEL文件存在的话删除它 
if fs.FileExists(filename) then 
fs.DeleteFile(filename) 
end if 


set rs=server.createobject("adodb.recordset") 

sql="select * from sutdent" 
'要导出的是test.mdb数据库中的student表
rs.open sql,conn,1,1 
Set ExcelApp =CreateObject("Excel.Application") 
ExcelApp.Application.Visible = True 
Set ExcelBook = ExcelApp.Workbooks.Add 
ExcelBook.WorkSheets(1).cells(1,1).value ="student表" 

ExcelBook.WorkSheets(1).cells(2,1).value ="序列号"   

ExcelBook.WorkSheets(1).cells(2,2).value = "姓名"  

ExcelBook.WorkSheets(1).cells(2,3).value = "身份证"  

ExcelBook.WorkSheets(1).cells(2,4).value = "学生卡号卡"  

ExcelBook.WorkSheets(1).cells(2,5).value = "状态" 

ExcelBook.WorkSheets(1).cells(2,6).value = "时间"  

ExcelBook.WorkSheets(1).cells(2,7).value = "手机号"   

cnt =3  

do while not rs.eof  

ExcelBook.WorkSheets(1).cells(cnt,1).value = rs("iden")  

ExcelBook.WorkSheets(1).cells(cnt,2).value = rs("name")  

ExcelBook.WorkSheets(1).cells(cnt,3).value = rs("id_card")  

ExcelBook.WorkSheets(1).cells(cnt,4).value = rs("studentno") 
ExcelBook.WorkSheets(1).cells(cnt,5).value = rs("state")
ExcelBook.WorkSheets(1).cells(cnt,6).value = rs("dtime")
ExcelBook.WorkSheets(1).cells(cnt,7).value = rs("phone")

rs.movenext  
cnt = cint(cnt) + 1  
loop  
rs.close
set rs=nothing

Excelbook.SaveAs filename   '这个是数据导出完毕以后存在filename处的 

ExcelApp.Application.Quit     '导出以后退出Excel 

Set ExcelApp = Nothing     '注销Excel对象 


response.write "<center>已导出为:"&filename &"<br></center>"
Response.Write "<BR><BR><center><b>导出成功,请选择继续操作</b></center>"
response.Write "<table width=90% cellspacing=1 cellpadding=3 align=center>"
Response.Write "<tr align=center> <td>"
response.write ("<font color=green>√</font><a href='"&fn&"'>下载</a>") & "  <font color=green>√</font><a href=javascript:window.close()>关闭</a>"
Response.Write "</td></tr></table>"

%>

 

基于互联网精神,在注明出处的前提下本站文章可自由转载!

本文链接:https://ranjuan.cn/asp导出数据库表为excel/

赞赏

微信赞赏支付宝赞赏

python-upload-pic2php
android-qrcode-error4system-language
asp过滤字符串