IF OBJECT_ID(''dbo.Proc_DBA_ExportExcel'') IS NOT NULL
 DROP PROC dbo.Proc_DBA_ExportExcel 
GO
CREATE  PROC dbo.Proc_DBA_ExportExcel 
 @sqlstr varchar(8000),--查詢語句,如果查詢語句中使用了order by ,請(qǐng)加上top 100 percent 
 @path nvarchar(1000),--文件存放目錄 
 @fname nvarchar(250),--文件名 
 @sheetname varchar(250)=''''--要?jiǎng)?chuàng)建的工作表名,默認(rèn)為文件名 
AS
BEGIN
 declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int 
 declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000) 
  
 --參數(shù)檢測 
 if isnull(@fname,'''')=''''set @fname=''temp2012.xls'' 
 if isnull(@sheetname,'''')='''' set @sheetname=replace(@fname,''.'',''#'') 
  
 --檢查文件是否已經(jīng)存在 
 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 
  
 --數(shù)據(jù)庫創(chuàng)建語句 
 set @sql=@path+@fname 
 if exists(select 1 from #tb where a=1) 
 set @constr=''DRIVER={Microsoft Excel Driver (*.xlsx)};DSN='''''''';READONLY=FALSE'' 
 +'';CREATE_DB="''+@sql+''";DBQ=''+@sql 
 else 
 set @constr=''Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties="Excel 8.0;HDR=YES'' 
 +'';DATABASE=''+@sql+''"'' 
  
 --連接數(shù)據(jù)庫 
 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 
  
 --創(chuàng)建表的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 like ''%char'' 
 then case when a.length>255 then ''memo'' 
 else ''text(''+cast(a.length as varchar)+'')'' end 
 when b.name like ''%int'' or b.name=''bit'' then ''int'' 
 when b.name like ''%datetime'' then ''datetime'' 
 when b.name like ''%money'' then ''money'' 
 when b.name like ''%text'' then ''memo'' 
 else b.name end 
 FROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertype 
 where b.name not in(''image'',''uniqueidentifier'',''sql_variant'',''varbinary'',''binary'',''timestamp'') 
 and a.id=(select id from tempdb..sysobjects where name=@tbname) 
  
 if @@rowcount=0 return 
  
 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 
  
 --導(dǎo)入數(shù)據(jù) 
 set @sql=''openrowset(''''Microsoft.ACE.OLEDB.12.0'''',''''Excel 8.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 錯(cuò)誤號(hào) 
 ,@src as 錯(cuò)誤源,@desc as 錯(cuò)誤描述 
 select @sql,@constr,@fdlist 
END
GO

使用：
sp_configure ''Ole Automation Procedures'', ''1''
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC dbo.Proc_DBA_ExportExcel
 @sqlstr=''SELECT * FROM [master].dbo.spt_values AS sv''
 ,@path=''d:\''
 ,@fname=''test.xlsx''
 ,@sheetname=''測試''
GO
sp_configure ''Ole Automation Procedures'', ''0''
GO
RECONFIGURE WITH OVERRIDE
GO