利用SQL脚本把数据库中的数据导出到Excel

if object_id('procExcelExportMultiSheets','p')is not null

drop proc procExcelExportMultiSheets

go

create proc [dbo].[procExcelExportMultiSheets]

    @sqlstr nvarchar(4000),           --查询语句,如果使用了orderby ,请加上top 100 percent 

    @primaryKey varchar(100),         --分页主键字段

    @path nvarchar(1000),             --文件存放目录 

    @fname nvarchar(250),             --文件名 

    @sheetname varchar(250)='Sheet1'  --要创建的工作表名,默认为文件名 

as  

set nocount on

declare @err int,@src nvarchar(255),@out int,@desc nvarchar(255)

        ,@sheetCount int,@i int,@topCount int

        ,@where varchar(1000),@from varchar(1000)

        ,@myWhere varchar(1000),@filename varchar(250)

 

declare @obj int,@constr nvarchar(1000),

        @sql varchar(8000),@fdlist varchar(8000)

        ,@totalCount int,@pageCount int,@mySql varchar(8000) 

    --创建临时表，存放数据，主要存放行计数

    create table #pageTb(totalCount int)

    --获取到表的名称

    set @from=substring(@sqlstr,charindex('from',@sqlstr)+5,len(@sqlstr)-charindex('from',@sqlstr)+1)

    --判断如果传进来的SQL语句是带条件的

    if charindex('where',@sqlstr)>0

    --得到where条件

    set @where=substring(@sqlstr,charindex('where',@sqlstr)+6,len(@sqlstr)-charindex('where',@sqlstr)+1)

    --否则给个空值

    else set @where=''

    set @pageCount=65000

    set @sql='select count(*) from ('+@sqlStr+') a'

    --把统计出的行计数插入到临时表中

    insert into #pageTb execute (@sql)

    --给变量赋值

    select @totalCount=totalCount from #pageTb

    --得出要导出的sheet数量

    if @totalCount>@pageCount

        set @sheetCount=@totalCount/@pageCount+1

    else

        set @sheetCount=1

    --参数检测

    --如果文件名为空，给它默认值

    if isnull(@fname,'')=''

        set @fname='temp.xls' 

    --如果工作表名为空，给它默认值

    if isnull(@sheetname,'')=''

        set @sheetname=replace(@fname,'.','#') 

    --检查文件是否已经存在 

    if right(@path,1)<>'\'

        set @path=@path+'\' 

    --创建#tab临时表

    create table #tb(a bit,b bit,c bit) 

    --创建表的SQL

    declare @tbname sysname 

    set @tbname='##tmp_'+convert(varchar(38),newid()) 

    set @sql='select top 1 * 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)+')' 

            whenb.name in('tynyint','int','bigint','tinyint') then 'int' 

            whenb.name in('smalldatetime','datetime') then 'datetime' 

            whenb.name in('money','smallmoney') then 'money'

            whenb.name in('uniqueidentifier') then 'varchar'

            elseb.name end 

    FROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertype 

    where b.name not in('image','text','sql_variant','ntext','varbinary','binary','timestamp') 

        and a.id=(select id from tempdb..sysobjects where name=@tbname) 

 

set @filename=@path+@fname

truncate table #tb

insert into #tb exec master..xp_fileexist@filename

    --连接EXCEL

    if exists(select 1 from #tb where a=1) 

        set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE;CREATE_DB="'+@filename+'";DBQ='+@filename

    else 

        set @constr='Provider=Microsoft.Jet.OLEDB.4.0;ExtendedProperties="Excel 8.0;HDR=YES;DATABASE='+@filename+'"'  

    --创建Excel文件

    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

    set @i=1

while @i<=@sheetCount

begin

    --创建sheet语句

    set @mySql='create table ['+@sheetname+cast(@i as varchar)+']('+substring(@sql,2,8000)+')' 

    --创建sheet

    exec @err=sp_oamethod@obj,'execute',@out out,@mySql 

    if @err<>0 goto lberr   

    set @i=@i+1 

end

    --关闭Excel

    exec   @err=sp_oamethod   @obj,'close',null    

    if   @err<>0   goto   lberr 

    exec @err=sp_oadestroy@obj

    set @fdlist=substring(@fdlist,2,8000)

    set @i=1

--导入数据 

while @i<=@sheetCount

begin

    set @topCount=(@i-1)*@pageCount

    set @sql='-1'

    if @topCount<>0

    begin

        set @sql='select top '+cast(@topCount as varchar)+' '+@primaryKey+' from '+@from+' order by '+@primaryKey+' asc'

        set @sql='select max('+@primaryKey+') from ('+@sql+') a'

    end

    if @where<>''

    begin

        set @myWhere=@primaryKey+' >('+@sql+') and '+@where

        set @sql='select top '+convert(varchar,@pageCount)+' '+@fdlist+' from '+substring(@from,1,charindex('where',@from)-2)+' where '+@myWhere

    end

    else

    begin

        set @myWhere=@primaryKey+' >('+@sql+') '

        set @sql='select top '+convert(varchar,@pageCount)+' '+@fdlist+' from '+@from+' where '+@myWhere

    end

    set @constr='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES;DATABASE='+@filename+''',['+@sheetname+cast(@i as varchar)+'$])'

    set ansi_nulls on

    set ansi_warnings on

    execute('insert into '+@constr+'('+@fdlist+') '+@sql)

    set @i=@i+1

end

set @sql='drop table ['+@tbname+']' 

exec(@sql)

set ansi_nulls off

set ansi_warnings off

return 

lberr: 

    exec sp_oageterrorinfo0,@src out,@desc out 

lbexit: 

    select cast(@err as varbinary(4)) as 错误号 

        ,@src as 错误源,@desc as 错误描述 

    select @sql,@constr,@fdlist

go

 

 

 

 

--调用存储过程

exec procExcelExportMultiSheets'select * from dbo.CS_DEPT_1'

    ,'VGUID'

    ,'D:\'

    ,'users.xls'

    ,'userinfo'