如何把查询的结果导出到EXCEL中去啊？？

wave214 2003-10-21 08:45:43

我用ASP编程，查询数据库里的记录，现在要把对应的记录导出到EXCEL里面去，我请各位高手帮忙，给我个简单的方法，我时间紧。就是希望我把符合条件的记录查出来之后，有个按纽，一点就能导到一个EXCEL中去

...全文

150 5 打赏收藏转发到动态举报

写回复

用AI写文章

5 条回复

切换为时间正序

请发表友善的回复…

发表回复

qdubit 2003-10-21

打赏
举报

看看我做的一段代码吧：

dim rs,sql,filename,fs,myfile,x,link

Set fs = Server.CreateObject("Scripting.FileSystemObject")
filename = "D:\yc511\sale_sys\xls\货物流向统计报表.xls"

if fs.FileExists(filename) then
fs.DeleteFile(filename)
else
Response.Write ("找不到文件D:\yc511\sale_sys\xls\货物流向统计报表.xls")&"<br>"
end if
set myfile = fs.CreateTextFile(filename,TRUE)

Set rs = Server.CreateObject("ADODB.Recordset")
'--从数据库中把你想放到EXCEL中的数据查出来
sql0="select r_id,yd_bianma from sale_manage_xsxx_ydjxc where dy_bianma like '"&m_dy_bianma&"%' and pinzhong like '"&m_cp_bianma&"%' and jhrq between '"&m_begin_time&"' and '"&m_end_time&"'"
set rs_bm=conn.Execute(sql0)

if rs_bm.EOF and rs_bm.BOF then
'先写入该表的表名
dim strLine1,strLine2,strLine3,strLine4,strLine5
strLine1=""
strLine1= strLine1 & "" & chr(9) & "" & chr(9) & "" & chr(9) & "" & chr(9) & "货物流向统计报表" & chr(9)
myfile.writeline strLine1

'再写入该表的字段名
strLine2=""
strLine2= strLine2 & "省区" & chr(9) & "客户名称" & chr(9) & "客户性质" & chr(9) & "进货日期" & chr(9) & "品种和规格" & chr(9) & "进货数量" & chr(9) & "进货金额" & chr(9) & "负责代表" & chr(9) & "供货商业" & chr(9) & "供货商业性质" & chr(9) & "附件编号" & chr(9) & "填表时间" & chr(9)
'--将表的列名先写入EXCEL
myfile.writeline strLine2

'最后提示用户没有查询到记录，请用户重新输入查询条件进行查询或者结束查询
strLine5=""
strLine5= strLine5 & "" & chr(9)
myfile.writeline strLine5

strLine3=""
strLine3= strLine3 & "" & chr(9) & "" & chr(9) & "" & chr(9) & "" & chr(9) & "对不起，没有查到任何记录！" & chr(9)
myfile.writeline strLine3

strLine4=""
strLine4= strLine4 & "" & chr(9) & "" & chr(9) & "" & chr(9) & "" & chr(9) & "如果您想继续查询，请重新输入查询条件或者联系系统管理员！" & chr(9)
myfile.writeline strLine4

else

'先写入该表的表名
strLine1=""
strLine1= strLine1 & "" & chr(9) & "" & chr(9) & "" & chr(9) & "" & chr(9) & "货物流向统计报表" & chr(9)
myfile.writeline strLine1

'再写入该表的字段名
strLine2=""
'strLine2= strLine2 & "省区" & chr(9) & "客户名称" & chr(9) & "客户性质" & chr(9) & "发货日期" & chr(9) & "品种和规格" & chr(9) & "发货数量" & chr(9) & "发货金额" & chr(9) & "负责代表" & chr(9) & "供货商业" & chr(9) & "供货商业性质" & chr(9) & "附加编号" & chr(9) & "填表时间" & chr(9)
strLine2= strLine2 & "省区" & chr(9) & "客户名称" & chr(9) & "客户性质" & chr(9) & "进货日期" & chr(9) & "品种和规格" & chr(9) & "进货数量" & chr(9) & "进货金额" & chr(9) & "负责代表" & chr(9) & "供货商业" & chr(9) & "供货商业性质" & chr(9) & "附件编号" & chr(9) & "填表时间" & chr(9)
'--将表的列名先写入EXCEL
myfile.writeline strLine2

rs_bm.movefirst
do while not rs_bm.eof

