SQLServer2000把指定查询输出为Excel文件的存储过程

 

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



ALTER   proc stp_ExportTable
    @sqlstr nvarchar(4000),        --查询语句,如果查询语句中使用了order by ,请加上top 100 percent,注意,如果导出表/视图,用上面的存储过程
    @orderstr nvarchar(255),    --Order by Field
    @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(4000),@fdlist varchar(8000)
    declare @IstmpTB as bit
    declare @tmpsql  as varchar(4000)


    SET @IstmpTB=0
    --参数检测
    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 8.0;HDR=YES'
        +';DATABASE='+@sql+'"'



    --连接数据库
    --print 'nn33'
    exec @err=sp_oacreate 'adodb.connection',@obj out
    if @err<>0 goto lberr
    --print 'nn44'
    exec @err=sp_oamethod @obj,'open',null,@constr
    if @err<>0 goto lberr

    --构造temp表的SQL
    declare @tbname sysname
    set @tbname='##tmp_'+convert(varchar(38),newid())
    set @sql='select * into ['+@tbname+'] from('+@sqlstr+') a'
    --print @sql
    exec(@sql)
    set @IstmpTB=1
    
    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('bit','int','bigint','tinyint','smallint') 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=substring(@sql,2,2000),@fdlist=substring(@fdlist,2,2000)

    --create table
    select @sql='create table ['+@sheetname+']('+@sql+')'
    --print @sql
    exec @err=sp_oamethod @obj,'execute',@out out,@sql
    if @err<>0 goto lberr
    --print 'nn'
    --destroy ole object
    exec @err=sp_oadestroy @obj
    if @err<>0 goto lberr
    --print 'nn1'
    --导入数据
    
    set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES
       ;DATABASE='+@path+@fname+''',['+@sheetname+'$])'
    
    set @tmpsql='insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+']'
    --print @tmpsql
    if @orderstr is not null or @orderstr<>''
    begin
        set @tmpsql=@tmpsql+' order by '+@orderstr
    end
    --print @tmpsql    
    exec(@tmpsql)
    
    set @sql='drop table ['+@tbname+']'
    exec(@sql)
    set @IstmpTB=0
    
    return 0

lberr:
   
       EXEC sp_displayoaerrorinfo @obj, @err
    --DELETE TmpTable While Error 
    IF @IstmpTB=1 
    BEGIN
        set @sql='drop table ['+@tbname+']'
        exec(@sql)
    END
    return -1
lbexit:
    SELECT @sql,@constr,@fdlist


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO