ASP中把資料導出為Excel的三種方法

方法一：用excel元件

    這種方法利用Excel元件直接導出excel檔案，要求伺服器端安裝有微軟office（Excel）程式，否則無法運作。

    完整示例如下：

Set conn=server.CreateObject("adodb.connection")
	connstr="provider=microsoft.jet.oledb.4.0;data source="& Server.MapPath("/db.mdb")
	conn.open connstr	

	set rs=server.createobject("adodb.recordset")
	sql="select * from xiaozu"
	rs.open sql,conn,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(1,2).value = "學生名單"
	ExcelBook.WorkSheets(1).cells(1,3).value = "所屬學院"
	ExcelBook.WorkSheets(1).cells(1,4).value = "實習景區"
	cnt = 2
	do while not rs.eof 
		ExcelBook.WorkSheets(1).cells(cnt,1).value = rs("XZName") 
		ExcelBook.WorkSheets(1).cells(cnt,2).value = rs("XZStudents") 
		ExcelBook.WorkSheets(1).cells(cnt,3).value = rs("XZCollage") 
		ExcelBook.WorkSheets(1).cells(cnt,4).value = rs("XZJD")
		rs.movenext
		cnt = cint(cnt) + 1 
	loop 
	Excelbook.SaveAs "d:\yourfile.xls"  '這個是資料導出完畢以後在D盤存成檔案
	ExcelApp.Application.Quit   '導出以後退出Excel
	Set ExcelApp = Nothing   '登出Excel對象
	rs.close
	set rs = nothing
	conn.close
	set conn = nothing
           

方法二：使用檔案元件

    這種方法導出的是文本檔案，隻不過字尾名改成了xls。

    完整示例如下：

Set conn=server.CreateObject("adodb.connection")
	connstr="provider=microsoft.jet.oledb.4.0;data source="& Server.MapPath("/db.mdb")
	conn.open connstr	

	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 xiaozu "
	Set rstData =conn.execute(strSql)
	if not rstData.EOF and not rstData.BOF then 
	   dim  strLine,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='order.xls' target='_blank'>下載下傳！"
	rstData.Close 
	set rstData = nothing
	Conn.Close
	Set Conn = nothing
           

方法三：

    該方法不使用任何元件。而是将所有導出的資料在網頁中以Table進行顯示，然後增加如下兩行代碼，即可實作打開網頁後直接下載下傳儲存為Excel：

    Response.ContentType = "application/vnd.ms-excel"

    Response.AddHeader "Content-Disposition", "attachment;filename=TestExcel.xls"

    完整示例如下：

<%
	Response.ContentType = "application/vnd.ms-excel"
	Response.AddHeader "Content-Disposition", "attachment;filename=TestExcel.xls"

	Set conn=server.CreateObject("adodb.connection")
	connstr="provider=microsoft.jet.oledb.4.0;data source="& Server.MapPath("/db.mdb")
	conn.open connstr	

	set rs=server.createobject("adodb.recordset")
	sql="select * from xiaozu"
	rs.open sql,conn,1,1
%>
<table >
	<tr>
    	<td>小組名稱</td>
        <td>學生名單</td>
        <td>所屬學院</td>
        <td>實習景區</td>
    </tr>
<%
	while not rs.eof and not rs.bof
%>    
	<tr>
    	<td><%=rs("XZName")%></td>
        <td><%=rs("XZStudents")%></td>
        <td><%=rs("XZCollage")%></td>
        <td><%=rs("XZJD")%></td>
    </tr>
<%		rs.movenext
	wend
%>
</table>
<%	
	rs.close
	set rs = nothing
	conn.close
	set conn = nothing
%>