大数据量导出Excel ---待测试

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/*--数据导出EXCEL

导出查询中的数据到Excel,包含字段名,文件为真正的Excel文件
如果文件不存在,将自动创建文件
如果表不存在,将自动创建表
基于通用性考虑,仅支持导出标准数据类型
--Modify
1、修改邹建的错误：关于provider的书写
在openrowset时为database
在ADODb.connection.open时为data source，一个很不容易找到的错误，否则0x80004005 Microsoft JET Database Engine 不能执行选定查询
2、执行过程过长时，中途无法取消
3、一些限制
--Jet SQL无法修改列名，应使用ADOX.Catagory
--SQLServer2005索引最大为1284 bytes，order by 最大为8060 bytes
--grant exec分配不了权限
4、要对@varQuerySQL中结果集排序，请在@varOrderBy指定结果集中的列，存储过程会按此排序，不要再在@varQuerySQL中再排序

--邹建 2003.10(引用请保留此信息)--*/

/*--调用示例--

--表的联合
p_QueryToExcel @varQuerySQL= 'select top 65000 A.* from a inner join B on A.a1=B.a1'
,@varExcelFullPath= 'E:\aa21.xls',@varSheetName= 'sheet'

select * from liupeng --256003
--102400条记录
--时间：
--@varQuerySQL没有order by TopName,Identitier
1:50，1:21, 1:55, 1:27(加索引), 1:34(加索引)
--@varQuerySQL有order by
大于9:00
--Select语句列必须带函数的列必须有别名
p_QueryToExcel @varQuerySQL= 'select MetaID as 标识,Title as 书名,Creator as 作者,Publisher as 出版者,Price as 单价,CopyNum as 复本数,Identitier as 分类号,TopName as 类目,issuedate as 出版时间,ISBN,publishdate as 公布时间,(case
when IsRef= 0 then ''否''
when IsRef = 1 then ''是''
else '''' end ) as 是否教参
from [liupeng]
where CategoryTypeID=1 ',@varExcelFullPath= 'E:\aa3.xls',@varSheetName= 'sheet'

select count(*) from ast_document --334138
--102400条记录
--时间：
--@varQuerySQL没有order by TopName,Identitier
2:00, 1:10, 1:27, 1:21, 1:20

p_QueryToExcel @varQuerySQL=' select top 2000 MetaID as 标识,TopIdentitier,DocID,Title,Creator,Publisher,Price,CopyNum,Identitier,TopName,substring(convert(varchar, issuedate, 20), 1, 7) as 出版日期,ISBN,substring(convert(varchar,publishdate, 20), 1, 10) as 发布日期,(case
when IsRef= 0 then''否''
when IsRef = 1 then ''是''
else ''''end ) as IsRef
,Abstract
from [liupeng]'
,@varExcelFullPath= 'E:\aa004.xls',@varSheetName= 'sheet',@varOrderBy='TopIdentitier,DocID'

p_QueryToExcel @varQuerySQL= 'select top 102400
DocID,
MetaTypeID,
MetaID,
Title,
Creator,
[Year],
Publisher,
PublishDate,
Price,
ISBN,
IssueDate,
IsScan,
Status,
InsertedTime,substring(Abstract,1,255) as 摘要 from ast_document'
,@varExcelFullPath= 'E:\aa2.xls',@varSheetName= 'sheet',@varOrderBy='MetaID,Title'

--Excel 12:[Sheet1$]则，会报错“书签无效”，应使用'select * from [Sheet1$]'
insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=D:\test.xlsx;',
'select * from [Sheet1$]') select top 10 DocID from Test.dbo.ast_document

--在Excel 12中一个sheet中加入10万条记录比在多个Sheet中加入10万条记录要满很多
--因此不用Excel 12存
SELECT *
FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0',
'Data Source=F:\aa.xls; Extended properties="Excel 8.0;HDR=YES"')...sheet$

*/

ALTER proc [dbo].[p_QueryToExcel]
@varQuerySQL varchar(8000), --@varQuerySQL:查询语句,各列必须有名称，没有的需要别名，由于
--如果查询语句中使用了order by,请加上top 100 percent
--对是否包含where分类限制不做要求
@varExcelFullPath nvarchar(4000), --@varExcelFullPath:生成Excel的完整路径
@varSheetName varchar(512) = 'sheet',
@varOrderBy varchar(512) = '类目', --必须是@varQuerySQl中的列名
@varArrayHiddenCols varchar(1024) = 'TopIdentitier,DocID'
as
begin
--声明和初始化变量

