Oracle读取excel数据
发布时间：2020-12-12 17:03:08 所属栏目：百科 来源：网络整理
导读：推荐阅读： 废话不多说了，直接给大家奔入主题了。 包体内容，包头： 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 I
推荐阅读：废话不多说了，直接给大家奔入主题了。 包体内容，包头：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_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,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,to_char(t_data(i).rows(r)(c).number_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),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,t_ind + 2),utl_raw.little_endian); t_grbit := dbms_lob.substr(t_workbook,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,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,t_ind + 4 + 1); t_sind := 1; else t_tmp := utl_raw.substr(t_rec,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,'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,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,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,33,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,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,t_byte_order); end loop; t_sectid := utl_raw.cast_to_binary_integer(utl_raw.substr(t_sector,-4,t_byte_order); end loop; for j in 0 .. t_msat.count() - 1 loop t_sector := dbms_lob.substr(p_cf,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,t_byte_order); end loop; end loop; t_sectid := utl_raw.cast_to_binary_integer(utl_raw.substr(t_header,61,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,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,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,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,t_byte_order); t_len := utl_raw.cast_to_binary_integer(utl_raw.substr(t_sector,i * 128 + 121,t_byte_order); if t_len >= utl_raw.cast_to_binary_integer(utl_raw.substr(t_header,57,t_byte_order) then while t_tmp_sectid != c_end_of_chain_secid loop dbms_lob.append(t_stream,dbms_lob.substr(p_cf,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,t_byte_order); while t_tmp_sectid != c_end_of_chain_secid loop dbms_lob.append(t_short_container,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,t_ind) = hextoraw('0908') and dbms_lob.substr(t_workbook,t_ind + 4) in (hextoraw('0005'),hextoraw('0006')) and dbms_lob.substr(t_workbook,t_ind + 6) = hextoraw('0500')) then t_biff5 := dbms_lob.substr(t_workbook,t_ind + 4) = hextoraw('0005'); t_len := utl_raw.cast_to_binary_integer(dbms_lob.substr(t_workbook,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,t_ind) = hextoraw('0A00'); t_len := utl_raw.cast_to_binary_integer(dbms_lob.substr(t_workbook,utl_raw.little_endian); if dbms_lob.substr(t_workbook,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,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,utl_raw.little_endian); if t_sind <= utl_raw.length(t_rec) then t_rec := utl_raw.concat(utl_raw.substr(t_rec,dbms_lob.substr(t_workbook,t_len,t_ind + 4)); else t_rec := dbms_lob.substr(t_workbook,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,utl_raw.little_endian); t_sind := t_sind + 2; t_grbit := utl_raw.substr(t_rec,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,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,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,t_ind) = hextoraw('8500') then t_rec := dbms_lob.substr(t_workbook,t_ind + 4); if t_biff5 then t_cnt := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,7,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,1)); t_grbit := utl_raw.substr(t_rec,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,t_cnt); for i in 1 .. utl_raw.length(t_tmp) loop t_str := utl_raw.concat(t_str,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,utl_raw.little_endian); t_sheets(t_sheets.count()) := t_sheet; elsif dbms_lob.substr(t_workbook,t_ind) = hextoraw('2200') then t_date1904 := dbms_lob.substr(t_workbook,t_ind + 4) = hextoraw('0100'); elsif dbms_lob.substr(t_workbook,t_ind) = hextoraw('1E04') then t_rec := dbms_lob.substr(t_workbook,3,t_cnt); t_fmt := utl_i18n.raw_to_char(t_tmp,utl_raw.little_endian); t_grbit := utl_raw.substr(t_rec,5,6,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,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,t_ind) = hextoraw('E000') then t_rec := dbms_lob.substr(t_workbook,t_ind + 4); t_xf_fmt(t_xf_fmt.count()) := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,utl_raw.little_endian); elsif dbms_lob.substr(t_workbook,t_ind) = hextoraw('4200') then t_rec := dbms_lob.substr(t_workbook,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,t_ind + 4) = hextoraw('0006') and dbms_lob.substr(t_workbook,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,t_ind) = hextoraw('0A00'); t_cell := null; t_len := utl_raw.cast_to_binary_integer(dbms_lob.substr(t_workbook,t_ind) = hextoraw('7E02') then t_rec := dbms_lob.substr(t_workbook,t_ind + 4); if t_xf_date(utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,utl_raw.little_endian)) then t_cell.data_type := 'D'; t_cell.date_val := num2date(rk2number(utl_raw.substr(t_rec,4))); else t_cell.data_type := 'N'; t_cell.number_val := rk2number(utl_raw.substr(t_rec,4)); end if; t_c := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,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,utl_raw.little_endian) + 1)(t_c) := t_cell; elsif dbms_lob.substr(t_workbook,t_ind) = hextoraw('0302') then t_rec := dbms_lob.substr(t_workbook,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,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,utl_raw.little_endian); end if; t_c := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,t_ind) = hextoraw('0600') then t_rec := dbms_lob.substr(t_workbook,t_ind + 4); if (rawtohex(utl_raw.substr(t_rec,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,utl_raw.little_endian) + 1)(t_c) := t_cell; else case rawtohex(utl_raw.substr(t_rec,1)) when '01' then t_cell.data_type := 'S'; t_cell.string_val := case rawtohex(utl_raw.substr(t_rec,1)) when '00' then 'FALSE' when '01' then 'TRUE' end; t_c := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,utl_raw.little_endian) + 1)(t_c) := t_cell; when '02' then null; when '00' then if dbms_lob.substr(t_workbook,t_ind + t_len + 4) = hextoraw('0702') then declare t_row pls_integer := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,utl_raw.little_endian) + 1; t_col pls_integer := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,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,utl_raw.little_endian); t_rec := dbms_lob.substr(t_workbook,t_ind + 4); t_cnt := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,utl_raw.little_endian); t_sind := 4; t_cell.data_type := 'S'; if t_biff5 then t_tmp := utl_raw.substr(t_rec,t_cnt); t_cell.string_val := utl_i18n.raw_to_char(t_tmp,t_char_set); else t_grbit := dbms_lob.substr(t_rec,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,utl_raw.little_endian) + 1)(t_c) := t_cell; end case; end if; elsif dbms_lob.substr(t_workbook,t_ind) = hextoraw('0402') then t_rec := dbms_lob.substr(t_workbook,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,utl_raw.little_endian); if t_cnt = 0 then t_cell.string_val := null; else t_tmp := utl_raw.substr(t_rec,t_char_set); end if; t_c := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,utl_raw.little_endian) + 1)(t_c) := t_cell; end if; elsif dbms_lob.substr(t_workbook,t_ind) = hextoraw('D600') then t_rec := dbms_lob.substr(t_workbook,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,t_ind) = hextoraw('FD00') then t_rec := dbms_lob.substr(t_workbook,t_ind + 4); t_cell.data_type := 'S'; t_c := utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,t_c); t_cell.string_val := t_sst(utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,utl_raw.little_endian)); t_rows(utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,t_ind) = hextoraw('BD00') then t_rec := dbms_lob.substr(t_workbook,utl_raw.little_endian); for i in utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,utl_raw.little_endian) .. utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,-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,utl_raw.little_endian)) then t_cell.data_type := 'D'; t_cell.date_val := num2date(rk2number(utl_raw.substr(t_tmp,4))); else t_cell.data_type := 'N'; t_cell.number_val := rk2number(utl_raw.substr(t_tmp,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,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,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),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,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,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,t_ind + 16) + 1; for i in 1 .. blob2num(p_zipped_blob,t_ind + 8) loop if utl_raw.bit_and(dbms_lob.substr(p_zipped_blob,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,t_hd_ind + 28),t_hd_ind + 46),t_encoding) then t_len := blob2num(p_zipped_blob,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,t_hd_ind + 10) = hextoraw('0800') then t_fl_ind := blob2num(p_zipped_blob,t_hd_ind + 42); t_tmp := hextoraw('1F8B0800000000000003'); dbms_lob.copy(t_tmp,p_zipped_blob,t_hd_ind + 20),11,t_fl_ind + 31 + blob2num(p_zipped_blob,t_fl_ind + 27) + blob2num(p_zipped_blob,t_fl_ind + 29)); dbms_lob.append(t_tmp,utl_raw.concat(dbms_lob.substr(p_zipped_blob,t_hd_ind + 16),little_endian(t_len))); return utl_compress.lz_uncompress(t_tmp); end if; if dbms_lob.substr(p_zipped_blob,t_hd_ind + 10) = hextoraw('0000') then t_fl_ind := blob2num(p_zipped_blob,t_hd_ind + 42); dbms_lob.createtemporary(t_tmp,true); dbms_lob.copy(t_tmp,t_fl_ind + 29)); return t_tmp; end if; end if; t_hd_ind := t_hd_ind + 46 + blob2num(p_zipped_blob,t_hd_ind + 28) + blob2num(p_zipped_blob,t_hd_ind + 30) + blob2num(p_zipped_blob,t_hd_ind + 32); end loop; return null; end; function parse_xlsx(p_doc blob,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,'@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,'@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,'@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,'.'); if t_strings(t_sc) is null then t_strings(t_sc) := dbms_xslprocessor.valueof(dbms_xmldom.item(t_nl,'*/text()'); if t_strings(t_sc) is null then t_nl2 := dbms_xslprocessor.selectnodes(dbms_xmldom.item(t_nl,'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,'v'); t_t := dbms_xslprocessor.valueof(dbms_xmldom.item(t_nl2,'@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,instr(t_val,'E') - 1),'D999999999') || 'EEEE' end,'NLS_NUMERIC_CHARACTERS=.,'); t_s := dbms_xslprocessor.valueof(dbms_xmldom.item(t_nl2,'@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,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,3),5),6),7),9),10),11),12),13),14),15),16),17),18),19),20),21),22),23),24),25),26),27),28),29),30),null)); else --t2(1)(r) := '' null; end if; end loop; end if; end loop; elsif dbms_lob.substr(p_document,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_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,g2(i,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; --使用方法 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |