IDC资讯中心
阿余常要把各种各样的查询结果输出到excel中，所以做了下面这段小程序，用于把一个sql的select查询出的结果输出为excel格式文件，这个程序你只要设好用于取得一个记录集的sql的select查询语句和一个文件名，程序就能输出excel格式文件了，这个程序一共由三个文件构成，第一个文件的文件名为：toexcel.asp是主文件，内容如下：
<%
前面是设置数据源并链接到数据库,请自行书写相关语句，此处略过
sql=session("toexcelsql") 这里是要输出excel的查询语句，如 "sesect * form cai where 性别=女"
filename="excel.xls" 要输出的excel文件的文件名, 你只要改以上两句就行了,其它的都不要改.
你只要修改以上两变量就行了.其它的我都做好了.
call toexcel(filename,sql)
set conn=nothing
function readtext(filename) 这是一个用于读出文件的函数
set adf=server.createobject("adodb.stream")
with adf
.type=2
.lineseparator=10
.open
.loadfromfile (server.mappath(filename))
.charset="gb2312"
.position=2
readtext=.readtext
.cancel()
.close()
end with
set ads=nothing
end function
sub savetext(filename,data) 这是一个用于写文件的函数
set fs= createobject("scripting.filesystemobject")
set ts=fs.createtextfile(server.mappath(filename),true)
ts.writeline(data)
ts.close
set ts=nothing
set fs=nothing
end sub
sub toexcel(filename,sql) 这是一个根据sql语句和filename生成excel文件
set rs=server.createobject("adodb.recordset")
rs.open sql,conn,1,3
toexcellr="<table width=100%><tr >"
set myfield=rs.fields
dim fieldname(50)
for i=0 to myfield.count-1
toexcellr=toexcellr&"<td class=xl24>"&myfield(i).name&"</td>"
fieldname(i)=myfield(i).name
if myfield(i).type=135 then datename=datename&myfield(i).name&","
next
toexcellr=toexcellr&"</tr>"
do while not rs.eof
toexcellr=toexcellr&"<tr>"
for i=0 to myfield.count-1
if instr(datename,fieldname(i)&",")<>0 then
if not isnull(rs(fieldname(i))) then
toexcellr=toexcellr&"<td class=xl25 ><p align=left>"&formatdatetime(rs(fieldname(i)),2)&"</p></td>"
else
toexcellr=toexcellr&"<td class=xl25 ><p align=left> </p></td>"
end if
else
toexcellr=toexcellr&"<td class=xl24 >"&rs(fieldname(i))&"</td>"
end if
next
toexcellr=toexcellr&"</tr>"
rs.movenext
loop
toexcellr=toexcellr&"</table>"
tou=readtext("tou.txt")
di=readtext("di.txt")
toexcellr=tou&toexcellr&di
call savetext(filename,toexcellr)
end sub
%>
<html>
<head>
<meta http-equiv="refresh" content="3;url=<%=filename%>">
<meta http-equiv="content-language" content="en-us">
<meta http-equiv="content-type" content="text/html; charset=gb2312">
<title>正在生成exlce文件</title>
</head>
<body>
正在生成exlce文件….
</body>
</html>
＊＊＊＊＊＊＊＊＊＊＊＊＊＊第二个文件名为：di.txt 内容如下：
<table x:str border=0 cellpadding=0 cellspacing=0 width=288 style=border-collapse:
collapse;table-layout:fixed;width:216pt>
<![if supportmisalignedcolumns]>
<tr height=0 style=display:none>
<td width=72 style=width:54pt></td>
<td width=72 style=width:54pt></td>
<td width=72 style=width:54pt></td>
<td width=72 style=width:54pt></td>
</tr>
<![endif]>
</table>
＊＊＊＊＊＊＊＊＊＊＊＊第三个文件的文件名为：tou.txt 内容如下：
<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/tr/rec-html40">
<head>
<meta http-equiv=content-type content="text/html; charset=gb2312">
<meta name=progid content=excel.sheet>
<meta name=generator content="microsoft excel 9">
<link rel=file-list href="./222.files/filelist.xml">
<link rel=edit-time-data href="./222.files/editdata.mso">
<link rel=ole-object-data href="./222.files/oledata.mso">
<!–[if gte mso 9]><xml>
<o:documentproperties>
<o:author>xky</o:author>
<o:lastauthor>xky</o:lastauthor>
<o:created>2002-05-27t17:51:00z</o:created>
<o:lastsaved>2002-06-22t10:03:03z</o:lastsaved>
<o:company>zydn</o:company>
<o:version>9.2812</o:version>
</o:documentproperties>
<o:officedocumentsettings>
<o:downloadcomponents/>
<o:locationofcomponents href="file:///e:/msowc.cab"/>
</o:officedocumentsettings>
</xml><![endif]–>
<style>
<!–table
{mso-displayed-decimal-separator:"\.";
mso-displayed-thousand-separator:"\,";}
@page
{margin:1.0in .75in 1.0in .75in;
mso-header-margin:.5in;
mso-footer-margin:.5in;}
tr
{mso-height-source:auto;
mso-ruby-visibility:none;}
col
{mso-width-source:auto;
mso-ruby-visibility:none;}
br
{mso-data-placement:same-cell;}
.style0
{mso-number-format:general;
text-align:general;
vertical-align:bottom;
white-space:nowrap;
mso-rotate:0;
mso-background-source:auto;
mso-pattern:auto;
color:windowtext;
font-size:9.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:宋体;
mso-generic-font-family:auto;
mso-font-charset:134;
border:none;
mso-protection:locked visible;
mso-style-name:常规;
mso-style-id:0;}
td
{mso-style-parent:style0;
padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:windowtext;
font-size:9.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:宋体;
mso-generic-font-family:auto;
mso-font-charset:134;
mso-number-format:general;
text-align:general;
vertical-align:bottom;
border:none;
mso-background-source:auto;
mso-pattern:auto;
mso-protection:locked visible;
white-space:nowrap;
mso-rotate:0;}
.xl24
{mso-style-parent:style0;
border:.5pt solid windowtext;}
.xl25
{mso-style-parent:style0;
mso-number-format:"long date";
text-align:left;
border:.5pt solid windowtext;}
ruby
{ruby-align:left;}
rt
{color:windowtext;
font-size:9.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:宋体;
mso-generic-font-family:auto;
mso-font-charset:134;
mso-char-type:none;
display:none;}
–>
</style>
<!–[if gte mso 9]><xml>
<x:excelworkbook>
<x:excelworksheets>
<x:excelworksheet>
<x:name>sheet1</x:name>
<x:worksheetoptions>
<x:defaultrowheight>225</x:defaultrowheight>
<x:print>
<x:validprinterinfo/>
<x:papersizeindex>9</x:papersizeindex>
<x:horizontalresolution>-3</x:horizontalresolution>
<x:verticalresolution>0</x:verticalresolution>
</x:print>
<x:selected/>
<x:panes>
<x:pane>
<x:number>3</x:number>
<x:activerow>24</x:activerow>
<x:activecol>5</x:activecol>
</x:pane>
</x:panes>
<x:protectcontents>false</x:protectcontents>
<x:protectobjects>false</x:protectobjects>
<x:protectscenarios>false</x:protectscenarios>
</x:worksheetoptions>
</x:excelworksheet>
<x:excelworksheet>
<x:name>sheet2</x:name>
<x:worksheetoptions>
<x:defaultrowheight>225</x:defaultrowheight>
<x:protectcontents>false</x:protectcontents>
<x:protectobjects>false</x:protectobjects>
<x:protectscenarios>false</x:protectscenarios>
</x:worksheetoptions>
</x:excelworksheet>
<x:excelworksheet>
<x:name>sheet3</x:name>
<x:worksheetoptions>
<x:defaultrowheight>225</x:defaultrowheight>
<x:protectcontents>false</x:protectcontents>
<x:protectobjects>false</x:protectobjects>
<x:protectscenarios>false</x:protectscenarios>
</x:worksheetoptions>
</x:excelworksheet>
</x:excelworksheets>
<x:windowheight>6600</x:windowheight>
<x:windowwidth>12000</x:windowwidth>
<x:windowtopx>0</x:windowtopx>
<x:windowtopy>1395</x:windowtopy>
<x:protectstructure>false</x:protectstructure>
<x:protectwindows>false</x:protectwindows>
</x:excelworkbook>
</xml><![endif]–>
</head>
<body link=blue vlink=purple>
