asp中把數(shù)據(jù)導(dǎo)出為excel的2種方法

我們?cè)谧鲰?xiàng)目的時(shí)候經(jīng)常要將數(shù)據(jù)庫(kù)的數(shù)據(jù)導(dǎo)出到excel中，很多asp用戶并不知道怎么寫。

這里明凱總結(jié)了兩種方法來導(dǎo)出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 = "用戶編號(hào)" 
ExcelBook.WorkSheets(1).cells(2,2).value = "登陸名" 
ExcelBook.WorkSheets(1).cells(2,3).value = "真實(shí)姓名" 
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"  '這個(gè)是數(shù)據(jù)導(dǎo)出完畢以后在D盤存成文件
ExcelApp.Application.Quit   '導(dǎo)出以后退出Excel
Set ExcelApp = Nothing   '注銷Excel對(duì)象
%> %
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 = "用戶編號(hào)" 
ExcelBook.WorkSheets(1).cells(2,2).value = "登陸名" 
ExcelBook.WorkSheets(1).cells(2,3).value = "真實(shí)姓名" 
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"  '這個(gè)是數(shù)據(jù)導(dǎo)出完畢以后在D盤存成文件
ExcelApp.Application.Quit   '導(dǎo)出以后退出Excel
Set ExcelApp = Nothing   '注銷Excel對(duì)象
%>

方法二：使用文件組件

 % 
dim s,sql,filename,fs,myfile,x 
 
Set fs = server.CreateObject("scripting.filesystemobject") 
'--假設(shè)你想讓生成的EXCEL文件做如下的存放 
filename = Server.MapPath("order.xls") 
'--如果原來的EXCEL文件存在的話刪除它 
if fs.FileExists(filename) then 
   fs.DeleteFile(filename) 
end  if 
'--創(chuàng)建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文件成功，點(diǎn)擊a href="/" rel="external nofollow" order.xls"" target=""_blank"">下載！"
rstData.Close 
set rstData = nothing
Conn.Close
Set Conn = nothing
%>

可以看出，第一種方法是直接導(dǎo)出的是excel文件，而第二張方法是到處的是文本文件，只不過后綴名改成了xls。

然后看起來就是excel了。

經(jīng)過對(duì)比第一種方法的效率沒有第二種方法的效率高，而且也不好控制。

推薦大家使用第二種寫文件的方法來進(jìn)行excel操作。