ASP导出EXCEL问题

twinsv 2005-02-01 03:31:49

如何,用利用ASP导出文件EXCEL,设置EXCEL的打印页眉为图片呢?

...全文

476 4 打赏收藏转发到动态举报

写回复

用AI写文章

4 条回复

切换为时间正序

请发表友善的回复…

发表回复

hqcsx 2005-02-04

打赏
举报

<%
'导出xml,csv,htm文件DEMO
'用法：ExportDB.asp?t=文件类型&sql=sql语句
'文件类型可以是htm,csv,xml，默认是htm
'http://www.51windows.Net
'tablename = "htm"
Dim shijian,dingdan
shijian = now()
dingdan = year(shijian)&month(shijian)&day(shijian)&hour(shijian)&minute(shijian)&second(shijian)

dim tablename
sql2 = request("sql")
tablename = request("tablename")
if tablename = "" then
tablename = "数据导出结果"&dingdan
else
tablename = tablename&dingdan
end if
sql = replace(sql2,";","")

filetype = lcase(request("t"))
dim dbid,IndexPageName
function HTMLEncode(fString)
if not isnull(fString) then

fString = Server.HTMLEncode(fString)
fString = Replace(fString, CHR(10) & CHR(10), "</P><P> ")
fString = Replace(fString, CHR(10), "<BR> ")
fString = Replace(fString, CHR(9), " ")

HTMLEncode = fString
end if
end function

function Myreplace(str)
if not isnull(str) then
fString = Replace(fString,"""", """""")
Myreplace = str
else
Myreplace = ""
end if
end function

dim def_export_val
def_export_sep = ","
def_export_val = """"
if lcase(left(sql,6))<>"select" then
Response.write "出错了。。。"
Response.end
end if
Set rs = Conn.Execute(sql)
if filetype="xml" then
Response.contenttype="text/xml"
Response.Charset = "gb2312"
Response.AddHeader "Content-Disposition", "attachment;filename="&tablename&".xml"
Response.write "<?xml version=""1.0"" encoding=""gb2312""?>"
Response.write vbnewline&"<root>"
strLine=""
dim thefield(50)
i = 0
For each x in rs.fields
thefield(i)=x.name
i=i+1
Next
While rs.EOF =false
strLine= vbnewline&chr(9)&"<row>"
k=0
For each x in rs.fields
strLine= strLine & vbnewline&chr(9)&chr(9)&"<"&thefield(k)&">"
if instr(x.value,"<") or instr(x.value,">") or len(x.value)>255 then
strLine= strLine &"<![CDATA["& x.value &"]]>"
else
strLine= strLine & x.value
end if
strLine= strLine &"</"&thefield(k)&">"

k=k+1
Next
rs.MoveNext
Response.write strLine &vbnewline& chr(9)&"</row>"
Wend
Response.write vbnewline&"</root>"
elseif filetype="csv" then
'Response.contenttype="application/vnd.ms-excel"
Response.contenttype="text/csv"
Response.AddHeader "Content-Disposition", "attachment;filename="&tablename&".csv"
strLine=""
For each x in rs.fields
strLine= strLine & def_export_val & x.name & def_export_val & def_export_sep
Next
Response.write strLine & vbnewline
While rs.EOF =false
strLine= ""
For each x in rs.fields
strLine= strLine & def_export_val & trim(Myreplace(x.value)) & def_export_val & def_export_sep
Next
rs.MoveNext
Response.write strLine & vbnewline
Wend
else
if filetype="htm" then'弹出下载html的对话框
Response.contenttype="application/ms-download"
Response.AddHeader "Content-Disposition", "attachment;filename="&tablename&".htm"
end if
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">

<title>导出数据。。</title>
<style>

</style>
</head>
<BODY style="overflow:auto;" topmargin=2 bgcolor=buttonface oncontextmenu=self.event.returnValue=false >
<div align="center">
<table border="0" cellpadding="0" cellspacing="1" bgcolor="#000000" width="100%">
<tr>
<%
i=0
For each x in rs.fields
strLine= strLine &chr(9)&chr(9)&"<th align=""center"">"& x.name &"</th>"& vbnewline
Next
Response.write strLine&chr(9)&"</tr>"& vbnewline & vbnewline
While rs.EOF =false
i=i+1
Response.write chr(9)&"<tr>"& vbnewline
strLine= ""
For each x in rs.fields
strLine= strLine &chr(9)&chr(9)&"<td>"& HTMLEncode(x.value) &"</td>"& vbnewline
Next
rs.MoveNext
Response.write strLine
Response.write chr(9)&"</tr>"& vbnewline & vbnewline
Wend
Response.write "</table>"& vbnewline
if filetype<>"htm" and filetype<>"xls" and filetype<>"txt" then
Response.write "<p style='line-height:160%;'>"&i&"条记录 <a href='?tablename="&request("tablename")&"&t=htm&sql="&server.urlencode(sql2)&"'>导出HTML</a>|<a href='?tablename="&request("tablename")&"&t=csv&sql="&server.urlencode(sql2)&"'>导出EXCEL</a>|<a href='?tablename="&request("tablename")&"&t=xml&sql="&server.urlencode(sql2)&"'>导出XML</a>"& vbnewline
end if
'Response.write "<p>Power by <A HREF=""http://www.51windows.Net"" target=""_blank"">51windows.Net</A>"& vbnewline
Response.write "</div>"& vbnewline
Response.write "</BODY>"& vbnewline
Response.write "</HTML>"& vbnewline
%>

<%
end if

rs.close
conn.close
Set rs=nothing
Set conn=nothing
%>

twinsv 2005-02-01

打赏
举报

以上方法不行的,
Set fs = server.CreateObject("scripting.filesystemobject")
ActiveX 部件不能创建对象

hackate 2005-02-01

打赏
举报

Set fs = server.CreateObject("scripting.filesystemobject")
filename = Request.ServerVariables("APPL_PHYSICAL_PATH") & "report\excel\a.xls"
if fs.FileExists(filename) then
fs.DeleteFile(filename)
end if

set myfile = fs.CreateTextFile(filename,true)
totalamount=0
if not rs.EOF and not rs.BOF then
dim strLine,responsestr
strLine=""
strLine= "No." & chr(9)&"合同"&chr(9)&Request.Form("selmethod1")&chr(9)&Request.Form("selmethod2")&chr(9)&Request.Form("selmethod3")&chr(9)&"签订日期"&chr(9)&"金额"&chr(9)&"所有者"

myfile.writeline strLine
i=1
Do while Not rs.EOF
strLine=""
strLine= i&chr(9)&rs("account") & chr(9)&rs(session("reportfield1")) & chr(9)&rs(session("reportfield2")) & chr(9)&rs(session("reportfield3")) & chr(9)&rs("createdate") & chr(9)&formatnumber(rs("totalamount"),2)& chr(9)&rs("owner")
myfile.writeline strLine
i=i+1
totalamount=totalamount+ccur(rs("totalamount"))
rs.MoveNext
loop

strLine=""
strLine= "合计"&chr(9)&chr(9)&chr(9)&chr(9)&chr(9)&chr(9)&formatnumber(totalamount,2)&chr(9)
myfile.writeline strLine
end if

这样就可以了！

chucb 2005-02-01