Oracle PLSQL读取(解析)Excel文档

http://www.itpub.net/thread-1921612-1-1.html

！！！https://code.google.com/p/plsql-utils/

Introduction介绍

Excel文档的数据如何读取到Oracle数据库一直是值得深究的问题。

目前相信大部分程序员都是直接用工具将Excel的数据读取到Oracle数据库的。例如Toad，PL/SQL Developer的Excel数据导入功能。

也有一部分程序开发的是：先将Excel转换为一个逗号分隔符等的文本文件(CSV等)，然后写代码通过特定的符号(TAB符号或者逗号等)将数据拆分并且识别。

其实上面的读取Excel的办法都不是最好用的办法。举个例子，用Toad等工具来做，程序开发工程师来做是可以的，但是不可以将这个工作交给普通用户做(当用户想导入数据的时候)。如果用文本来做，先要做一个转换，对于用户来说无疑是多做了一个动作，操作不方便。

 

综上所述，其实最方便的还是：如何在Oracle数据库用PL/SQL直接读取Excel的内容，这才是最好的做法。

在PL/SQL直接解析并读取Excel，有一个蛮不错的办法，就是用Java的POI组件。确实是不错，但是要先导入POI的Java类，然后再在Oracle里面写一段Pkg的class类解析Excel。是可行的。我也研究过，发现有一些限制(可能是和POI的版本太低有关系)。

这里提出一个更加好的办法，就是用Oracle自带的utl_raw 包（二进制处理包）和dbms_xmldom包，将Excel文件对应的Blob大文本字段进行解析，最后再将解析出来的内容用管道表函数输出一个虚拟的表格。这样子就是很直观了，直接将一个Excel文档解析为一个表格！

可能大家比较关心解析的效率。所以针对效率方面，经过测试，还是非常不错的。下面有专门测试解析效率的主题。

另外一个比较关心的问题：Excel里面可以有很多公式列，那对于公式列读取出来的结果是什么？另外，一些特殊的格式栏位是否可以正确识别？经过测试，这些都可以得到正确的解析。例如公式列，解析的是公式计算的结果。

先简单测试一下实现的效果。

现在有这么一个Excel文档：XLS文件导入样本.xls，想将它导入到Oracle数据库中。

<ignore_js_op>

然后将它上传到服务器的某个文件夹。注意，这个文件路径(/data/uat/apps/apps_st/appl/attchment/12.0.0/BATCH_UPLOAD_TEMP/)必须是在all_directories有定义的。否则用PLSQL无法直接读取文件。值得一提的是，这个步骤并不是必须的，你也可以将Excel文件上传到Blob大字段中，然后再直接读取。常见的是FND_LOBS表的FILE_DATA字段。

<ignore_js_op> 

最后一步，用下面的函数就可以直接读取出Excel文本的内容了(注意输入的参数)：

SELECT* FROM TABLE(XYG_PUB_DATA_UPLOAD_PKG.CONVER_EXCEL_TO_TAB(XYG_ALD_FILE_PKG.CONVERT_FILE_BLOB('XYG_BATFILE','XLS文件导入样本.xls'),'',1))

<ignore_js_op>

必须要说明的是，栏位BATCH_CODE=工作表的名称。

如果有多个工作表，那BATCH_CODE是不同的。

附上Blob字段的Excel的读取方法：

SELECT * FROM TABLE(XYG_PUB_DATA_UPLOAD_PKG.CONVER_EXCEL_TO_TAB((SELECT FILE_DATA FROM FND_LOBS WHERE FILE_ID = 81171130),NULL,1));

 

注意和说明

如果是用Excel直接导入，需要注意的地方：

1 目前只支持30个栏位导入！应该是足够了！另外，管道表函数的对象的每个栏位都是Varchar2类型，内容最长4000字节。

2 由于一个Excel可能会有多个工作表，所以，在导入的时候，必须要指定导入的Excel的工作表页签的名称。

3 完全支持直接日期栏位的导入！日期栏位的格式也没限制。非常好！

4 对于数字的类型，由于系统自动转换为浮点型，为了转换的时候不出异常，所以精度不可以超过15位。

5 对于公式列，它也完美支持！导入的结果就是公式的计算结果。举个例子，一个栏位引用另外一个日期的栏位的，那导入的就是日期！

6 如果用户在打开Excel(未关闭文档的情况下)，也可以用FND的标准加载功能直接加载。不过必须要注意导入之前先保存。