declare @varSheetName_pre varchar(1024)
declare @intOneSheetMaxNumber int
declare @intRecCount int
declare @sinSheetNumber smallint
declare @sinCircle int
declare @varTempSQL varchar(8000)

set @intOneSheetMaxNumber = 50000
set @sinSheetNumber = 1
set @varExcelFullPath = convert(varchar(8000),ltrim(rtrim(@varExcelFullPath)))
set @varQuerySQL = lower(ltrim(rtrim(@varQuerySQL)))
set @sinCircle = 0
set @varSheetName_pre = @varSheetName
set @varOrderBy = ltrim(rtrim(@varOrderBy))
set @varArrayHiddenCols = lower(ltrim(rtrim(@varArrayHiddenCols)))
--select top 1 1 from ast_document where metaid not in ('CategoryTypeID','DocID')
set @varArrayHiddenCols = replace(@varArrayHiddenCols,'，',',') --CategoryTypeID,DocID
print @varArrayHiddenCols
-- set @varOrderByWithNoAliasTable = ltrim(rtrim(@varOrderByWithNoAliasTable))
-- --@varQuerySQL的别名这里取AAA
-- if @varOrderByWithNoAliasTable is null or @varOrderByWithNoAliasTable = ''
-- set @varOrderBy = 'Order by AAA.CategoryTypeid, AAA.HiberarchyCode'
-- else
-- set @varOrderBy = 'AAA.' + replace(@varOrderByWithNoAliasTable,',' ,',AAA.' )

/*=================================检测参数有效性====================*/
----判断@varExcelFullPath
if (@varExcelFullPath is null) or (@varExcelFullPath='')
begin
RAISERROR ('Excel文件路径不能为空。',1,1)
return 50001
end

----判断@varQuerySQL
if (@varQuerySQL is null) or (@varQuerySQL = '')
begin
RAISERROR ('查询语句不能为空。',1,2)
return 50001
end

----判断@varQuerySQL 'SQL语句'
----假设用户没有恶意调用
set @varTempSQL = @varQuerySQL
if left(@varTempSQL,1) = '('
begin
RAISERROR ('不能将整个SQL语句用括号包起来。',2,16)
return 50002
end

if charindex('select ',@varTempSQL,1) = 0
begin
RAISERROR ('Error 缺少select语句。',2,16)
return 50002
end

if charindex(' aaa',@varTempSQL,1) > 0 or charindex(' bbb',@varTempSQL,1) > 0 or charindex(' myrownumber',@varTempSQL,1) > 0
begin
RAISERROR ('请使用AAA、BBB、MyRowNumber以外的别名。',2,16)
return 50002
end

if left(@varTempSQL,1) = '('
begin
RAISERROR ('不能将整个SQL语句用括号包起来。',2,16)
return 50002
end
--针对'
--set @varQuerySQL = replace(@varQuerySQL,char(39),char(39)+char(39))
/*
if charindex(char(39),@varTempSQL,1) > 0
begin
--RAISERROR ('Error 传输的SQL语句不能包含英文撇，请使用''代替。',3,17)
--return 50002
end
*/

if @varOrderBy is null or @varOrderBy =''
begin
RAISERROR ('排序不能为空。',1,5)
return 50001
end
else
begin
set @varOrderBy = 'BBB.' + replace(@varOrderBy,',',',BBB.')
end
print @varOrderBy

--exec ('select 1 from (select * from ast_document where docid < 0) as A')
--select 1以便得到count记录数，保证@varQuerySQL不包含AAA别名
set @varTempSQL = 'select 1 from (' + @varQuerySQL + ') as AAA'
exec (@varTempSQL)

--@@rowcount在下一个begin...end之后就成为0
set @intRecCount = @@rowcount
if @intRecCount = 0
begin
RAISERROR ('查询记录集为空。',1,5)
return 50001
end
print @intRecCount


declare @varTopCategoryCode varchar(256)
declare @varTopCategoryName varchar(512)
declare @err int, @src varchar(255), @desc varchar(255), @out int --Error跟踪
declare @obj int, @constr varchar(1000), @fdlist varchar(8000), @fdlist_AAA varchar(8000)
declare @tbname sysname

--检查Excel文件是否已经存在
create table #tb(a bit,b bit,c bit)
set @varExcelFullPath = ltrim(rtrim(@varExcelFullPath))

