保存excel文件出现乱码，

2012-03-26

保存excel文件出现乱码，在线等！！！withresponse.buffertrue.AddHeaderContent-Disposition ,attachment

保存excel文件出现乱码，在线等！！！
with response
.buffer = true
.AddHeader "Content-Disposition ", "attachment; fileName= " & request.Form( "sToExcel ") & ".xls "
.ContentType = "application/vnd.ms-excel "
.Flush
End with

使用上面的代码将查询结果集保存成Excel文件，但是有时中文会出现乱码。原来以为和记录数有关，但是后来减少记录数了，仍然存在这个问题。是否与excel文件的列数有关？我把excel文件的列数减半就不会出现乱码了。
请各位大侠多多指教，谢谢！

[解决办法]
一般这种情况是缓存的问题，只要在文件头加上如下代码即可：

Response.Clear() '这一句和下面这一句很重要
response.Charset = "gb2312 " '这一句很重要
Server.ScriptTimeOut = 999999 '设定超时时间长些
response.Buffer=true
Response.ContentType = "application/vnd.ms-excel "
[解决办法]
我有asp导出excel的,大数据量速度特别快.
修改以前的.三个文件
di.txt,tou.txt,OutExcel.asp
第一个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 ">

<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;}
.xl29
{mso-style-parent:style0;
mso-number-format: "\@ ";
border-top:none;
border-right:.5pt solid windowtext;
border-bottom:.5pt solid windowtext;
border-left:none;}
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>
------------------------------------------------------
第三个OutExcel.asp
------------------------------------------------------

<%
'这里放sql语句,例如:
sql= "select * from **** order by id desc "
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 fs= createobject( "scripting.filesystemobject ")
set ts=fs.createtextfile(server.MapPath(FileName),true)

Set rs=Server.CreateObject( "ADODB.RecordSet ")
rs.Open sql,conn,1,3

tou=readtext( "tou.txt ")
ts.writeline(tou)
ts.writeline( " <table width=’100%’> ")
set myfield=rs.fields

TOEXCELLR= " <tr > "
dim fieldname(50)
dim fieldtype(50)
for i=0 to myfield.count-1
toexcellr=toexcellr& " <td class=xl24> "&MYFIELD(I).NAME& " </td> "
fieldname(i)=myfield(i).name
fieldtype(i)=myfield(i).type
if myfield(i).type=135 then datename=datename&myfield(i).name& ", "
next
toexcellr=toexcellr& " </tr> "
ts.writeline(toexcellr)

do while not rs.eof
toexcellr= " <tr> "
for i=0 to myfield.count-1
if ((fieldtype(i)=3)or(fieldtype(i)=5)) then
TOEXCELLR=TOEXCELLR& " <td align=right x:num> "&rs(fieldname(i))& " </td> "
else
TOEXCELLR=TOEXCELLR& " <td class=xl29 > "&rs(fieldname(i))& " </td> "
end if
next
toexcellr=toexcellr& " </tr> "
ts.writeline(toexcellr)
rs.movenext
loop
ts.writeline( " </table> ")

di=readtext( "di.txt ")
ts.writeline(di)
ts.close
set ts=nothing
set fs=nothing

end sub
%>
<html>
<head>

<meta http-equiv= "Content-Type " content= "text/html; charset=gb2312 ">
<title> 正在生成EXLCE文件 </title>
</head>
<BODY>
生成完毕
<%
response.redirect "Excel.xls "
%>
</BODY>
</HTML>

热点排行

ASP

保存excel文件出现乱码，