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

本文涉及的产品
云原生大数据计算服务MaxCompute,500CU*H 100GB 3个月
云原生大数据计算服务 MaxCompute,5000CU*H 100GB 3个月
简介:   set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go /*--数据导出EXCEL 导出查询中的数据到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

相关实践学习
基于Hologres轻松玩转一站式实时仓库
本场景介绍如何利用阿里云MaxCompute、实时计算Flink和交互式分析服务Hologres开发离线、实时数据融合分析的数据大屏应用。
SaaS 模式云数据仓库必修课
本课程由阿里云开发者社区和阿里云大数据团队共同出品,是SaaS模式云原生数据仓库领导者MaxCompute核心课程。本课程由阿里云资深产品和技术专家们从概念到方法,从场景到实践,体系化的将阿里巴巴飞天大数据平台10多年的经过验证的方法与实践深入浅出的讲给开发者们。帮助大数据开发者快速了解并掌握SaaS模式的云原生的数据仓库,助力开发者学习了解先进的技术栈,并能在实际业务中敏捷的进行大数据分析,赋能企业业务。 通过本课程可以了解SaaS模式云原生数据仓库领导者MaxCompute核心功能及典型适用场景,可应用MaxCompute实现数仓搭建,快速进行大数据分析。适合大数据工程师、大数据分析师 大量数据需要处理、存储和管理,需要搭建数据仓库?学它! 没有足够人员和经验来运维大数据平台,不想自建IDC买机器,需要免运维的大数据平台?会SQL就等于会大数据?学它! 想知道大数据用得对不对,想用更少的钱得到持续演进的数仓能力?获得极致弹性的计算资源和更好的性能,以及持续保护数据安全的生产环境?学它! 想要获得灵活的分析能力,快速洞察数据规律特征?想要兼得数据湖的灵活性与数据仓库的成长性?学它! 出品人:阿里云大数据产品及研发团队专家 产品 MaxCompute 官网 https://www.aliyun.com/product/odps&nbsp;
目录
相关文章
|
7天前
|
数据采集 自然语言处理 大数据
​「Python大数据」词频数据渲染词云图导出HTML
使用Python,本文展示数据聚类和办公自动化,焦点在于通过jieba分词处理VOC数据,构建词云图并以HTML保存。`wordCloud.py`脚本中,借助pyecharts生成词云,如图所示,关键词如&quot;Python&quot;、&quot;词云&quot;等。示例代码创建了词云图实例,添加词频数据,并输出到&quot;wordCloud.html&quot;。
18 1
​「Python大数据」词频数据渲染词云图导出HTML
|
25天前
|
人工智能 分布式计算 DataWorks
首批!阿里云 MaxCompute 完成中国信通院数据智能平台专项测试
2024年5月31日,在中国信通院组织的首批数据智能平台专项测试中,阿里云数据智能平台解决方案(MaxCompute、DataWorks、PAI)顺利完成测试。
102 5
首批!阿里云 MaxCompute 完成中国信通院数据智能平台专项测试
|
17天前
|
分布式计算 DataWorks API
DataWorks操作报错合集之在将ODPS空间设置成保护模式后,导出到OSS的任务出现了权限问题,该怎么解决
DataWorks是阿里云提供的一站式大数据开发与治理平台,支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
44 1
|
21天前
|
分布式计算 运维 DataWorks
MaxCompute产品使用问题之数据如何导出到本地部署的CK
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
21天前
|
分布式计算 DataWorks Oracle
MaxCompute产品使用问题之dataworks怎么导出所有maxcompute表的表结构
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
26天前
|
easyexcel Java API
SpringBoot集成EasyExcel 3.x:高效实现Excel数据的优雅导入与导出
SpringBoot集成EasyExcel 3.x:高效实现Excel数据的优雅导入与导出
105 1
|
27天前
|
JSON 资源调度 JavaScript
蓝易云 - vue实现导出excel的多种方式
以上两种方式都可以实现在Vue中导出Excel的功能,你可以根据你的需求选择合适的方式。
22 1
|
1月前
|
JavaScript 前端开发
JS导出excel功能
JS导出excel功能
|
10天前
|
运维 监控 大数据
部署-Linux01,后端开发,运维开发,大数据开发,测试开发,后端软件,大数据系统,运维监控,测试程序,网页服务都要在Linux中进行部署
部署-Linux01,后端开发,运维开发,大数据开发,测试开发,后端软件,大数据系统,运维监控,测试程序,网页服务都要在Linux中进行部署
|
13天前
|
JSON JavaScript 数据格式
vue 电子表格Excel的上传导入、导出下载、读取本地Excel、json转Excel
vue 电子表格Excel的上传导入、导出下载、读取本地Excel、json转Excel
17 0