【玩转Excel】Oracle PLSQL处理生成XLSX文件

原创 2015年07月06日 20:14:05
INTRODUCTION介绍
    之前发表了一个研究心得(当然是站在别人的肩膀上的),在Oracle中直接用PL/SQL解析并读取Excel的内容。很多人都感兴趣,按照我的写法也可以成功实现了。
不过,有很多朋友提出了另外一个要求:读取Excel是可以了,那是否可以在Oracle数据库端直接生成Excel文件?二进制的文件。
    开始我对这个问题是不怎么在意的。因为,在Oracle数据库中完全可以直接生成逗号或者Tab制表符的分隔符的文本(例如csv),然后用Excel打开,那是可以完全达到目的(之前我一直就是这样子做,特别是导数据给User的时候)。----后来,由于这边老总要接收Excel数据的邮件,然后又必须要求用手机也可以正常查看数据,所以也只好研究这个产生Excel二进制文件的功能了。
实际上,这个办法也挺好,开发也简单。只不过,文本的文件相对于二进制的文件,有其优点,也有其缺点。请容我按照我的理解慢慢分析。
2.1        文本文件的优点
主要的优点,当然是开发简单,只需要将SQL的数据用游标获取,然后用逗号或者tab符号作为列分隔符,再用UTL_FILE将输出结果到服务器,产生文本文件即可。
怎么开发的我就不多说了,随便百度一下,资料都一大堆。

2.2        文本文件的缺点
文件文件的缺点,对应的就是Excel二进制文件的优点。
1)文本文件用Excel打开带来的问题
虽然它开发简单,但是由于数据是用逗号分隔符或者Tab制表符作为栏位的列分隔原则,导致了其数据解析的时候,非常容易出错!假设数据里面本来就有Tab符号,那么如果用Excel打开的时候,数据都乱了(因为多了一列)。如果是产生Excel文件,就肯定不会有这个额外的问题!
由于文本自动转换到Excel的特性导致,某些栏位,Excel会自作聪明地进行类型的转换!不信,你将4-1作为一个文本内容,然后用Excel打开看看?肯定自动变为日期类型2015/4/1了。
2)产生的文件容量的问题
通过测试得知,当输出的内容越多,用文本存储所占的容量就越大。而用Excel输出的文件就越小(相对于文本来说)。这说明,二进制的文件,应该是对数据有一些压缩的算法,所以其处理起来更加有效率。(附上实际的测试:162605行数据,文本:98MB,Excel:32.38MB)。
3)用户的操作体验
更加方便用户操作。如果是产生一个excel文件,当用户打开文件的时候,不会有那个烦人的自动转换内容的提示;另外,用户也不需要另存为这个Excel了,因为这个本来就是Excel文件,修改之后直接保存即可。细节决定成败,越细小的地方,只要是可以方便用户工作的,在能力范围之内,应该要做得更好的!
另外,现在手机或者平板电脑的用户越来越多了。如果ERP系统导出来的数据是一个文本(假设是发邮件给用户),然后用户拿手机(或者平板)打开的时候,格式会很乱(就是一个逗号分隔符的文本内容,不清楚的用户还以为是乱码!)。
但是,如果系统导出来的是一个二进制的Excel文件,用户在手机端用Excel读取的app看数据的时候,就非常方便了。格式也可以控制得很好。

2.3        PLSQL产生Excel文件的缺点
优点我不说了。上面提到的文本文件的缺点就是它的优点!我直接说Excel文件的缺点,准备使用这个功能的可能要三思,先考虑清楚。
最特出的缺点:产生Xlsx文件的处理过程,耗内存!
这个程序的大概逻辑是,先将数据存到Session的Table变量,然后整理成一个Excel类型的xml文件(用clob变量存储),最后经过压缩,产生一个二进制的Xlsx文件。而这些过程,都是在内存中完成的,所以,当处理的数据量越大,处理起来就越慢。备注:所产生的结果xlsx文档是一个blob文件,可以存到数据库文件系统里面。

