將asp中把數據導出為excel
時間：
張偉670由 分享
我們在做項目的時候經常要將數據庫的數據導出到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 | 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 | %> |
將asp中數據導出為excel方法二：用excel組件
| 1 | < % |
| 2 | set rs=server.createobject("adodb.recordset") |
| 3 | sql="select * from mkusers" |
| 4 | rs.open sql,objconn,1,1 |
| 5 | Set ExcelApp =CreateObject("Excel.Application") |
| 6 |
ExcelApp.Application.Visible = True |
| 7 | Set ExcelBook = 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 | Set ExcelApp = Nothing '注銷Excel對象 |
| 25 | %>< % |
| 26 | set rs=server.createobject("adodb.recordset") |
| 27 | sql="select * from mkusers" |
| 28 | rs.open sql,objconn,1,1 |
| 29 | Set ExcelApp =CreateObject("Excel.Application") |
| 30 | ExcelApp.Application.Visible = True |
| 31 | Set ExcelBook = 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 | Set ExcelApp = Nothing '注銷Excel對象 |
| 49 |
%> |
猜你喜歡：
將asp中把數據導出為excel
我們在做項目的時候經常要將數據庫的數據導出到excel中，很多asp用戶并不知道怎么寫,下面小編告訴你們兩個能將asp導入excel的方法，希望對你有幫助！ 將asp中數據導出為excel方法一：使用文件組件 1 % 2 dim s,sql,filename,fs,myfile,x
推薦度：
點擊下載文檔文檔為doc格式
