| read excel cell value [message #611203] |
Sat, 29 March 2014 15:53 |
meteo
Messages: 89
Registered: April 2010
Location: beirut
|
Member |
|
|
i write a procedure to read values from excel file this procedure is:
Procedure p30_9i_read_Xls_file_OE_smr
IS
-- Declare handles to OLE objects
application OLE2.OBJ_TYPE;
workbooks OLE2.OBJ_TYPE;
workbook OLE2.OBJ_TYPE;
worksheets OLE2.OBJ_TYPE;
worksheet OLE2.OBJ_TYPE;
cell OLE2.OBJ_TYPE;
args OLE2.OBJ_TYPE;
Check_file text_io.file_type;
path varchar2(500);
diretory varchar2(500);
file_xl varchar2(500);
no_file exception;
PRAGMA exception_INIT (no_file, -302000);
cell_value varchar2(5000);
cell_value1 NUMBER(10,4);
eod Boolean := false;
l integer := 1;
lb integer;
head integer;
le integer := 1;
lend integer ;
tail integer;
BEGIN
lb:=0;
head:=0;
lend:=0;
tail:=0;
path:='C:\Climate_File';
diretory := substr(:TRANSFERTS.file_name,17,22);
file_xl := :TRANSFERTS.EXCEL_FILE||'.xls' ;
Check_file := TEXT_IO.FOPEN(path || '\' || diretory || '\' || file_xl,'R');
TEXT_IO.FCLOSE(Check_file);
-- -----------------------------------------------------------
application := OLE2.CREATE_OBJ('Excel.Application');
ole2.set_property(application,'Visible','false');
workbooks := OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');
args := OLE2.CREATE_ARGLIST;
ole2.add_arg(args,path || '\' || diretory || '\' || file_xl);
workbook := ole2.GET_OBJ_PROPERTY(workbooks,'Open',args);
ole2.destroy_arglist(args); -- create args a list of arg and initiate the arg inside
worksheets := ole2.GET_OBJ_PROPERTY(workbook, 'worksheets');
worksheet := OLE2.GET_OBJ_PROPERTY(application,'activesheet');
OLE2.SET_PROPERTY(worksheet , 'Value','Sheet1');
-- -----------------------------------------------------------
args:= OLE2.create_arglist;
OLE2.add_arg(args, 2);
OLE2.add_arg(args, 4);
cell:= OLE2.get_obj_property(worksheet, 'Cells', args);
OLE2.destroy_arglist(args);
cell_value := oLE2.get_char_property(cell, 'Value');
p05_show_alert_message ('value of cell i= '|| to_char(cell_value));
-- -----------------------------------------------------------
-- Release the OLE2 object handles
ole2.release_obj(cell);
ole2.release_obj(worksheet);
ole2.release_obj(worksheets);
ole2.release_obj(workbook);
ole2.release_obj(workbooks);
ole2.invoke(application,'Quit');
ole2.release_obj(application);
-- ASSIGN RETURN VALUE FROM EXCEL TO Text Field
--:PLANETS.pid := cell_value;
EXCEPTION
WHEN no_file THEN
MESSAGE('file not found.');
WHEN OTHERS THEN
MESSAGE(sqlerrm);
PAUSE;
FOR i IN 1 .. tool_err.nerrors LOOP
MESSAGE(tool_err.message);
PAUSE;
tool_err.pop;
END LOOP;
END;
-- ****************************************************************
My excel file also has the following data:
1 12.9 18.8 10.9 1010.3 75
2 14.7 19.4 10.9 1020.3 63
-- *****************************************************************
when i execute my procedure to read the value from cell:
OLE2.add_arg(args, 2); --line 2
OLE2.add_arg(args, 4); -- column 4
i am getting 1 instead of 10.9 as output
OLE2.add_arg(args, 1); --line 2
OLE2.add_arg(args, 6); -- column 4
i am getting 7 instead of 75 as output
...
my problem is: any cell value i choose to read it, i am getting just the first digit from the cell value.
any help i appreciate it
thank you.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
