你的位置:首页 > 数据库

[数据库]Oracle读取excel


--解析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_is  begin   if p_blob is null or dbms_lob.getlength(p_blob) = 0 then    return null;   end if;   return dbms_'AL32UTF8')))));  exception   when others then    declare     t_nd      dbms_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_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) := '';   t_nd     dbms_varchar2(1);   t_max_c    pls_integer;  begin   --my_log('parsing XLSX');   t_nd    := blob2node(get_file(p_doc, 'xl/workbook.'));   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_- 1 loop    t_sheet_ids(i + 1) := dbms_xslprocessor.valueof(dbms_'@r:id',                            '');    t_sheet_names(i + 1) := dbms_xslprocessor.valueof(dbms_'@name');   end loop;   dbms_= blob2node(get_file(p_doc, 'xl/styles.'));   t_nl := dbms_xslprocessor.selectnodes(t_nd,                      '/styleSheet/numFmts/numFmt',                      t_ns);   for i in 0 .. dbms_- 1 loop    t_val := dbms_xslprocessor.valueof(dbms_'@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_'@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_- 1 loop    t_xf_date(i) := t_numfmt_date.exists(dbms_end loop;   dbms_= blob2node(get_file(p_doc, 'xl/sharedStrings.'));   if not dbms_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_= 0;     t_x := t_x + 1;     for i in 0 .. dbms_- 1 loop      t_sc := t_strings.count;      t_strings(t_sc) := dbms_xslprocessor.valueof(dbms_'.');      if t_strings(t_sc) is null then       t_strings(t_sc) := dbms_xslprocessor.valueof(dbms_'*/text()');       if t_strings(t_sc) is null then        t_nl2 := dbms_xslprocessor.selectnodes(dbms_'r/t/text()');        for j in 0 .. dbms_- 1 loop         t_strings(t_sc) := t_strings(t_sc) ||                   dbms_end loop;       end if;      end if;     end loop;    end loop;   end if;   t_nd2 := blob2node(get_file(p_doc, 'xl/_rels/workbook.'));   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',                       '');     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_= 0;      t_x := t_x + 1;      for r in 0 .. dbms_- 1 loop       t_nl2 := dbms_xslprocessor.selectnodes(dbms_'c[v]');       for j in 0 .. dbms_- 1 loop        t_r   := dbms_xslprocessor.valueof(dbms_'@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_'v');        t_t   := dbms_xslprocessor.valueof(dbms_'@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_'@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_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_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;

View Code

 

--使用方法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))