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

if object_id('procExcelExportMultiSheets','p')isnot 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),@outint,@descnvarchar(255)

        ,@sheetCountint,@iint,@topCountint

        ,@wherevarchar(1000),@fromvarchar(1000)

        ,@myWherevarchar(1000),@filenamevarchar(250)

 

declare @obj int,@constrnvarchar(1000),

        @sql varchar(8000),@fdlistvarchar(8000)

        ,@totalCountint,@pageCountint,@mySqlvarchar(8000) 

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

    create table#pageTb(totalCountint)

    --获取到表的名称

    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=totalCountfrom #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(abit,bbit,cbit) 

    --创建表的SQL

    declare @tbnamesysname 

    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.namein('char','nchar','varchar','nvarchar')then 

            'text('+cast(casewhen a.length>255then 255 else a.length end as varchar)+')' 

            whenb.namein('tynyint','int','bigint','tinyint')then 'int' 

            whenb.namein('smalldatetime','datetime')then 'datetime' 

            whenb.namein('money','smallmoney')then 'money'

            whenb.namein('uniqueidentifier')then 'varchar'

            elseb.nameend 

    FROM tempdb..syscolumnsa left join tempdb..systypesb on a.xtype=b.xusertype 

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

        and a.id=(selectid from tempdb..sysobjectswhere name=@tbname) 

 

set @filename=@path+@fname

truncate table#tb

insert into #tb exec master..xp_fileexist@filename

    --连接EXCEL

    if exists(select 1from #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',@objout 

    if @err<>0goto lberr 

    exec @err=sp_oamethod@obj,'open',null,@constr 

    if @err<>0goto lberr

    set @i=1

while @i<=@sheetCount

begin

    --创建sheet语句

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

    --创建sheet

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

    if @err<>0goto 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(@topCountas 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(@ias varchar)+'$])'

    set ansi_nullson

    set ansi_warningson

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

    set @i=@i+1

end

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

exec(@sql)

set ansi_nullsoff

set ansi_warningsoff

return 

lberr: 

    exec sp_oageterrorinfo0,@srcout,@descout 

lbexit: 

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

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

    select @sql,@constr,@fdlist

go

 

 

 

 

--调用存储过程

exec procExcelExportMultiSheets'select * from dbo.CS_DEPT_1'

    ,'VGUID'

    ,'D:\'

    ,'users.xls'

    ,'userinfo'