28,391
社区成员
发帖
与我相关
我的任务
分享
<!--#include file="func\func.asp" -->
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=big5">
<title>xxx明细表导出</title>
</head>
<script type="text/javascript">
function closeEvent(FileStr)
{
location.href = "/test/filedel.asp?delpath="+FileStr;
}
function fileDelete(FileStr){
location.href = "/test/filedel.asp?delpath="+FileStr;
window.close();
}
</script>
<%
Dim location
date1 = Format_Time(Request("date1"),2)
date2 = Format_Time(Request("date2"),2)
If date1 = date2 Then
tablename = date1&"xxx明细表"
Else
tablename = date1&"~"&date2&"xxx明细表"
End If
FileStr = "/test/xls/"&tablename&".xlsx"
%>
<body onbeforeunload="closeEvent('<%=FileStr%>')">
<%
Set rs = Server.CreateObject("Adodb.Recordset")
sql = "SELECT * "
sql = sql + "FROM templetable "
sql = sql + "WHERE ACCOUNTDATE BETWEEN '"&date1&"' AND '"&date2&"' "
rs.Open sql,Sql05Conn,1,1
title = Array("aaa","bbb","ccc","ddd","eee")
Set xlApp = Server.CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Add
'xlApp.Visible = True '是否显示exce程序
xlApp.DisplayAlerts = False '关闭覆盖询问的对话框
Set xlWs = xlWb.Worksheets(1)
xlWs.Activate
For i=0 to UBound(title)
xlWs.cells(1,i+1).value = title(i)
xlWs.cells(1,i+1).Interior.colorindex = 15 '设置字段背景颜色
'xlWs.cells(1,i+1).horizontalalignment = 3 '列位置中
Next
xlWs.select
xlWs.Name = tablename
xlWs.Range("A2").CopyFromRecordset rs '从A2贴上,因A1要放字段名
xlWs.range("A:Z").font.size = 10 '设置字体大小
xlWs.Range("A:Z").Columns.AutoFit '自动调整栏宽
xlWb.worksheets(tablename).Select
location = Server.MapPath ("/test/xls/"&tablename&".xlsx")
xlApp.ActiveWorkbook.Saveas location '保存文件
xlApp.ActiveWorkbook.Close
xlApp.Application.Quit
rs.close
Set rs = Nothing
Sql05Conn.close
Set Sql05Conn = Nothing
%>
<a href="<%=FileStr%>">点我下载Excel档</a>
<br><br>
<input type="button" name="close" value="关闭" onClick="fileDelete('<%=FileStr%>')">
</body>
</html>
Response.AddHeader "Content-Disposition","attachment;filename="&tablename&".xls"
Response.ContentType="application/vnd.ms-excel"
这样是可以汇出Excel档,但是有缺点,有时候直接开启会打不开,要下载才可以打开
然后又会有开启慢的问题,所以才会使用Excel.Application的方式。
谢谢!