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'