我们在把数据从数据库中直接导成excel格式时，经常我会遇到一些问题（如服务器上无法创建excel对象等）。在这里我使用了一个把TXT文件转成excel的方法（可能存在一定的局限性），与大家共享。
首先用excel制作出需要的excel摸板
然后把文件另存为HTML文件，找到excel数据区的内容并用$importdata替换掉然后存成TXT格式
然后用asp打开刚才存储的TXT文件，并用asp生成数据区的内容，填充到$importdata中去，再把文件另存为后缀为.xls的excel文件即可。
==========temp.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 10">
<!--[if gte mso 9]><xml>
<o:DocumentProperties>
<o:Author>vince</o:Author>
<o:LastAuthor>vince</o:LastAuthor>
<o:Created>2005-12-16T02:31:59Z</o:Created>
<o:LastSaved>2005-12-16T03:07:42Z</o:LastSaved>
<o:Company>tianheng</o:Company>
<o:Version>10.2625</o:Version>
</o:DocumentProperties>
<o:OfficeDocumentSettings>
<o:DownloadComponents/>
<o:LocationOfComponents HRef="file:/"/>
</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:middle;
white-space:nowrap;
mso-rotate:0;
mso-background-source:auto;
mso-pattern:auto;
color:windowtext;
font-size:12.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:12.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:middle;
border:none;
mso-background-source:auto;
mso-pattern:auto;
mso-protection:locked visible;
white-space:nowrap;
mso-rotate:0;}
.xl24
{mso-style-parent:style0;
text-align:center;}
.xl25
{mso-style-parent:style0;
white-space:normal;}
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>$importdate</x:Name>
<x:WorksheetOptions>
<x:DefaultRowHeight>285</x:DefaultRowHeight>
<x:Selected/>
<x:LeftColumnVisible>7</x:LeftColumnVisible>
<x:Panes>
<x:Pane>
<x:Number>3</x:Number>
<x:ActiveRow>2</x:ActiveRow>
<x:ActiveCol>11</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>285</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>285</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>9225</x:WindowHeight>
<x:WindowWidth>11700</x:WindowWidth>
<x:WindowTopX>240</x:WindowTopX>
<x:WindowTopY>120</x:WindowTopY>
<x:ProtectStructure>False</x:ProtectStructure>
<x:ProtectWindows>False</x:ProtectWindows>
</x:ExcelWorkbook>
</xml><![endif]-->
</head>
<body link=blue vlink=purple>
<table x:str border=0 cellpadding=0 cellspacing=0 width=1871 style='border-collapse:
collapse;table-layout:fixed;width:1402pt'>
<col width=68 style='mso-width-source:userset;mso-width-alt:2176;width:51pt'>
<col width=44 span=2 style='mso-width-source:userset;mso-width-alt:1408;
width:33pt'>
<col width=191 style='mso-width-source:userset;mso-width-alt:6112;width:143pt'>
<col width=155 style='mso-width-source:userset;mso-width-alt:4960;width:116pt'>
<col width=111 style='mso-width-source:userset;mso-width-alt:3552;width:83pt'>
<col width=155 style='mso-width-source:userset;mso-width-alt:4960;width:116pt'>
<col width=120 style='mso-width-source:userset;mso-width-alt:3840;width:90pt'>
<col width=76 style='mso-width-source:userset;mso-width-alt:2432;width:57pt'>
<col width=424 style='mso-width-source:userset;mso-width-alt:13568;width:318pt'>
<col width=93 style='mso-width-source:userset;mso-width-alt:2976;width:70pt'>
<col class=xl24 width=111 style='mso-width-source:userset;mso-width-alt:3552;
width:83pt'>
<col width=203 style='mso-width-source:userset;mso-width-alt:6496;width:152pt'>
<col width=76 style='mso-width-source:userset;mso-width-alt:2432;width:57pt'>
<tr class=xl24 height=19 style='height:14.25pt'>
<td height=19 class=xl24 width=68 style='height:14.25pt;width:51pt'>姓名</td>
<td class=xl24 width=44 style='width:33pt'>年龄</td>
<td class=xl24 width=44 style='width:33pt'>性别</td>
<td class=xl24 width=191 style='width:143pt'>应聘企业</td>
<td class=xl24 width=155 style='width:116pt'>应聘职位</td>
<td class=xl24 width=111 style='width:83pt'>期望工作地点</td>
<td class=xl24 width=155 style='width:116pt'>毕业学校</td>
<td class=xl24 width=120 style='width:90pt'>专业</td>
<td class=xl24 width=76 style='width:57pt'>最后学历</td>
<td class=xl24 width=424 style='width:318pt'>工作履历</td>
<td class=xl24 width=93 style='width:70pt'>原单位收入</td>
<td class=xl24 width=111 style='width:83pt'>期望工资收入</td>
<td class=xl24 width=203 style='width:152pt'>联系方式</td>
<td class=xl24 width=76 style='width:57pt'>简历状态</td>
</tr>
$importdata
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=68 style='width:51pt'></td>
<td width=44 style='width:33pt'></td>
<td width=44 style='width:33pt'></td>
<td width=191 style='width:143pt'></td>
<td width=155 style='width:116pt'></td>
<td width=111 style='width:83pt'></td>
<td width=155 style='width:116pt'></td>
<td width=120 style='width:90pt'></td>
<td width=76 style='width:57pt'></td>
<td width=424 style='width:318pt'></td>
<td width=93 style='width:70pt'></td>
<td width=111 style='width:83pt'></td>
<td width=203 style='width:152pt'></td>
<td width=76 style='width:57pt'></td>
</tr>
<![endif]>
</table>
</body>
</html>
===============importdata.asp=======================
为了表示简单这里略去了数据库的连接部分
<%
suc=0
temppath="temp.TXT" '摸板文件位置
excelpath="./" '导出文件位置
fname="Resume_"&year(date())&month(date())&day(date())&hour(now())&minute(now())&second(now())&timer()&".xls"
cname=server.MapPath(excelpath&fname)
oname=server.MapPath(temppath)
Set fs = CreateObject("Scripting.FileSystemObject") '创建文件对象
Set openfile = fs.OpenTextFile(oname,1,0)'打开模板文件
tempstr=openfile.readall'读取模板文件数据
openfile.close'关闭文件对象
if request("action")="sel" then
importid=request("id")
sql="select * from xyimportexcel where id in ("&importid&")"
set rs=server.CreateObject("adodb.recordset")
rs.open sql,conn,3,1
if rs.recordcount>200 then
response.Write("<script>alert(""符合条件的纪录过多，大于200条，请重新选择条件！"");history.back();</script>")
response.End()
end if
if not rs.eof or not rs.bof then
bodytxt=""
while not rs.eof
bodytxt=bodytxt&"<tr height=19 style='height:14.25pt'>"
bodytxt=bodytxt&"<td height=19 class=xl24 style='height:14.25pt'>"&rs("xm")&"</td>"
bodytxt=bodytxt&"<td class=xl24>"&rs("age")&"</td>"
bodytxt=bodytxt&"<td class=xl24>"&rs("sex")&"</td>"
bodytxt=bodytxt&"<td class=xl24>"&rs("jg")&"</td>"
bodytxt=bodytxt&"<td class=xl24>"&rs("workplace")&"</td>"
bodytxt=bodytxt&"<td class=xl24>"&rs("byxx")&"</td>"
zhxl=rs("l_name")
if isnull(zhxl) or zhxl="" then zhxl=rs("zhxl")
bodytxt=bodytxt&"<td class=xl24>"&zhxl&"</td>"
bodytxt=bodytxt&"<td class=xl24>"&rs("zzmm")&"</td>"
bodytxt=bodytxt&"<td class=xl24>"&rs("byxx")&"</td>"
if trim(rs("pxnl"))<>"" then pxnl=(replace(trim(rs("pxnl")),chr(10),"<br>"))
bodytxt=bodytxt&"<td>"&pxnl&"</td>"
bodytxt=bodytxt&"<td class=xl24>"&rs("wysp")&"</td>"
bodytxt=bodytxt&"<td class=xl24>"&rs("jsjsp")&"</td>"
bodytxt=bodytxt&"<td class=xl24>"&rs("qwgzsr")&"</td>"
bodytxt=bodytxt&"<td>联系地址："&trim(rs("lxdz"))&"<br>邮政编码："&trim(rs("yb"))&" <br>电话："&trim(rs("dh"))&"<br>电子邮件："&trim(rs("email"))&"</td>"
bodytxt=bodytxt&"<td class=xl24>"&trim(rs("resume_name"))&"</td>"
bodytxt=bodytxt&"</tr>"
rs.movenext
wend
end if
rs.close
set rs=nothing
suc=1
end if
if suc=1 then
mysheet=date()
Set newfile = fs.CreateTextFile(cname, True)
tempstr=replace(tempstr,"$importdate",mysheet)
tempstr=replace(tempstr,"$importdata",bodytxt)
newfile.write tempstr
newfile.close
session("downname")=cname
response.Redirect("down.asp")
end if
%>
1673
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
