如何将asp中把数据导出为excel
相关话题
我们在做项目的时候经常要将数据库的数据导出到excel中，很多asp用户并不知道怎么写,下面小编告诉你们两个能将asp导入excel的方法
将asp中数据导出为excel方法一：使用文件组件
1
< %
2
dim s,sql,filename,fs,myfile,x
3
4
Setfs = server.CreateObject("scripting.filesystemobject")
5
'--假设你想让生成的EXCEL文件做如下的存放
6
filename = Server.MapPath("order.xls")
7
'--如果原来的EXCEL文件存在的话删除它
8
if fs.FileExists(filename) then
9
fs.DeleteFile(filename)
10
end if
11
'--创建EXCEL文件
12
set myfile = fs.CreateTextFile(filename,true)
13
14
15
StartTime = Request("StartTime")
16
EndTime = Request("EndTime")
17
StartEndTime = "AddTime between #"& StartTime &" 00:00:00# and #"& EndTime &" 23:59:59#"
18
strSql = "select * from mksuers "
19
SetrstData =conn.execute(strSql)
20
if not rstData.EOF and not rstData.BOF then
21
22
dim trLine,responsestr
23
strLine=""
24
Foreach x in rstData.fields
25
strLine = strLine & x.name & chr(9)
26
Next
27
28
'--将表的列名先写入EXCEL
29
myfile.writeline strLine
30
31
Dowhile###NotrstData.EOF
32
strLine=""
33
34
for each x in rstData.Fields
35
strLine = strLine & x.value & chr(9)
36
next
37
myfile.writeline strLine
38
39
rstData.MoveNext
40
loop
41
42
end if
43
Response.Write "生成EXCEL文件成功，点击<a href="/" rel="external nofollow" order.xls"" target=""_blank"">下载！"
44
rstData.Close
45
set rstData = nothing
46
Conn.Close
47
SetConn = nothing
48
%>
###将asp中数据导出为excel###方法二：用excel组件
1
< %
2
set rs=server.createobject(###"adodb.recordset###")
3
sql="select * from mkusers"
4
rs.open sql,objconn,1,1
5
SetExcelApp =CreateObject("Excel.Application")
6
ExcelApp.Application.Visible =###True
7
SetExcelBook = ExcelApp.Workbooks.Add
8
ExcelBook.WorkSheets(1).cells(1,1).value ="用户表"
9
ExcelBook.WorkSheets(1).cells(2,1).value = "用户编号"
10
ExcelBook.WorkSheets(1).cells(2,2).value = "登陆名"
11
ExcelBook.WorkSheets(1).cells(2,3).value = "真实姓名"
12
ExcelBook.WorkSheets(1).cells(2,4).value = "密码"
13
cnt =3
14
do while not rs.eof
15
ExcelBook.WorkSheets(1).cells(cnt,1).value = rs("provinceid")
16
ExcelBook.WorkSheets(1).cells(cnt,2).value = rs("province")
17
ExcelBook.WorkSheets(1).cells(cnt,3).value = rs("flag")
18
ExcelBook.WorkSheets(1).cells(cnt,4).value = rs("id")
19
rs.movenext
20
cnt = cint(cnt) + 1
21
loop
22
Excelbook.SaveAs "d:yourfile.xls" '这个是数据导出完毕以后在D盘存成文件
23
ExcelApp.Application.Quit '导出以后退出Excel
24
SetExcelApp =Nothing'注销Excel对象
25
%>< %
26
set rs=server.createobject("adodb.recordset")
27
sql="select * from mkusers"
28
rs.open sql,objconn,1,1
29
SetExcelApp =CreateObject("Excel.Application")
30
ExcelApp.Application.Visible =###True
31
SetExcelBook = ExcelApp.Workbooks.Add
32
ExcelBook.WorkSheets(1).cells(1,1).value ="用户表"
33
ExcelBook.WorkSheets(1).cells(2,1).value = "用户编号"
34
ExcelBook.WorkSheets(1).cells(2,2).value = "登陆名"
35
ExcelBook.WorkSheets(1).cells(2,3).value = "真实姓名"
36
ExcelBook.WorkSheets(1).cells(2,4).value = "密码"
37
cnt =3
38
do while not rs.eof
39
ExcelBook.WorkSheets(1).cells(cnt,1).value = rs("provinceid")
40
ExcelBook.WorkSheets(1).cells(cnt,2).value = rs("province")
41
ExcelBook.WorkSheets(1).cells(cnt,3).value = rs("flag")
42
ExcelBook.WorkSheets(1).cells(cnt,4).value = rs("id")
43
rs.movenext
44
cnt = cint(cnt) + 1
45
loop
46
Excelbook.SaveAs "d:yourfile.xls" '这个是数据导出完毕以后在D盘存成文件
47
ExcelApp.Application.Quit '导出以后退出Excel
48
SetExcelApp =Nothing'注销Excel对象
49
%>