关闭

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

2066人阅读 评论(0) 收藏 举报
分类:
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;







0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:121305次
    • 积分:1863
    • 等级:
    • 排名:千里之外
    • 原创:66篇
    • 转载:11篇
    • 译文:1篇
    • 评论:8条
    最新评论