asp中把資料匯出為excel的2種方法_ASP編程

我們在做項目的時候經常要將資料庫的資料匯出到excel中，很多asp使用者並不知道怎麼寫。

這裡明凱總結了兩種方法來匯出excel，希望能幫到大家。

方法一：用excel組件

< %set rs=server.createobject("adodb.recordset")sql="select * from mkusers"rs.open sql,objconn,1,1Set ExcelApp =CreateObject("Excel.Application")ExcelApp.Application.Visible = TrueSet ExcelBook = ExcelApp.Workbooks.AddExcelBook.WorkSheets(1).cells(1,1).value ="使用者表" ExcelBook.WorkSheets(1).cells(2,1).value = "使用者編號" ExcelBook.WorkSheets(1).cells(2,2).value = "登陸名" ExcelBook.WorkSheets(1).cells(2,3).value = "真實姓名" 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"  '這個是資料匯出完畢以後在D盤存成檔案ExcelApp.Application.Quit   '匯出以後退出ExcelSet ExcelApp = Nothing   '登出Excel對象%>< %set rs=server.createobject("adodb.recordset")sql="select * from mkusers"rs.open sql,objconn,1,1Set ExcelApp =CreateObject("Excel.Application")ExcelApp.Application.Visible = TrueSet ExcelBook = ExcelApp.Workbooks.AddExcelBook.WorkSheets(1).cells(1,1).value ="使用者表" ExcelBook.WorkSheets(1).cells(2,1).value = "使用者編號" ExcelBook.WorkSheets(1).cells(2,2).value = "登陸名" ExcelBook.WorkSheets(1).cells(2,3).value = "真實姓名" 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"  '這個是資料匯出完畢以後在D盤存成檔案ExcelApp.Application.Quit   '匯出以後退出ExcelSet ExcelApp = Nothing   '登出Excel對象%>

方法二：使用檔案組件

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

可以看出，第一種方法是直接匯出的是excel檔案，而第二張方法是到處的是文字檔，只不過尾碼名改成了xls。

然後看起來就是excel了。

經過對比第一種方法的效率沒有第二種方法的效率高，而且也不好控制。

推薦大家使用第二種寫檔案的方法來進行excel操作。