来至http://blog.chinaunix.net/uid-173640-id-4187684.html
之前有用java封裝一個讀取excel 2000的 oracle package
這個是用來讀取 openoffice ods格式的 package
原理是:
1. 用as_zip解壓得到xml
2. 用oracle xmltype讀取每一行數據, pipelined方式返回.
測試代碼如下
点击(此处)折叠或打开
- select b.*
- from table( ods_utl_pkg.get_ods_table(
- utl_lob.url2blob('ftp://172.17.2.43/pub/test/work.ods'))) b
oracle package: ods_utl_pkg封裝如下.
点击(此处)折叠或打开
- create or replace package ods_utl_pkg
- as
- /* ********************************************************************
- Ver Date Author Description
- --------- ---------- --------------- ------------------------------------
- 1.0 2014-3-29 gangjh 1. 用sql select 讀取open office ods數據
-
- ********************************************************************/
- subtype celltype is varchar2(4000 byte) ; -- limit for oracle 10g
- type xls_row is record (
- sheet int ,
- row_idx int ,
- col0 celltype,
- col1 celltype,
- col2 celltype,
- col3 celltype,
- col4 celltype,
- col5 celltype,
- col6 celltype,
- col7 celltype,
- col8 celltype,
- col9 celltype,
- col10 celltype,
- col11 celltype,
- col12 celltype,
- col13 celltype,
- col14 celltype,
- col15 celltype,
- col16 celltype,
- col17 celltype,
- col18 celltype,
- col19 celltype
- );
-
- type xls_table is table of xls_row ;
-
- function get_xml (p_blob in blob, p_file_name in varchar2) return xmltype ;
- function get_ods_table(p_data in blob) return xls_table pipelined ;
- function get_ods_table2(p_data in blob) return xls_table pipelined ;
- end ods_utl_pkg;
- /
- create or replace package body ods_utl_pkg
- as
- subtype ods_table is xmltype;
- ns_ods constant varchar2(32767) := '
- xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0"
- xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0"
- xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0"
- xmlns:number="urn:oasis:names:tc:opendocument:xmlns:datastyle:1.0"
- ';
- -- for openoffice ods
- /*
- select ods_utl_pkg.get_xml(utl_lob.url2blob('ftp://172.17.2.43/pub/test/work.ods'),'content.xml') aa
- from dual;
- */
- function get_xml(p_blob in blob, p_file_name varchar2) return xmltype is
- l_clob clob;
- l_blob blob ;
- dest_offset integer := 1;
- src_offset integer := 1;
- lang_context integer := 0;
- warning integer :=0;
- begin
-
- l_blob := as_zip.get_file (p_blob, p_file_name);
- dbms_lob.createtemporary(l_clob, true, dbms_lob.call) ;
- dbms_lob.convertToclob( l_clob
- , l_blob
- , dbms_lob.lobmaxsize
- , dest_offset
- , src_offset
- , nls_charset_id( 'AL32UTF8' )
- , lang_context
- , warning
- );
-
- return xmltype (l_clob);
- end get_xml;
- function get_table(p_ods xmltype , idx pls_integer) return xmltype is
- xpath_tab constant varchar2(200) :=
- '/office:document-content/office:body/office:spreadsheet/table:table['|| idx ||']' ;
- begin
- if p_ods.existsNode(xpath_tab, ns_ods) > 0 then
- return p_ods.extract(xpath_tab, ns_ods);
- else
- return null;
- end if;
- end ;
- function getStringval(p_node xmltype, p_xpath varchar2, p_ns varchar2 default ns_ods ) return varchar2 is
- t xmltype;
- begin
- t := p_node.extract(p_xpath, p_ns) ;
- return case when t is null then null else t.getStringval() end ;
- end ;
- function getNumberval(p_node xmltype, p_xpath varchar2, p_ns varchar2 default ns_ods ) return Number is
- t xmltype;
- begin
- t := p_node.extract(p_xpath, p_ns) ;
- return case when t is null then null else t.getNumberval() end ;
- end ;
- function get_Cellvalue(p_cell xmltype) return varchar2 is
- vdata_type varchar2(20) ;
- vdata celltype ;
- begin
- vdata_type := getStringval(p_cell, '//@office:value-type') ;
-
- if vdata_type = 'string' then
- vdata := getStringval(p_cell, '//text:p/node()');
- if vdata like '<text:s xmlns:text%' then
- vdata := getStringval(xmltype(vdata), '/text:s/text()') ;
- end if;
- elsif vdata_type = 'float' then
- vdata := getStringval(p_cell, '//text:p/node()');
- else
- vdata := null;
- end if;
-
- return vdata ;
- end ;
- procedure fill_cell(p_row in out xls_row, v_cellval celltype, c pls_integer) is
- begin
- case
- when c=0 then p_row.col0 := v_cellval ;
- when c=1 then p_row.col1 := v_cellval ;
- when c=2 then p_row.col2 := v_cellval ;
- when c=3 then p_row.col3 := v_cellval ;
- when c=4 then p_row.col4 := v_cellval ;
- when c=5 then p_row.col5 := v_cellval ;
- when c=6 then p_row.col6 := v_cellval ;
- when c=7 then p_row.col7 := v_cellval ;
- when c=8 then p_row.col8 := v_cellval ;
- when c=9 then p_row.col9 := v_cellval ;
- when c=10 then p_row.col10 := v_cellval ;
- when c=11 then p_row.col11 := v_cellval ;
- when c=12 then p_row.col12 := v_cellval ;
- when c=13 then p_row.col13 := v_cellval ;
- when c=14 then p_row.col14 := v_cellval ;
- when c=15 then p_row.col15 := v_cellval ;
- when c=16 then p_row.col16 := v_cellval ;
- when c=17 then p_row.col17 := v_cellval ;
- when c=18 then p_row.col18 := v_cellval ;
- when c=19 then p_row.col19 := v_cellval ;
- end case;
- end;
- function get_ods_row_value(p_xmlrow xmltype) return xls_row is
- c_cell_path constant varchar2(200) :='/table:table-row/table:covered-table-cell |
- /table:table-row/table:table-cell';
- cursor cur_cell is
- select rownum,value(p) data
- from table(xmlsequence( extract(p_xmlrow, c_cell_path, ns_ods))) p;
- v_row xls_row ;
- vrepeated pls_integer ;
- v_current_col pls_integer := 1 ;
- begin
- for r in cur_cell loop
- vrepeated := getNumberval(r.data, '//@table:number-columns-repeated') ;
-
- fill_cell(v_row, get_Cellvalue(r.data), v_current_col -1) ;
-
- v_current_col := v_current_col + nvl(vrepeated,1) ;
-
- exit when v_current_col > 20 ; --limit for 20 column
- end loop;
- return v_row;
- end get_ods_row_value;
-
- function get_ods_table(p_data in blob) return xls_table pipelined is
- xpath_row varchar2(2000) := '/table:table/table:table-row' ;
-
- v_row xls_row ;
- cursor c_2(p_table xmltype)
- is
- select value(p) row_data
- from table(xmlsequence(extract(p_table, xpath_row, ns_ods))) p;
-
- vx_tab ods_table := null;
- vx_ods xmltype;
- v_rows_repeated pls_integer ;
- v_current_row pls_integer ;
- begin
- vx_ods := get_xml(p_data, 'content.xml') ;
-
- for i in 1..100 loop
- vx_tab := get_table(vx_ods, i) ;
- exit when vx_tab is null;
- v_current_row :=1;
- for r2 in c_2(vx_tab) loop
- v_rows_repeated := getNumberval(r2.row_data, '/table:table-row/@table:number-rows-repeated');
-
- v_row := get_ods_row_value(r2.row_data) ;
- v_row.sheet := i;
- v_row.row_idx := v_current_row ;
- v_current_row := v_current_row + nvl(v_rows_repeated, 1);
- pipe row( v_row) ;
- end loop;
- end loop;
- return ;
- end get_ods_table;
- end ods_utl_pkg;
- /
ods_utl_pkg需要as_zip 包來解壓blob數據.
as_zip可由網上取得http://technology.amis.nl/wp-content/uploads/images/as_zip.txt
讀取基本的數據是沒有問題的.
如使用中遇到bug, 可站內告知我,十分感謝.
阿飛
2014/03/29