Output the common function of Excel file, very practical

Source: Internet
Author: User
Tags format count query xmlns
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 ' >
&LT;TD width=72 style= ' width:54pt ' ></td>
&LT;TD width=72 style= ' width:54pt ' ></td>
&LT;TD width=72 style= ' width:54pt ' ></td>
&LT;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>


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.