yd_bianma=rs_bm("yd_bianma")
r_id=rs_bm("r_id")

sql="select b.dy_mingcheng,c.yd_mingcheng,e.khb_shuoming,a.jhrq,f.mc,a.jhsl,f.hyj,a.otc_daibiao,a.ghsy,a.fjbh,a.tbrq"&_
" from sale_manage_xsxx_ydjxc as a join "&_
"sale_base_diqu as b on b.dy_bianma=a.dy_bianma join "&_
"sale_compact_yd_dangan as c on c.yd_bianma=a.yd_bianma join "&_
"sale_base_khlx as e on e.khb_bianma=c.khb_bianma join "&_
"sale_base_chanpin as f on f.cp_bianma=a.pinzhong "&_
" where a.r_id='"&r_id&"'"
set rs=conn.Execute(sql)
if rs.EOF then
Response.Write "药店数据出现错误，请联系系统管理员：010-68435666-8199！"
Response.End
end if
sql="select a.yd_mingcheng,b.khb_shuoming from sale_compact_yd_dangan as a join sale_base_khlx as b on a.khb_bianma=b.khb_bianma where a.yd_bianma='"&yd_bianma&"'"
set rs_yd=conn.Execute(sql)
if not rs_yd.eof then
ydmc=rs_yd("yd_mingcheng")
khbsm=rs_yd("khb_shuoming")
end if
rs_yd.close

sql="select a.khb_shuoming from sale_base_khlx as a join sale_compact_kehu as b on b.khb_bianma=a.khb_bianma where b.kh_mingcheng='"&rs("ghsy")&"'"
set rs_kb=conn.Execute(sql)
if not rs_kb.eof then
syxz=rs_kb("khb_shuoming")
else
end if
rs_kb.close

jhje=Clng(rs("jhsl"))*Clng(rs("hyj"))

strLine=""
strLine= strLine & rs(0).value & chr(9) & ydmc & chr(9) & khbsm & chr(9) & rs(3).value & chr(9) & rs(4).value & chr(9) & rs(5).value & chr(9) & jhje & chr(9) & rs(7).value & chr(9) & rs(8).value & chr(9) & syxz & chr(9) & rs(9).value & chr(9) & rs(10).value & chr(9)
myfile.writeline strLine

rs_bm.MoveNext
loop
''在此处需要加上一条命令语句对字段值进行“自动筛选”

end if
'rs.close
rs_bm.Close
set rs_bm = nothing

lovehwq21 2003-10-21

打赏
举报

其实不难吧，你在那个按钮那里写个提交事件，一按提交，提交处理的事情就是：生成一个EXCEL文件，然后用一个RECORDSET指向那个EXCEL，然后把记录都插进去，只是记录多的时候要处理的数据量比较多

wave214 2003-10-21

打赏
举报

ricky003(ricky) ：要的话是不是很难？难就不要拉，要当然是好的，下载到本地的哪个目录我想过了，就在代码里设成固定目录，这样比较方便。你说呢？我的MSN 是waveagain2hotmail.com QQ 75349479

awaysrain 2003-10-21

打赏
举报

简单的办法就是用FSO写HTM标记，有安全提示，需要调整IE的安全级别
要不你就在服务器端生成，让客户端下载

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<script language="JavaScript">
function makeExcel()
{
var fso,f1;
var ForWriting = 2;
try{
fso = new ActiveXObject("Scripting.FileSystemObject");
f1 = fso.CreateTextFile("c:\\test.xls",ForWriting,true);
}
catch(e){
alert("无法建立文件，可能是您的浏览器的安全级别太高。");
return;
}

f1.WriteLine(tab1.outerHTML);
f1.Close();
alert("保存完毕！文件为c:\\test.xls");
}
</script>
</head>

<body>
<table width="300" border="1" cellpadding="0" cellspacing="0" id="tab1">
<tr>
<td rowspan="2">1</td>
<td>2</td>
<td>3</td>
</tr>
<tr>
<td colspan="2">34</td>
</tr>
<tr>
<td>5</td>
<td rowspan="3">6</td>
<td>7</td>
</tr>
<tr>
<td>8</td>
<td>9</td>
</tr>
<tr>
<td>10</td>
<td>11</td>
</tr>
</table>
<p>
<input type="button" name="Button" value="生成EXCEL" onClick="makeExcel()">
</p>
</body>
</html>

ricky003 2003-10-21