ITPUB论坛-中国最专业的IT技术社区

 
 注册
热搜:
查看: 48536|回复: 133

[精华] Oracle PLSQL读取(解析)Excel文档

[复制链接]
认证徽章
论坛徽章:
8
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:522011新春纪念徽章
日期:2011-02-18 11:43:332013年新春福章
日期:2013-02-25 14:51:24优秀写手
日期:2013-12-18 09:29:092014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02秀才
日期:2017-03-20 13:42:20秀才
日期:2017-07-11 13:54:02
跳转到指定楼层
1#
发表于 2015-5-14 07:35 | 只看该作者 |只看大图 回帖奖励 |倒序浏览 |阅读模式
本帖最后由 samt007 于 2015-5-29 16:41 编辑

Introduction介绍
Excel文档的数据如何读取到Oracle数据库一直是值得深究的问题。
目前相信大部分程序员都是直接用工具将Excel的数据读取到Oracle数据库的。例如ToadPL/SQL DeveloperExcel数据导入功能。
也有一部分程序开发的是:先将Excel转换为一个逗号分隔符等的文本文件(CSV),然后写代码通过特定的符号(TAB符号或者逗号等)将数据拆分并且识别。
其实上面的读取Excel的办法都不是最好用的办法。举个例子,用Toad等工具来做,程序开发工程师来做是可以的,但是不可以将这个工作交给普通用户做(当用户想导入数据的时候)。如果用文本来做,先要做一个转换,对于用户来说无疑是多做了一个动作,操作不方便。

综上所述,其实最方便的还是:如何在Oracle数据库用PL/SQL直接读取Excel的内容,这才是最好的做法。
PL/SQL直接解析并读取Excel,有一个蛮不错的办法,就是用JavaPOI组件。确实是不错,但是要先导入POIJava类,然后再在Oracle里面写一段Pkgclass类解析Excel。是可行的。我也研究过,发现有一些限制(可能是和POI的版本太低有关系)
这里提出一个更加好的办法,就是用Oracle自带的utl_raw 包(二进制处理包)和dbms_xmldom包,将Excel文件对应的Blob大文本字段进行解析,最后再将解析出来的内容用管道表函数输出一个虚拟的表格。这样子就是很直观了,直接将一个Excel文档解析为一个表格!
可能大家比较关心解析的效率。所以针对效率方面,经过测试,还是非常不错的。下面有专门测试解析效率的主题。
另外一个比较关心的问题:Excel里面可以有很多公式列,那对于公式列读取出来的结果是什么?另外,一些特殊的格式栏位是否可以正确识别?经过测试,这些都可以得到正确的解析。例如公式列,解析的是公式计算的结果。

先简单测试一下实现的效果。
现在有这么一个Excel文档:XLS文件导入样本.xls,想将它导入到Oracle数据库中。

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


最后一步,用下面的函数就可以直接读取出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))
必须要说明的是,栏位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 目前已经完美支持xlsxlsx文档的导入,系统会自动对导入的文档进行识别,然后分别调用不同的代码,将文档的内容解析出来。
  但是,我觉得用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分钟左右:

附上开发的时候参考的源代码如下:
parse_excel.txt (15.1 KB, 下载次数: 605)

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


百度文库的文档:

添加一个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






论坛徽章:
1088
金色在线徽章
日期:2007-04-25 04:02:08金色在线徽章
日期:2007-06-29 04:02:43金色在线徽章
日期:2007-03-11 04:02:02在线时间
日期:2007-04-11 04:01:02在线时间
日期:2007-04-12 04:01:02在线时间
日期:2007-03-07 04:01:022008版在线时间
日期:2010-05-01 00:01:152008版在线时间
日期:2011-05-01 00:01:342008版在线时间
日期:2008-06-03 11:59:43ITPUB年度最佳技术原创精华奖
日期:2013-03-22 13:18:30
2#
发表于 2015-5-14 09:06 | 只看该作者
支持65536行以上的吗?

使用道具 举报

回复
论坛徽章:
1088
金色在线徽章
日期:2007-04-25 04:02:08金色在线徽章
日期:2007-06-29 04:02:43金色在线徽章
日期:2007-03-11 04:02:02在线时间
日期:2007-04-11 04:01:02在线时间
日期:2007-04-12 04:01:02在线时间
日期:2007-03-07 04:01:022008版在线时间
日期:2010-05-01 00:01:152008版在线时间
日期:2011-05-01 00:01:342008版在线时间
日期:2008-06-03 11:59:43ITPUB年度最佳技术原创精华奖
日期:2013-03-22 13:18:30
3#
发表于 2015-5-14 09:08 | 只看该作者


这里好多。。。

使用道具 举报