7 目前已经完美支持xls和xlsx文档的导入，系统会自动对导入的文档进行识别，然后分别调用不同的代码，将文档的内容解析出来。

  但是，我觉得用xls是最好的，因为并不是所有的电脑都安装了2003以上的版本，用xls是最大兼容性的。

导入效率的测试

效率测试：

1 解析文档：DG订单排产顺序20150423.xls

文档大小：1.5mb，数据量：4600行。栏位数：27，其中有某些字段的内容比较多，例如物料编码描述等

解析时间：36秒。

2 解析文档：科目为差旅费.xls

文档大小：3mb，数据量：9000行。栏位数：20，其中有某些字段的内容比较多，例如2个科目的描述字段等

解析时间：65秒

3 解析文档：20150212用户职责明细导出.xls

文档大小：577kb，数据量：3000行，栏位数：11。字段内容比较少。

解析时间：14秒

4 解析文档：数据收集模板(深圳汽玻夹层第二批）2011-11-08.xls

文档大小：347kb，数据量：1572行，栏位数：17。字段的内容都不多。以数字为主。

解析时间：7秒

从上面发现一个规律，基本上解析时间和文档的大小成正比。

平均1mb的文件的解析时间要30秒左右。

应该还是可以接受的，因为导入的数据量一般不会很多！

需要注意的是，这个数据量不单纯是指记录行数，也包括记录的内容的多少。例如有某些字段的内容如果很多的话，就算行数少，数据量也很大！

 

---------------------------

---版主提醒了一下，让测试数据行比较多的，所以我干脆测试了11万行的记录（Excel行数），xlsx还是可以正常读取的。解析耗时4分钟左右：

<ignore_js_op>

<ignore_js_op>

 

附上开发的时候参考的源代码如下：

<ignore_js_op> parse_excel.txt (15.1 KB, 下载次数: 298)

文档：
<ignore_js_op> Oracle PLSQL读取Excel功能开发(Web共享版) V1.0.pdf (656.5 KB, 下载次数: 316) 
核心代码：
<ignore_js_op> XYG_PUB_DATA_UPLOAD_PKG_BODY_W.sql (78.79 KB, 下载次数: 367) 

百度文库的文档：http://wenku.baidu.com/view/bff55ea35901020207409cb2

添加一个EBS的公用Excel上传程序开发，配合这个直接解析Excel的技术，做起来应该很好用的了：
Oracle EBS公用Excel数据批量上传开发(Web共享版) V1.0
http://wenku.baidu.com/view/4866e3171711cc7931b716da

贴上一个代码：XYG_PUB_CONST_PKG是一些固定值来的，你可以先干掉这个参数也可以的。
我也贴一下给你吧：
CREATE OR REPLACE PACKAGE APPS.XYG_PUB_CONST_PKG
AS
   /******************************************************************************
      NAME:       XYG_PUB_CONST_PKG
      PURPOSE:    固定值的PKG 

      REVISIONS:
      Ver        Date        Author           Description
      ---------  ----------  ---------------  ------------------------------------
      1.0        2010/10/13   Sam.T          1,New Create the pkg
      1.1        2010/10/15   Sam.T          1,Add Function Err_XXXX
      1.2        2011/01/18   Sam.T          1,Add AUTO MAIL DEFAULT SENDER
      1.3        2014/12/26   Sam.T          1,修正自动邮件的收件人等信息的获取。
   ******************************************************************************/
   C_ITEM_RETURN_NUM CONSTANT NUMBER:=0;
   C_ITEM_RETURN_CHAR CONSTANT VARCHAR2(1):=NULL;
   C_ITEM_ERR_CODE CONSTANT NUMBER:= -20120; 
   C_RETURN_NUM CONSTANT NUMBER:=-1;
   C_RETURN_CHAR CONSTANT VARCHAR2(1):=NULL;
   C_TRUE CONSTANT NUMBER:=1;
   C_FALSE CONSTANT NUMBER:=0;
END;


-----------------------bug修复日志：
2015.5.27：
感谢网上的兄台的的测试和反馈，我开始确实没太注意这个问题。
经过确认，这确实是这个程序的一个bug，我已经修正过来了。
修正过程：
1 查找代码：
t_sheets(i-1).name--P_BATCH_CODE
替换为：
t_data(i).name--t_sheets(i-1).name--P_BATCH_CODE fix sheets name bug by sam.t2015.5.27
2 查找代码：
t_sheet_names(i)--P_BATCH_CODE
替换为：
t_data(i).name--t_sheet_names(i)--P_BATCH_CODE  fix sheets name bug by sam.t2015.5.27

-------END 2015.5.27

FUNCTION CONVERT_FILE_BLOB(
      P_FILEDIR  IN VARCHAR2  ---文件路径 /usr/usr/glmr/customer
     ,P_FILENAME  IN VARCHAR2  ---文件名称 DHS.csv
     ,P_RAISE          IN NUMBER DEFAULT XYG_PUB_CONST_PKG.C_TRUE
   )
      RETURN BLOB
   IS
       L_RESULT              BLOB := empty_blob();
       L_FILEDIR             VARCHAR2(240);
       l_files               bfile;
       l_dest_offset        BINARY_INTEGER;
       l_src_offset        BINARY_INTEGER;
       L_PROCESS_PHASE       NUMBER;
   BEGIN
      L_PROCESS_PHASE := 0;
      
      SELECT DIRECTORY_NAME
        INTO L_FILEDIR
        FROM all_directories
       WHERE 1=1
         AND (UPPER(DIRECTORY_PATH) = CASE WHEN SUBSTR(P_FILEDIR,-1) = '/' THEN UPPER(SUBSTR(P_FILEDIR,1,LENGTH(P_FILEDIR)-1))
                                      ELSE UPPER(P_FILEDIR)
                                      END
          OR UPPER(DIRECTORY_PATH) = UPPER(P_FILEDIR)
          OR DIRECTORY_NAME = P_FILEDIR)
         AND ROWNUM <=1;
         
      --DBMS_OUTPUT.PUT_LINE('L_FILEDIR:'||L_FILEDIR);
      L_PROCESS_PHASE := 1;
      
      l_files := bfilename (L_FILEDIR,P_FILENAME);
      dbms_lob.createtemporary(lob_loc => L_RESULT,  
                           cache   => true,  
                           dur     => dbms_lob.call);
      l_dest_offset := 1;
      l_src_offset := 1;
      dbms_lob.open(l_files,dbms_lob.lob_readonly);
      dbms_lob.loadblobfromfile(L_RESULT--dest_lob    IN OUT NOCOPY  BLOB,
                              ,l_files--src_lob     IN            BFILE,
                              ,dbms_lob.lobmaxsize--amount      IN            INTEGER,
                              ,l_dest_offset--dest_offset IN            INTEGER := 1,
                              ,l_src_offset--src_offset  IN            INTEGER := 1
                               );
      L_PROCESS_PHASE := 2;
      dbms_lob.close(l_files);
      
      L_PROCESS_PHASE := 99;
      
      RETURN L_RESULT;
   EXCEPTION
      WHEN OTHERS
      THEN
         IF P_RAISE = XYG_PUB_CONST_PKG.C_TRUE
         THEN
            XYG_PUB_COMMON_PKG.RAISE_ERROR(
               '-20001'                                   --'ERR_DEFAULT_CODE'
              ,SQLERRM
              ,'转换文件有异常错误!进度:'||L_PROCESS_PHASE
            );
                  --DBMS_OUTPUT.PUT_LINE ('THERE ARE SOME ERROR, PLEASE CONTACT WITH MIS');
         ELSE
            --UTL_FILE.FCLOSE (L_FILEHANDLE);
            RETURN empty_blob();
         END IF;
   END;

----

CREATE OR REPLACE PACKAGE APPS.XYG_PUB_CONST_PKG
AS
   /******************************************************************************
      NAME:       XYG_PUB_CONST_PKG
      PURPOSE:    固定值的PKG 

      REVISIONS:
      Ver        Date        Author           Description
      ---------  ----------  ---------------  ------------------------------------
      1.0        2010/10/13   Sam.T          1,New Create the pkg
      1.1        2010/10/15   Sam.T          1,Add Function Err_XXXX
      1.2        2011/01/18   Sam.T          1,Add AUTO MAIL DEFAULT SENDER
      1.3        2014/12/26   Sam.T          1,修正自动邮件的收件人等信息的获取。
   ******************************************************************************/
   C_ITEM_RETURN_NUM CONSTANT NUMBER:=0;
   C_ITEM_RETURN_CHAR CONSTANT VARCHAR2(1):=NULL;
   C_ITEM_ERR_CODE CONSTANT NUMBER:= -20120; 
   C_RETURN_NUM CONSTANT NUMBER:=-1;
   C_RETURN_CHAR CONSTANT VARCHAR2(1):=NULL;
   C_TRUE CONSTANT NUMBER:=1;
   C_FALSE CONSTANT NUMBER:=0;
END;

---