将asp中把数据导出为excel【通用两篇】
我们在做项目的时候经常要将数据库的数据导出到excel中，很多asp用户并不知道怎么写,下面小编告诉你们两个能将asp导入excel的方法，希望对你有帮助！
将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 | %> |
将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 | DowhileNotrstData.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 | %> |