excel| function Ayu often to output a variety of query results to Excel, so do the following small program, for a SQL select query results output as an Excel format file, This program you just set up to get a recordset of SQL SELECT query statement and a filename, the program can output Excel format file, this program is composed of three files, the first file file name is: Toexcel.asp is the main file, the contents are as follows:
<%
' Before you set up your data source and link to the database, write the relevant statement yourself, skip
Sql=session ("Toexcelsql") ' Here is the query statement to output Excel, such as "Sesect * FORM CAI where gender = ' female '"
Filename= "Excel.xls" ' to output the file name of the Excel file, you just change the above two lines, the other do not change.
' All you have to do is change the above two variables. I've done all the rest.
Call Toexcel (Filename,sql)
Set conn=nothing
function ReadText (FileName) ' This is a functions for reading out a file
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) ' This is a function for writing files
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) ' This is an Excel file generated from SQL statements and filename
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 as 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
%>
<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> Generating Exlce file </title>
<BODY>
Generating Exlce file ....
</BODY>
</HTML>
The second file name is: Di.txt reads as follows:
<table x:str border=0 cellpadding=0 cellspacing=0 width=288 ' style=:
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>
The file name for the third file is: Tou. TXT content is as follows:
xmlns:x= "Urn:schemas-microsoft-com:office:excel"
xmlns= "HTTP://WWW.W3.ORG/TR/REC-HTML40" >
<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>
