记录:asp导出excel，并且格式化数值为文本
上一篇 / 下一篇 2008-03-25 11:09:51 / 个人分类：网络技术
今天做asp导出excel的时候，发现如果数值很大的话，excel会自动科学计数，手动变为文本后，excel自动把后面的尾数全部换成0000，这样的话在实际中就有很多问题。网上查了一下解决方式。记录之：
ASP/Visual Basic代码
-
-
- <%
- 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
- %>