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

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;