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_xmldom.domnode is
    begin
      if p_blob is null or dbms_lob.getlength(p_blob) = 0 then
        return null;
      end if;
      return dbms_xmldom.makenode(dbms_xmldom.getdocumentelement(dbms_xmldom.newdomdocument(xmltype(p_blob,
                                                                                                    nls_charset_id('AL32UTF8')))));
    exception
      when others then
        declare
          t_nd           dbms_xmldom.domnode;
          t_clob         clob;
          t_dest_offset  integer;
          t_src_offset   integer;
          t_lang_context number := dbms_lob.default_lang_ctx;
          t_warning      integer;
        begin
          dbms_lob.createtemporary(t_clob, true);
          t_dest_offset := 1;
          t_src_offset  := 1;
          dbms_lob.converttoclob(t_clob, p_blob, dbms_lob.lobmaxsize,
                                 t_dest_offset, t_src_offset,
                                 nls_charset_id('AL32UTF8'), t_lang_context,
                                 t_warning);
          t_nd := dbms_xmldom.makenode(dbms_xmldom.getdocumentelement(dbms_xmldom.newdomdocument(t_clob)));
          dbms_lob.freetemporary(t_clob);
          return t_nd;
        end;
    end;

    function blob2num(p_blob blob, p_len integer, p_pos integer)
      return number is
    begin
      return utl_raw.cast_to_binary_integer(dbms_lob.substr(p_blob, p_len,
                                                            p_pos),
                                            utl_raw.little_endian);
    end;

    function little_endian(p_big number, p_bytes pls_integer := 4) return raw is
    begin
      return utl_raw.substr(utl_raw.cast_from_binary_integer(p_big,
                                                             utl_raw.little_endian),
                            1, p_bytes);
    end;

    function col_alfan(p_col varchar2) return pls_integer is
    begin
      return ascii(substr(p_col, -1)) - 64 + nvl((ascii(substr(p_col, -2, 1)) - 64) * 26,
                                                 0) + nvl((ascii(substr(p_col,
                                                                        -3,
                                                                        1)) - 64) * 676,
                                                          0);
    end;

    function get_file(p_zipped_blob blob, p_file_name varchar2) return blob is
      t_tmp      blob;
      t_ind      integer;
      t_hd_ind   integer;
      t_fl_ind   integer;
      t_encoding varchar2(10);
      t_len      integer;
    begin
      t_ind := dbms_lob.getlength(p_zipped_blob) - 21;
      loop
        exit when t_ind < 1 or dbms_lob.substr(p_zipped_blob, 4, t_ind) = hextoraw('504B0506');
        t_ind := t_ind - 1;
      end loop;
      if t_ind <= 0 then
        return null;
      end if;
      t_hd_ind := blob2num(p_zipped_blob, 4, t_ind + 16) + 1;
      for i in 1 .. blob2num(p_zipped_blob, 2, t_ind + 8) loop
        if utl_raw.bit_and(dbms_lob.substr(p_zipped_blob, 1, t_hd_ind + 9),
                           hextoraw('08')) = hextoraw('08') then
          t_encoding := 'AL32UTF8';
        else
          t_encoding := 'US8PC437';
        end if;
        if p_file_name =
           utl_i18n.raw_to_char(dbms_lob.substr(p_zipped_blob,
                                                blob2num(p_zipped_blob, 2,
                                                          t_hd_ind + 28),
                                                t_hd_ind + 46), t_encoding) then
          t_len := blob2num(p_zipped_blob, 4, t_hd_ind + 24);
          if t_len = 0 then
            if substr(p_file_name, -1) in ('/', '\') then
              return null;
            else
              return empty_blob();
            end if;
          end if;
          if dbms_lob.substr(p_zipped_blob, 2, t_hd_ind + 10) =
             hextoraw('0800') then
            t_fl_ind := blob2num(p_zipped_blob, 4, t_hd_ind + 42);
            t_tmp    := hextoraw('1F8B0800000000000003');
            dbms_lob.copy(t_tmp, p_zipped_blob,
                          blob2num(p_zipped_blob, 4, t_hd_ind + 20), 11,
                          t_fl_ind + 31 +
                           blob2num(p_zipped_blob, 2, t_fl_ind + 27) +
                           blob2num(p_zipped_blob, 2, t_fl_ind + 29));
            dbms_lob.append(t_tmp,
                            utl_raw.concat(dbms_lob.substr(p_zipped_blob, 4,
                                                            t_hd_ind + 16),
                                            little_endian(t_len)));
            return utl_compress.lz_uncompress(t_tmp);
          end if;
          if dbms_lob.substr(p_zipped_blob, 2, t_hd_ind + 10) =
             hextoraw('0000') then
            t_fl_ind := blob2num(p_zipped_blob, 4, t_hd_ind + 42);
            dbms_lob.createtemporary(t_tmp, true);
            dbms_lob.copy(t_tmp, p_zipped_blob, t_len, 1,
                          t_fl_ind + 31 +
                           blob2num(p_zipped_blob, 2, t_fl_ind + 27) +
                           blob2num(p_zipped_blob, 2, t_fl_ind + 29));
            return t_tmp;
          end if;
        end if;
        t_hd_ind := t_hd_ind + 46 +
                    blob2num(p_zipped_blob, 2, t_hd_ind + 28) +
                    blob2num(p_zipped_blob, 2, t_hd_ind + 30) +
                    blob2num(p_zipped_blob, 2, t_hd_ind + 32);
      end loop;
      return null;
    end;

    function parse_xlsx(p_doc    blob,
                        p_sheets varchar2 := null,
                        p_extra  dbmsoutput_linesarray := null)
      return tp_data is
      t_rows     tp_rows;
      t_data     tp_data;
      t_date1904 boolean;
      type tp_date is table of boolean index by pls_integer;
      t_xf_date     tp_date;
      t_numfmt_date tp_date;
      t_strings     tp_strings;
      t_r           varchar2(32767);
      t_s           varchar2(32767);
      t_t           varchar2(32767);
      t_val         varchar2(32767);
      t_nr          number;
      t_x           pls_integer;
      t_xx          pls_integer;
      t_c           pls_integer;
      t_sc          pls_integer;
      t_rr          pls_integer;
      t_ns          varchar2(200) := 'xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"';
      t_nd          dbms_xmldom.domnode;
      t_nd2         dbms_xmldom.domnode;
      t_nl          dbms_xmldom.domnodelist;
      t_nl2         dbms_xmldom.domnodelist;
      t_nl3         dbms_xmldom.domnodelist;
      t_type        varchar2(1);
      t_max_c       pls_integer;
    begin
      --my_log('parsing XLSX');
      t_nd       := blob2node(get_file(p_doc, 'xl/workbook.xml'));
      t_date1904 := lower(dbms_xslprocessor.valueof(t_nd,
                                                    '/workbook/workbookPr/@date1904',
                                                    t_ns)) in ('true', '1');
      t_nl       := dbms_xslprocessor.selectnodes(t_nd,
                                                  '/workbook/sheets/sheet',
                                                  t_ns);
      for i in 0 .. dbms_xmldom.getlength(t_nl) - 1 loop
        t_sheet_ids(i + 1) := dbms_xslprocessor.valueof(dbms_xmldom.item(t_nl,
                                                                         i),
                                                        '@r:id',
                                                        'xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"');
        t_sheet_names(i + 1) := dbms_xslprocessor.valueof(dbms_xmldom.item(t_nl,
                                                                           i),
                                                          '@name');
      end loop;
      dbms_xmldom.freenode(t_nd);
      t_nd := blob2node(get_file(p_doc, 'xl/styles.xml'));
      t_nl := dbms_xslprocessor.selectnodes(t_nd,
                                            '/styleSheet/numFmts/numFmt',
                                            t_ns);
      for i in 0 .. dbms_xmldom.getlength(t_nl) - 1 loop
        t_val := dbms_xslprocessor.valueof(dbms_xmldom.item(t_nl, i),
                                           '@formatCode');
        if (instr(t_val, 'dd') > 0 or instr(t_val, 'mm') > 0 or
           instr(t_val, 'yy') > 0) then
          t_numfmt_date(dbms_xslprocessor.valueof(dbms_xmldom.item(t_nl, i), '@numFmtId')) := true;
        end if;
      end loop;
      t_numfmt_date(14) := true;
      t_numfmt_date(15) := true;
      t_numfmt_date(16) := true;
      t_numfmt_date(17) := true;
      t_numfmt_date(22) := true;
      t_nl := dbms_xslprocessor.selectnodes(t_nd,
                                            '/styleSheet/cellXfs/xf/@numFmtId',
                                            t_ns);
      for i in 0 .. dbms_xmldom.getlength(t_nl) - 1 loop
        t_xf_date(i) := t_numfmt_date.exists(dbms_xmldom.getnodevalue(dbms_xmldom.item(t_nl,
                                                                                       i)));
      end loop;
      dbms_xmldom.freenode(t_nd);
      t_nd := blob2node(get_file(p_doc, 'xl/sharedStrings.xml'));
      if not dbms_xmldom.isnull(t_nd) then
        t_x  := 0;
        t_xx := 10000;
        loop
          t_nl := dbms_xslprocessor.selectnodes(t_nd,
                                                '/sst/si[position()>="' ||
                                                 to_char(t_x * t_xx + 1) ||
                                                 '" and position()<="' ||
                                                 to_char((t_x + 1) * t_xx) || '"]',
                                                t_ns);
          exit when dbms_xmldom.getlength(t_nl) = 0;
          t_x := t_x + 1;
          for i in 0 .. dbms_xmldom.getlength(t_nl) - 1 loop
            t_sc := t_strings.count;
            t_strings(t_sc) := dbms_xslprocessor.valueof(dbms_xmldom.item(t_nl,
                                                                          i),
                                                         '.');
            if t_strings(t_sc) is null then
              t_strings(t_sc) := dbms_xslprocessor.valueof(dbms_xmldom.item(t_nl,
                                                                            i),
                                                           '*/text()');
              if t_strings(t_sc) is null then
                t_nl2 := dbms_xslprocessor.selectnodes(dbms_xmldom.item(t_nl,
                                                                        i),
                                                       'r/t/text()');
                for j in 0 .. dbms_xmldom.getlength(t_nl2) - 1 loop
                  t_strings(t_sc) := t_strings(t_sc) ||
                                     dbms_xmldom.getnodevalue(dbms_xmldom.item(t_nl2,
                                                                               j));
                end loop;
              end if;
            end if;
          end loop;
        end loop;
      end if;
      t_nd2 := blob2node(get_file(p_doc, 'xl/_rels/workbook.xml.rels'));
      for i in 1 .. t_sheet_ids.count loop
        if (p_sheets is null or
           instr(':' || p_sheets || ':', ':' || to_char(i) || ':') > 0 or
           instr(':' || p_sheets || ':', ':' || t_sheet_names(i) || ':') > 0) then
          --跟踪日志
          --p_ins_log(t_sheet_names(i));
          t_max_c := 0;
          t_rows.delete;
          t_data(t_data.count + 1).name := t_sheet_names(i);
          --my_log('read ' || t_sheet_names(i));
          t_val := dbms_xslprocessor.valueof(t_nd2,
                                             '/Relationships/Relationship[@Id="' ||
                                              t_sheet_ids(i) || '"]/@Target',
                                             'xmlns="http://schemas.openxmlformats.org/package/2006/relationships"');
          t_nd  := blob2node(get_file(p_doc, 'xl/' || t_val));
          t_x   := 0;
          t_xx  := 10000;
          loop
            t_nl3 := dbms_xslprocessor.selectnodes(t_nd,
                                                   '/worksheet/sheetData/row[position()>="' ||
                                                    to_char(t_x * t_xx + 1) ||
                                                    '" and position()<="' ||
                                                    to_char((t_x + 1) * t_xx) || '"]');
            exit when dbms_xmldom.getlength(t_nl3) = 0;
            t_x := t_x + 1;
            for r in 0 .. dbms_xmldom.getlength(t_nl3) - 1 loop
              t_nl2 := dbms_xslprocessor.selectnodes(dbms_xmldom.item(t_nl3,
                                                                      r),
                                                     'c[v]');
              for j in 0 .. dbms_xmldom.getlength(t_nl2) - 1 loop
                t_r     := dbms_xslprocessor.valueof(dbms_xmldom.item(t_nl2,
                                                                      j),
                                                     '@r', t_ns);
                t_rr    := ltrim(t_r, rtrim(t_r, '0123456789'));
                t_c     := col_alfan(rtrim(t_r, '0123456789'));
                t_max_c := greatest(t_max_c, t_c);
                t_val   := dbms_xslprocessor.valueof(dbms_xmldom.item(t_nl2,
                                                                      j), 'v');
                t_t     := dbms_xslprocessor.valueof(dbms_xmldom.item(t_nl2,
                                                                      j),
                                                     '@t');
                if t_t = 's' then
                  if t_val is not null then
                    t_rows(t_rr)(t_c).data_type := 'S';
                    t_rows(t_rr)(t_c).string_val := t_strings(to_number(t_val));
                  end if;
                elsif t_t in ('str', 'inlineStr', 'e') then
                  if t_val is not null then
                    t_rows(t_rr)(t_c).data_type := 'S';
                    t_rows(t_rr)(t_c).string_val := t_val;
                  end if;
                else
                  t_nr := to_number(t_val,
                                    case
                                       when instr(t_val, 'E') = 0 then
                                        translate(t_val, '.012345678,-+', 'D999999999')
                                       else
                                        translate(substr(t_val, 1, instr(t_val, 'E') - 1),
                                                  '.012345678,-+', 'D999999999') || 'EEEE'
                                     end, 'NLS_NUMERIC_CHARACTERS=.,');
                  t_s  := dbms_xslprocessor.valueof(dbms_xmldom.item(t_nl2,
                                                                     j), '@s');
                  if t_s is not null and t_xf_date.exists(to_number(t_s)) and
                     t_xf_date(to_number(t_s)) then
                    t_rows(t_rr)(t_c).data_type := 'D';
                    if t_date1904 then
                      t_rows(t_rr)(t_c).date_val := to_date('01-01-1904',
                                                            'DD-MM-YYYY') + t_nr;
                    else
                      t_rows(t_rr)(t_c).date_val := to_date('01-03-1900',
                                                            'DD-MM-YYYY') +
                                                    (t_nr - 61);
                    end if;
                  else
                    t_rows(t_rr)(t_c).data_type := 'N';
                    t_rows(t_rr)(t_c).number_val := t_nr;
                  end if;
                end if;
              end loop;
            end loop;
          end loop;
          dbms_xmldom.freenode(t_nd);
          if t_rows.count > 0 then
            t_c    := t_rows(t_rows.last).first;
            t_type := t_rows(t_rows.last)(t_c).data_type;
            for r in 1 .. t_rows.last - 1 loop
              if not t_rows.exists(r) then
                t_rows(r)(t_c).data_type := t_type;
              end if;
            end loop;
            if t_rows.count > 1 then
              for c in 1 .. t_max_c loop
                t_type := null;
                for r in 2 .. t_rows.last loop
                  if t_rows(r).exists(c) then
                    t_type := t_rows(r)(c).data_type;
                    exit;
                  end if;
                end loop;
                if t_type is null then
                  if t_rows(1).exists(c) then
                    t_type := t_rows(1)(c).data_type;
                  else
                    t_type := 'S';
                  end if;
                end if;
                for r in 1 .. t_rows.last loop
                  if not t_rows(r).exists(c) then
                    t_rows(r)(c).data_type := t_type;
                  end if;
                end loop;
              end loop;
            else
              for c in 1 .. t_max_c loop
                if not t_rows(1).exists(c) then
                  t_rows(1)(c).data_type := 'S';
                end if;
              end loop;
            end if;
          end if;
          t_data(t_data.count).rows := t_rows;
        end if;
      end loop;
      dbms_xmldom.freenode(t_nd2);
      return t_data;
    end;

  begin
    if dbms_lob.substr(p_document, 8, 1) = hextoraw('D0CF11E0A1B11AE1') then
      --dbms_output.put_line( 'parsing XLS' );
      --t_what := 'XLS-file';
      --t_collection_base := :col_name;
      l_process_phase := 0;
      t_data          := parse_xls(p_document, p_sheets);
      l_process_phase := 10;
      --DBMS_OUTPUT.PUT_LINE('parsed,' || t_data.count || ' sheets found');
      --my_log('moving to Collection(s)');
      --apex_collection.create_or_truncate_collection(t_collection_base ||'_$MAP');
      for i in 1 .. t_data.count loop
        --t_collection_name := t_collection_base || to_char(nullif(i, 1));
        --my_log('moving sheet ' || i || ': ' || t_data(i).name || ' to ' ||t_collection_name);
        /*
        apex_collection.add_member(t_collection_base || '_$MAP',
                                   p_c001 => t_data(i).name,
                                   p_c002 => t_collection_name,
                                   p_n001 => i);
        apex_collection.create_or_truncate_collection(t_collection_name);*/
        if t_data(i).rows.count() > 0 then
          --t2.delete;
          --DBMS_OUTPUT.PUT_LINE('t_data(i).rows.count():' || t_data(i).rows.count());
          --DBMS_OUTPUT.PUT_LINE('t_sheets(i).name:' || t_sheets(i-1).name);
          for r in 1 .. t_data(i).rows.last loop
            if t_data(i).rows.exists(r) then
              pipe row(xyg_pub_data_upload_obj('EXCEL-XLS' --P_SOURCE_TYPE
                                              , t_sheets(i - 1).name
                                               --P_BATCH_CODE
                                              , null
                                               --P_BATCH_NAME
                                              , r, g1(i, r, 1), g1(i, r, 2),
                                               g1(i, r, 3), g1(i, r, 4),
                                               g1(i, r, 5), g1(i, r, 6),
                                               g1(i, r, 7), g1(i, r, 8),
                                               g1(i, r, 9), g1(i, r, 10),
                                               g1(i, r, 11), g1(i, r, 12),
                                               g1(i, r, 13), g1(i, r, 14),
                                               g1(i, r, 15), g1(i, r, 16),
                                               g1(i, r, 17), g1(i, r, 18),
                                               g1(i, r, 19), g1(i, r, 20),
                                               g1(i, r, 21), g1(i, r, 22),
                                               g1(i, r, 23), g1(i, r, 24),
                                               g1(i, r, 25), g1(i, r, 26),
                                               g1(i, r, 27), g1(i, r, 28),
                                               g1(i, r, 29), g1(i, r, 30), 0,
                                               null));
            else
              --t2(1)(r) := ''
              null;
            end if;
          end loop;
        end if;
      end loop;
    elsif dbms_lob.substr(p_document, 4, 1) = hextoraw('504B0304') then
      --log( 'parsing XLSX' );
      --t_what := 'XLSX-file';
      --t_collection_base := :col_name;
      t_data := parse_xlsx(p_document, p_sheets);
      --my_log('parsed,' || t_data.count || ' sheets found');
      --my_log('moving to Collection(s)');
      --apex_collection.create_or_truncate_collection(t_collection_base ||'_$MAP');
      for i in 1 .. t_data.count loop
        /*
        t_collection_name := t_collection_base || to_char(nullif(i, 1));
        my_log('moving sheet ' || i || ': ' || t_data(i).name || ' to ' ||t_collection_name);
        apex_collection.add_member(t_collection_base || '_$MAP',
                                   p_c001 => t_data(i).name,
                                   p_c002 => t_collection_name,
                                   p_n001 => i);
        apex_collection.create_or_truncate_collection(t_collection_name);*/
        if t_data(i).rows.count() > 0 then
          --t2.delete;
          for r in 1 .. t_data(i).rows.last loop
            if t_data(i).rows.exists(r) then
              pipe row(xyg_pub_data_upload_obj('EXCEL-XLSX' --P_SOURCE_TYPE
                                              , t_sheet_names(i)
                                               --P_BATCH_CODE
                                              , null
                                               --P_BATCH_NAME
                                              , r, g2(i, r, 1), g2(i, r, 2),
                                               g2(i, r, 3), g2(i, r, 4),
                                               g2(i, r, 5), g2(i, r, 6),
                                               g2(i, r, 7), g2(i, r, 8),
                                               g2(i, r, 9), g2(i, r, 10),
                                               g2(i, r, 11), g2(i, r, 12),
                                               g2(i, r, 13), g2(i, r, 14),
                                               g2(i, r, 15), g2(i, r, 16),
                                               g2(i, r, 17), g2(i, r, 18),
                                               g2(i, r, 19), g2(i, r, 20),
                                               g2(i, r, 21), g2(i, r, 22),
                                               g2(i, r, 23), g2(i, r, 24),
                                               g2(i, r, 25), g2(i, r, 26),
                                               g2(i, r, 27), g2(i, r, 28),
                                               g2(i, r, 29), g2(i, r, 30), 0,
                                               null));
            else
              --t2(1)(r) := '';
              null;
            end if;
          end loop;
        end if;
      end loop;
      --RETURN CONVER_XLSX_TO_TAB(P_DOCUMENT,P_SHEETS,P_RAISE);
    else
      if p_raise = /*xyg_pub_const_pkg.*/
         c_true then
        l_process_phase := 97;
        raise no_data_found;
      else
        l_process_phase := 98;
        null;
      end if;
    end if;
    l_process_phase := 99;
    return;
  exception
    when others then
      if p_raise = /*xyg_pub_const_pkg.*/
         c_true then
        --DBMS_OUTPUT.PUT_LINE (R_LINE || '-' || V_PROCESS_MESSAGE);
        /*XYG_PUB_COMMON_PKG.RAISE_ERROR (
           '-20001'                                   --'ERR_DEFAULT_CODE'
          ,SQLERRM
          ,'ERROR RAISE!程序进度:' || L_PROCESS_PHASE
        );*/
        dbms_output.put_line('程序进度:' || l_process_phase);
        raise;
      else
        return;
        --return -1
      end if;
  end;

end xyg_pub_data_upload_pkg;
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))

 

posted @ 2016-03-15 00:44  mellowsmile  阅读(2821)  评论(0编辑  收藏  举报