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

Contact Us

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.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.