sql2005 通过存储过程生成 Excel

  USE testexcel
  GO
  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  
 
   
  /*--调用示例
  select c.classeName [班级名],s.name [学生姓名],s.sex [性别年龄] from tb_studen s left join classe c on s.id = c.classeId  
  p_exporttb @sqlstr='select id as [学号], name as [姓名],sex [性别年龄] from tb_student',@path='D:/SqlServer2005/Excel',@fname='Exce.xls',@over=1  
  p_exporttb @sqlstr='select c.classeName [班级名],s.name [学生姓名],s.sex [性别年龄] from tb_student s left join classe c on s.id = c.classeId',@path='D:/SqlServer2005/Excel',@fname='Exce.xls',@over=1  

  --*/  
  CREATE PROC p_exporttb  
  @sqlstr varchar(8000),  --要导出的查询语句  
  @path nvarchar(1000),   --文件存放目录  
  @fname nvarchar(250),   --文件名,默认为temp  
  @over bit=0             --是否覆盖已经存在的文件,如果不覆盖,则直接追加  
  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='temp64.xls'  
  set @fname =replace(@fname,'.xls',year(getdate())*1000+month(getdate())*100+day(getdate()))+'.xls'

  --检查文件是否已经存在  
  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  

  if exists(SELECT 1 FROM #tb WHERE a=1)  
  if @over=1  
  begin  
  set @sql='del '+@sql  
  exec master..xp_cmdshell @sql,no_output  
  end  
  else  
  set @over=0  
  else  
  set @over=1  
   
  --数据库创建语句  
  set @sql=@path+@fname 
 
  set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 4.0'  
  +';HDR=YES;DATABASE='+@path+'"'  

   
  --创建表的SQL  
  declare @tbname sysname  
  set @tbname='##tmp_'+convert(varchar(38),newid())  
  set @sql='select * into ['+@tbname+'] from('+@sqlstr+') a'  
  exec(@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  
  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>250 then 250 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 ['+@fname  
  +']('+substring(@sql,2,8000)+')'  
  ,@fdlist=substring(@fdlist,2,8000)  
   
  if @over=1  
  begin  
  exec @err=sp_oamethod @obj,'execute',@out out,@sql  
  if @err<>0 goto lberr  
  end  
   
  exec @err=sp_oadestroy @obj  

  set @sql='Openrowset(''Microsoft.Jet.OLEDB.4.0'',''Excel 4.0;HDR=YES;DATABASE='+@path+@fname+''',''select * from ['+@fname+']'')'
   
  --导入数据  
  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

 

一般情况下再运行下：

Regsvr32 c:/windows/system32/msexcl40.dll