（转）使用Sql语句导出Excel

转自 http://edu.codepub.com/2011/0426/31119.php

if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[p_exporttb] ' )  and   OBJECTPROPERTY (id, N ' IsProcedure ' )  =   1 )
drop   procedure   [ dbo ] . [ p_exporttb ]
GO

create   proc  p_exporttb
@sqlstr  sysname, -- 查询语句,如果查询语句中使用了order by ,请加上top 100 percent,注意,如果导出表/视图,用上面的存储过程
@path   nvarchar ( 1000 ), -- 文件存放目录
@fname   nvarchar ( 250 ), -- 文件名
@sheetname   varchar ( 250 ) = '' -- 要创建的工作表名,默认为文件名
as
declare   @err   int , @src   nvarchar ( 255 ), @desc   nvarchar ( 255 ), @out   int
declare   @obj   int , @constr   nvarchar ( 1000 ), @sql   varchar ( 8000 ), @fdlist   varchar ( 8000 )

-- 参数检测
if   isnull ( @fname , '' ) = '' set   @fname = ' temp.xls '
if   isnull ( @sheetname , '' ) = ''   set   @sheetname = replace ( @fname , ' . ' , ' # ' )

-- 检查文件是否已经存在
if   right ( @path , 1 ) <> ' \ '   set   @path = @path + ' \ '
create   table  #tb(a  bit ,b  bit ,c  bit )
set   @sql = @path + @fname
insert   into  #tb  exec  master..xp_fileexist  @sql

-- 数据库创建语句
set   @sql = @path + @fname
if   exists ( select   1   from  #tb  where  a = 1 )
set   @constr = ' DRIVER={Microsoft Excel Driver (*.xls)};DSN= '''' ;READONLY=FALSE '
     + ' ;CREATE_DB=" ' + @sql + ' ";DBQ= ' + @sql
else
set   @constr = ' Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 5.0;HDR=YES '
+ ' ;DATABASE= ' + @sql + ' " '

-- 连接数据库
exec   @err = sp_oacreate  ' adodb.connection ' , @obj  out
if   @err <> 0   goto  lberr

exec   @err = sp_oamethod  @obj , ' open ' , null , @constr
if   @err <> 0   goto  lberr

-- 创建表的SQL
declare   @tbname  sysname
set   @tbname = ' ##tmp_ ' + convert ( varchar ( 38 ), newid ())
set   @sql = ' select * into [ ' + @tbname + ' ] from( ' + @sqlstr + ' ) a '
exec ( @sql )

select   @sql = '' , @fdlist = ''
select   @fdlist = @fdlist + ' , ' + a.name
, @sql = @sql + ' ,[ ' + a.name + ' ]  '
+ case   when  b.name  in ( ' char ' , ' nchar ' , ' varchar ' , ' nvarchar ' )  then
' text( ' + cast ( case   when  a.length > 255   then   255   else  a.length  end   as   varchar ) + ' ) '
when  b.name  in ( ' tynyint ' , ' int ' , ' bigint ' , ' tinyint ' )  then   ' int '
when  b.name  in ( ' smalldatetime ' , ' datetime ' )  then   ' datetime '
when  b.name  in ( ' money ' , ' smallmoney ' )  then   ' money '
else  b.name  end
FROM  tempdb..syscolumns a  left   join  tempdb..systypes b  on  a.xtype = b.xusertype
where  b.name  not   in ( ' image ' , ' text ' , ' uniqueidentifier ' , ' sql_variant ' , ' ntext ' , ' varbinary ' , ' binary ' , ' timestamp ' )
and  a.id = ( select  id  from  tempdb..sysobjects  where  name = @tbname )
select   @sql = ' create table [ ' + @sheetname
+ ' ]( ' + substring ( @sql , 2 , 8000 ) + ' ) '
, @fdlist = substring ( @fdlist , 2 , 8000 )

exec   @err = sp_oamethod  @obj , ' execute ' , @out  out, @sql
if   @err <> 0   goto  lberr

exec   @err = sp_oadestroy  @obj

-- 导入数据
set   @sql = ' openrowset( '' MICROSOFT.JET.OLEDB.4.0 '' , '' Excel 5.0;HDR=YES
;DATABASE= ' + @path + @fname + ''' ,[ ' + @sheetname + ' $]) '

exec ( ' insert into  ' + @sql + ' ( ' + @fdlist + ' ) select  ' + @fdlist + '  from [ ' + @tbname + ' ] ' )

set   @sql = ' drop table [ ' + @tbname + ' ] '
exec ( @sql )
return

lberr:
exec  sp_oageterrorinfo  0 , @src  out, @desc  out
lbexit:
select   cast ( @err   as   varbinary ( 4 ))  as  错误号
, @src   as  错误源, @desc   as  错误描述
select   @sql , @constr , @fdlist
go

/*
调用示例：
p_exporttb @sqlstr='select * from dbo.AE_Equipment'
,@path='c:\',@fname='aa.xls',@sheetname='设备信息' */

  

第二种导出excel的办法：

hostname是主机名，-U"sa" -P"sa"'分别是sql用户名和密码

EXEC master..xp_cmdshell 'bcp "select * from test..test" queryout "c:\test.xls" -c -S"hostname" -U"sa" -P"sa"' 

转载于:https://www.cnblogs.com/mxw09/archive/2011/08/15/2139102.html