asp中xls的下载提示将页面下载到excel而不是数据

Question

我正在尝试设置一个下载 sql 表并提示用户将其保存到 Excel 的页面。这将被用在名为 IE7 的可怕东西上。

问题是，它没有下载我知道存在的文件，而是将 asp 页面生成到 xls 文件中，并将其标题为“DataManager.xls”。因此，如果我打开它，我会得到 asp 页面的页眉栏，如果我省略 resonse.end，我会得到页面正文和页脚。但我所有的研究都表明这段代码应该下载现有文件，而不是创建文件。

我的 response.write 跟踪器都不起作用。但 strSQL 正在正确运行，因为它会生成该文件。

        dim strSQL

    if(Request.QueryString("submitbutton") = "Download") then
        dim fn
        dim FPath
        strSQL = "EXECute [TABLE2EXCEL] 'S007\SQLSRV','" & UCase(request.QueryString("SelTABLE")) & "','" & UCase(request.QueryString("SelTABLE")) & "','" & Replace(Request.ServerVariables("LOGON_USER"),"CORP\","") & "'"
        fn = cnt.execute(strSQL)

        response.write strSQL
        response.write fn
        response.write FPath

         Response.ContentType = "application/vnd.ms-excel" ' arbitrary 
        FPath = fn 
        Response.AddHeader "Content-Disposition","attachment; filename=ASSOCIATEROSTERFeb102012909AMNBKMZEJ.xls" '& fn 

        Set adoStream = CreateObject("ADODB.Connection") 
        adoStream.Open() 
        adoStream.Type = 2
        adoStream.LoadFromFile("\\s007\folder\DataFactory\ASSOCIATEROSTERFeb102012909AMNBKMZEJ.xls")'(FPath) 
        Response.BinaryWrite adoStream.Read() 
        adoStream.Close 
        Set adoStream = Nothing 
Response.End 
end if

为了完整的数据，这是我的 sql 代码：

ALTER procedure [dbo].[TABLE2EXCEL]
(
    @db_name    varchar(100),
    @table_name varchar(100),   
    @file_name  varchar(100),
    @NBK    varchar(10)
)
as

--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)

select 
    @columns=coalesce(@columns+',','')+column_name+' as '+column_name 
from 
    information_schema.columns
where 
    table_name=@table_name
select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

declare @TimeStamp varchar(50)
SELECT @TimeStamp = Replace(Replace(CAST(CONVERT(datetime,getdate())as varchar),':',''),' ','') + @NBK

print @TimeStamp

--Set folder for output file
set @file_name = '\\s007\folder\DataFactory\' + @file_name + @TimeStamp + '.xls'

--Create a dummy file to have actual data
select @data_file='\\s007\folder\DataFactory\data_fildat' + @TimeStamp + '.xls'--substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_filez.xls'
print @data_file
--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c -S S007\SQLSRV -U userlogin -P passwordisbestpassword -k'''
exec(@sql)

--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp " select * from '+@table_name+'" queryout "'+@data_file+'" -c  -S S007\SQLSRV -U userlogin -P passwordisbestpassword -k'''

exec(@sql)
PRINT @sql 
PRINT @data_file 

--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type "'+@data_file+'" >> "'+@file_name+'"'''
exec(@sql)

--Delete dummy file 
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql)


return @file_name

Answer 1

通常，这是通过生成包含其中数据的常规 HTML 表并发送 Excel MIME 类型来完成的。

Response.ContentType = "application/vnd.ms-excel"

（然后输出 HTML 表）

请参阅此问题的示例： 从经典 ASP 导出数据到 Excel 文件失败