CodeCREATE proc sqlToMultiExcelFile
&＃64;sqlstr nvarchar(4000), --查询语句,如果查询语句中使用了order by ,请加上top 100 percent
&＃64;primaryKey varchar(100),--分页主键字段
&＃64;path nvarchar(1000), --文件存放目录
&＃64;fname nvarchar(250), --文件名
&＃64;sheetname varchar(250)&＃61;&＃39;sheet1&＃39; --要创建的工作表名,默认为文件名
as
set nocount on
declare &＃64;err int,&＃64;src nvarchar(255),&＃64;out int,&＃64;desc nvarchar(255),&＃64;sheetCount int,&＃64;i int,&＃64;topCount int,&＃64;where varchar(1000),&＃64;from varchar(1000),&＃64;myWhere varchar(1000),&＃64;filename varchar(250)
declare &＃64;obj int,&＃64;constr nvarchar(1000),&＃64;sql varchar(8000),&＃64;fdlist varchar(8000),&＃64;totalCount int,&＃64;pageCount int
create table #tbMemory
(
recId int identity(1,1) primary key,
primaryKey varchar(50)
)
create table #pageTb(totalCount int)
set &＃64;from&＃61;substring(&＃64;sqlstr,charindex(&＃39;from&＃39;,&＃64;sqlstr)&＃43;5,len(&＃64;sqlstr)-charindex(&＃39;from&＃39;,&＃64;sqlstr)&＃43;1)
if charindex(&＃39;where&＃39;,&＃64;sqlstr)>0
set &＃64;where&＃61;substring(&＃64;sqlstr,charindex(&＃39;where&＃39;,&＃64;sqlstr)&＃43;6,len(&＃64;sqlstr)-charindex(&＃39;where&＃39;,&＃64;sqlstr)&＃43;1)
else
set &＃64;where&＃61;&＃39;&＃39;
set &＃64;pageCount&＃61;65000
set &＃64;sql&＃61;&＃39;select count(*) from (&＃39;&＃43;&＃64;sqlStr&＃43;&＃39;) a&＃39;
insert into #pageTb execute (&＃64;sql)
select &＃64;totalCount&＃61;totalCount from #pageTb
insert into #tbMemory execute(&＃39;select top &＃39;&＃43;&＃64;totalCount&＃43;&＃39; &＃39;&＃43;&＃64;primaryKey&＃43;&＃39; from &＃39;&＃43;&＃64;from)
--得出要导出的sheet数量
if &＃64;totalCount>&＃64;pageCount
set &＃64;sheetCount&＃61;&＃64;totalCount/&＃64;pageCount&＃43;1
else
set &＃64;sheetCount&＃61;1
--参数检测
if isnull(&＃64;fname,&＃39;&＃39;)&＃61;&＃39;&＃39; set &＃64;fname&＃61;&＃39;temp.xls&＃39;
if isnull(&＃64;sheetname,&＃39;&＃39;)&＃61;&＃39;&＃39; set &＃64;sheetname&＃61;replace(&＃64;fname,&＃39;.&＃39;,&＃39;#&＃39;)
--检查文件是否已经存在
if right(&＃64;path,1)<>&＃39;\&＃39; set &＃64;path&＃61;&＃64;path&＃43;&＃39;\&＃39;
create table #tb(a bit,b bit,c bit)
--创建表的SQL
declare &＃64;tbname sysname
set &＃64;tbname&＃61;&＃39;##tmp_&＃39;&＃43;convert(varchar(38),newid())
set &＃64;sql&＃61;&＃39;select top 1 * into [&＃39;&＃43;&＃64;tbname&＃43;&＃39;] from(&＃39;&＃43;&＃64;sqlstr&＃43;&＃39;) a&＃39;
--print &＃64;sql
exec(&＃64;sql)
select &＃64;sql&＃61;&＃39;&＃39;,&＃64;fdlist&＃61;&＃39;&＃39;
select &＃64;fdlist&＃61;&＃64;fdlist&＃43;&＃39;,[&＃39;&＃43;a.name &＃43;&＃39;]&＃39;
,&＃64;sql&＃61;&＃64;sql&＃43;&＃39;,[&＃39;&＃43;a.name&＃43;&＃39;] &＃39;
&＃43;case when b.name in(&＃39;char&＃39;,&＃39;nchar&＃39;,&＃39;varchar&＃39;,&＃39;nvarchar&＃39;) then
&＃39;text(&＃39;&＃43;cast(case when a.length>255 then 255 else a.length end as varchar)&＃43;&＃39;)&＃39;
when b.name in(&＃39;tynyint&＃39;,&＃39;int&＃39;,&＃39;bigint&＃39;,&＃39;tinyint&＃39;) then &＃39;int&＃39;
when b.name in(&＃39;smalldatetime&＃39;,&＃39;datetime&＃39;) then &＃39;datetime&＃39;
when b.name in(&＃39;money&＃39;,&＃39;smallmoney&＃39;) then &＃39;money&＃39;
else b.name end
FROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype&＃61;b.xusertype
where b.name not in(&＃39;image&＃39;,&＃39;text&＃39;,&＃39;uniqueidentifier&＃39;,&＃39;sql_variant&＃39;,&＃39;ntext&＃39;,&＃39;varbinary&＃39;,&＃39;binary&＃39;,&＃39;timestamp&＃39;)
and a.id&＃61;(select id from tempdb..sysobjects where name&＃61;&＃64;tbname)
--创建sheet语句
set &＃64;sql&＃61;&＃39;create table [&＃39;&＃43;&＃64;sheetname&＃43;&＃39;](&＃39;&＃43;substring(&＃64;sql,2,8000)&＃43;&＃39;)&＃39;
set &＃64;i&＃61;1
while &＃64;i<&＃61;&＃64;sheetCount
begin
set &＃64;filename&＃61;&＃64;path&＃43;cast(&＃64;i as varchar)&＃43;&＃64;fname
truncate table #tb
insert into #tb exec master..xp_fileexist &＃64;filename
if exists(select 1 from #tb where a&＃61;1)
set &＃64;constr&＃61;&＃39;DRIVER&＃61;{Microsoft Excel Driver (*.xls)};DSN&＃61;&＃39;&＃39;&＃39;&＃39;;READONLY&＃61;FALSE;CREATE_DB&＃61;"&＃39;&＃43;&＃64;filename&＃43;&＃39;";DBQ&＃61;&＃39;&＃43;&＃64;filename
else
set &＃64;constr&＃61;&＃39;Provider&＃61;Microsoft.Jet.OLEDB.4.0;Extended Properties&＃61;"Excel 8.0;HDR&＃61;YES;DATABASE&＃61;&＃39;&＃43;&＃64;filename&＃43;&＃39;"&＃39;
--创建Excel文件
exec &＃64;err&＃61;sp_oacreate &＃39;adodb.connection&＃39;,&＃64;obj out
if &＃64;err<>0 goto lberr
exec &＃64;err&＃61;sp_oamethod &＃64;obj,&＃39;open&＃39;,null,&＃64;constr
if &＃64;err<>0 goto lberr
exec &＃64;err&＃61;sp_oamethod &＃64;obj,&＃39;execute&＃39;,&＃64;out out,&＃64;sql
if &＃64;err<>0 goto lberr
--关闭Excel
exec &＃64;err&＃61;sp_oamethod &＃64;obj,&＃39;close&＃39;,null
if &＃64;err<>0 goto lberr
exec &＃64;err&＃61;sp_oadestroy &＃64;obj
set &＃64;i&＃61;&＃64;i&＃43;1
end
set &＃64;fdlist&＃61;substring(&＃64;fdlist,2,8000)
set &＃64;i&＃61;1
--导入数据
while &＃64;i<&＃61;&＃64;sheetCount
begin
set &＃64;topCount&＃61;(&＃64;i-1)*&＃64;pageCount
--可取记录小于页面所需数时取剩余记录
if &＃64;totalCount-&＃64;topCount<&＃64;pageCount
set &＃64;pageCount&＃61;&＃64;totalCount-&＃64;topCount
if &＃64;where<>&＃39;&＃39;
begin
set &＃64;myWhere&＃61;&＃39;z1.recId >&＃39;&＃43;cast(&＃64;topCount as varchar)&＃43;&＃39; and &＃39;&＃43;&＃64;where
set &＃64;sql&＃61;&＃39;select top &＃39;&＃43;convert(varchar,&＃64;pageCount)&＃43;&＃39; &＃39;&＃43;&＃64;fdlist&＃43;&＃39; from &＃39;&＃43;substring(&＃64;from,1,charindex(&＃39;where&＃39;,&＃64;from)-2)
end
else
begin
set &＃64;myWhere&＃61;&＃39;z1.recId >&＃39;&＃43;cast(&＃64;topCount as varchar)
set &＃64;sql&＃61;&＃39;select top &＃39;&＃43;convert(varchar,&＃64;pageCount)&＃43;&＃39; &＃39;&＃43;&＃64;fdlist&＃43;&＃39; from &＃39;&＃43;&＃64;from
end
set &＃64;sql&＃61;&＃64;sql&＃43;&＃39; left join #tbMemory z1 on &＃39;&＃43;&＃64;primaryKey&＃43;&＃39;&＃61;primaryKey where &＃39;&＃43;&＃64;myWhere
set &＃64;constr&＃61;&＃39;openrowset(&＃39;&＃39;MICROSOFT.JET.OLEDB.4.0&＃39;&＃39;,&＃39;&＃39;Excel 8.0;HDR&＃61;YES;DATABASE&＃61;&＃39;&＃43;&＃64;path&＃43;cast(&＃64;i as varchar)&＃43;&＃64;fname&＃43;&＃39;&＃39;&＃39;,[&＃39;&＃43;&＃64;sheetname&＃43;&＃39;$])&＃39;
-- set ansi_nulls on
-- set ansi_warnings on
--print &＃64;sql
execute(&＃39;insert into &＃39;&＃43;&＃64;constr&＃43;&＃39;(&＃39;&＃43;&＃64;fdlist&＃43;&＃39;) &＃39;&＃43;&＃64;sql)
set &＃64;i&＃61;&＃64;i&＃43;1
end
set &＃64;sql&＃61;&＃39;drop table [&＃39;&＃43;&＃64;tbname&＃43;&＃39;]&＃39;
exec(&＃64;sql)
--set ansi_nulls off
--set ansi_warnings off
return
lberr:
exec sp_oageterrorinfo 0,&＃64;src out,&＃64;desc out
lbexit:
select cast(&＃64;err as varbinary(4)) as 错误号
,&＃64;src as 错误源,&＃64;desc as 错误描述
select &＃64;sql,&＃64;constr,&＃64;fdlist
GO
京公网安备 11010802041100号