输出EXCEL文件的通用函数，很实用

以下文章来自于 http://www.dalianit.com/edu/|78|107|86|121|79|120|62|62|.html ，在此向原作者表示感谢！

阿余常要把各种各样的查询结果输出到EXCEL中，所以做了下面这段小程序，用于把一个SQL的SELECT查询出的结果输出为EXCEL格式文件，这个程序你只要设好用于取得一个记录集的SQL的SELECT查询语句和一个文件名，程序就能输出EXCEL格式文件了，这个程序一共由三个文件构成，第一个文件的文件名为：toexcel.asp是主文件，内容如下：

 1 
 2
 3'前面是和来链接到数据库,请自行书写相关语句，此处略过 
 4
 5sql=session("toexcelsql") '这里是要输出EXCEL的查询语句，如 "SESECT * FORM CAI WHERE 性别='女'"   
 6filename="excel.xls" ' 要输出的EXCEL文件的文件名, 你只要改以上两句就行了,其它的都不要改. 
 7
 8'你只要修改以上两变量就行了.其它的我都做好了. 
 9
10call toexcel(FILENAME,sql)   
11set conn=nothing 
12
13function ReadText(FileName) '这是一个用于读出文件的函数   
14set adf=server.CreateObject("Adodb.Stream")   
15with adf   
16.Type=2   
17.LineSeparator=10   
18.Open   
19.LoadFromFile (server.MapPath(FileName))   
20.Charset="GB2312"   
21.Position=2   
22ReadText=.ReadText   
23.Cancel()   
24.Close()   
25end with   
26set ads=nothing   
27end function 
28
29sub SaveText(FileName,Data) '这是一个用于写文件的函数   
30set fs= createobject("scripting.filesystemobject")   
31set ts=fs.createtextfile(server.MapPath(FileName),true)   
32ts.writeline(data)   
33ts.close   
34set ts=nothing   
35set fs=nothing   
36end sub 
37
38sub toexcel(filename,sql) '这是一个根据SQL语句和FILENAME生成EXCEL文件   
39Set rs=Server.CreateObject("ADODB.RecordSet")   
40rs.Open sql,conn,1,3   
41TOEXCELLR="

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

