sql生成execl

前台页
sql_to_execl.asp

<HTML>
<HEAD>
<meta content="text/html; charset=gb2312" http-equiv="Content-Type">
<TITLE>生成EXCEL文件</TITLE>
</HEAD>
<body>
<%table=request("table")%>
<form name="frm" action="sql_to_execl_deal.asp" method="post">
<input type="hidden" name="act" value="edit">
<table width="100%" border="0" cellpadding="2" cellspacing="1">
<tr>
<td colspan="4"><div align="center">SQL数据生成execl</div></td>
</tr>
<tr>
<td colspan="4"> </td>
</tr>
<tr>
<td colspan="4">
<div align="center">选择导出表 

<input type="text" name="table" value="<%=table%>"> 
<input type="button" name="columns" value="显示字段" onClick="view();">

</div>
</td>
</tr>
</table>

<table width="100%" border="1" cellpadding="2" cellspacing="1">
<tr>
<td width="10%">选择</td>
<td width="25%">字段名称</td>
<td width="30%">字段描述</td>

</tr>
<%

if table<>"" and not isnull(table) then
   condition=condition+" 1=1"
   sql="SELECT a.colorder,a.name,isnull(g.[value],'') as [desc] FROM syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id left join sysproperties g on a.id=g.id and a.colid=g.smallid where d.name='"&table&"'"
   set Rs=Server.createobject("Adodb.Recordset")
    Rs.Open sql, Conn, 1, 1
    if not rs.eof then
    do while not rs.eof
%>

<tr>
    <td><input name="cid" type="checkbox" value="<%=rs(0)%>"></td>
    <td><input name="column" type="text" value="<%=rs(1)%>"> </td>
<td><input name="desc<%=rs(0)%>" type="text" value=""> </td>

</tr>
<%rs.movenext
loop
end if
else
response.Write("<tr><td colspan='3' align='center'>查无此表！</td></tr>")
end if%>
<tr>
      <td align="center">管理</td>
      <td><a href="javascript:SelectAll()">全选/反选</a><input type="checkbox" name="check" value="checkbox" onClick="javascript:SelectAll()"></td>
      <td><input type="hidden" name="button" value="更新描述" class="oa_button" onClick="{if(checkbox('cid')){if(confirm('您确定执行的操作吗?')){this.document.frm.submit();return true;}return false;}return false;}"></td>
</tr>
</table>
<br>
<table width="100%" border="0" cellpadding="2" cellspacing="1">
<tr>
<td align="center">
<input name="radio" type="radio" value="0" checked="checked">字段名称
<input name="radio" type="radio" value="1">字段描述
<input name="button2" type="button" value=" 导 出 " onClick="make();"></td>
</tr>
<tr><td align="center">
文件命名 <input name="fname" type="text" value="">
</td>
</tr>
</table>
</form>
<script language="JavaScript">
<!--//
function view(){
if(document.all.frm.table.value==""){
   alert('请添写表名！');
   return false;
   }

this.location="sql_to_execl.asp?table="+frm.table.value;
}
function SelectAll() {
for (var i=0;i<document.frm.cid.length;i++) {
var e=document.frm.cid[i];
e.checked=!e.checked;
}
}

}
function make(){
if(!document.all.cid){
alert('请先查询表字段');
return false;
}

if(checkbox('cid')){
   if(document.all.frm.fname.value==""){
   alert('请添写文件名！');
   return false;
   }
   if(confirm('文件名相同将删除原文件，您确定执行的操作吗?')){
       document.all.frm.act.value="make";
       frm.submit();
    return true;
   }
   return false;}
return false;}
//-->
</script>
</BODY>
</HTML>

后台处理页

<HTML>
<HEAD>
<meta content="text/html; charset=gb2312" http-equiv="Content-Type">
<TITLE>生成EXCEL文件</TITLE>
</HEAD>
<body>

table=request("table")
cid=request("cid")
column=request("column")
act=Request("act")
radio=request("radio")

dim rs,sql,filename,fs,myfile,x,fname,cid_new,column_new
'furl=request("furl")          '文件路径
fname=request("fname")&".xls" '文件名
column_new=""
if instr(cid,",")>0 then
   cid_new=split(cid,",")

   for i=0 to ubound(cid_new)
       if radio=0 then      '判断显示字段还是中文描述
       column_new=column_new&split(column,",")(cid_new(i)-1)&","
    else
    desc_new="desc"&trim(cid_new(i))

    column_new=column_new&split(column,",")(cid_new(i)-1)&" as "&request(desc_new)&""&","
    end if
   next
       column_new=left(column_new,len(column_new)-1)
else
   if radio=0 then      '判断显示字段还是中文描述
   column_new=split(column,",")(cid-1)
   else
   desc_new="desc"&trim(cid)
   column_new=split(column,",")(cid-1)&" as "&request(desc_new)&""
   end if
end if

Set fs = server.CreateObject("scripting.filesystemobject")
'--假设你想让生成的EXCEL文件做如下的存放
filename = Server.MapPath(fname)
'--如果原来的EXCEL文件存在的话删除它
if fs.FileExists(filename) then
fs.DeleteFile(filename)
end if
'--创建EXCEL文件
set myfile = fs.CreateTextFile(filename,true)

Set rs = Server.CreateObject("ADODB.Recordset")
'--从数据库中把你想放到EXCEL中的数据查出来
sql = "select "&column_new&" from "&table&""
response.Write(sql)
rs.Open sql,conn,1,1
if rs.EOF and rs.BOF then
Response.Write "库里暂时没有数据！"
else
dim strLine,responsestr
strLine=""
For each x in rs.fields
strLine= strLine & x.name & chr(9)
Next

'--将表的列名先写入EXCEL
myfile.writeline strLine

Do while Not rs.EOF
strLine=""

for each x in rs.Fields
strLine= strLine & x.value & chr(9)
next
'--将表的数据写入EXCEL
myfile.writeline strLine

rs.MoveNext
loop
end if
rs.Close
set rs = nothing
conn.close
set conn = nothing
set myfile = nothing
Set fs=Nothing

%>
<%if act = "make" then%>
<a href="<%=fname%>">打开EXCEL</a>
<hr size=1 align=left width=300px>
<%end if%>

</BODY>
</HTML>

oracle更改数据连接即可！！

posted on 2007-05-31 15:40 许维光阅读(312) 评论(0) 收藏举报