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
%>