Oracle读取excel数据_Oracle

废话不多说了，直接给大家奔入主题了。

				?

									--解析excel，转换成table，可供查询，支持xls、xlsx

									--首先修改这个Type，长度改为4000. 

									CREATE OR REPLACE TYPE XYG_PUB_DATA_UPLOAD_Obj AS OBJECT( 

									SOURCE_TYPE VARCHAR2(240)--EXCEL/TXT 

									,BATCH_CODE VARCHAR2 (480 BYTE)--批的Code，Excel用，因为一个Excel可能有多个 

									,BATCH_NAME VARCHAR2 (4000 BYTE)--批的名称 

									,ROW_NUM NUMBER 

									,ATTRIBUTE1 VARCHAR2 (4000 BYTE) 

									,ATTRIBUTE2 VARCHAR2 (4000 BYTE) 

									,ATTRIBUTE3 VARCHAR2 (4000 BYTE) 

									,ATTRIBUTE4 VARCHAR2 (4000 BYTE) 

									,ATTRIBUTE5 VARCHAR2 (4000 BYTE) 

									,ATTRIBUTE6 VARCHAR2 (4000 BYTE) 

									,ATTRIBUTE7 VARCHAR2 (4000 BYTE)

									,ATTRIBUTE8 VARCHAR2 (4000 BYTE) 

									,ATTRIBUTE9 VARCHAR2 (4000 BYTE) 

									,ATTRIBUTE10 VARCHAR2 (4000 BYTE) 

									,ATTRIBUTE11 VARCHAR2 (4000 BYTE) 

									,ATTRIBUTE12 VARCHAR2 (4000 BYTE) 

									,ATTRIBUTE13 VARCHAR2 (4000 BYTE) 

									,ATTRIBUTE14 VARCHAR2 (4000 BYTE) 

									,ATTRIBUTE15 VARCHAR2 (4000 BYTE) 

									,ATTRIBUTE16 VARCHAR2 (4000 BYTE) 

									,ATTRIBUTE17 VARCHAR2 (4000 BYTE) 

									,ATTRIBUTE18 VARCHAR2 (4000 BYTE) 

									,ATTRIBUTE19 VARCHAR2 (4000 BYTE) 

									,ATTRIBUTE20 VARCHAR2 (4000 BYTE) 

									,ATTRIBUTE21 VARCHAR2 (4000 BYTE) 

									,ATTRIBUTE22 VARCHAR2 (4000 BYTE) 

									,ATTRIBUTE23 VARCHAR2 (4000 BYTE) 

									,ATTRIBUTE24 VARCHAR2 (4000 BYTE) 

									,ATTRIBUTE25 VARCHAR2 (4000 BYTE) 

									,ATTRIBUTE26 VARCHAR2 (4000 BYTE) 

									,ATTRIBUTE27 VARCHAR2 (4000 BYTE) 

									,ATTRIBUTE28 VARCHAR2 (4000 BYTE) 

									,ATTRIBUTE29 VARCHAR2 (4000 BYTE) 

									,ATTRIBUTE30 VARCHAR2 (4000 BYTE) 

									,PROCESS_FLAG NUMBER ---0:INIT,3:SUCESSFUL 

									,PROCESS_MESSAGE VARCHAR2(4000));