insert into #tb
exec master..xp_fileexist @varExcelFullPath
print 'xp_fileexist'+@varExcelFullPath
--select * from #tb
/*
xp_fileexist 返回的三个列, 分别代表

文件已存在 文件是目录 父目录已存在
----- ----- ------
0 0 1
*/

/*
declare @saveas varchar(2048),@sheet int

set @saveas = 'ActiveWorkbook.SaveAs("'+@varExcelFullPath+'")'

exec @err = sp_oacreate 'excel.application' ,@obj output
if @err <> 0 goto lberr

exec @err = sp_oamethod @obj ,'workbooks.add' ,@sheet output
if @err <> 0 goto lberr

exec @err = sp_oamethod @obj ,@saveas
if @err <> 0 goto lberr

--exec @err = sp_oamethod @obj ,'ActiveWorkbook.Save'
if @err <> 0 goto lberr

exec @err = sp_oamethod @obj ,'Workbooks.Close'
if @err <> 0 goto lberr

exec @err = sp_oamethod @obj ,'quit'
exec @err = sp_oadestroy @obj

return
*/

--数据库创建语句
set @varTempSQL = @varExcelFullPath
if exists(select 1 from #tb where a=1)
begin
--set @constr= 'DRIVER={Microsoft Excel Driver (*.xls)};DSN= '''';READONLY=FALSE '
-- + ';CREATE_DB= "'+@sql+ '";DBQ='+@sql
RAISERROR ('暂不支持对已经存在的Excel，做导出操作。',1,5)
return 50001
end
else
set @constr= 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+@varExcelFullPath+ ';Extended Properties="Excel 8.0;HDR=YES"'
--set @constr= 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source='+@varExcelFullPath+ ';Extended Properties="Excel 12.0;HDR=YES"'

--连接数据库
EXEC @err=sp_OACreate 'ADODB.Connection', @obj OUT
print '连接数据库1'+convert(varchar(50),@obj)
if @err <> 0 goto lberr

EXEC @err=sp_OASetProperty @obj,'ConnectionString', @constr
if @err <> 0 goto lberr

exec @err=sp_oamethod @obj, 'Open'
print '连接数据库2'+@constr
if @err <> 0 goto lberr

print '创建临时表1'

--创建相同表结构的临时表
set @tbname= '##tmp_'+convert(varchar(38),newid())
set @varTempSQL= 'select top 1 AAA.* into ['+@tbname+ '] from ('+@varQuerySQL+ ') AAA'
print '创建临时表2'+@varTempSQL
exec(@varTempSQL)

set @varTempSQL = ''
set @fdlist = ''
set @fdlist_AAA = ''
select @fdlist=@fdlist+ ',['+a.name+']'
,@varTempSQL=@varTempSQL+',['+a.name+'] '
+case
when b.name like '%char'
then case when a.length> 255 then 'text(255)'
else 'text('+cast(a.length as varchar)+')' end
when b.name like '%int' or b.name='bit' then 'int'
when b.name like '%datetime' then 'datetime'
when b.name like '%money' then 'money'
when b.name like '%text' then 'memo'
else b.name end
FROM tempdb..syscolumns a
left join tempdb..systypes b
on a.xtype=b.xusertype
where b.name not in('image','uniqueidentifier','sql_variant','varbinary','binary','timestamp')
and a.id=(select id from tempdb..sysobjects where name=@tbname)
and charindex(a.name, @varArrayHiddenCols)=0

select @fdlist_AAA=@fdlist_AAA+ ',AAA.['+a.name+']'
FROM tempdb..syscolumns a
left join tempdb..systypes b
on a.xtype=b.xusertype
where b.name not in('image','uniqueidentifier','sql_variant','varbinary','binary','timestamp')
and a.id=(select id from tempdb..sysobjects where name=@tbname)
and charindex(a.name, @varArrayHiddenCols)=0

print @fdlist + ' ' +@fdlist_AAA
print'转换数据类型'

/*=====================分页插入Excel的Sheet=======================*/
--如果不按分类存放

--获得记录总数@intRecCount，上面判断时已经获得
----设置Sheet名称
----判断@intRecCount与@intOneSheetMaxNumber大小，定义Sheet名称后
----分页插入各个Sheet

--如果<=@intOneSheetMaxNumber
if @intRecCount <= @intOneSheetMaxNumber
begin
set @varSheetName = @varSheetName_pre + '1'
--直接创建Excel和导入数据
set @varTempSQL= 'create table ['+@varSheetName + '] ('
+ substring(@varTempSQL,2,8000)+ ')'
set @fdlist=substring(@fdlist,2,8000)
set @fdlist_AAA=substring(@fdlist_AAA,2,8000)

print '准备创建Table '+@varTempSQL
exec @err=sp_oamethod @obj, 'Execute',@out out,@varTempSQL
if @err <> 0 goto lberr

--为导入数据
set @varTempSQL= 'openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=Yes;database='+@varExcelFullPath+ ''',['+@varSheetName+ '$])'
--set @varTempSQL= 'openrowset(''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0;HDR=Yes;database='+@path+@fname+ ''',''select * from ['+@varSheetName+ '$]'')'
print '导入数据:'+'insert into '+@varTempSQL+ '('+@fdlist+ ') select '+@fdlist_AAA+ ' from ('+@varQuerySQL+ ') as AAA'
exec('insert into '+@varTempSQL+ '('+@fdlist+ ') select '+@fdlist_AAA+ ' from ('+@varQuerySQL+ ') as AAA')
end
--如果>@intOneSheetMaxNumber
else
begin
set @sinSheetNumber = @intRecCount / @intOneSheetMaxNumber
+ (case (@intRecCount % @intOneSheetMaxNumber) when 0 then 0 else 1 end)

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

while ( @sinCircle < @sinSheetNumber )
begin
set @varSheetName = @varSheetName_pre + convert(varchar(50),@sinCircle+1)
print '第 ' + convert(varchar(50),@sinCircle+1) + ' 个Sheet / 总共 ' + convert(varchar(50),@sinSheetNumber) + ' 个Sheet'
--直接创建Excel和导入数据
declare @varTempSQL2 varchar(8000)
set @varTempSQL2 = ''
select @varTempSQL2= 'create table ['+@varSheetName
+ '] ('+substring(@varTempSQL,2,8000)+ ')'


print '准备创建Table ' + @varTempSQL2
exec @err=sp_oamethod @obj, 'Execute',@out out,@varTempSQL2
print 'create table :'+@varTempSQL
if @err <> 0 goto lberr

--为导入数据
/*
WITH OrderedTable AS
(
SELECT BBB.*,ROW_NUMBER() OVER (ORDER BY Docid) AS 'MyRowNumber'
FROM (@varQuerySQL) as BBB
)
insert into openrowset() (@fdlist)
SELECT @fdlist
FROM OrderedTable
WHERE MyRowNumber BETWEEN (@intOneSheetMaxNumber * @sinCircle) AND (@intOneSheetMaxNumber * (@sinCircle+1))
*/
--set @varTempSQL= 'openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=Yes;database='+@varExcelFullPath+ ''',['+@varSheetName+ '$])'
--set @varTempSQL= 'openrowset(''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0;HDR=Yes;database='+@path+@fname+ ''',''select * from ['+@varSheetName+ '$]'')'

set @varTempSQL2 = 'WITH OrderedTable AS
(
SELECT BBB.*,ROW_NUMBER() OVER (ORDER BY '+@varOrderBy +') AS ''MyRowNumber''
FROM (' + @varQuerySQL + ') as BBB
)
insert into openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=Yes;database='+@varExcelFullPath+ ''',['+@varSheetName+ '$])'
+ ' select ' + @fdlist + ' FROM OrderedTable WHERE MyRowNumber BETWEEN ' + convert(varchar(50),@intOneSheetMaxNumber * @sinCircle+1) + ' AND ' + convert(varchar(50),@intOneSheetMaxNumber * (@sinCircle+1))

print '导入数据:'+@varTempSQL2
exec(@varTempSQL2)

set @sinCircle = @sinCircle + 1
end
end


--关闭和释放OA对象
EXEC @err=sp_OAMethod @obj, 'Close'
EXEC @err=sp_OAMethod @obj, 'Dispose'
print 'sp_oadestroy'+convert(varchar(50),@obj)
EXEC @err=sp_OADestroy @obj

set @varTempSQL= 'drop table ['+@tbname+ ']'
print 'drop table '+@varTempSQL
exec(@varTempSQL)

return
lberr:
exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
select cast(@err as varbinary(4)) as 错误号
,@src as 错误源,@desc as 错误描述
select @varTempSQL,@constr,@fdlist

end