怎么在sql的存储过程中用代码将所查询到表的数据导出到execl表中

--导出excel处理declare @s nvarchar(4000)set @s='bcp "'++N'select top 100 percent '+N' case c.colid when 1 then o.name else N'''' end as 表名,'+N' c.colid as 序号,'+N' c.name as 字段名,'+N' t.name 数据类型,'+N' c.prec as 长度,'+N' p.value as 字段说明,'+N' m.text as 默认值'+N' from '+quotename(@dbname)+N'.dbo.sysobjects o'+N' inner join '+quotename(@dbname)+N'.dbo.syscolumns c on o.id=c.id'+N' inner join '+quotename(@dbname)+N'.dbo.systypes t on c.xusertype=t.xusertype'+N' left join '+quotename(@dbname)+N'.dbo.sysproperties p on c.id=p.id and c.colid = p.smallid'+N' left join '+quotename(@dbname)+N'.dbo.syscomments m on c.cdefault=m.id'+N' where o.xtype in(N''U'') and o.status>=0'+N' order by c.id,c.colid'+N'" queryout "[email protected]+N'" /P"" /w'exec master..xp_cmdshell @s,no_outputgo--查询sqlserver中直接通过代码访问excelSELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions甚至可以：DELETE FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',  'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions你还可以：EXEC sp_addlinkedserver 'ExcelSource',   'Jet 4.0',   'Microsoft.Jet.OLEDB.4.0',   'c:\Temp\Part.xls',   NULL,   'Excel 5.0'GOEXEC sp_addlinkedsrvlogin 'excelsource', 'false', NULL, NULL, NULL在excel中选定范围,选择菜单'插入'->'名称'->'定义'示例SELECT *FROM EXCEL...SalesData  (整页使用 Sheet1$) 如果碰到转换为NULL值请使用文本文件来做DTS转换
------解决方案--------------------
别人的 改的

你自己改改就可以了
SET QUOTED_IDENTIFIER ON  
GO
SET ANSI_NULLS ON  
GO



















/*--数据导出EXCEL
导出表中的数据到Excel,包含字段名,文件为真正的Excel文件
,如果文件不存在,将自动创建文件
,如果表不存在,将自动创建表
基于通用性考虑,仅支持导出标准数据类型
--邹建 2003.10(引用请保留此信息)--*/
/*--调用示例
p_exporttb @SqlTableName='地区资料',@filePath='c:\',@excelTableName='aa.xls'
--*/
ALTER                proc p_exporttb
@SqlTableName sysname,--要导出的表名
@filePath nvarchar(1000),--文件存放目录
@excelTableName nvarchar(250)='',--文件名
@excelSheetName nvarchar(250) = 'sheet1' , --表名  
@tmBgn datetime = '1970-1-1 8:00',
@tmEnd  datetime = '1970-1-1 8:00'
as
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)
declare @ibgn int
declare @iend int
declare @filterstr nvarchar(255)
--参数检测
if isnull(@excelTableName,'')=''set @[email protected]+'.xls'

select @ibgn = datediff(ss,'1970-1-1 8:00',@tmBgn)
select @iend = datediff(ss,'1970-1-1 8:00',@tmend)
select @filterstr = ' where EventTime >' +cast(@ibgn as varchar(12))+ ' and EventTime <' + cast(@iend as varchar(12))
--检查文件是否已经存在
if right(@filePath,1)<>'\' set @[email protected]+'\'
create table #tb(a bit,b bit,c bit)
set @[email protected][email protected]
insert into #tb exec master..xp_fileexist @sql
--数据库创建语句
set @[email protected][email protected]
if exists(select 1 from #tb where a=1)
set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'