大数据导出excel大小限制_大数据量导出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