Solve the problem of ASP exporting query results to the ID number in Excel
Author：Eve Cole
Update Time：2009-06-23 17:01:06
Recently, when using asp to export the query results of the access database to an excel file, a problem occurred because one field is the ID number. In the exported excel file, the last three digits of all ID numbers became 0! How depressing! After checking online, I found out that the default number format of Excel is "normal", which can only display the input 11 digits normally. If the number exceeds 11 digits, it will be displayed in scientific notation.
In order to solve this problem, I can only mark the query data as text data. The following is part of the code:
<%
'Export to excel
set rs = server.createobject("adodb.recordset")
sqlstr="select * from tabname where"&searhsql 'query statement
rs.open sqlstr,oconn,1,3
%>
<%set fs = server.createobject("script_ing.filesystemobject")
'Save files
filename="info.xls"
temp=filename
filename=request.servervariables("appl_physical_path")&"\"+filename
'Delete existing excel files
'if fs.fileexists(filename) then
'fs.deletefile(filename)
'end if
'-Create excel file
set myfile = fs.createtextfile(filename,true)
dim strline,responsestr
strline=""
for each x in rs.fields
strline= strline & x.name & chr(9)
next
'Write the column names of the table to excel first
myfile.writeline strline
while not rs.eof
strline=""
for each x in rs.fields
'Here the data is marked as text type
strline= strline & "="""&x.value &""""& chr(9)
next
'Write table data to excel
myfile.writeline strline
rs.movenext
wend
link="<a href=\" & temp & " title=Save data to eecel table><font color=red><b>Export excel file</b></font></a> "
response.write link
set myfile = nothing
set fs=nothing
rs.close
%>
Example source code download: http://code.knowsky.com/down/12254.html
