怎么把查询数据库的结果生成Excel文件--高分,急急急!!!

[kangjial]

因为服务器上的数据太多,想做备份,
所以写个程序,把服务器上数据库的东西查询出来,备份到本地Excel上,,
请高手大大给个思路或者代码,,,
小弟这感激不尽,,!!!

[hookee]

最简单点就是用rs.getString()方法 一句生成csv格式的文本输出到客户端
或者用第三方组件 http://officewriter.softartisans.com/officewriter-371.aspx
复杂点的可以写个服务做负责生成excel，通过msmq异步请求生成
服务可调用存储过程，存储过程中可使用bcp

	SET NOCOUNT ON;
	EXEC master.dbo.sp_configure 'show advanced options', 1;
	RECONFIGURE;
	EXEC master.dbo.sp_configure 'xp_cmdshell', 1;
	RECONFIGURE;
	EXECUTE('EXEC master..xp_cmdshell ''bcp "SELECT * FROM [db]..[tal]" queryout "' + @Filename + '.xls" -c -T''');
	EXEC master.dbo.sp_configure 'xp_cmdshell', 0;
	RECONFIGURE;

[kangjial]

我的意思是,把数据查询出来,生成文件,,然后下载到本地,有没有办法实现, ?

[toury]

[Quote=引用 5 楼 kangjial 的回复:]
我的意思是,把数据查询出来,生成文件,,然后下载到本地,有没有办法实现, ?
[/Quote]
本地也放个相同结构的库，复制过来就是了

[lzp4881]

<!--#include file="conn.asp" -->

<%
Dim sql : sql = "Select * from table where ..."

Dim xmlStr
xmlStr = "<table border='1' cellspacing='0' cellpadding='0'>"
xmlStr = xmlStr & "<tr><th colspan='10'>申报单位明细表</th></tr>"
xmlStr = xmlStr & "<tr>"
xmlStr = xmlStr & "<td>序号</td><td>单位名称</td><td>单位地址</td><td>负责人</td><td>职工总数</td><td>联系人</td><td>联系电话</td><td>归口部门</td><td>单位类别</td><td>审核状态</td>"
xmlStr = xmlStr & "</tr>"

OpenConn()
Dim rs : Set rs = conn.execute(sql)
dim i : i=1
do while not rs.eof
xmlStr = xmlStr & "<tr>"
xmlStr = xmlStr & "<td>" & i & "</td>"
xmlStr = xmlStr & "<td>" & rs("companyName") & "</td>"
xmlStr = xmlStr & "<td>" & rs("Address") & "</td>"
xmlStr = xmlStr & "<td>" & rs("Corporation") & "</td>"
xmlStr = xmlStr & "<td>" & rs("Personnel") & "</td>"
xmlStr = xmlStr & "<td>" & rs("receiver") & "</td>"
xmlStr = xmlStr & "<td>" & rs("Phone")& "</td>"
xmlStr = xmlStr & "<td>" & rs("abbreviateName") & "</td>"
xmlStr = xmlStr & "<td>" & rs("kind")& "</td>"
xmlStr = xmlStr & "<td>" & iif(rs("LockUser")=0,"未审","已审") & "</td>"
xmlStr = xmlStr & "</tr>" &vbCrLf
rs.movenext
i=i+1
loop
Co(rs) : CloseConn()
xmlStr = xmlStr & "</table>"

Response.ContentType="application/vnd.ms-excel"
Response.AddHeader "content-disposition","attachment;filename=sbdwUser.xls"

Response.Write xmlStr
%>

[kangjial]

请高手门,帮帮忙,,,谢谢了,

[toury]

什么数据库？为什么一定要导出到EXCEL保存呢？要知道，那样既费时又不保险

[lzp4881]

你可以直接打开数据库导出啊

[litaoso]

我的空间有,有时间去我空间看吧.
http://hi.baidu.com/litaoso

[ER_JIN_ZHI]

Set fs = server.CreateObject("scripting.filesystemobject")
filename = Server.MapPath("新建 Microsoft Excel 工作表.xls")
strLine = strLine&"标题1"&chr(9)&"标题2"&chr(9)&"标题3"&chr(9)&"标题4"&chr(9)&"标题5"&chr(9)&"标题6"&chr(9)&"标题7"&chr(9)&"标题8"&chr(9)
'如果文件不存在就创建
if fs.FileExists(filename) then
else
Set fout = fs.CreateTextFile (filename,true)
fout.WriteLine strLine
fout.close
end if
'追加信息
set fout = fs.openTextFile(filename,8,true)
strLine = bianliang1&chr(9)&bianliang2&chr(9)&bianliang3&chr(9)&bianliang4&chr(9)&bianliang5&chr(9)&bianliang6&chr(9)&bianliang7&chr(9)&bianliang8&chr(9)
fout.Writeline strLine
fout.close
Set fs=Nothing

[半山闲人]

http://topic.csdn.net/u/20090205/20/0d37f824-3dd2-4b67-af5d-b138b3291002.html
请看15楼回复