我要评论我们在做项目的时候经常要将数据库的数据导出到excel中，很多asp用户并不知道怎么写。
这里明凯总结了两种方法来导出excel，希望能帮到大家。
方法一：用excel组件
< %
set rs=server.createobject("adodb.recordset")
sql="select * from mkusers"
rs.open sql,objconn,1,1
set excelapp =createobject("excel.application")
excelapp.application.visible = true
set excelbook = excelapp.workbooks.add
excelbook.worksheets(1).cells(1,1).value ="用户表"
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 = "密码"
cnt =3
do while not rs.eof
excelbook.worksheets(1).cells(cnt,1).value = rs("provinceid")
excelbook.worksheets(1).cells(cnt,2).value = rs("province")
excelbook.worksheets(1).cells(cnt,3).value = rs("flag")
excelbook.worksheets(1).cells(cnt,4).value = rs("id")
rs.movenext
cnt = cint(cnt) + 1
loop
excelbook.saveas "d:\yourfile.xls" '这个是数据导出完毕以后在d盘存成文件
excelapp.application.quit '导出以后退出excel
set excelapp = nothing '注销excel对象
%>< %
set rs=server.createobject("adodb.recordset")
sql="select * from mkusers"
rs.open sql,objconn,1,1
set excelapp =createobject("excel.application")
excelapp.application.visible = true
set excelbook = excelapp.workbooks.add
excelbook.worksheets(1).cells(1,1).value ="用户表"
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 = "密码"
cnt =3
do while not rs.eof
excelbook.worksheets(1).cells(cnt,1).value = rs("provinceid")
excelbook.worksheets(1).cells(cnt,2).value = rs("province")
excelbook.worksheets(1).cells(cnt,3).value = rs("flag")
excelbook.worksheets(1).cells(cnt,4).value = rs("id")
rs.movenext
cnt = cint(cnt) + 1
loop
excelbook.saveas "d:\yourfile.xls" '这个是数据导出完毕以后在d盘存成文件
excelapp.application.quit '导出以后退出excel
set excelapp = nothing '注销excel对象
%>
方法二：使用文件组件
< %
dim s,sql,filename,fs,myfile,x
set fs = server.createobject("scripting.filesystemobject")
'--假设你想让生成的excel文件做如下的存放
filename = server.mappath("order.xls")
'--如果原来的excel文件存在的话删除它
if fs.fileexists(filename) then
fs.deletefile(filename)
end if
'--创建excel文件
set myfile = fs.createtextfile(filename,true)
starttime = request("starttime")
endtime = request("endtime")
startendtime = "addtime between #"& starttime &" 00:00:00# and #"& endtime &" 23:59:59#"
strsql = "select * from mksuers "
set rstdata =conn.execute(strsql)
if not rstdata.eof and not rstdata.bof then
dim trline,responsestr
strline=""
for each x in rstdata.fields
strline = strline & x.name & chr(9)
next
'--将表的列名先写入excel
myfile.writeline strline
do while not rstdata.eof
strline=""
for each x in rstdata.fields
strline = strline & x.value & chr(9)
next
myfile.writeline strline
rstdata.movenext
loop
end if
response.write "生成excel文件成功，点击<a href="/" rel="external nofollow" order.xls"" target=""_blank"">下载！"
rstdata.close
set rstdata = nothing
conn.close
set conn = nothing
%>
可以看出，第一种方法是直接导出的是excel文件，而第二张方法是到处的是文本文件，只不过后缀名改成了xls。
然后看起来就是excel了。
经过对比第一种方法的效率没有第二种方法的效率高，而且也不好控制。
推荐大家使用第二种写文件的方法来进行excel操作。
您可能感兴趣的文章:
如您对本文有疑问或者有任何想说的，请 点击进行留言回复，万千网友为您解惑！
网友评论