Output the common function of Excel file, very practical
Source: Internet
Author: User
Ayu often want to output a wide variety of query results to Excel, so do the following small program to output an SQL select query results to the Excel format file, the program you just set to get a recordset of SQL SELECT query statement and a file name, The program can output Excel format file, this program is composed of three files, the first file name is: Toexcel.asp is the main file, the contents are as follows:
<%
' Front is and to link to the database, please write the relevant statement, here 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>"
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.