今天做asp导出excel的时候，发现如果数值很大的话，excel会自动科学计数，手动变为文本后，excel自动把后面的尾数全部换成0000，这样的话在实际中就有很多问题。网上查了一下解决方式。记录之：

ASP/Visual Basic代码

"750" border="0" cellspacing="0" cellpadding="0" align="center">
"center">
点击这里生成excel
<%
if request("act")="" then
'response.write ("生成excel文件")
else
'dim conn,strconn
'strconn=driver=;server=wen;uid=sa;pwd=;database=db_test
'set conn=server.createobject("adodb.connection")
'conn.open strconn
dim rs,sql,filename,fs,myfile,x
set fs=server.createobject("scripting.filesystemobject")
'--假设你想让生成的excel文件做如下的存放
filename = server.mappath("data.xls")
'--如果原来的excel文件存在的话删除它
if fs.fileexists(filename) then
fs.deletefile(filename)
'response.write "dele"
end if
'--创建excel文件
set myfile = fs.createtextfile(filename,true)
set rs = server.createobject("adodb.recordset")
'--从数据库中把你想放到excel中的数据查出来
sql = "Select mid(zgbm_no,1,6) as dzdm,dwmc as x01,xmmc as x02,frdm as a05,jsdz_bm as a03,v4 as x04,v5 as x05,v6 as x06,v7 as x07,v8 as x08,v9 as x09,v10_1 as x10,v11_1 as x11,v12 as x12,v13 as x13,v14 as x14,v15 as x15,v16 as x16,v17 as x17,v18 as x18,v19 as x19,dwfzr as xdw,tjfzr as xtj,tbr as xtb,rq as xrq FROM xmzb where zgbm_no like '"+session("zgbm_no")+"%' order by id desc"
'response.write sql
rs.open sql,conn,1,1
if rs.eof and rs.bof then
else
dim strline,responsestr
strline=""
for each x in rs.fields
strline = strline & x.name & chr(9)
next
'--将表的列名先写入excel
myfile.writeline strline
do while not rs.eof
strline=""
for each x in rs.fields
strline= strline & "="""&x.value """"& chr(9)
'if x<>"" then
'strline = strline & cstr(x.value) & chr(9)
'else
'strline = strline & x.value & chr(9)
'end if
next
'--将表的数据写入excel
myfile.writeline strline
rs.movenext
loop
end if
response.write "生成成功！请 "+""+"点击这里下载"+""
rs.close
set rs = nothing
end if
%>

其中：strline= strline & "="""&x.value """"& chr(9) 就是把数值转换成文本文件。

其中top文件是数据库联接文件。内容如下：

ASP/Visual Basic代码

<%
ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("access.mdb")
Set conn = Server.CreateObject("ADODB.Connection")
conn.open ConnectString
%>

Link URL: http://www.csjrc.com/default.asp?id=89

ITPub博客

记录:asp导出excel，并且格式化数值为文本

最新文章