3        对国外的XLSX生成的脚本优化
首先必须得说明,这个处理产生Xlsx文件的PLSQL脚本,并不是我写的。我是在国外的一个blob中找到的代码(Copyright (C) 2011, 2012 by Anton Scheffer)。但是,我拿来使用之后,发现有一些地方必须得修改的,特别是执行效率方面,必须要优化(几千行的数据产生XLSX居然要几分钟,这个是无法忍受的!)。同时也参考了国外blob的一些写法做了一些调优,再按照我的习惯,增加一些小功能,得出现在的最终版本。
下面都有提及到。
3.1        导出Excel的脚本的优化点
3.1.1        最主要的优化是,处理EXCEL文件生成的速度大大提高
下面的表格是我实际测试的结果:
生成xlsx的数据量        优化前执行时间        优化后执行时间
2948行数据        2:18        1秒
16500行数据        1:06:52        15秒
目前,这个功能我这边已经在正式环境中使用,用户的反馈效果还是很好的。几千行的 数据的导出,只需要1秒,这个等待时间还是非常不错的!
3.1.2        动态SQL支持绑定变量的输入
绑定变量的作用是什么大家都清楚。对于查询量超大的在线事务处理系统来说,这个优化是必须的。
参数        值说明        例子        实际使用语句
COL_ID        绑定变量的ID        1        WHERE COL = :1
COL_VALUE        值        106        106
具体看代码:query2sheet的输入参数:p_col_value_tab
3.1.3        增加了一个过程CURSOR2SHEET
增加了一个过程cursor2sheet(参考别人分享的)。该过程的作用是将游标的结果输出到Excel,也是很实用的东西。不过目前没测试过~暂时没用到。
3.1.4        程序增加简单的调试模式
调试模式的开关在包头变量里面。所以,要调试的时候,只需要在PLSQL里面增加这个代码:
SMT_XLSX_MAKER_PKG.G_DEBUG_MODE := 'TRUE';
调试方式G_DEBUG_TYPE也可以改:DBMS_OUTPUT直接输出(默认)/FILE_OUTPUT文档输出/REQUEST_OUTPUT请求日志输出/CONTEXT_OUTPUT 将日志改为上下文输出
具体看代码。调试的内容也主要是执行的时间。简单的调试。
备注:如果系统用的不是EBS,由于我这边有另外的客户化开发处理生成Log的文档,所以,这个我先remark掉了。用output做调试也是很简单的。
3.1.5        产生EXCEL文件的日期系统改为默认是1900日期系统
也是一个非常重要的改动:产生Excel文件的日期系统改为默认是1900日期系统!因为Windows系统的Excel的默认日期系统就是这个1900。
(如果不知道什么是Excel的日期系统的兄台请看这个链接http://blog.sina.com.cn/s/blog_3fc594a201016p89.html)

另外说一下,因为这个问题,在这个功能刚上线使用的时候,用户还反馈了一个问题:为什么我这里的日期是2015年,复制到别的Excel文件,就自动变为2011年?神奇的变化!
所以,这里也说明了一点:国外的代码,如果用在国内的系统,或多或少都有一些是需要改动的。就算这并不是程序本身的bug!
-------------------------------------------------------------------
----------华丽的分割线:
程序测试部分:

declare

  l_sql varchar2(30000);

begin

l_sql := 'SELECT120 + TRUNC (RN / 12) + ROUND (DBMS_RANDOM.VALUE (1, 10)) SAM_NUMBER

       ,RN||''数据Excel导出'' SAM_CHAR

       ,SYSDATE+ TRUNC (RN / 12)+ROUND(DBMS_RANDOM.VALUE (1, 10)) SAM_DATE

  FROM (   SELECT LEVEL, ROWNUM RN

              FROM DUAL

        CONNECT BY ROWNUM <= 12000)';

SMT_XLSX_MAKER_PKG.query2sheet(l_sql,true,'ECX_UTL_LOG_DIR_OBJ','Export2.xlsx');

end;

结果:


begin

  SMT_XLSX_MAKER_PKG.clear_workbook;

  SMT_XLSX_MAKER_PKG.new_sheet;

  SMT_XLSX_MAKER_PKG.cell( 5, 1, 5 );

  SMT_XLSX_MAKER_PKG.cell( 3, 1, 3 );

  SMT_XLSX_MAKER_PKG.cell( 2, 2, 45 );

  SMT_XLSX_MAKER_PKG.cell( 3, 2, 'AntonScheffer', p_alignment => SMT_XLSX_MAKER_PKG.get_alignment( p_wraptext =>true ) );

  SMT_XLSX_MAKER_PKG.cell( 1, 4, sysdate,p_fontId => SMT_XLSX_MAKER_PKG.get_font( 'Calibri', p_rgb => 'FFFF0000' ));

  SMT_XLSX_MAKER_PKG.cell( 2, 4, sysdate,p_numFmtId => SMT_XLSX_MAKER_PKG.get_numFmt( 'dd/mm/yyyy h:mm' ) );

  SMT_XLSX_MAKER_PKG.cell( 3, 4, sysdate,p_numFmtId => SMT_XLSX_MAKER_PKG.get_numFmt(SMT_XLSX_MAKER_PKG.orafmt2excel( 'dd/mon/yyyy' ) ) );

  SMT_XLSX_MAKER_PKG.cell( 5, 5, 75, p_borderId=> SMT_XLSX_MAKER_PKG.get_border( 'double', 'double', 'double', 'double' ));

  SMT_XLSX_MAKER_PKG.cell( 2, 3, 33 );

  SMT_XLSX_MAKER_PKG.hyperlink( 1, 6,'http://www.amis.nl', 'Amis site' );

  SMT_XLSX_MAKER_PKG.cell( 1, 7, 'Some mergedcells', p_alignment => SMT_XLSX_MAKER_PKG.get_alignment( p_horizontal =>'center' ) );

  SMT_XLSX_MAKER_PKG.mergecells( 1, 7, 3, 7 );

  for i in 1 .. 5

  loop

    SMT_XLSX_MAKER_PKG.comment( 3, i + 3, 'Row' || (i+3), 'Anton' );

  end loop;

  SMT_XLSX_MAKER_PKG.new_sheet;

  SMT_XLSX_MAKER_PKG.set_row( 1, p_fillId =>SMT_XLSX_MAKER_PKG.get_fill( 'solid', 'FFFF0000' ) ) ;

  for i in 1 .. 5

  loop

    SMT_XLSX_MAKER_PKG.cell( 1, i, i );

    SMT_XLSX_MAKER_PKG.cell( 2, i, i * 3 );

    SMT_XLSX_MAKER_PKG.cell( 3, i, 'x ' || i *3 );

  end loop;

  SMT_XLSX_MAKER_PKG.query2sheet( 'selectrownum, x.*

, case when mod(rownum, 2 ) = 0 then rownum * 3 end demo

, case when mod(rownum, 2 ) = 1 then ''demo '' || rownum end demo2 from dual x connect byrownum <= 5' );

  SMT_XLSX_MAKER_PKG.save('ECX_UTL_LOG_DIR_OBJ', 'Export3.xlsx' );

end;







plsql 导出数据到 excel

说明:xls:能保存65535条数据,多出的就要用xlsx格式的excel 方法一:(适用于导出少量数据)(plsql版本可以导出xlsx,有的只能xls) 1.首先在SQL Window窗口...

Oracle查询大数据量后导出到Excle文件的经验

手头上有个报表任务,运行SQL查询脚本近1个小时后,得到60多万条查询记录(导出为Excel的文件后,有三四十兆字节大小)。 先后尝试了以下几种办法以将查询结果导出到Excel文件: 1. 默认导...

Oracle采用存储过程执行sql语句,将查询到的数据导出到EXCEL中

Oracle采用存储过程执行sql语句,将查询到的数据导出到EXCEL中
  • wyc_L
  • wyc_L
  • 2017年06月02日 10:46
  • 1832

Excel的xls和xlsx文件转csv

  • 2016年10月28日 17:45
  • 370KB
  • 下载

Java POI根据模板生成Excel(xlsx)文件

  • 2017年11月26日 11:10
  • 8.56MB
  • 下载

Excel VBA 如何生成.csv或.xlsx文件

生成.csv或.xlsx文件,本身是件easy的事情,比如可以用R,Python。。。但R有个局限不能对excel里的文字底纹格式等进行修改,如需要字体加粗、底纹为蓝等等,于是想到用excel的VBA...

NPOI导出Excel2007-xlsx格式文件,用于web时需要注意的问题-XSSFWorkbook处理问题

转自:http://www.cnblogs.com/kaiwanlin/p/5782834.html 1.今天再处理Excel2007、2010文件,格式.xlsx文件存在一个问题,在调用 Writ...

java操作poi生成excel文件(.xlsx)

import java.io.FileOutputStream; import java.io.OutputStream; import java.util.ArrayList; import ...

JAVA代码自动生成.xls和.xlsx类型的excel文件的详细过程和代码

JAVA代码自动生成.xls和.xlsx类型的excel

c#如何生成Excel(.xls和.xlsx)文件

在工作中经常遇到需要用c#生成Excel文件(.xls和.xlsx格式),完全免费开源的ExcelLibrary是一个不错的选择。 ExcelLibrary项目的地址为: https:...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:【玩转Excel】Oracle PLSQL处理生成XLSX文件
举报原因:
原因补充:

(最多只允许输入30个字)