OraclePLSQL读取（解析）Excel⽂档
www.itpub/thread-1921612-1-1.html
！！！le/p/plsql-utils/
Introduction介绍
Excel⽂档的数据如何读取到数据库⼀直是值得深究的问题。
⽬前相信⼤部分程序员都是直接⽤⼯具将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>T1.jpg
然后将它上传到服务器的某个⽂件夹。注意，这个⽂件路径(/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>T3.jpg
最后⼀步，⽤下⾯的函数就可以直接读取出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>T2.jpg
必须要说明的是，栏位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>T5.jpgplsql developer导入数据
<ignore_js_op>T4.jpg
附上开发的时候参考的源代码如下：
<ignore_js_op>  (15.1 KB, 下载次数: 298)
⽂档：
<ignore_js_op>  (656.5 KB, 下载次数: 316)
核⼼代码：
<ignore_js_op>  (78.79 KB, 下载次数: 367)
百度⽂库的⽂档：
添加⼀个EBS的公⽤Excel上传程序开发，配合这个直接解析Excel的技术，做起来应该很好⽤的了：
Oracle EBS公⽤Excel数据批量上传开发(Web共享版) V1.0
贴上⼀个代码：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);
atetemporary(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;
---

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。