<%
set conn = opendb( " EMSC " )
' 前面是设置数据源并链接到数据库,请自行书写相关语句,此处略过
sql = " select * from sys_user " ' 这里是要输出EXCEL的查询语句,如 "SESECT * FORM CAI WHERE 性别='女'"
filename = " excel.xls " ' 要输出的EXCEL文件的文件名, 你只要改以上两句就行了,其它的都不要改.
' 你只要修改以上两变量就行了.其它的我都做好了.
call toexcel(FILENAME,sql)
set conn = nothing
function ReadText(FileName) ' 这是一个用于读出文件的函数
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) ' 这是一个用于写文件的函数
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) ' 这是一个根据SQL语句和FILENAME生成EXCEL文件
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 while 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
%>
< html >
< head >
< 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 > 正在生成EXLCE文件 </ title >
</ head >
< BODY >
正在生成EXLCE文件
.
</ BODY >
</ HTML >
set conn = opendb( " EMSC " )
' 前面是设置数据源并链接到数据库,请自行书写相关语句,此处略过
sql = " select * from sys_user " ' 这里是要输出EXCEL的查询语句,如 "SESECT * FORM CAI WHERE 性别='女'"
filename = " excel.xls " ' 要输出的EXCEL文件的文件名, 你只要改以上两句就行了,其它的都不要改.
' 你只要修改以上两变量就行了.其它的我都做好了.
call toexcel(FILENAME,sql)
set conn = nothing
function ReadText(FileName) ' 这是一个用于读出文件的函数
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) ' 这是一个用于写文件的函数
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) ' 这是一个根据SQL语句和FILENAME生成EXCEL文件
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 while 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
%>
< html >
< head >
< 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 > 正在生成EXLCE文件 </ title >
</ head >
< BODY >
正在生成EXLCE文件
![](https://www.cnblogs.com/Images/dot.gif)
</ BODY >
</ HTML >
********第二个文件名为:di.txt 内容如下:
<table x:str border=0 cellpadding=0 cellspacing=0 width=288 style='border-collapse:
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>
************第三个文件的文件名为:tou.TXT 内容如下:
<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
<head>
<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>
<o:Created>2002-05-27T17:51:00Z</o:Created>
<o:LastSaved>2002-06-22T10:03:03Z</o:LastSaved>
<o:Company>zydn</o:Company>
<o:Version>9.2812</o:Version>
</o:DocumentProperties>
<o:OfficeDocumentSettings>
<o:DownloadComponents/>
<o:LocationOfComponents HRef="file:///E:/msowc.cab"/>
</o:OfficeDocumentSettings>
</xml><![endif]-->
<style>
<!--table
{mso-displayed-decimal-separator:"\.";
mso-displayed-thousand-separator:"\,";}
@page
{margin:1.0in .75in 1.0in .75in;
mso-header-margin:.5in;
mso-footer-margin:.5in;}
tr
{mso-height-source:auto;
mso-ruby-visibility:none;}
col
{mso-width-source:auto;
mso-ruby-visibility:none;}
br
{mso-data-placement:same-cell;}
.style0
{mso-number-format:General;
text-align:general;
vertical-align:bottom;
white-space:nowrap;
mso-rotate:0;
mso-background-source:auto;
mso-pattern:auto;
color:windowtext;
font-size:9.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:宋体;
mso-generic-font-family:auto;
mso-font-charset:134;
border:none;
mso-protection:locked visible;
mso-style-name:常规;
mso-style-id:0;}
td
{mso-style-parent:style0;
padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:windowtext;
font-size:9.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:宋体;
mso-generic-font-family:auto;
mso-font-charset:134;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
border:none;
mso-background-source:auto;
mso-pattern:auto;
mso-protection:locked visible;
white-space:nowrap;
mso-rotate:0;}
.xl24
{mso-style-parent:style0;
border:.5pt solid windowtext;}
.xl25
{mso-style-parent:style0;
mso-number-format:"Long Date";
text-align:left;
border:.5pt solid windowtext;}
ruby
{ruby-align:left;}
rt
{color:windowtext;
font-size:9.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:宋体;
mso-generic-font-family:auto;
mso-font-charset:134;
mso-char-type:none;
display:none;}
-->
</style>
<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Sheet1</x:Name>
<x:WorksheetOptions>
<x:DefaultRowHeight>225</x:DefaultRowHeight>
<x:Print>
<x:ValidPrinterInfo/>
<x:PaperSizeIndex>9</x:PaperSizeIndex>
<x:HorizontalResolution>-3</x:HorizontalResolution>
<x:VerticalResolution>0</x:VerticalResolution>
</x:Print>
<x:Selected/>
<x:Panes>
<x:Pane>
<x:Number>3</x:Number>
<x:ActiveRow>24</x:ActiveRow>
<x:ActiveCol>5</x:ActiveCol>
</x:Pane>
</x:Panes>
<x:ProtectContents>False</x:ProtectContents>
<x:ProtectObjects>False</x:ProtectObjects>
<x:ProtectScenarios>False</x:ProtectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>
<x:ExcelWorksheet>
<x:Name>Sheet2</x:Name>
<x:WorksheetOptions>
<x:DefaultRowHeight>225</x:DefaultRowHeight>
<x:ProtectContents>False</x:ProtectContents>
<x:ProtectObjects>False</x:ProtectObjects>
<x:ProtectScenarios>False</x:ProtectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>
<x:ExcelWorksheet>
<x:Name>Sheet3</x:Name>
<x:WorksheetOptions>
<x:DefaultRowHeight>225</x:DefaultRowHeight>
<x:ProtectContents>False</x:ProtectContents>
<x:ProtectObjects>False</x:ProtectObjects>
<x:ProtectScenarios>False</x:ProtectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
<x:WindowHeight>6600</x:WindowHeight>
<x:WindowWidth>12000</x:WindowWidth>
<x:WindowTopX>0</x:WindowTopX>
<x:WindowTopY>1395</x:WindowTopY>
<x:ProtectStructure>False</x:ProtectStructure>
<x:ProtectWindows>False</x:ProtectWindows>
</x:ExcelWorkbook>
</xml><![endif]-->
</head>
<body link=blue vlink=purple>