包体内容，包头：

				?

									create or replace package xyg_pub_data_upload_pkg as

									/*TYPE XYG_PUB_DATA_UPLOAD_Obj is record(

									SOURCE_TYPE VARCHAR2(240)--EXCEL/TXT

									,BATCH_CODE VARCHAR2 (480 BYTE)--批的Code，Excel用，因为一个Excel可能有多个

									,BATCH_NAME VARCHAR2 (4000 BYTE)--批的名称

									,ROW_NUM NUMBER

									,ATTRIBUTE1 VARCHAR2 (4000 BYTE)

									,ATTRIBUTE2 VARCHAR2 (4000 BYTE)

									,ATTRIBUTE3 VARCHAR2 (4000 BYTE)

									,ATTRIBUTE4 VARCHAR2 (4000 BYTE)

									,ATTRIBUTE5 VARCHAR2 (4000 BYTE)

									,ATTRIBUTE6 VARCHAR2 (4000 BYTE)

									,ATTRIBUTE7 VARCHAR2 (4000 BYTE)

									,ATTRIBUTE8 VARCHAR2 (4000 BYTE)

									,ATTRIBUTE9 VARCHAR2 (4000 BYTE)

									,ATTRIBUTE10 VARCHAR2 (4000 BYTE)

									,ATTRIBUTE11 VARCHAR2 (4000 BYTE)

									,ATTRIBUTE12 VARCHAR2 (4000 BYTE)

									,ATTRIBUTE13 VARCHAR2 (4000 BYTE)

									,ATTRIBUTE14 VARCHAR2 (4000 BYTE)

									,ATTRIBUTE15 VARCHAR2 (4000 BYTE)

									,ATTRIBUTE16 VARCHAR2 (4000 BYTE)

									,ATTRIBUTE17 VARCHAR2 (4000 BYTE)

									,ATTRIBUTE18 VARCHAR2 (4000 BYTE)

									,ATTRIBUTE19 VARCHAR2 (4000 BYTE)

									,ATTRIBUTE20 VARCHAR2 (4000 BYTE)

									,ATTRIBUTE21 VARCHAR2 (4000 BYTE)

									,ATTRIBUTE22 VARCHAR2 (4000 BYTE)

									,ATTRIBUTE23 VARCHAR2 (4000 BYTE)

									,ATTRIBUTE24 VARCHAR2 (4000 BYTE)

									,ATTRIBUTE25 VARCHAR2 (4000 BYTE)

									,ATTRIBUTE26 VARCHAR2 (4000 BYTE)

									,ATTRIBUTE27 VARCHAR2 (4000 BYTE)

									,ATTRIBUTE28 VARCHAR2 (4000 BYTE)

									,ATTRIBUTE29 VARCHAR2 (4000 BYTE)

									,ATTRIBUTE30 VARCHAR2 (4000 BYTE)

									,PROCESS_FLAG NUMBER ---0:INIT,3:SUCESSFUL

									,PROCESS_MESSAGE VARCHAR2(4000));

									*/

									type xyg_pub_data_upload_obj_tab is table of xyg_pub_data_upload_obj;

									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;

									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;

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

									------程序主体部分----

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

									function conver_excel_to_tab(p_document blob,

									p_sheets in varchar2 default null,

									p_raise in number default /*xyg_pub_const_pkg.*/ c_true)

									return xyg_pub_data_upload_obj_tab

									pipelined;

									end xyg_pub_data_upload_pkg; 

									包体：

									create or replace package body xyg_pub_data_upload_pkg as

									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

									dbms_output.put_line('转换文件有异常错误!进度:' || l_process_phase);

									raise;

									/*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;

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

									------程序主体部分----

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

									function conver_excel_to_tab(p_document blob,

									p_sheets in varchar2 default null,

									p_raise in number default /*xyg_pub_const_pkg.*/ c_true)

									return xyg_pub_data_upload_obj_tab

									pipelined is

									type tp_cell is record(

									data_type varchar2(1),

									string_val varchar2(32767),

									number_val number,

									date_val date,

									blob_val blob);

									type tp_row is table of tp_cell index by pls_integer;

									type tp_rows is table of tp_row index by pls_integer;

									type tp_sheet is record(

									name varchar2(2000),

									rows tp_rows);

									type tp_data is table of tp_sheet index by pls_integer;

									t_data tp_data;

									t_collection_base varchar2(32767);

									t_collection_name varchar2(32767);

									--type tp_2col is table of vc_arr2 index by pls_integer;

									--t2 tp_2col;

									l_round char(1) := 'Y';

									l_process_phase number;

									---xls:

									type tp_sheet_rec is record(

									name varchar2(32767),

									ind integer);

									type tp_sheets is table of tp_sheet_rec index by pls_integer;

									t_sheets tp_sheets;

									t_sheet tp_sheet_rec;

									--xlsx:

									type tp_strings is table of varchar2(32767) index by pls_integer;

									t_sheet_ids tp_strings;

									t_sheet_names tp_strings;

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

									----XLS解析器----

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

									function g1(i pls_integer, r pls_integer, c pls_integer) return varchar2 is

									l_return varchar2(4000);

									begin

									if l_round = 'Y' then

									l_return := case

									when t_data(i).rows(r).exists(c) then

									coalesce(substr(t_data(i).rows(r)(c).string_val, 1, 4000),

									to_char(round(t_data(i).rows(r)(c).number_val,

									14 -

									substr(to_char(t_data(i).rows(r)(c)

									.number_val, 'TME'), -3)),

									'TM9'),

									to_char(t_data(i).rows(r)(c).date_val,

									'yyyy-mm-dd hh24:mi:ss'))

									end;

									else

									l_return := case

									when t_data(i).rows(r).exists(c) then

									coalesce(substr(t_data(i).rows(r)(c).string_val, 1, 4000),

									to_char(t_data(i).rows(r)(c).number_val, 'TM9'),

									to_char(t_data(i).rows(r)(c).date_val,

									'yyyy-mm-dd hh24:mi:ss'))

									end;

									end if;

									return l_return;

									--return case when t_data(i)(r).exists(c) then substr( t_data(i)(r)(c), 1, 4000 ) end;

									end;

									function parse_xls(p_document blob,

									p_sheets varchar2 := null,

									p_extra dbmsoutput_linesarray := null)

									return tp_data is

									t_cell tp_cell;

									t_rows tp_rows;

									t_data tp_data;

									t_workbook blob;

									t_ind integer;

									t_sind integer;

									t_len integer;

									t_max_len integer;

									t_cnt integer;

									t_grbit raw(1);

									t_biff5 boolean;

									t_str varchar2(32767);

									t_tmp raw(32767);

									t_rec raw(32767);

									t_date1904 boolean;

									type tp_sst is table of varchar2(32767) index by pls_integer;

									t_sst tp_sst;

									type tp_date is table of boolean index by pls_integer;

									t_xf_date tp_date;

									t_fmt_date tp_date;

									type tp_xf_fmt is table of pls_integer index by pls_integer;

									t_xf_fmt tp_xf_fmt;

									t_fmt varchar2(32767);

									t_char_set varchar2(100) := 'WE8MSWIN1252';

									t_c pls_integer;

									t_type varchar2(1);

									t_max_c pls_integer;

									procedure read_unicode_string is

									t_uni raw(32767);

									begin

									t_str := null;

									while t_cnt > 0 loop

									if utl_raw.bit_and(t_grbit, hextoraw('01')) = hextoraw('01') then

									if (t_sind + t_cnt * 2 > utl_raw.length(t_rec) + 1 and

									dbms_lob.substr(t_workbook, 2, t_ind + t_len + 4) =

									hextoraw('3C00')) then

									t_str := t_str ||

									utl_i18n.raw_to_char(utl_raw.substr(t_rec, t_sind),

									'AL16UTF16LE');

									t_cnt := t_cnt -

									utl_raw.length(utl_raw.substr(t_rec, t_sind)) / 2;

									t_ind := t_ind + t_len + 4;

									t_len := utl_raw.cast_to_binary_integer(dbms_lob.substr(t_workbook,

									2,

									t_ind + 2),

									utl_raw.little_endian);

									t_grbit := dbms_lob.substr(t_workbook, 1, t_ind + 4);

									t_rec := dbms_lob.substr(t_workbook, t_len - 1,

									t_ind + 4 + 1);

									t_sind := 1;

									else

									t_str := t_str ||

									utl_i18n.raw_to_char(utl_raw.substr(t_rec, t_sind,

									t_cnt * 2),

									'AL16UTF16LE');

									t_sind := t_sind + t_cnt * 2;

									t_cnt := 0;

									end if;

									else

									if (t_sind + t_cnt > utl_raw.length(t_rec) + 1 and

									dbms_lob.substr(t_workbook, 2, t_ind + t_len + 4) =

									hextoraw('3C00')) then

									t_tmp := utl_raw.substr(t_rec, t_sind);

									t_cnt := t_cnt -

									utl_raw.length(utl_raw.substr(t_rec, t_sind));

									t_ind := t_ind + t_len + 4;

									t_len := utl_raw.cast_to_binary_integer(dbms_lob.substr(t_workbook,

									2,

									t_ind + 2),

									utl_raw.little_endian);

									t_grbit := dbms_lob.substr(t_workbook, 1, t_ind + 4);

									t_rec := dbms_lob.substr(t_workbook, t_len - 1,

									t_ind + 4 + 1);

									t_sind := 1;

									else

									t_tmp := utl_raw.substr(t_rec, t_sind, t_cnt);

									t_sind := t_sind + t_cnt;

									t_cnt := 0;

									end if;

									t_uni := null;

									for i in 1 .. utl_raw.length(t_tmp) loop

									t_uni := utl_raw.concat(t_uni, utl_raw.substr(t_tmp, i, 1),

									hextoraw('00'));

									end loop;

									t_str := t_str || utl_i18n.raw_to_char(t_uni, 'AL16UTF16LE');

									end if;

									end loop;

									end;

									function rk2number(p_rk raw) return number is

									begin

									return case rawtohex(utl_raw.bit_and(utl_raw.substr(p_rk, 1, 1),

									'03')) when '02' then utl_raw.cast_to_binary_integer(utl_raw.bit_and(p_rk,

									'FCFFFFFF'),

									utl_raw.little_endian) / 4 when '03' then utl_raw.cast_to_binary_integer(utl_raw.bit_and(p_rk,

									'FCFFFFFF'),

									utl_raw.little_endian) / 400 when '00' then utl_raw.cast_to_binary_double(utl_raw.concat('00000000',

									p_rk),

									utl_raw.little_endian) when '01' then utl_raw.cast_to_binary_double(utl_raw.concat('00000000',

									utl_raw.bit_and(p_rk,

									'FCFFFFFF')),

									utl_raw.little_endian) / 100 end;

									end;

									function num2date(p_num number) return date is

									begin

									if t_date1904 then

									return to_date('01-01-1904', 'DD-MM-YYYY') + p_num;

									end if;

									return to_date('01-03-1900', 'DD-MM-YYYY') +(p_num - 61);

									end;

									procedure read_cfb(p_cf blob) is

									t_header raw(512);

									t_byte_order pls_integer;

									t_encoding varchar2(30);

									t_ssz pls_integer;

									t_sssz pls_integer;

									t_sectid pls_integer;

									t_tmp_sectid t_sectid%type;

									type tp_secids is table of t_sectid%type index by pls_integer;

									t_msat tp_secids;

									t_sat tp_secids;

									t_ssat tp_secids;

									t_sector raw(2048);

									t_short_container blob;

									t_stream blob;

									t_len pls_integer;

									t_name varchar2(32 char);

									c_free_secid constant pls_integer := -1;

									c_end_of_chain_secid constant pls_integer := -2;

									c_sat_secid constant pls_integer := -3;

									c_msat_secid constant pls_integer := -4;

									c_dir_empty constant raw(1) := hextoraw('00');

									c_dir_storage constant raw(1) := hextoraw('01');

									c_dir_stream constant raw(1) := hextoraw('02');

									c_dir_lock constant raw(1) := hextoraw('03');

									c_dir_property constant raw(1) := hextoraw('04');

									c_dir_root constant raw(1) := hextoraw('05');

									begin

									t_header := dbms_lob.substr(p_cf, 512, 1);

									if (t_header is null or utl_raw.length(t_header) < 512 or

									utl_raw.substr(t_header, 1, 8) != hextoraw('D0CF11E0A1B11AE1')) then

									return;

									end if;

									t_byte_order := case

									when utl_raw.substr(t_header, 29, 2) = hextoraw('FEFF') then

									utl_raw.little_endian

									else

									utl_raw.big_endian

									end;

									if t_byte_order = utl_raw.little_endian then

									t_encoding := 'AL16UTF16LE';

									else

									t_encoding := 'AL16UTF16';

									end if;

									t_ssz := power(2,

									utl_raw.cast_to_binary_integer(utl_raw.substr(t_header,

									31, 2),

									t_byte_order));

									t_sssz := power(2,

									utl_raw.cast_to_binary_integer(utl_raw.substr(t_header,

									33, 2),

									t_byte_order));

									for i in 0 .. 109 - 1 loop

									t_sectid := utl_raw.cast_to_binary_integer(utl_raw.substr(t_header,

									77 +

									i * 4, 4),

									t_byte_order);

									exit when t_sectid = c_free_secid;

									t_msat(i) := t_sectid;

									end loop;

									t_sectid := utl_raw.cast_to_binary_integer(utl_raw.substr(t_header,

									69, 4),

									t_byte_order);

									while t_sectid != c_end_of_chain_secid loop

									t_sector := dbms_lob.substr(p_cf, t_ssz,

									512 + t_ssz * t_sectid + 1);

									for i in 0 .. t_ssz / 4 - 2 loop

									t_msat(t_msat.count()) := utl_raw.cast_to_binary_integer(utl_raw.substr(t_sector,

									i * 4 + 1,

									4),

									t_byte_order);

									end loop;

									t_sectid := utl_raw.cast_to_binary_integer(utl_raw.substr(t_sector,

									-4, 4),

									t_byte_order);

									end loop;

									for j in 0 .. t_msat.count() - 1 loop

									t_sector := dbms_lob.substr(p_cf, t_ssz,

									512 + t_ssz * t_msat(j) + 1);

									for i in 0 .. t_ssz / 4 - 1 loop

									t_sat(t_sat.count()) := utl_raw.cast_to_binary_integer(utl_raw.substr(t_sector,

									i * 4 + 1,

									4),

									t_byte_order);

									end loop;

									end loop;

									t_sectid := utl_raw.cast_to_binary_integer(utl_raw.substr(t_header,

									61, 4),

									t_byte_order);

									while t_sectid != c_end_of_chain_secid loop

									t_sector := dbms_lob.substr(p_cf, t_ssz,

									512 + t_ssz * t_sectid + 1);

									for i in 0 .. t_ssz / 4 - 1 loop

									t_ssat(t_ssat.count()) := utl_raw.cast_to_binary_integer(utl_raw.substr(t_sector,

									i * 4 + 1,

									4),

									t_byte_order);

									end loop;

									t_sectid := t_sat(t_sectid);

									end loop;

									t_sectid := utl_raw.cast_to_binary_integer(utl_raw.substr(t_header,

									49, 4),

									t_byte_order);

									while t_sectid != c_end_of_chain_secid loop

									t_sector := dbms_lob.substr(p_cf, t_ssz,

									512 + t_ssz * t_sectid + 1);

									for i in 0 .. t_ssz / 128 - 1 loop

									t_len := utl_raw.cast_to_binary_integer(utl_raw.substr(t_sector,

									i * 128 + 65,

									2),

									t_byte_order);

									if t_len > 2 then

									t_name := utl_i18n.raw_to_char(utl_raw.substr(t_sector,

									i * 128 + 1,

									t_len - 2),

									t_encoding);

									end if;

									case utl_raw.substr(t_sector, i * 128 + 67, 1)

									when c_dir_stream then

									dbms_lob.createtemporary(t_stream, true);

									t_tmp_sectid := utl_raw.cast_to_binary_integer(utl_raw.substr(t_sector,

									i * 128 + 117,

									4),

									t_byte_order);

									t_len := utl_raw.cast_to_binary_integer(utl_raw.substr(t_sector,

									i * 128 + 121,

									4),

									t_byte_order);

									if t_len >=

									utl_raw.cast_to_binary_integer(utl_raw.substr(t_header,

									57, 4),

									t_byte_order) then

									while t_tmp_sectid != c_end_of_chain_secid loop

									dbms_lob.append(t_stream,

									dbms_lob.substr(p_cf, t_ssz,

									512 +

									t_ssz * t_tmp_sectid + 1));

									t_tmp_sectid := t_sat(t_tmp_sectid);

									end loop;

									else

									while t_tmp_sectid != c_end_of_chain_secid loop

									dbms_lob.append(t_stream,

									dbms_lob.substr(t_short_container,

									t_sssz,

									t_sssz * t_tmp_sectid + 1));

									t_tmp_sectid := t_ssat(t_tmp_sectid);

									end loop;

									end if;

									dbms_lob.trim(t_stream, t_len);

									if t_name = 'Workbook' then

									t_workbook := t_stream;

									end if;

									if t_name = 'Book' then

									t_workbook := t_stream;

									end if;

									when c_dir_root then

									dbms_lob.createtemporary(t_short_container, true);

									t_tmp_sectid := utl_raw.cast_to_binary_integer(utl_raw.substr(t_sector,

									i * 128 + 117,

									4),

									t_byte_order);

									while t_tmp_sectid != c_end_of_chain_secid loop

									dbms_lob.append(t_short_container,

									dbms_lob.substr(p_cf, t_ssz,

									512 + t_ssz * t_tmp_sectid + 1));

									t_tmp_sectid := t_sat(t_tmp_sectid);

									end loop;

									else

									null;

									end case;

									end loop;

									t_sectid := t_sat(t_sectid);

									end loop;

									if dbms_lob.istemporary(t_short_container) = 1 then

									dbms_lob.freetemporary(t_short_container);

									end if;

									if dbms_lob.istemporary(t_stream) = 1 then

									dbms_lob.freetemporary(t_stream);

									end if;

									end;

									begin

									--my_log('parsing XLS');

									read_cfb(p_document);

									if t_workbook is null or dbms_lob.getlength(t_workbook) = 0 then

									--my_log('No workbook file found');

									raise_application_error(-20003, 'Not a valid XLS-file', true);

									end if;

									t_ind := 1;

									t_max_len := dbms_lob.getlength(t_workbook);

									if (dbms_lob.substr(t_workbook, 2, t_ind) = hextoraw('0908') and

									dbms_lob.substr(t_workbook, 2, t_ind + 4) in

									(hextoraw('0005'), hextoraw('0006')) and

									dbms_lob.substr(t_workbook, 2, t_ind + 6) = hextoraw('0500')) then

									t_biff5 := dbms_lob.substr(t_workbook, 2, t_ind + 4) =

									hextoraw('0005');

									t_len := utl_raw.cast_to_binary_integer(dbms_lob.substr(t_workbook,

									2,

									t_ind + 2),

									utl_raw.little_endian);

									t_ind := t_ind + t_len + 4;

									loop

									exit when t_ind >= t_max_len;

									exit when dbms_lob.substr(t_workbook, 2, t_ind) = hextoraw('0A00');

									t_len := utl_raw.cast_to_binary_integer(dbms_lob.substr(t_workbook,

									2,

									t_ind + 2),

									utl_raw.little_endian);

									if dbms_lob.substr(t_workbook, 2, t_ind) = hextoraw('FC00') then

									declare

									t_run integer;

									t_ext integer;

									procedure add_cont(p_len pls_integer) is

									begin

									if (t_sind + p_len > utl_raw.length(t_rec) + 1 and

									dbms_lob.substr(t_workbook, 2, t_ind + t_len + 4) =

									hextoraw('3C00')) then

									t_ind := t_ind + t_len + 4;

									t_len := utl_raw.cast_to_binary_integer(dbms_lob.substr(t_workbook,

									2,

									t_ind + 2),

									utl_raw.little_endian);

									if t_sind <= utl_raw.length(t_rec) then

									t_rec := utl_raw.concat(utl_raw.substr(t_rec, t_sind),

									dbms_lob.substr(t_workbook, t_len,

									t_ind + 4));

									else

									t_rec := dbms_lob.substr(t_workbook, t_len, t_ind + 4);

									end if;

									t_sind := 1;

									end if;

									end;

									begin

									t_sind := 1;

									t_rec := dbms_lob.substr(t_workbook, t_len - 8, t_ind + 12);

									for j in 1 .. utl_raw.cast_to_binary_integer(dbms_lob.substr(t_workbook,

									4,

									t_ind + 8),

									utl_raw.little_endian) loop

									add_cont(3);

									t_cnt := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,

									t_sind,

									2),

									utl_raw.little_endian);

									t_sind := t_sind + 2;

									t_grbit := utl_raw.substr(t_rec, t_sind, 1);

									t_sind := t_sind + 1;

									if utl_raw.bit_and(t_grbit, hextoraw('08')) =

									hextoraw('08') then

									add_cont(2);

									t_run := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,

									t_sind,

									2),

									utl_raw.little_endian);

									t_sind := t_sind + 2;

									else

									t_run := 0;

									end if;

									if utl_raw.bit_and(t_grbit, hextoraw('04')) =

									hextoraw('04') then

									add_cont(4);

									t_ext := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,

									t_sind,

									4),

									utl_raw.little_endian);

									if t_ext < 0 then

									t_ext := t_ext + 4294967296;

									end if;

									t_sind := t_sind + 4;

									else

									t_ext := 0;

									end if;

									read_unicode_string;

									t_sst(t_sst.count()) := t_str;

									add_cont(t_run * 4 + t_ext);

									t_sind := t_sind + t_run * 4 + t_ext;

									end loop;

									end;

									elsif dbms_lob.substr(t_workbook, 2, t_ind) = hextoraw('8500') then

									t_rec := dbms_lob.substr(t_workbook, t_len, t_ind + 4);

									if t_biff5 then

									t_cnt := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,

									7,

									1),

									utl_raw.little_endian);

									t_tmp := utl_raw.substr(t_rec, 8, t_cnt);

									t_sheet.name := utl_i18n.raw_to_char(t_tmp, t_char_set);

									else

									t_cnt := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,

									7, 1));

									t_grbit := utl_raw.substr(t_rec, 8, 1);

									if utl_raw.bit_and(t_grbit, hextoraw('01')) = hextoraw('01') then

									t_str := utl_raw.substr(t_rec, 9, t_cnt * 2);

									else

									t_str := null;

									t_tmp := utl_raw.substr(t_rec, 9, t_cnt);

									for i in 1 .. utl_raw.length(t_tmp) loop

									t_str := utl_raw.concat(t_str, utl_raw.substr(t_tmp, i, 1),

									hextoraw('00'));

									end loop;

									end if;

									t_sheet.name := utl_i18n.raw_to_char(t_str, 'AL16UTF16LE');

									end if;

									t_sheet.ind := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,

									1, 4),

									utl_raw.little_endian);

									t_sheets(t_sheets.count()) := t_sheet;

									elsif dbms_lob.substr(t_workbook, 2, t_ind) = hextoraw('2200') then

									t_date1904 := dbms_lob.substr(t_workbook, 2, t_ind + 4) =

									hextoraw('0100');

									elsif dbms_lob.substr(t_workbook, 2, t_ind) = hextoraw('1E04') then

									t_rec := dbms_lob.substr(t_workbook, t_len, t_ind + 4);

									if t_biff5 then

									t_cnt := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,

									3, 1),

									utl_raw.little_endian);

									t_tmp := utl_raw.substr(t_rec, 4, t_cnt);

									t_fmt := utl_i18n.raw_to_char(t_tmp, t_char_set);

									else

									t_cnt := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,

									3, 2),

									utl_raw.little_endian);

									t_grbit := utl_raw.substr(t_rec, 5, 1);

									if utl_raw.bit_and(t_grbit, hextoraw('01')) = hextoraw('01') then

									t_str := utl_raw.substr(t_rec, 6, t_cnt * 2);

									else

									t_str := null;

									t_tmp := utl_raw.substr(t_rec, 6, t_cnt);

									for i in 1 .. utl_raw.length(t_tmp) loop

									t_str := utl_raw.concat(t_str, utl_raw.substr(t_tmp, i, 1),

									hextoraw('00'));

									end loop;

									end if;

									t_fmt := utl_i18n.raw_to_char(t_str, 'AL16UTF16LE');

									end if;

									t_fmt_date(utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec, 1, 2), utl_raw.little_endian)) := (instr(t_fmt,

									'dd') > 0 or

									instr(t_fmt,

									'mm') > 0 or

									instr(t_fmt,

									'yy') > 0);

									elsif dbms_lob.substr(t_workbook, 2, t_ind) = hextoraw('E000') then

									t_rec := dbms_lob.substr(t_workbook, t_len, t_ind + 4);

									t_xf_fmt(t_xf_fmt.count()) := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,

									3,

									2),

									utl_raw.little_endian);

									elsif dbms_lob.substr(t_workbook, 2, t_ind) = hextoraw('4200') then

									t_rec := dbms_lob.substr(t_workbook, t_len, t_ind + 4);

									if (rawtohex(t_rec) in ('1027', '0080') and

									nls_charset_id('WE8MACROMAN8') is not null) then

									t_char_set := 'WE8MACROMAN8';

									end if;

									end if;

									t_ind := t_ind + t_len + 4;

									end loop;

									t_fmt_date(14) := true;

									t_fmt_date(15) := true;

									t_fmt_date(16) := true;

									t_fmt_date(17) := true;

									t_fmt_date(22) := true;

									for i in 0 .. t_xf_fmt.count() - 1 loop

									t_xf_date(i) := t_fmt_date.exists(t_xf_fmt(i)) and

									t_fmt_date(t_xf_fmt(i));

									end loop;

									end if;

									for s in 0 .. t_sheets.count - 1 loop

									t_ind := t_sheets(s).ind + 1;

									if (dbms_lob.substr(t_workbook, 2, t_ind) = hextoraw('0908') and

									dbms_lob.substr(t_workbook, 2, t_ind + 4) = hextoraw('0006') and

									dbms_lob.substr(t_workbook, 2, t_ind + 6) = hextoraw('1000') and

									(p_sheets is null or

									instr(':' || p_sheets || ':', ':' || to_char(s + 1) || ':') > 0 or

									instr(':' || p_sheets || ':', ':' || t_sheets(s).name || ':') > 0)) then

									t_max_c := 0;

									t_rows.delete;

									t_data(t_data.count + 1).name := t_sheets(s).name;

									--my_log('read ' || t_sheets(s).name);

									t_len := utl_raw.cast_to_binary_integer(dbms_lob.substr(t_workbook,

									2,

									t_ind + 2),

									utl_raw.little_endian);

									t_ind := t_ind + t_len + 4;

									loop

									exit when t_ind >= t_max_len;

									exit when dbms_lob.substr(t_workbook, 2, t_ind) = hextoraw('0A00');

									t_cell := null;

									t_len := utl_raw.cast_to_binary_integer(dbms_lob.substr(t_workbook,

									2,

									t_ind + 2),

									utl_raw.little_endian);

									if dbms_lob.substr(t_workbook, 2, t_ind) = hextoraw('7E02') then

									t_rec := dbms_lob.substr(t_workbook, t_len, t_ind + 4);

									if t_xf_date(utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,

									5, 2),

									utl_raw.little_endian)) then

									t_cell.data_type := 'D';

									t_cell.date_val := num2date(rk2number(utl_raw.substr(t_rec,

									7, 4)));

									else

									t_cell.data_type := 'N';

									t_cell.number_val := rk2number(utl_raw.substr(t_rec, 7, 4));

									end if;

									t_c := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec, 3,

									2),

									utl_raw.little_endian) + 1;

									t_max_c := greatest(t_max_c, t_c);

									t_rows(utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec, 1, 2), utl_raw.little_endian) + 1)(t_c) := t_cell;

									elsif dbms_lob.substr(t_workbook, 2, t_ind) = hextoraw('0302') then

									t_rec := dbms_lob.substr(t_workbook, t_len, t_ind + 4);

									if t_xf_date(utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,

									5, 2),

									utl_raw.little_endian)) then

									t_cell.data_type := 'D';

									t_cell.date_val := num2date(utl_raw.cast_to_binary_double(utl_raw.substr(t_rec,

									7,

									8),

									utl_raw.little_endian));

									else

									t_cell.data_type := 'N';

									t_cell.number_val := utl_raw.cast_to_binary_double(utl_raw.substr(t_rec,

									7,

									8),

									utl_raw.little_endian);

									end if;

									t_c := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec, 3,

									2),

									utl_raw.little_endian) + 1;

									t_max_c := greatest(t_max_c, t_c);

									t_rows(utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec, 1, 2), utl_raw.little_endian) + 1)(t_c) := t_cell;

									elsif dbms_lob.substr(t_workbook, 2, t_ind) = hextoraw('0600') then

									t_rec := dbms_lob.substr(t_workbook, t_len, t_ind + 4);

									if (rawtohex(utl_raw.substr(t_rec, 7, 1)) not in

									('00', '01', '02', '03') or

									utl_raw.substr(t_rec, 13, 2) != hextoraw('FFFF')) then

									if t_xf_date(utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,

									5,

									2),

									utl_raw.little_endian)) then

									t_cell.data_type := 'D';

									t_cell.date_val := num2date(utl_raw.cast_to_binary_double(utl_raw.substr(t_rec,

									7,

									8),

									utl_raw.little_endian));

									else

									t_cell.data_type := 'N';

									t_cell.number_val := utl_raw.cast_to_binary_double(utl_raw.substr(t_rec,

									7,

									8),

									utl_raw.little_endian);

									end if;

									t_c := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,

									3, 2),

									utl_raw.little_endian) + 1;

									t_max_c := greatest(t_max_c, t_c);

									t_rows(utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec, 1, 2), utl_raw.little_endian) + 1)(t_c) := t_cell;

									else

									case rawtohex(utl_raw.substr(t_rec, 7, 1))

									when '01' then

									t_cell.data_type := 'S';

									t_cell.string_val := case

									rawtohex(utl_raw.substr(t_rec, 9, 1))

									when '00' then

									'FALSE'

									when '01' then

									'TRUE'

									end;

									t_c := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,

									3, 2),

									utl_raw.little_endian) + 1;

									t_max_c := greatest(t_max_c, t_c);

									t_rows(utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec, 1, 2), utl_raw.little_endian) + 1)(t_c) := t_cell;

									when '02' then

									null;

									when '00' then

									if dbms_lob.substr(t_workbook, 2, t_ind + t_len + 4) =

									hextoraw('0702') then

									declare

									t_row pls_integer := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,

									1,

									2),

									utl_raw.little_endian) + 1;

									t_col pls_integer := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,

									3,

									2),

									utl_raw.little_endian) + 1;

									begin

									t_ind := t_ind + t_len + 4;

									t_len := utl_raw.cast_to_binary_integer(dbms_lob.substr(t_workbook,

									2,

									t_ind + 2),

									utl_raw.little_endian);

									t_rec := dbms_lob.substr(t_workbook,

									t_len, t_ind + 4);

									t_cnt := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,

									1,

									2),

									utl_raw.little_endian);

									t_sind := 4;

									t_cell.data_type := 'S';

									if t_biff5 then

									t_tmp := utl_raw.substr(t_rec, 3,

									t_cnt);

									t_cell.string_val := utl_i18n.raw_to_char(t_tmp,

									t_char_set);

									else

									t_grbit := dbms_lob.substr(t_rec, 1, 3);

									read_unicode_string;

									t_cell.string_val := t_str;

									end if;

									t_max_c := greatest(t_max_c, t_col);

									t_rows(t_row)(t_col) := t_cell;

									end;

									end if;

									else

									t_cell.data_type := 'S';

									t_cell.string_val := '';

									t_c := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,

									3, 2),

									utl_raw.little_endian) + 1;

									t_max_c := greatest(t_max_c, t_c);

									t_rows(utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec, 1, 2), utl_raw.little_endian) + 1)(t_c) := t_cell;

									end case;

									end if;

									elsif dbms_lob.substr(t_workbook, 2, t_ind) = hextoraw('0402') then

									t_rec := dbms_lob.substr(t_workbook, t_len, t_ind + 4);

									if t_biff5 then

									t_cell.data_type := 'S';

									t_cnt := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,

									7,

									2),

									utl_raw.little_endian);

									if t_cnt = 0 then

									t_cell.string_val := null;

									else

									t_tmp := utl_raw.substr(t_rec, 9, t_cnt);

									t_cell.string_val := utl_i18n.raw_to_char(t_tmp,

									t_char_set);

									end if;

									t_c := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,

									3, 2),

									utl_raw.little_endian) + 1;

									t_max_c := greatest(t_max_c, t_c);

									t_rows(utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec, 1, 2), utl_raw.little_endian) + 1)(t_c) := t_cell;

									end if;

									elsif dbms_lob.substr(t_workbook, 2, t_ind) = hextoraw('D600') then

									t_rec := dbms_lob.substr(t_workbook, t_len, t_ind + 4);

									if t_biff5 then

									t_cnt := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,

									7, 2),

									utl_raw.little_endian);

									t_tmp := utl_raw.substr(t_rec, 9, t_cnt);

									t_cell.data_type := 'S';

									t_cell.string_val := utl_i18n.raw_to_char(t_tmp, t_char_set);

									t_c := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,

									3, 2),

									utl_raw.little_endian) + 1;

									t_max_c := greatest(t_max_c, t_c);

									t_rows(utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec, 1, 2), utl_raw.little_endian) + 1)(t_c) := t_cell;

									end if;

									elsif dbms_lob.substr(t_workbook, 2, t_ind) = hextoraw('FD00') then

									t_rec := dbms_lob.substr(t_workbook, t_len, t_ind + 4);

									t_cell.data_type := 'S';

									t_c := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec, 3,

									2),

									utl_raw.little_endian) + 1;

									t_max_c := greatest(t_max_c, t_c);

									t_cell.string_val := t_sst(utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,

									7,

									4),

									utl_raw.little_endian));

									t_rows(utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec, 1, 2), utl_raw.little_endian) + 1)(t_c) := t_cell;

									elsif dbms_lob.substr(t_workbook, 2, t_ind) = hextoraw('BD00') then

									t_rec := dbms_lob.substr(t_workbook, t_len, t_ind + 4);

									t_cnt := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,

									3, 2),

									utl_raw.little_endian);

									for i in utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,

									3, 2),

									utl_raw.little_endian) .. utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,

									-2,

									2),

									utl_raw.little_endian) loop

									t_tmp := utl_raw.substr(t_rec, 5 + 6 * (i - t_cnt), 6);

									if t_xf_date(utl_raw.cast_to_binary_integer(utl_raw.substr(t_tmp,

									1,

									2),

									utl_raw.little_endian)) then

									t_cell.data_type := 'D';

									t_cell.date_val := num2date(rk2number(utl_raw.substr(t_tmp,

									3, 4)));

									else

									t_cell.data_type := 'N';

									t_cell.number_val := rk2number(utl_raw.substr(t_tmp, 3, 4));

									end if;

									t_max_c := greatest(t_max_c, i + 1);

									t_rows(utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec, 1, 2), utl_raw.little_endian) + 1)(i + 1) := t_cell;

									end loop;

									end if;

									t_ind := t_ind + t_len + 4;

									end loop;

									if t_rows.count > 0 then

									t_c := t_rows(t_rows.last).first;

									t_type := t_rows(t_rows.last)(t_c).data_type;

									for r in 1 .. t_rows.last - 1 loop

									if not t_rows.exists(r) then

									t_rows(r)(t_c).data_type := t_type;

									end if;

									end loop;

									if t_rows.count > 1 then

									for c in 1 .. t_max_c loop

									t_type := null;

									for r in 2 .. t_rows.last loop

									if t_rows(r).exists(c) then

									t_type := t_rows(r)(c).data_type;

									exit;

									end if;

									end loop;

									if t_type is null then

									if t_rows(1).exists(c) then

									t_type := t_rows(1)(c).data_type;

									else

									t_type := 'S';

									end if;

									end if;

									for r in 1 .. t_rows.last loop

									if not t_rows(r).exists(c) then

									t_rows(r)(c).data_type := t_type;

									end if;

									end loop;

									end loop;

									else

									for c in 1 .. t_max_c loop

									if not t_rows(1).exists(c) then

									t_rows(1)(c).data_type := 'S';

									end if;

									end loop;

									end if;

									end if;

									t_data(t_data.count).rows := t_rows;

									end if;

									end loop;

									return t_data;

									end;

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

									----XLSX解析器----

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

									function g2(i pls_integer, r pls_integer, c pls_integer) return varchar2 is

									l_return varchar2(4000);

									begin

									if l_round = 'Y' then

									l_return := case

									when t_data(i).rows(r).exists(c) then

									coalesce(substr(t_data(i).rows(r)(c).string_val, 1, 4000),

									to_char(round(t_data(i).rows(r)(c).number_val,

									14 -

									substr(to_char(t_data(i).rows(r)(c)

									.number_val, 'TME'), -3)),

									'TM9'),

									to_char(t_data(i).rows(r)(c).date_val,

									'yyyy-mm-dd hh24:mi:ss'))

									end;

									else

									l_return := case

									when t_data(i).rows(r).exists(c) then

									coalesce(substr(t_data(i).rows(r)(c).string_val, 1, 4000),

									to_char(t_data(i).rows(r)(c).number_val, 'TM9'),

									to_char(t_data(i).rows(r)(c).date_val,

									'yyyy-mm-dd hh24:mi:ss'))

									end;

									end if;

									return l_return;

									--return case when t_data(i)(r).exists(c) then substr( t_data(i)(r)(c), 1, 4000 ) end;

									end;

									function blob2node(p_blob blob) return dbms_xmldom.domnode is

									begin

									if p_blob is null or dbms_lob.getlength(p_blob) = 0 then

									return null;

									end if;

									return dbms_xmldom.makenode(dbms_xmldom.getdocumentelement(dbms_xmldom.newdomdocument(xmltype(p_blob,

									nls_charset_id('AL32UTF8')))));

									exception

									when others then

									declare

									t_nd dbms_xmldom.domnode;

									t_clob clob;

									t_dest_offset integer;

									t_src_offset integer;

									t_lang_context number := dbms_lob.default_lang_ctx;

									t_warning integer;

									begin

									dbms_lob.createtemporary(t_clob, true);

									t_dest_offset := 1;

									t_src_offset := 1;

									dbms_lob.converttoclob(t_clob, p_blob, dbms_lob.lobmaxsize,

									t_dest_offset, t_src_offset,

									nls_charset_id('AL32UTF8'), t_lang_context,

									t_warning);

									t_nd := dbms_xmldom.makenode(dbms_xmldom.getdocumentelement(dbms_xmldom.newdomdocument(t_clob)));

									dbms_lob.freetemporary(t_clob);

									return t_nd;

									end;

									end;

									function blob2num(p_blob blob, p_len integer, p_pos integer)

									return number is

									begin

									return utl_raw.cast_to_binary_integer(dbms_lob.substr(p_blob, p_len,

									p_pos),

									utl_raw.little_endian);

									end;

									function little_endian(p_big number, p_bytes pls_integer := 4) return raw is

									begin

									return utl_raw.substr(utl_raw.cast_from_binary_integer(p_big,

									utl_raw.little_endian),

									1, p_bytes);

									end;

									function col_alfan(p_col varchar2) return pls_integer is

									begin

									return ascii(substr(p_col, -1)) - 64 + nvl((ascii(substr(p_col, -2, 1)) - 64) * 26,

									0) + nvl((ascii(substr(p_col,

									-3,

									1)) - 64) * 676,

									0);

									end;

									function get_file(p_zipped_blob blob, p_file_name varchar2) return blob is

									t_tmp blob;

									t_ind integer;

									t_hd_ind integer;

									t_fl_ind integer;

									t_encoding varchar2(10);

									t_len integer;

									begin

									t_ind := dbms_lob.getlength(p_zipped_blob) - 21;

									loop

									exit when t_ind < 1 or dbms_lob.substr(p_zipped_blob, 4, t_ind) = hextoraw('504B0506');

									t_ind := t_ind - 1;

									end loop;

									if t_ind <= 0 then

									return null;

									end if;

									t_hd_ind := blob2num(p_zipped_blob, 4, t_ind + 16) + 1;

									for i in 1 .. blob2num(p_zipped_blob, 2, t_ind + 8) loop

									if utl_raw.bit_and(dbms_lob.substr(p_zipped_blob, 1, t_hd_ind + 9),

									hextoraw('08')) = hextoraw('08') then

									t_encoding := 'AL32UTF8';

									else

									t_encoding := 'US8PC437';

									end if;

									if p_file_name =

									utl_i18n.raw_to_char(dbms_lob.substr(p_zipped_blob,

									blob2num(p_zipped_blob, 2,

									t_hd_ind + 28),

									t_hd_ind + 46), t_encoding) then

									t_len := blob2num(p_zipped_blob, 4, t_hd_ind + 24);

									if t_len = 0 then

									if substr(p_file_name, -1) in ('/', '\') then

									return null;

									else

									return empty_blob();

									end if;

									end if;

									if dbms_lob.substr(p_zipped_blob, 2, t_hd_ind + 10) =

									hextoraw('0800') then

									t_fl_ind := blob2num(p_zipped_blob, 4, t_hd_ind + 42);

									t_tmp := hextoraw('1F8B0800000000000003');

									dbms_lob.copy(t_tmp, p_zipped_blob,

									blob2num(p_zipped_blob, 4, t_hd_ind + 20), 11,

									t_fl_ind + 31 +

									blob2num(p_zipped_blob, 2, t_fl_ind + 27) +

									blob2num(p_zipped_blob, 2, t_fl_ind + 29));

									dbms_lob.append(t_tmp,

									utl_raw.concat(dbms_lob.substr(p_zipped_blob, 4,

									t_hd_ind + 16),

									little_endian(t_len)));

									return utl_compress.lz_uncompress(t_tmp);

									end if;

									if dbms_lob.substr(p_zipped_blob, 2, t_hd_ind + 10) =

									hextoraw('0000') then

									t_fl_ind := blob2num(p_zipped_blob, 4, t_hd_ind + 42);

									dbms_lob.createtemporary(t_tmp, true);

									dbms_lob.copy(t_tmp, p_zipped_blob, t_len, 1,

									t_fl_ind + 31 +

									blob2num(p_zipped_blob, 2, t_fl_ind + 27) +

									blob2num(p_zipped_blob, 2, t_fl_ind + 29));

									return t_tmp;

									end if;

									end if;

									t_hd_ind := t_hd_ind + 46 +

									blob2num(p_zipped_blob, 2, t_hd_ind + 28) +

									blob2num(p_zipped_blob, 2, t_hd_ind + 30) +

									blob2num(p_zipped_blob, 2, t_hd_ind + 32);

									end loop;

									return null;

									end;

									function parse_xlsx(p_doc blob,

									p_sheets varchar2 := null,

									p_extra dbmsoutput_linesarray := null)

									return tp_data is

									t_rows tp_rows;

									t_data tp_data;

									t_date1904 boolean;

									type tp_date is table of boolean index by pls_integer;

									t_xf_date tp_date;

									t_numfmt_date tp_date;

									t_strings tp_strings;

									t_r varchar2(32767);

									t_s varchar2(32767);

									t_t varchar2(32767);

									t_val varchar2(32767);

									t_nr number;

									t_x pls_integer;

									t_xx pls_integer;

									t_c pls_integer;

									t_sc pls_integer;

									t_rr pls_integer;

									t_ns varchar2(200) := 'xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"';

									t_nd dbms_xmldom.domnode;

									t_nd2 dbms_xmldom.domnode;

									t_nl dbms_xmldom.domnodelist;

									t_nl2 dbms_xmldom.domnodelist;

									t_nl3 dbms_xmldom.domnodelist;

									t_type varchar2(1);

									t_max_c pls_integer;

									begin

									--my_log('parsing XLSX');

									t_nd := blob2node(get_file(p_doc, 'xl/workbook.xml'));

									t_date1904 := lower(dbms_xslprocessor.valueof(t_nd,

									'/workbook/workbookPr/@date1904',

									t_ns)) in ('true', '1');

									t_nl := dbms_xslprocessor.selectnodes(t_nd,

									'/workbook/sheets/sheet',

									t_ns);

									for i in 0 .. dbms_xmldom.getlength(t_nl) - 1 loop

									t_sheet_ids(i + 1) := dbms_xslprocessor.valueof(dbms_xmldom.item(t_nl,

									i),

									'@r:id',

									'xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"');

									t_sheet_names(i + 1) := dbms_xslprocessor.valueof(dbms_xmldom.item(t_nl,

									i),

									'@name');

									end loop;

									dbms_xmldom.freenode(t_nd);

									t_nd := blob2node(get_file(p_doc, 'xl/styles.xml'));

									t_nl := dbms_xslprocessor.selectnodes(t_nd,

									'/styleSheet/numFmts/numFmt',

									t_ns);

									for i in 0 .. dbms_xmldom.getlength(t_nl) - 1 loop

									t_val := dbms_xslprocessor.valueof(dbms_xmldom.item(t_nl, i),

									'@formatCode');

									if (instr(t_val, 'dd') > 0 or instr(t_val, 'mm') > 0 or

									instr(t_val, 'yy') > 0) then

									t_numfmt_date(dbms_xslprocessor.valueof(dbms_xmldom.item(t_nl, i), '@numFmtId')) := true;

									end if;

									end loop;

									t_numfmt_date(14) := true;

									t_numfmt_date(15) := true;

									t_numfmt_date(16) := true;

									t_numfmt_date(17) := true;

									t_numfmt_date(22) := true;

									t_nl := dbms_xslprocessor.selectnodes(t_nd,

									'/styleSheet/cellXfs/xf/@numFmtId',

									t_ns);

									for i in 0 .. dbms_xmldom.getlength(t_nl) - 1 loop

									t_xf_date(i) := t_numfmt_date.exists(dbms_xmldom.getnodevalue(dbms_xmldom.item(t_nl,

									i)));

									end loop;

									dbms_xmldom.freenode(t_nd);

									t_nd := blob2node(get_file(p_doc, 'xl/sharedStrings.xml'));

									if not dbms_xmldom.isnull(t_nd) then

									t_x := 0;

									t_xx := 10000;

									loop

									t_nl := dbms_xslprocessor.selectnodes(t_nd,

									'/sst/si[position()>="' ||

									to_char(t_x * t_xx + 1) ||

									'" and position()<="' ||

									to_char((t_x + 1) * t_xx) || '"]',

									t_ns);

									exit when dbms_xmldom.getlength(t_nl) = 0;

									t_x := t_x + 1;

									for i in 0 .. dbms_xmldom.getlength(t_nl) - 1 loop

									t_sc := t_strings.count;

									t_strings(t_sc) := dbms_xslprocessor.valueof(dbms_xmldom.item(t_nl,

									i),

									'.');

									if t_strings(t_sc) is null then

									t_strings(t_sc) := dbms_xslprocessor.valueof(dbms_xmldom.item(t_nl,

									i),

									'*/text()');

									if t_strings(t_sc) is null then

									t_nl2 := dbms_xslprocessor.selectnodes(dbms_xmldom.item(t_nl,

									i),

									'r/t/text()');

									for j in 0 .. dbms_xmldom.getlength(t_nl2) - 1 loop

									t_strings(t_sc) := t_strings(t_sc) ||

									dbms_xmldom.getnodevalue(dbms_xmldom.item(t_nl2,

									j));

									end loop;

									end if;

									end if;

									end loop;

									end loop;

									end if;

									t_nd2 := blob2node(get_file(p_doc, 'xl/_rels/workbook.xml.rels'));

									for i in 1 .. t_sheet_ids.count loop

									if (p_sheets is null or

									instr(':' || p_sheets || ':', ':' || to_char(i) || ':') > 0 or

									instr(':' || p_sheets || ':', ':' || t_sheet_names(i) || ':') > 0) then

									--跟踪日志

									--p_ins_log(t_sheet_names(i));

									t_max_c := 0;

									t_rows.delete;

									t_data(t_data.count + 1).name := t_sheet_names(i);

									--my_log('read ' || t_sheet_names(i));

									t_val := dbms_xslprocessor.valueof(t_nd2,

									'/Relationships/Relationship[@Id="' ||

									t_sheet_ids(i) || '"]/@Target',

									'xmlns="http://schemas.openxmlformats.org/package/2006/relationships"');

									t_nd := blob2node(get_file(p_doc, 'xl/' || t_val));

									t_x := 0;

									t_xx := 10000;

									loop

									t_nl3 := dbms_xslprocessor.selectnodes(t_nd,

									'/worksheet/sheetData/row[position()>="' ||

									to_char(t_x * t_xx + 1) ||

									'" and position()<="' ||

									to_char((t_x + 1) * t_xx) || '"]');

									exit when dbms_xmldom.getlength(t_nl3) = 0;

									t_x := t_x + 1;

									for r in 0 .. dbms_xmldom.getlength(t_nl3) - 1 loop

									t_nl2 := dbms_xslprocessor.selectnodes(dbms_xmldom.item(t_nl3,

									r),

									'c[v]');

									for j in 0 .. dbms_xmldom.getlength(t_nl2) - 1 loop

									t_r := dbms_xslprocessor.valueof(dbms_xmldom.item(t_nl2,

									j),

									'@r', t_ns);

									t_rr := ltrim(t_r, rtrim(t_r, '0123456789'));

									t_c := col_alfan(rtrim(t_r, '0123456789'));

									t_max_c := greatest(t_max_c, t_c);

									t_val := dbms_xslprocessor.valueof(dbms_xmldom.item(t_nl2,

									j), 'v');

									t_t := dbms_xslprocessor.valueof(dbms_xmldom.item(t_nl2,

									j),

									'@t');

									if t_t = 's' then

									if t_val is not null then

									t_rows(t_rr)(t_c).data_type := 'S';

									t_rows(t_rr)(t_c).string_val := t_strings(to_number(t_val));

									end if;

									elsif t_t in ('str', 'inlineStr', 'e') then

									if t_val is not null then

									t_rows(t_rr)(t_c).data_type := 'S';

									t_rows(t_rr)(t_c).string_val := t_val;

									end if;

									else

									t_nr := to_number(t_val,

									case

									when instr(t_val, 'E') = 0 then

									translate(t_val, '.012345678,-+', 'D999999999')

									else

									translate(substr(t_val, 1, instr(t_val, 'E') - 1),

									'.012345678,-+', 'D999999999') || 'EEEE'

									end, 'NLS_NUMERIC_CHARACTERS=.,');

									t_s := dbms_xslprocessor.valueof(dbms_xmldom.item(t_nl2,

									j), '@s');

									if t_s is not null and t_xf_date.exists(to_number(t_s)) and

									t_xf_date(to_number(t_s)) then

									t_rows(t_rr)(t_c).data_type := 'D';

									if t_date1904 then

									t_rows(t_rr)(t_c).date_val := to_date('01-01-1904',

									'DD-MM-YYYY') + t_nr;

									else

									t_rows(t_rr)(t_c).date_val := to_date('01-03-1900',

									'DD-MM-YYYY') +

									(t_nr - 61);

									end if;

									else

									t_rows(t_rr)(t_c).data_type := 'N';

									t_rows(t_rr)(t_c).number_val := t_nr;

									end if;

									end if;

									end loop;

									end loop;

									end loop;

									dbms_xmldom.freenode(t_nd);

									if t_rows.count > 0 then

									t_c := t_rows(t_rows.last).first;

									t_type := t_rows(t_rows.last)(t_c).data_type;

									for r in 1 .. t_rows.last - 1 loop

									if not t_rows.exists(r) then

									t_rows(r)(t_c).data_type := t_type;

									end if;

									end loop;

									if t_rows.count > 1 then

									for c in 1 .. t_max_c loop

									t_type := null;

									for r in 2 .. t_rows.last loop

									if t_rows(r).exists(c) then

									t_type := t_rows(r)(c).data_type;

									exit;

									end if;

									end loop;

									if t_type is null then

									if t_rows(1).exists(c) then

									t_type := t_rows(1)(c).data_type;

									else

									t_type := 'S';

									end if;

									end if;

									for r in 1 .. t_rows.last loop

									if not t_rows(r).exists(c) then

									t_rows(r)(c).data_type := t_type;

									end if;

									end loop;

									end loop;

									else

									for c in 1 .. t_max_c loop

									if not t_rows(1).exists(c) then

									t_rows(1)(c).data_type := 'S';

									end if;

									end loop;

									end if;

									end if;

									t_data(t_data.count).rows := t_rows;

									end if;

									end loop;

									dbms_xmldom.freenode(t_nd2);

									return t_data;

									end;

									begin

									if dbms_lob.substr(p_document, 8, 1) = hextoraw('D0CF11E0A1B11AE1') then

									--dbms_output.put_line( 'parsing XLS' );

									--t_what := 'XLS-file';

									--t_collection_base := :col_name;

									l_process_phase := 0;

									t_data := parse_xls(p_document, p_sheets);

									l_process_phase := 10;

									--DBMS_OUTPUT.PUT_LINE('parsed,' || t_data.count || ' sheets found');

									--my_log('moving to Collection(s)');

									--apex_collection.create_or_truncate_collection(t_collection_base ||'_$MAP');

									for i in 1 .. t_data.count loop

									--t_collection_name := t_collection_base || to_char(nullif(i, 1));

									--my_log('moving sheet ' || i || ': ' || t_data(i).name || ' to ' ||t_collection_name);

									/*

									apex_collection.add_member(t_collection_base || '_$MAP',

									p_c001 => t_data(i).name,

									p_c002 => t_collection_name,

									p_n001 => i);

									apex_collection.create_or_truncate_collection(t_collection_name);*/

									if t_data(i).rows.count() > 0 then

									--t2.delete;

									--DBMS_OUTPUT.PUT_LINE('t_data(i).rows.count():' || t_data(i).rows.count());

									--DBMS_OUTPUT.PUT_LINE('t_sheets(i).name:' || t_sheets(i-1).name);

									for r in 1 .. t_data(i).rows.last loop

									if t_data(i).rows.exists(r) then

									pipe row(xyg_pub_data_upload_obj('EXCEL-XLS' --P_SOURCE_TYPE

									, t_sheets(i - 1).name

									--P_BATCH_CODE

									, null

									--P_BATCH_NAME

									, r, g1(i, r, 1), g1(i, r, 2),

									g1(i, r, 3), g1(i, r, 4),

									g1(i, r, 5), g1(i, r, 6),

									g1(i, r, 7), g1(i, r, 8),

									g1(i, r, 9), g1(i, r, 10),

									g1(i, r, 11), g1(i, r, 12),

									g1(i, r, 13), g1(i, r, 14),

									g1(i, r, 15), g1(i, r, 16),

									g1(i, r, 17), g1(i, r, 18),

									g1(i, r, 19), g1(i, r, 20),

									g1(i, r, 21), g1(i, r, 22),

									g1(i, r, 23), g1(i, r, 24),

									g1(i, r, 25), g1(i, r, 26),

									g1(i, r, 27), g1(i, r, 28),

									g1(i, r, 29), g1(i, r, 30), 0,

									null));

									else

									--t2(1)(r) := ''

									null;

									end if;

									end loop;

									end if;

									end loop;

									elsif dbms_lob.substr(p_document, 4, 1) = hextoraw('504B0304') then

									--log( 'parsing XLSX' );

									--t_what := 'XLSX-file';

									--t_collection_base := :col_name;

									t_data := parse_xlsx(p_document, p_sheets);

									--my_log('parsed,' || t_data.count || ' sheets found');

									--my_log('moving to Collection(s)');

									--apex_collection.create_or_truncate_collection(t_collection_base ||'_$MAP');

									for i in 1 .. t_data.count loop

									/*

									t_collection_name := t_collection_base || to_char(nullif(i, 1));

									my_log('moving sheet ' || i || ': ' || t_data(i).name || ' to ' ||t_collection_name);

									apex_collection.add_member(t_collection_base || '_$MAP',

									p_c001 => t_data(i).name,

									p_c002 => t_collection_name,

									p_n001 => i);

									apex_collection.create_or_truncate_collection(t_collection_name);*/

									if t_data(i).rows.count() > 0 then

									--t2.delete;

									for r in 1 .. t_data(i).rows.last loop

									if t_data(i).rows.exists(r) then

									pipe row(xyg_pub_data_upload_obj('EXCEL-XLSX' --P_SOURCE_TYPE

									, t_sheet_names(i)

									--P_BATCH_CODE

									, null

									--P_BATCH_NAME

									, r, g2(i, r, 1), g2(i, r, 2),

									g2(i, r, 3), g2(i, r, 4),

									g2(i, r, 5), g2(i, r, 6),

									g2(i, r, 7), g2(i, r, 8),

									g2(i, r, 9), g2(i, r, 10),

									g2(i, r, 11), g2(i, r, 12),

									g2(i, r, 13), g2(i, r, 14),

									g2(i, r, 15), g2(i, r, 16),

									g2(i, r, 17), g2(i, r, 18),

									g2(i, r, 19), g2(i, r, 20),

									g2(i, r, 21), g2(i, r, 22),

									g2(i, r, 23), g2(i, r, 24),

									g2(i, r, 25), g2(i, r, 26),

									g2(i, r, 27), g2(i, r, 28),

									g2(i, r, 29), g2(i, r, 30), 0,

									null));

									else

									--t2(1)(r) := '';

									null;

									end if;

									end loop;

									end if;

									end loop;

									--RETURN CONVER_XLSX_TO_TAB(P_DOCUMENT,P_SHEETS,P_RAISE);

									else

									if p_raise = /*xyg_pub_const_pkg.*/

									c_true then

									l_process_phase := 97;

									raise no_data_found;

									else

									l_process_phase := 98;

									null;

									end if;

									end if; l_process_phase := 99;

									return;

									exception

									when others then

									if p_raise = /*xyg_pub_const_pkg.*/

									c_true then

									--DBMS_OUTPUT.PUT_LINE (R_LINE || '-' || V_PROCESS_MESSAGE);

									/*XYG_PUB_COMMON_PKG.RAISE_ERROR (

									'-20001' --'ERR_DEFAULT_CODE'

									,SQLERRM

									,'ERROR RAISE!程序进度:' || L_PROCESS_PHASE

									);*/

									dbms_output.put_line('程序进度:' || l_process_phase);

									raise;

									else

									return;

									--return -1

									end if;

									end;

									end xyg_pub_data_upload_pkg;

--使用方法

				?

									select *

									from table(xyg_pub_data_upload_pkg.conver_excel_to_tab(

									xyg_pub_data_upload_pkg.convert_file_blob('XLS_DIR_TEST','test.xlsx'),'', 1))