回复
论坛徽章:
1088
金色在线徽章
日期:2007-04-25 04:02:08金色在线徽章
日期:2007-06-29 04:02:43金色在线徽章
日期:2007-03-11 04:02:02在线时间
日期:2007-04-11 04:01:02在线时间
日期:2007-04-12 04:01:02在线时间
日期:2007-03-07 04:01:022008版在线时间
日期:2010-05-01 00:01:152008版在线时间
日期:2011-05-01 00:01:342008版在线时间
日期:2008-06-03 11:59:43ITPUB年度最佳技术原创精华奖
日期:2013-03-22 13:18:30
4#
发表于 2015-5-14 09:13 | 只看该作者
文档传上来吧,我给你精华

使用道具 举报

回复
认证徽章
论坛徽章:
8
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:522011新春纪念徽章
日期:2011-02-18 11:43:332013年新春福章
日期:2013-02-25 14:51:24优秀写手
日期:2013-12-18 09:29:092014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02秀才
日期:2017-03-20 13:42:20秀才
日期:2017-07-11 13:54:02
5#
发表于 2015-5-14 09:29 | 只看该作者
dingjun123 发表于 2015-5-14 09:06
支持65536行以上的吗?

我待会儿找时间测试一下~理论上是支持的。。。

使用道具 举报

回复
认证徽章
论坛徽章:
8
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:522011新春纪念徽章
日期:2011-02-18 11:43:332013年新春福章
日期:2013-02-25 14:51:24优秀写手
日期:2013-12-18 09:29:092014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02秀才
日期:2017-03-20 13:42:20秀才
日期:2017-07-11 13:54:02
6#
发表于 2015-5-14 09:33 | 只看该作者
dingjun123 发表于 2015-5-14 09:13
文档传上来吧,我给你精华

已经上传了,包括我自己测试和处理过的核心代码。。。。
有了这个核心代码,自己随便搞都可以做一个Excel解析的Pkg了~

使用道具 举报

回复
认证徽章
论坛徽章:
8
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:522011新春纪念徽章
日期:2011-02-18 11:43:332013年新春福章
日期:2013-02-25 14:51:24优秀写手
日期:2013-12-18 09:29:092014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02秀才
日期:2017-03-20 13:42:20秀才
日期:2017-07-11 13:54:02
7#
发表于 2015-5-14 09:34 | 只看该作者
dingjun123 发表于 2015-5-14 09:08
https://code.google.com/p/plsql-utils/

这里好多。。。

希望版主可以搞一个解析效率更高的~

使用道具 举报

回复
认证徽章
论坛徽章:
8
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:522011新春纪念徽章
日期:2011-02-18 11:43:332013年新春福章
日期:2013-02-25 14:51:24优秀写手
日期:2013-12-18 09:29:092014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02秀才
日期:2017-03-20 13:42:20秀才
日期:2017-07-11 13:54:02
8#
发表于 2015-5-14 09:50 | 只看该作者
dingjun123 发表于 2015-5-14 09:06
支持65536行以上的吗?

xlsx的文档,111151行可以正常读取,只不过速度有点慢。。。4分钟左右。文档大小:934kb。

使用道具 举报

回复
论坛徽章:
12
2012新春纪念徽章
日期:2012-01-04 11:49:54懒羊羊
日期:2015-03-09 13:14:372015年新春福章
日期:2015-03-06 11:57:31美羊羊
日期:2015-03-04 14:48:58秀才
日期:2015-10-26 09:24:12秀才
日期:2015-09-11 10:43:06马上有房
日期:2014-07-21 13:13:25凯迪拉克
日期:2013-09-12 15:56:12蜘蛛蛋
日期:2013-06-18 16:09:38咸鸭蛋
日期:2013-03-14 08:29:23
9#
发表于 2015-5-14 10:44 | 只看该作者
我到是希望有写入XLS的功能

使用道具 举报

回复
认证徽章
论坛徽章:
8
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:522011新春纪念徽章
日期:2011-02-18 11:43:332013年新春福章
日期:2013-02-25 14:51:24优秀写手
日期:2013-12-18 09:29:092014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02秀才
日期:2017-03-20 13:42:20秀才
日期:2017-07-11 13:54:02
10#
发表于 2015-5-14 10:50 | 只看该作者
本帖最后由 samt007 于 2015-5-14 10:52 编辑
doni 发表于 2015-5-14 10:44
我到是希望有写入XLS的功能

可以考虑将内容导出到一个逗号分隔符或者Tab分隔符的文档,用Excel打开。另外,我提到的Java POI组件可以完整实现将数据写到xls Excel文档的功能。你有兴趣可以研究一下。

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 |
  | | |
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表