1"   
2tou=readtext("tou.txt")   
3di=readtext("di.txt")   
4toexcellr=tou&toexcellr&di   
5call savetext(filename,toexcellr)   
6end sub

 1<html>
 2<head>
 3<meta content="3;URL=```
 4=filename
 5```" http-equiv="refresh"/>
 6<meta content="en-us" http-equiv="Content-Language"/>
 7<meta content="text/html; charset=utf-8" http-equiv="Content-Type"/>
 8<title>正在生成EXLCE文件</title>
 9</head>
10<body>   
11正在生成EXLCE文件....   
12</body>
13</html>

＊＊＊＊＊＊＊＊＊＊＊＊＊＊第二个文件名为：di.txt 内容如下：

 1<table border="0" cellpadding="0" cellspacing="0" style="border-collapse:   
 2collapse;table-layout:fixed;width:216pt" width="288" x:str="">
 3<?if supportMisalignedColumns?>
 4<tr height="0" style="display:none">
 5<td style="width:54pt" width="72"></td>
 6<td style="width:54pt" width="72"></td>
 7<td style="width:54pt" width="72"></td>
 8<td style="width:54pt" width="72"></td>
 9</tr>
10<?endif?>
11</table>

＊＊＊＊＊＊＊＊＊＊＊＊第三个文件的文件名为：tou.TXT 内容如下：

  1<html xmlns="http://www.w3.org/TR/REC-html40" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel">
  2<head>
  3<meta content="text/html; charset=utf-8" http-equiv="Content-Type"/>
  4<meta content="Excel.Sheet" name="ProgId"/>
  5<meta content="Microsoft Excel 9" name="Generator"/>
  6<link href="./222.files/filelist.xml" rel="File-List"/>
  7<link href="./222.files/editdata.mso" rel="Edit-Time-Data"/>
  8<link href="./222.files/oledata.mso" rel="OLE-Object-Data"/>
  9<!--[if gte mso 9]><xml>   
 10<o:DocumentProperties>   
 11<o:Author>xky</o:Author>   
 12<o:LastAuthor>xky</o:LastAuthor>   
 13<o:Created>2002-05-27T17:51:00Z</o:Created>   
 14<o:LastSaved>2002-06-22T10:03:03Z</o:LastSaved>   
 15<o:Company>zydn</o:Company>   
 16<o:Version>9.2812</o:Version>   
 17</o:DocumentProperties>   
 18<o:OfficeDocumentSettings>   
 19<o:DownloadComponents/>   
 20<o:LocationOfComponents HRef="file:///E:/msowc.cab"/>   
 21</o:OfficeDocumentSettings>   
 22</xml><![endif]-->
 23<style>   
 24<!--table   
 25{mso-displayed-decimal-separator:"\\.";   
 26mso-displayed-thousand-separator:"\,";}   
 27@page   
 28{margin:1.0in .75in 1.0in .75in;   
 29mso-header-margin:.5in;   
 30mso-footer-margin:.5in;}   
 31tr   
 32{mso-height-source:auto;   
 33mso-ruby-visibility:none;}   
 34col   
 35{mso-width-source:auto;   
 36mso-ruby-visibility:none;}   
 37br   
 38{mso-data-placement:same-cell;}   
 39.style0   
 40{mso-number-format:General;   
 41text-align:general;   
 42vertical-align:bottom;   
 43white-space:nowrap;   
 44mso-rotate:0;   
 45mso-background-source:auto;   
 46mso-pattern:auto;   
 47color:windowtext;   
 48font-size:9.0pt;   
 49font-weight:400;   
 50font-style:normal;   
 51text-decoration:none;   
 52font-family:宋体;   
 53mso-generic-font-family:auto;   
 54mso-font-charset:134;   
 55border:none;   
 56mso-protection:locked visible;   
 57mso-style-name:常规;   
 58mso-style-id:0;}   
 59td   
 60{mso-style-parent:style0;   
 61padding-top:1px;   
 62padding-right:1px;   
 63padding-left:1px;   
 64mso-ignore:padding;   
 65color:windowtext;   
 66font-size:9.0pt;   
 67font-weight:400;   
 68font-style:normal;   
 69text-decoration:none;   
 70font-family:宋体;   
 71mso-generic-font-family:auto;   
 72mso-font-charset:134;   
 73mso-number-format:General;   
 74text-align:general;   
 75vertical-align:bottom;   
 76border:none;   
 77mso-background-source:auto;   
 78mso-pattern:auto;   
 79mso-protection:locked visible;   
 80white-space:nowrap;   
 81mso-rotate:0;}   
 82.xl24   
 83{mso-style-parent:style0;   
 84border:.5pt solid windowtext;}   
 85.xl25   
 86{mso-style-parent:style0;   
 87mso-number-format:"Long Date";   
 88text-align:left;   
 89border:.5pt solid windowtext;}   
 90ruby   
 91{ruby-align:left;}   
 92rt   
 93{color:windowtext;   
 94font-size:9.0pt;   
 95font-weight:400;   
 96font-style:normal;   
 97text-decoration:none;   
 98font-family:宋体;   
 99mso-generic-font-family:auto;   
100mso-font-charset:134;   
101mso-char-type:none;   
102display:none;}   
103\-->   
104</style>
105<!--[if gte mso 9]><xml>   
106<x:ExcelWorkbook>   
107<x:ExcelWorksheets>   
108<x:ExcelWorksheet>   
109<x:Name>Sheet1</x:Name>   
110<x:WorksheetOptions>   
111<x:DefaultRowHeight>225</x:DefaultRowHeight>   
112<x:Print>   
113<x:ValidPrinterInfo/>   
114<x:PaperSizeIndex>9</x:PaperSizeIndex>   
115<x:HorizontalResolution>-3</x:HorizontalResolution>   
116<x:VerticalResolution>0</x:VerticalResolution>   
117</x:Print>   
118<x:Selected/>   
119<x:Panes>   
120<x:Pane>   
121<x:Number>3</x:Number>   
122<x:ActiveRow>24</x:ActiveRow>   
123<x:ActiveCol>5</x:ActiveCol>   
124</x:Pane>   
125</x:Panes>   
126<x:ProtectContents>False</x:ProtectContents>   
127<x:ProtectObjects>False</x:ProtectObjects>   
128<x:ProtectScenarios>False</x:ProtectScenarios>   
129</x:WorksheetOptions>   
130</x:ExcelWorksheet>   
131<x:ExcelWorksheet>   
132<x:Name>Sheet2</x:Name>   
133<x:WorksheetOptions>   
134<x:DefaultRowHeight>225</x:DefaultRowHeight>   
135<x:ProtectContents>False</x:ProtectContents>   
136<x:ProtectObjects>False</x:ProtectObjects>   
137<x:ProtectScenarios>False</x:ProtectScenarios>   
138</x:WorksheetOptions>   
139</x:ExcelWorksheet>   
140<x:ExcelWorksheet>   
141<x:Name>Sheet3</x:Name>   
142<x:WorksheetOptions>   
143<x:DefaultRowHeight>225</x:DefaultRowHeight>   
144<x:ProtectContents>False</x:ProtectContents>   
145<x:ProtectObjects>False</x:ProtectObjects>   
146<x:ProtectScenarios>False</x:ProtectScenarios>   
147</x:WorksheetOptions>   
148</x:ExcelWorksheet>   
149</x:ExcelWorksheets>   
150<x:WindowHeight>6600</x:WindowHeight>   
151<x:WindowWidth>12000</x:WindowWidth>   
152<x:WindowTopX>0</x:WindowTopX>   
153<x:WindowTopY>1395</x:WindowTopY>   
154<x:ProtectStructure>False</x:ProtectStructure>   
155<x:ProtectWindows>False</x:ProtectWindows>   
156</x:ExcelWorkbook>   
157</xml><![endif]-->
158</head>
159<body link="blue" vlink="purple"></body></html>