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 content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.