Oracle读取excel数据
推荐阅读:Oracle导出excel数据
废话不多说了,直接给大家奔入主题了。
--解析excel,转换成table,可供查询,支持xls、xlsx --首先修改这个Type,长度改为4000. CREATEORREPLACETYPEXYG_PUB_DATA_UPLOAD_ObjASOBJECT( SOURCE_TYPEVARCHAR2(240)--EXCEL/TXT ,BATCH_CODEVARCHAR2(480BYTE)--批的Code,Excel用,因为一个Excel可能有多个 ,BATCH_NAMEVARCHAR2(4000BYTE)--批的名称 ,ROW_NUMNUMBER ,ATTRIBUTE1VARCHAR2(4000BYTE) ,ATTRIBUTE2VARCHAR2(4000BYTE) ,ATTRIBUTE3VARCHAR2(4000BYTE) ,ATTRIBUTE4VARCHAR2(4000BYTE) ,ATTRIBUTE5VARCHAR2(4000BYTE) ,ATTRIBUTE6VARCHAR2(4000BYTE) ,ATTRIBUTE7VARCHAR2(4000BYTE) ,ATTRIBUTE8VARCHAR2(4000BYTE) ,ATTRIBUTE9VARCHAR2(4000BYTE) ,ATTRIBUTE10VARCHAR2(4000BYTE) ,ATTRIBUTE11VARCHAR2(4000BYTE) ,ATTRIBUTE12VARCHAR2(4000BYTE) ,ATTRIBUTE13VARCHAR2(4000BYTE) ,ATTRIBUTE14VARCHAR2(4000BYTE) ,ATTRIBUTE15VARCHAR2(4000BYTE) ,ATTRIBUTE16VARCHAR2(4000BYTE) ,ATTRIBUTE17VARCHAR2(4000BYTE) ,ATTRIBUTE18VARCHAR2(4000BYTE) ,ATTRIBUTE19VARCHAR2(4000BYTE) ,ATTRIBUTE20VARCHAR2(4000BYTE) ,ATTRIBUTE21VARCHAR2(4000BYTE) ,ATTRIBUTE22VARCHAR2(4000BYTE) ,ATTRIBUTE23VARCHAR2(4000BYTE) ,ATTRIBUTE24VARCHAR2(4000BYTE) ,ATTRIBUTE25VARCHAR2(4000BYTE) ,ATTRIBUTE26VARCHAR2(4000BYTE) ,ATTRIBUTE27VARCHAR2(4000BYTE) ,ATTRIBUTE28VARCHAR2(4000BYTE) ,ATTRIBUTE29VARCHAR2(4000BYTE) ,ATTRIBUTE30VARCHAR2(4000BYTE) ,PROCESS_FLAGNUMBER---0:INIT,3:SUCESSFUL ,PROCESS_MESSAGEVARCHAR2(4000));
包体内容,包头:
createorreplacepackagexyg_pub_data_upload_pkgas /*TYPEXYG_PUB_DATA_UPLOAD_Objisrecord( SOURCE_TYPEVARCHAR2(240)--EXCEL/TXT ,BATCH_CODEVARCHAR2(480BYTE)--批的Code,Excel用,因为一个Excel可能有多个 ,BATCH_NAMEVARCHAR2(4000BYTE)--批的名称 ,ROW_NUMNUMBER ,ATTRIBUTE1VARCHAR2(4000BYTE) ,ATTRIBUTE2VARCHAR2(4000BYTE) ,ATTRIBUTE3VARCHAR2(4000BYTE) ,ATTRIBUTE4VARCHAR2(4000BYTE) ,ATTRIBUTE5VARCHAR2(4000BYTE) ,ATTRIBUTE6VARCHAR2(4000BYTE) ,ATTRIBUTE7VARCHAR2(4000BYTE) ,ATTRIBUTE8VARCHAR2(4000BYTE) ,ATTRIBUTE9VARCHAR2(4000BYTE) ,ATTRIBUTE10VARCHAR2(4000BYTE) ,ATTRIBUTE11VARCHAR2(4000BYTE) ,ATTRIBUTE12VARCHAR2(4000BYTE) ,ATTRIBUTE13VARCHAR2(4000BYTE) ,ATTRIBUTE14VARCHAR2(4000BYTE) ,ATTRIBUTE15VARCHAR2(4000BYTE) ,ATTRIBUTE16VARCHAR2(4000BYTE) ,ATTRIBUTE17VARCHAR2(4000BYTE) ,ATTRIBUTE18VARCHAR2(4000BYTE) ,ATTRIBUTE19VARCHAR2(4000BYTE) ,ATTRIBUTE20VARCHAR2(4000BYTE) ,ATTRIBUTE21VARCHAR2(4000BYTE) ,ATTRIBUTE22VARCHAR2(4000BYTE) ,ATTRIBUTE23VARCHAR2(4000BYTE) ,ATTRIBUTE24VARCHAR2(4000BYTE) ,ATTRIBUTE25VARCHAR2(4000BYTE) ,ATTRIBUTE26VARCHAR2(4000BYTE) ,ATTRIBUTE27VARCHAR2(4000BYTE) ,ATTRIBUTE28VARCHAR2(4000BYTE) ,ATTRIBUTE29VARCHAR2(4000BYTE) ,ATTRIBUTE30VARCHAR2(4000BYTE) ,PROCESS_FLAGNUMBER---0:INIT,3:SUCESSFUL ,PROCESS_MESSAGEVARCHAR2(4000)); */ typexyg_pub_data_upload_obj_tabistableofxyg_pub_data_upload_obj; c_item_return_numconstantnumber:=0; c_item_return_charconstantvarchar2(1):=null; c_item_err_codeconstantnumber:=-20120; c_return_numconstantnumber:=-1; c_return_charconstantvarchar2(1):=null; c_trueconstantnumber:=1; c_falseconstantnumber:=0; functionconvert_file_blob(p_filedirinvarchar2---文件路径/usr/usr/glmr/customer , p_filenameinvarchar2---文件名称DHS.csv , p_raiseinnumberdefault/*xyg_pub_const_pkg.*/c_true) returnblob; ---------------------- ------程序主体部分---- ---------------------- functionconver_excel_to_tab(p_documentblob, p_sheetsinvarchar2defaultnull, p_raiseinnumberdefault/*xyg_pub_const_pkg.*/c_true) returnxyg_pub_data_upload_obj_tab pipelined; endxyg_pub_data_upload_pkg; 包体: createorreplacepackagebodyxyg_pub_data_upload_pkgas functionconvert_file_blob(p_filedirinvarchar2---文件路径/usr/usr/glmr/customer , p_filenameinvarchar2---文件名称DHS.csv , p_raiseinnumberdefault/*xyg_pub_const_pkg.*/c_true) returnblobis l_resultblob:=empty_blob(); l_filedirvarchar2(240); l_filesbfile; l_dest_offsetbinary_integer; l_src_offsetbinary_integer; l_process_phasenumber; begin l_process_phase:=0; selectdirectory_name intol_filedir fromall_directories where1=1 and(upper(directory_path)=case whensubstr(p_filedir,-1)='/'then upper(substr(p_filedir,1,length(p_filedir)-1)) else upper(p_filedir) endorupper(directory_path)=upper(p_filedir)or directory_name=p_filedir) andrownum<=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_lobINOUTNOCOPYBLOB, ,l_files --src_lobINBFILE, ,dbms_lob.lobmaxsize --amountININTEGER, ,l_dest_offset --dest_offsetININTEGER:=1, ,l_src_offset --src_offsetININTEGER:=1 ); l_process_phase:=2; dbms_lob.close(l_files); l_process_phase:=99; returnl_result; exception whenothersthen ifp_raise=/*xyg_pub_const_pkg.*/ c_truethen 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('THEREARESOMEERROR,PLEASECONTACTWITHMIS'); else --UTL_FILE.FCLOSE(L_FILEHANDLE); returnempty_blob(); endif; end; ---------------------- ------程序主体部分---- ---------------------- functionconver_excel_to_tab(p_documentblob, p_sheetsinvarchar2defaultnull, p_raiseinnumberdefault/*xyg_pub_const_pkg.*/c_true) returnxyg_pub_data_upload_obj_tab pipelinedis typetp_cellisrecord( data_typevarchar2(1), string_valvarchar2(32767), number_valnumber, date_valdate, blob_valblob); typetp_rowistableoftp_cellindexbypls_integer; typetp_rowsistableoftp_rowindexbypls_integer; typetp_sheetisrecord( namevarchar2(2000), rowstp_rows); typetp_dataistableoftp_sheetindexbypls_integer; t_datatp_data; t_collection_basevarchar2(32767); t_collection_namevarchar2(32767); --typetp_2colistableofvc_arr2indexbypls_integer; --t2tp_2col; l_roundchar(1):='Y'; l_process_phasenumber; ---xls: typetp_sheet_recisrecord( namevarchar2(32767), indinteger); typetp_sheetsistableoftp_sheet_recindexbypls_integer; t_sheetstp_sheets; t_sheettp_sheet_rec; --xlsx: typetp_stringsistableofvarchar2(32767)indexbypls_integer; t_sheet_idstp_strings; t_sheet_namestp_strings; ----------------- ----XLS解析器---- ----------------- functiong1(ipls_integer,rpls_integer,cpls_integer)returnvarchar2is l_returnvarchar2(4000); begin ifl_round='Y'then l_return:=case whent_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-ddhh24:mi:ss')) end; else l_return:=case whent_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-ddhh24:mi:ss')) end; endif; returnl_return; --returncasewhent_data(i)(r).exists(c)thensubstr(t_data(i)(r)(c),1,4000)end; end; functionparse_xls(p_documentblob, p_sheetsvarchar2:=null, p_extradbmsoutput_linesarray:=null) returntp_datais t_celltp_cell; t_rowstp_rows; t_datatp_data; t_workbookblob; t_indinteger; t_sindinteger; t_leninteger; t_max_leninteger; t_cntinteger; t_grbitraw(1); t_biff5boolean; t_strvarchar2(32767); t_tmpraw(32767); t_recraw(32767); t_date1904boolean; typetp_sstistableofvarchar2(32767)indexbypls_integer; t_ssttp_sst; typetp_dateistableofbooleanindexbypls_integer; t_xf_datetp_date; t_fmt_datetp_date; typetp_xf_fmtistableofpls_integerindexbypls_integer; t_xf_fmttp_xf_fmt; t_fmtvarchar2(32767); t_char_setvarchar2(100):='WE8MSWIN1252'; t_cpls_integer; t_typevarchar2(1); t_max_cpls_integer; procedureread_unicode_stringis t_uniraw(32767); begin t_str:=null; whilet_cnt>0loop ifutl_raw.bit_and(t_grbit,hextoraw('01'))=hextoraw('01')then if(t_sind+t_cnt*2>utl_raw.length(t_rec)+1and 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; endif; else if(t_sind+t_cnt>utl_raw.length(t_rec)+1and 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; endif; t_uni:=null; foriin1..utl_raw.length(t_tmp)loop t_uni:=utl_raw.concat(t_uni,utl_raw.substr(t_tmp,i,1), hextoraw('00')); endloop; t_str:=t_str||utl_i18n.raw_to_char(t_uni,'AL16UTF16LE'); endif; endloop; end; functionrk2number(p_rkraw)returnnumberis begin returncaserawtohex(utl_raw.bit_and(utl_raw.substr(p_rk,1,1), '03'))when'02'thenutl_raw.cast_to_binary_integer(utl_raw.bit_and(p_rk, 'FCFFFFFF'), utl_raw.little_endian)/4when'03'thenutl_raw.cast_to_binary_integer(utl_raw.bit_and(p_rk, 'FCFFFFFF'), utl_raw.little_endian)/400when'00'thenutl_raw.cast_to_binary_double(utl_raw.concat('00000000', p_rk), utl_raw.little_endian)when'01'thenutl_raw.cast_to_binary_double(utl_raw.concat('00000000', utl_raw.bit_and(p_rk, 'FCFFFFFF')), utl_raw.little_endian)/100end; end; functionnum2date(p_numnumber)returndateis begin ift_date1904then returnto_date('01-01-1904','DD-MM-YYYY')+p_num; endif; returnto_date('01-03-1900','DD-MM-YYYY')+(p_num-61); end; procedureread_cfb(p_cfblob)is t_headerraw(512); t_byte_orderpls_integer; t_encodingvarchar2(30); t_sszpls_integer; t_ssszpls_integer; t_sectidpls_integer; t_tmp_sectidt_sectid%type; typetp_secidsistableoft_sectid%typeindexbypls_integer; t_msattp_secids; t_sattp_secids; t_ssattp_secids; t_sectorraw(2048); t_short_containerblob; t_streamblob; t_lenpls_integer; t_namevarchar2(32char); c_free_secidconstantpls_integer:=-1; c_end_of_chain_secidconstantpls_integer:=-2; c_sat_secidconstantpls_integer:=-3; c_msat_secidconstantpls_integer:=-4; c_dir_emptyconstantraw(1):=hextoraw('00'); c_dir_storageconstantraw(1):=hextoraw('01'); c_dir_streamconstantraw(1):=hextoraw('02'); c_dir_lockconstantraw(1):=hextoraw('03'); c_dir_propertyconstantraw(1):=hextoraw('04'); c_dir_rootconstantraw(1):=hextoraw('05'); begin t_header:=dbms_lob.substr(p_cf,512,1); if(t_headerisnullorutl_raw.length(t_header)<512or utl_raw.substr(t_header,1,8)!=hextoraw('D0CF11E0A1B11AE1'))then return; endif; t_byte_order:=case whenutl_raw.substr(t_header,29,2)=hextoraw('FEFF')then utl_raw.little_endian else utl_raw.big_endian end; ift_byte_order=utl_raw.little_endianthen t_encoding:='AL16UTF16LE'; else t_encoding:='AL16UTF16'; endif; 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)); foriin0..109-1loop t_sectid:=utl_raw.cast_to_binary_integer(utl_raw.substr(t_header, 77+ i*4,4), t_byte_order); exitwhent_sectid=c_free_secid; t_msat(i):=t_sectid; endloop; t_sectid:=utl_raw.cast_to_binary_integer(utl_raw.substr(t_header, 69,4), t_byte_order); whilet_sectid!=c_end_of_chain_secidloop t_sector:=dbms_lob.substr(p_cf,t_ssz, 512+t_ssz*t_sectid+1); foriin0..t_ssz/4-2loop t_msat(t_msat.count()):=utl_raw.cast_to_binary_integer(utl_raw.substr(t_sector, i*4+1, 4), t_byte_order); endloop; t_sectid:=utl_raw.cast_to_binary_integer(utl_raw.substr(t_sector, -4,4), t_byte_order); endloop; forjin0..t_msat.count()-1loop t_sector:=dbms_lob.substr(p_cf,t_ssz, 512+t_ssz*t_msat(j)+1); foriin0..t_ssz/4-1loop t_sat(t_sat.count()):=utl_raw.cast_to_binary_integer(utl_raw.substr(t_sector, i*4+1, 4), t_byte_order); endloop; endloop; t_sectid:=utl_raw.cast_to_binary_integer(utl_raw.substr(t_header, 61,4), t_byte_order); whilet_sectid!=c_end_of_chain_secidloop t_sector:=dbms_lob.substr(p_cf,t_ssz, 512+t_ssz*t_sectid+1); foriin0..t_ssz/4-1loop t_ssat(t_ssat.count()):=utl_raw.cast_to_binary_integer(utl_raw.substr(t_sector, i*4+1, 4), t_byte_order); endloop; t_sectid:=t_sat(t_sectid); endloop; t_sectid:=utl_raw.cast_to_binary_integer(utl_raw.substr(t_header, 49,4), t_byte_order); whilet_sectid!=c_end_of_chain_secidloop t_sector:=dbms_lob.substr(p_cf,t_ssz, 512+t_ssz*t_sectid+1); foriin0..t_ssz/128-1loop t_len:=utl_raw.cast_to_binary_integer(utl_raw.substr(t_sector, i*128+65, 2), t_byte_order); ift_len>2then t_name:=utl_i18n.raw_to_char(utl_raw.substr(t_sector, i*128+1, t_len-2), t_encoding); endif; caseutl_raw.substr(t_sector,i*128+67,1) whenc_dir_streamthen 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); ift_len>= utl_raw.cast_to_binary_integer(utl_raw.substr(t_header, 57,4), t_byte_order)then whilet_tmp_sectid!=c_end_of_chain_secidloop 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); endloop; else whilet_tmp_sectid!=c_end_of_chain_secidloop 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); endloop; endif; dbms_lob.trim(t_stream,t_len); ift_name='Workbook'then t_workbook:=t_stream; endif; ift_name='Book'then t_workbook:=t_stream; endif; whenc_dir_rootthen 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); whilet_tmp_sectid!=c_end_of_chain_secidloop 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); endloop; else null; endcase; endloop; t_sectid:=t_sat(t_sectid); endloop; ifdbms_lob.istemporary(t_short_container)=1then dbms_lob.freetemporary(t_short_container); endif; ifdbms_lob.istemporary(t_stream)=1then dbms_lob.freetemporary(t_stream); endif; end; begin --my_log('parsingXLS'); read_cfb(p_document); ift_workbookisnullordbms_lob.getlength(t_workbook)=0then --my_log('Noworkbookfilefound'); raise_application_error(-20003,'NotavalidXLS-file',true); endif; 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 exitwhent_ind>=t_max_len; exitwhendbms_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); ifdbms_lob.substr(t_workbook,2,t_ind)=hextoraw('FC00')then declare t_runinteger; t_extinteger; procedureadd_cont(p_lenpls_integer)is begin if(t_sind+p_len>utl_raw.length(t_rec)+1and 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); ift_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); endif; t_sind:=1; endif; end; begin t_sind:=1; t_rec:=dbms_lob.substr(t_workbook,t_len-8,t_ind+12); forjin1..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; ifutl_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; endif; ifutl_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); ift_ext<0then t_ext:=t_ext+4294967296; endif; t_sind:=t_sind+4; else t_ext:=0; endif; 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; endloop; end; elsifdbms_lob.substr(t_workbook,2,t_ind)=hextoraw('8500')then t_rec:=dbms_lob.substr(t_workbook,t_len,t_ind+4); ift_biff5then 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); ifutl_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); foriin1..utl_raw.length(t_tmp)loop t_str:=utl_raw.concat(t_str,utl_raw.substr(t_tmp,i,1), hextoraw('00')); endloop; endif; t_sheet.name:=utl_i18n.raw_to_char(t_str,'AL16UTF16LE'); endif; 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; elsifdbms_lob.substr(t_workbook,2,t_ind)=hextoraw('2200')then t_date1904:=dbms_lob.substr(t_workbook,2,t_ind+4)= hextoraw('0100'); elsifdbms_lob.substr(t_workbook,2,t_ind)=hextoraw('1E04')then t_rec:=dbms_lob.substr(t_workbook,t_len,t_ind+4); ift_biff5then 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); ifutl_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); foriin1..utl_raw.length(t_tmp)loop t_str:=utl_raw.concat(t_str,utl_raw.substr(t_tmp,i,1), hextoraw('00')); endloop; endif; t_fmt:=utl_i18n.raw_to_char(t_str,'AL16UTF16LE'); endif; t_fmt_date(utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec,1,2),utl_raw.little_endian)):=(instr(t_fmt, 'dd')>0or instr(t_fmt, 'mm')>0or instr(t_fmt, 'yy')>0); elsifdbms_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); elsifdbms_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')isnotnull)then t_char_set:='WE8MACROMAN8'; endif; endif; t_ind:=t_ind+t_len+4; endloop; 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; foriin0..t_xf_fmt.count()-1loop t_xf_date(i):=t_fmt_date.exists(t_xf_fmt(i))and t_fmt_date(t_xf_fmt(i)); endloop; endif; forsin0..t_sheets.count-1loop 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_sheetsisnullor instr(':'||p_sheets||':',':'||to_char(s+1)||':')>0or 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 exitwhent_ind>=t_max_len; exitwhendbms_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); ifdbms_lob.substr(t_workbook,2,t_ind)=hextoraw('7E02')then t_rec:=dbms_lob.substr(t_workbook,t_len,t_ind+4); ift_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)); endif; 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; elsifdbms_lob.substr(t_workbook,2,t_ind)=hextoraw('0302')then t_rec:=dbms_lob.substr(t_workbook,t_len,t_ind+4); ift_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); endif; 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; elsifdbms_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))notin ('00','01','02','03')or utl_raw.substr(t_rec,13,2)!=hextoraw('FFFF'))then ift_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); endif; 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 caserawtohex(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 ifdbms_lob.substr(t_workbook,2,t_ind+t_len+4)= hextoraw('0702')then declare t_rowpls_integer:=utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec, 1, 2), utl_raw.little_endian)+1; t_colpls_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'; ift_biff5then 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; endif; t_max_c:=greatest(t_max_c,t_col); t_rows(t_row)(t_col):=t_cell; end; endif; 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; endcase; endif; elsifdbms_lob.substr(t_workbook,2,t_ind)=hextoraw('0402')then t_rec:=dbms_lob.substr(t_workbook,t_len,t_ind+4); ift_biff5then t_cell.data_type:='S'; t_cnt:=utl_raw.cast_to_binary_integer(utl_raw.substr(t_rec, 7, 2), utl_raw.little_endian); ift_cnt=0then 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); endif; 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; endif; elsifdbms_lob.substr(t_workbook,2,t_ind)=hextoraw('D600')then t_rec:=dbms_lob.substr(t_workbook,t_len,t_ind+4); ift_biff5then 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; endif; elsifdbms_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; elsifdbms_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); foriinutl_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); ift_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)); endif; 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; endloop; endif; t_ind:=t_ind+t_len+4; endloop; ift_rows.count>0then t_c:=t_rows(t_rows.last).first; t_type:=t_rows(t_rows.last)(t_c).data_type; forrin1..t_rows.last-1loop ifnott_rows.exists(r)then t_rows(r)(t_c).data_type:=t_type; endif; endloop; ift_rows.count>1then forcin1..t_max_cloop t_type:=null; forrin2..t_rows.lastloop ift_rows(r).exists(c)then t_type:=t_rows(r)(c).data_type; exit; endif; endloop; ift_typeisnullthen ift_rows(1).exists(c)then t_type:=t_rows(1)(c).data_type; else t_type:='S'; endif; endif; forrin1..t_rows.lastloop ifnott_rows(r).exists(c)then t_rows(r)(c).data_type:=t_type; endif; endloop; endloop; else forcin1..t_max_cloop ifnott_rows(1).exists(c)then t_rows(1)(c).data_type:='S'; endif; endloop; endif; endif; t_data(t_data.count).rows:=t_rows; endif; endloop; returnt_data; end; ----------------- ----XLSX解析器---- ----------------- functiong2(ipls_integer,rpls_integer,cpls_integer)returnvarchar2is l_returnvarchar2(4000); begin ifl_round='Y'then l_return:=case whent_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-ddhh24:mi:ss')) end; else l_return:=case whent_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-ddhh24:mi:ss')) end; endif; returnl_return; --returncasewhent_data(i)(r).exists(c)thensubstr(t_data(i)(r)(c),1,4000)end; end; functionblob2node(p_blobblob)returndbms_xmldom.domnodeis begin ifp_blobisnullordbms_lob.getlength(p_blob)=0then returnnull; endif; returndbms_xmldom.makenode(dbms_xmldom.getdocumentelement(dbms_xmldom.newdomdocument(xmltype(p_blob, nls_charset_id('AL32UTF8'))))); exception whenothersthen declare t_nddbms_xmldom.domnode; t_clobclob; t_dest_offsetinteger; t_src_offsetinteger; t_lang_contextnumber:=dbms_lob.default_lang_ctx; t_warninginteger; 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); returnt_nd; end; end; functionblob2num(p_blobblob,p_leninteger,p_posinteger) returnnumberis begin returnutl_raw.cast_to_binary_integer(dbms_lob.substr(p_blob,p_len, p_pos), utl_raw.little_endian); end; functionlittle_endian(p_bignumber,p_bytespls_integer:=4)returnrawis begin returnutl_raw.substr(utl_raw.cast_from_binary_integer(p_big, utl_raw.little_endian), 1,p_bytes); end; functioncol_alfan(p_colvarchar2)returnpls_integeris begin returnascii(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; functionget_file(p_zipped_blobblob,p_file_namevarchar2)returnblobis t_tmpblob; t_indinteger; t_hd_indinteger; t_fl_indinteger; t_encodingvarchar2(10); t_leninteger; begin t_ind:=dbms_lob.getlength(p_zipped_blob)-21; loop exitwhent_ind<1ordbms_lob.substr(p_zipped_blob,4,t_ind)=hextoraw('504B0506'); t_ind:=t_ind-1; endloop; ift_ind<=0then returnnull; endif; t_hd_ind:=blob2num(p_zipped_blob,4,t_ind+16)+1; foriin1..blob2num(p_zipped_blob,2,t_ind+8)loop ifutl_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'; endif; ifp_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); ift_len=0then ifsubstr(p_file_name,-1)in('/','\')then returnnull; else returnempty_blob(); endif; endif; ifdbms_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))); returnutl_compress.lz_uncompress(t_tmp); endif; ifdbms_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)); returnt_tmp; endif; endif; 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); endloop; returnnull; end; functionparse_xlsx(p_docblob, p_sheetsvarchar2:=null, p_extradbmsoutput_linesarray:=null) returntp_datais t_rowstp_rows; t_datatp_data; t_date1904boolean; typetp_dateistableofbooleanindexbypls_integer; t_xf_datetp_date; t_numfmt_datetp_date; t_stringstp_strings; t_rvarchar2(32767); t_svarchar2(32767); t_tvarchar2(32767); t_valvarchar2(32767); t_nrnumber; t_xpls_integer; t_xxpls_integer; t_cpls_integer; t_scpls_integer; t_rrpls_integer; t_nsvarchar2(200):='xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"'; t_nddbms_xmldom.domnode; t_nd2dbms_xmldom.domnode; t_nldbms_xmldom.domnodelist; t_nl2dbms_xmldom.domnodelist; t_nl3dbms_xmldom.domnodelist; t_typevarchar2(1); t_max_cpls_integer; begin --my_log('parsingXLSX'); 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); foriin0..dbms_xmldom.getlength(t_nl)-1loop 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'); endloop; 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); foriin0..dbms_xmldom.getlength(t_nl)-1loop t_val:=dbms_xslprocessor.valueof(dbms_xmldom.item(t_nl,i), '@formatCode'); if(instr(t_val,'dd')>0orinstr(t_val,'mm')>0or instr(t_val,'yy')>0)then t_numfmt_date(dbms_xslprocessor.valueof(dbms_xmldom.item(t_nl,i),'@numFmtId')):=true; endif; endloop; 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); foriin0..dbms_xmldom.getlength(t_nl)-1loop t_xf_date(i):=t_numfmt_date.exists(dbms_xmldom.getnodevalue(dbms_xmldom.item(t_nl, i))); endloop; dbms_xmldom.freenode(t_nd); t_nd:=blob2node(get_file(p_doc,'xl/sharedStrings.xml')); ifnotdbms_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)|| '"andposition()<="'|| to_char((t_x+1)*t_xx)||'"]', t_ns); exitwhendbms_xmldom.getlength(t_nl)=0; t_x:=t_x+1; foriin0..dbms_xmldom.getlength(t_nl)-1loop t_sc:=t_strings.count; t_strings(t_sc):=dbms_xslprocessor.valueof(dbms_xmldom.item(t_nl, i), '.'); ift_strings(t_sc)isnullthen t_strings(t_sc):=dbms_xslprocessor.valueof(dbms_xmldom.item(t_nl, i), '*/text()'); ift_strings(t_sc)isnullthen t_nl2:=dbms_xslprocessor.selectnodes(dbms_xmldom.item(t_nl, i), 'r/t/text()'); forjin0..dbms_xmldom.getlength(t_nl2)-1loop t_strings(t_sc):=t_strings(t_sc)|| dbms_xmldom.getnodevalue(dbms_xmldom.item(t_nl2, j)); endloop; endif; endif; endloop; endloop; endif; t_nd2:=blob2node(get_file(p_doc,'xl/_rels/workbook.xml.rels')); foriin1..t_sheet_ids.countloop if(p_sheetsisnullor instr(':'||p_sheets||':',':'||to_char(i)||':')>0or 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)|| '"andposition()<="'|| to_char((t_x+1)*t_xx)||'"]'); exitwhendbms_xmldom.getlength(t_nl3)=0; t_x:=t_x+1; forrin0..dbms_xmldom.getlength(t_nl3)-1loop t_nl2:=dbms_xslprocessor.selectnodes(dbms_xmldom.item(t_nl3, r), 'c[v]'); forjin0..dbms_xmldom.getlength(t_nl2)-1loop 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'); ift_t='s'then ift_valisnotnullthen t_rows(t_rr)(t_c).data_type:='S'; t_rows(t_rr)(t_c).string_val:=t_strings(to_number(t_val)); endif; elsift_tin('str','inlineStr','e')then ift_valisnotnullthen t_rows(t_rr)(t_c).data_type:='S'; t_rows(t_rr)(t_c).string_val:=t_val; endif; else t_nr:=to_number(t_val, case wheninstr(t_val,'E')=0then 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'); ift_sisnotnullandt_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'; ift_date1904then 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); endif; else t_rows(t_rr)(t_c).data_type:='N'; t_rows(t_rr)(t_c).number_val:=t_nr; endif; endif; endloop; endloop; endloop; dbms_xmldom.freenode(t_nd); ift_rows.count>0then t_c:=t_rows(t_rows.last).first; t_type:=t_rows(t_rows.last)(t_c).data_type; forrin1..t_rows.last-1loop ifnott_rows.exists(r)then t_rows(r)(t_c).data_type:=t_type; endif; endloop; ift_rows.count>1then forcin1..t_max_cloop t_type:=null; forrin2..t_rows.lastloop ift_rows(r).exists(c)then t_type:=t_rows(r)(c).data_type; exit; endif; endloop; ift_typeisnullthen ift_rows(1).exists(c)then t_type:=t_rows(1)(c).data_type; else t_type:='S'; endif; endif; forrin1..t_rows.lastloop ifnott_rows(r).exists(c)then t_rows(r)(c).data_type:=t_type; endif; endloop; endloop; else forcin1..t_max_cloop ifnott_rows(1).exists(c)then t_rows(1)(c).data_type:='S'; endif; endloop; endif; endif; t_data(t_data.count).rows:=t_rows; endif; endloop; dbms_xmldom.freenode(t_nd2); returnt_data; end; begin ifdbms_lob.substr(p_document,8,1)=hextoraw('D0CF11E0A1B11AE1')then --dbms_output.put_line('parsingXLS'); --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||'sheetsfound'); --my_log('movingtoCollection(s)'); --apex_collection.create_or_truncate_collection(t_collection_base||'_$MAP'); foriin1..t_data.countloop --t_collection_name:=t_collection_base||to_char(nullif(i,1)); --my_log('movingsheet'||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);*/ ift_data(i).rows.count()>0then --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); forrin1..t_data(i).rows.lastloop ift_data(i).rows.exists(r)then piperow(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; endif; endloop; endif; endloop; elsifdbms_lob.substr(p_document,4,1)=hextoraw('504B0304')then --log('parsingXLSX'); --t_what:='XLSX-file'; --t_collection_base:=:col_name; t_data:=parse_xlsx(p_document,p_sheets); --my_log('parsed,'||t_data.count||'sheetsfound'); --my_log('movingtoCollection(s)'); --apex_collection.create_or_truncate_collection(t_collection_base||'_$MAP'); foriin1..t_data.countloop /* t_collection_name:=t_collection_base||to_char(nullif(i,1)); my_log('movingsheet'||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);*/ ift_data(i).rows.count()>0then --t2.delete; forrin1..t_data(i).rows.lastloop ift_data(i).rows.exists(r)then piperow(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; endif; endloop; endif; endloop; --RETURNCONVER_XLSX_TO_TAB(P_DOCUMENT,P_SHEETS,P_RAISE); else ifp_raise=/*xyg_pub_const_pkg.*/ c_truethen l_process_phase:=97; raiseno_data_found; else l_process_phase:=98; null; endif; endif;l_process_phase:=99; return; exception whenothersthen ifp_raise=/*xyg_pub_const_pkg.*/ c_truethen --DBMS_OUTPUT.PUT_LINE(R_LINE||'-'||V_PROCESS_MESSAGE); /*XYG_PUB_COMMON_PKG.RAISE_ERROR( '-20001'--'ERR_DEFAULT_CODE' ,SQLERRM ,'ERRORRAISE!程序进度:'||L_PROCESS_PHASE );*/ dbms_output.put_line('程序进度:'||l_process_phase); raise; else return; --return-1 endif; end; endxyg_pub_data_upload_pkg;
--使用方法
select* fromtable(xyg_pub_data_upload_pkg.conver_excel_to_tab( xyg_pub_data_upload_pkg.convert_file_blob('XLS_DIR_TEST','test.xlsx'),'',1))