| Slow performance using OLE2 reading xls file [message #526384] |
Mon, 10 October 2011 15:28  |
 |
rafael_bianchi
Messages: 1
Registered: October 2011
Location: Brazil
|
Junior Member |
|
|
Hi all
I just trying to import some informations from excel to Oracle using OLE2 over Oracle Forms 6i, but It´s very slow when I have import under then 10k lines. Could you please let me know if there is anything to optimize that ? Follow the code used...
application OLE2.Obj_Type;
workbooks OLE2.Obj_Type;
workbook OLE2.Obj_Type;
worksheets OLE2.Obj_Type;
worksheet OLE2.Obj_Type;
worksheet2 OLE2.Obj_Type;
cell OLE2.OBJ_TYPE;
args OLE2.OBJ_TYPE;
cell_value varchar2(500);
cell_date_value date;
num_wrkshts NUMBER;
wksht_name VARCHAR2(250);
eod Boolean := false;
j integer := 1;
v_fName VARCHAR2(250);
BEGIN
SET_APPLICATION_PROPERTY(CURSOR_STYLE,'BUSY');
-- Get the name of the file to open
v_fName := :TXT_PATH;
-- Make sure the user selected a file
IF ( v_fName IS NOT NULL ) THEN
-- The following sets up communication with the excel spreadsheet
-- --------------------------------------------------------------
-- Open the OLE application
application := OLE2.create_obj('Excel.Application');
-- Keep the application hidden
OLE2.set_property(application,'Visible','false');
workbooks := OLE2.Get_Obj_Property(application, 'Workbooks');
args := OLE2.CREATE_ARGLIST;
-- Open the selected File
-- ----------------------
OLE2.add_arg(args,v_fName);
BEGIN
workbook := OLE2.GET_OBJ_PROPERTY(workbooks,'Open',args);
EXCEPTION
WHEN OTHERS THEN
RAISE NO_FILE_FOUND;
END;
OLE2.destroy_arglist(args);
worksheets := OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets');
-- Get number of worksheets
-- ------------------------
num_wrkshts := OLE2.GET_NUM_PROPERTY(worksheets, 'Count');
worksheet := OLE2.GET_OBJ_PROPERTY(application,'activesheet');
-- Loop through the Block and create a new row if needed.
loop
-- Exit when the last row of the spreadsheet is reached.
exit when eod or IC_SAIR = 'Sim';
-- Loop through the spreadsheet and get cell values
for k in 1..29 loop --29 fields per record
-- You have to know fields there are
args:= OLE2.create_arglist;
OLE2.add_arg(args, j);
OLE2.add_arg(args, k);
cell:= OLE2.get_obj_property(worksheet, 'Cells', args);
OLE2.destroy_arglist(args);
-- alteração para tentar recuperar a inf
if j > 1 then
cell_value :=OLE2.get_num_property(cell, 'Value');
if cell_value = '0' then
cell_value :=OLE2.get_char_property(cell, 'Value');
end if;
else
cell_value :=OLE2.get_char_property(cell, 'Value');
end if;
-- Check for End of Data...
if upper(cell_value) = 'EOD' then
eod:=true;
--Message('End of Data');
exit;
end if;
if j = 1 then
if cell_value <> FCN_RETORNA_VALOR_CHAVE('LAYOUT_FATURA_COL_' || k) then
exit_form;
end if;
else
IF k = 1 THEN
IF j > 2 AND TAB_FATURAS(I).CD_FATURA <> cell_value AND cell_value > '0' THEN
exit form;
END IF;
I := I + 1;
TAB_FATURAS(I).CD_FATURA := cell_value;
ELSIF k = 2 THEN
TAB_FATURAS(I).CD_CNPJ_B2W := cell_value;
ELSIF k = 3 THEN
TAB_FATURAS(I).CD_CNPJ_TRANSP := cell_value;
ELSIF k = 4 THEN
TAB_FATURAS(I).CD_CRTC := cell_value;
ELSIF k = 5 THEN
TAB_FATURAS(I).CD_PEDIDO := cell_value;
ELSIF k = 6 THEN
TAB_FATURAS(I).CD_NF := cell_value;
ELSIF k = 7 THEN
TAB_FATURAS(I).CD_SERIE := cell_value;
ELSIF k = 8 THEN
TAB_FATURAS(I).TP_TIPO := cell_value;
ELSIF k = 9 THEN
TAB_FATURAS(I).TP_FRETE := cell_value;
ELSIF k = 10 THEN
TAB_FATURAS(I).NM_DESTINATARIO := cell_value;
ELSIF k = 11 THEN
TAB_FATURAS(I).CD_CEP := cell_value;
ELSIF k = 12 THEN
TAB_FATURAS(I).NM_MUNICIPIO := cell_value;
ELSIF k = 13 THEN
TAB_FATURAS(I).SG_UF := cell_value;
ELSIF k = 14 THEN
TAB_FATURAS(I).NM_REGIAO := cell_value;
ELSIF k = 15 THEN
BEGIN
TAB_FATURAS(I).DT_EMISSAO := (to_date('01011900','DDMMYYYY')-2)+to_number(cell_value);
EXCEPTION
WHEN OTHERS THEN TAB_FATURAS(I).DT_EMISSAO := to_date(cell_value,'dd/mm/yyyy');
END;
ELSIF k = 16 THEN
BEGIN
TAB_FATURAS(I).DT_FINALIZACAO := (to_date('01011900','DDMMYYYY')-2)+to_number(cell_value);
EXCEPTION
WHEN OTHERS THEN TAB_FATURAS(I).DT_FINALIZACAO := to_date(cell_value,'dd/mm/yyyy');
END;
ELSIF k = 17 THEN
TAB_FATURAS(I).DS_ARQUIVO_EDI := cell_value;
ELSIF k = 18 THEN
TAB_FATURAS(I).VL_NF := cell_value;
ELSIF k = 19 THEN
TAB_FATURAS(I).VL_PESO_REAL := cell_value;
ELSIF k = 20 THEN
TAB_FATURAS(I).VL_PESO_CUBADO := cell_value;
ELSIF k = 21 THEN
TAB_FATURAS(I).VL_PESO_CONS := cell_value;
ELSIF k = 22 THEN
TAB_FATURAS(I).VL_PESO_FRETE := cell_value;
ELSIF k = 23 THEN
TAB_FATURAS(I).VL_AD_VALOREM := cell_value;
ELSIF k = 24 THEN
TAB_FATURAS(I).VL_GRIS := cell_value;
ELSIF k = 25 THEN
TAB_FATURAS(I).VL_PEDAGIO := cell_value;
ELSIF k = 26 THEN
TAB_FATURAS(I).VL_OUTRAS_TARIFAS := cell_value;
ELSIF k = 27 THEN
TAB_FATURAS(I).DS_TARIFA := cell_value;
ELSIF k = 28 THEN
TAB_FATURAS(I).VL_FRETE_TOTAL := cell_value;
ELSE
TAB_FATURAS(I).IC_REMESSA := cell_value;
END IF;
end if;
end loop; --for
:BL_IMP_FRETES.DSP_STATUS := I || ' registros lidos';
SYNCHRONIZE;
j:=j+1;
end loop; --main loop
-- Release the OLE2 object handles
IF (cell IS NOT NULL) THEN
OLE2.release_obj(cell);
END IF;
IF (worksheet IS NOT NULL) THEN
OLE2.release_obj(worksheet);
END IF;
IF (worksheets IS NOT NULL) THEN
OLE2.release_obj(worksheets);
END IF;
IF (worksheet2 IS NOT NULL) THEN
OLE2.release_obj(worksheet2);
END IF;
IF (workbook IS NOT NULL) THEN
OLE2.release_obj(workbook);
END IF;
IF (workbooks IS NOT NULL) THEN
OLE2.release_obj(workbooks);
END IF;
OLE2.invoke(application,'Quit');
OLE2.release_obj(application);
ELSE
Message('No File selected.');
RAISE Form_Trigger_Failure;
END IF;
Best regards
Rafael
[EDITED by LF: applied [code] tags]
[Updated on: Tue, 11 October 2011 03:23] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
