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))