將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 | '--創(chuàng)建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格式