Home » Developer & Programmer » Forms » Loading XLS file into forms (forms 6I)
Loading XLS file into forms [message #360717] Sun, 23 November 2008 04:23 Go to next message
AHMADF124
Messages: 30
Registered: January 2008
Location: KSA
Member

Dear all;
I am using the following code to load an XLS file but once it reche record no 51 it will stop loading, I checked data but no problem in it, I need your help please....


DECLARE 
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; 
ctr NUMBER(2); 
cols NUMBER(2);
name_var1 VARCHAR2(2000); 
name_var2 VARCHAR2(2000); 
name_var3 VARCHAR2(2000); 
name_var4 VARCHAR2(2000);
name_var5 VARCHAR2(2000);  
name_var6 VARCHAR2(2000); 
name_var7 VARCHAR2(2000); 
name_var8 VARCHAR2(2000); 
name_var9 VARCHAR2(2000); 
name_var10 VARCHAR2(2000); 
name_var11 VARCHAR2(2000); 
name_var12 VARCHAR2(2000); 
name_var13 VARCHAR2(2000); 
name_var14 VARCHAR2(2000); 
name_var15 VARCHAR2(2000); 
name_var16 VARCHAR2(2000); 
name_var17 VARCHAR2(2000); 
filename varchar2(100);

PROCEDURE OLEARG IS
args OLE2.OBJ_TYPE; 
BEGIN
	GO_BLOCK('GENCONTACT');
args := OLE2.CREATE_ARGLIST; 
ole2.add_arg(args,ctr); -- row value
ole2.add_arg(args,cols); -- column value
cell := ole2.GET_OBJ_PROPERTY(worksheet,'Cells',args); -- initializing cell
ole2.destroy_arglist(args); 
END;

BEGIN
filename :=GET_FILE_NAME('c:\CONTACT', File_Filter=>'Excel Files (*.xls)|*.xls|'); -- to pick the file
application := OLE2.CREATE_OBJ('Excel.Application'); 
ole2.set_property(application,'Visible','true'); 
workbooks := OLE2.GET_OBJ_PROPERTY(application, 'Workbooks'); 
args := OLE2.CREATE_ARGLIST; 
ole2.add_arg(args,filename); --'c:\13s002.xls'); -- file path and name
workbook := ole2.GET_OBJ_PROPERTY(workbooks,'Open',args); 
ole2.destroy_arglist(args); 
args := OLE2.CREATE_ARGLIST; 
ole2.add_arg(args,'Sheet1'); 
worksheet := ole2.GET_OBJ_PROPERTY(workbook,'Worksheets',args); 
ole2.destroy_arglist(args); 

ctr := 2; --row number
cols := 1; -- column number

FIRST_RECORD; 

LOOP 
OLEARG;
name_var1 := ole2.get_char_property(cell,'Value'); --cell value of the argument
cols := cols+1;

OLEARG;
name_var2 := ole2.get_char_property(cell,'Value'); --cell value of the argument
cols := cols+1;

OLEARG;
name_var3 := ole2.get_char_property(cell,'Value'); --cell value of the argument
cols := cols+1;

OLEARG;
name_var4 := ole2.get_CHAR_property(cell,'Value'); --cell value of the argument
cols := cols+1;
OLEARG;
name_var5 := ole2.get_CHAR_property(cell,'Value'); --cell value of the argument
cols := cols+1;
OLEARG;
name_var6 := ole2.get_CHAR_property(cell,'Value'); --cell value of the argument
cols := cols+1;
OLEARG;
name_var7 := ole2.get_CHAR_property(cell,'Value'); --cell value of the argument
cols := cols+1;
OLEARG;
name_var8 := ole2.get_CHAR_property(cell,'Value'); --cell value of the argument
cols := cols+1;
OLEARG;
name_var9 := ole2.get_CHAR_property(cell,'Value'); --cell value of the argument
cols := cols+1;
OLEARG;
name_var10 := ole2.get_CHAR_property(cell,'Value'); --cell value of the argument
cols := cols+1;
OLEARG;
name_var11 := ole2.get_CHAR_property(cell,'Value'); --cell value of the argument
cols := cols+1;
OLEARG;
name_var12 := ole2.get_CHAR_property(cell,'Value'); --cell value of the argument
cols := cols+1;
OLEARG;
name_var13 := ole2.get_CHAR_property(cell,'Value'); --cell value of the argument
cols := cols+1;
OLEARG;
name_var14 := ole2.get_CHAR_property(cell,'Value'); --cell value of the argument
cols := cols+1;
OLEARG;
name_var15 := ole2.get_CHAR_property(cell,'Value'); --cell value of the argument
cols := cols+1;
OLEARG;
name_var16 := ole2.get_CHAR_property(cell,'Value'); --cell value of the argument
cols := cols+1;
OLEARG;
name_var17 := ole2.get_CHAR_property(cell,'Value'); --cell value of the argument
IF ctr = 1 then 
:PREFIX      := name_var1; 
:FIRSTNAME   := name_var2; 
:name:= name_var2;
--message(name_var2);message(name_var2);
:MIDDLENAME  := name_var3; 
:LASTNAME    := name_var4; 
:SUFFIX      := name_var5; 
:TITLE       := name_var6; 
:COMPANY     := name_var7; 
:ADDR1       := name_var8; 
:CITY        := name_var9; 
:STATE       := name_var10; 
:ZIPCODE     := name_var11;
:COUNTRY     := name_var12;  
:PHONE       := name_var13; 
:FAX         := name_var14; 
:EMAIL       := name_var15; 
:WEB         := name_var16; 
:CATEGORY    := name_var17; 
ELSE 
NEXT_RECORD; 
:PREFIX      := name_var1; 
:FIRSTNAME   := name_var2; 
:name:= name_var2;
--message(name_var2);
:MIDDLENAME  := name_var3; 
:LASTNAME    := name_var4; 
:SUFFIX      := name_var5; 
:TITLE       := name_var6; 
:COMPANY     := name_var7; 
:ADDR1       := name_var8; 
:CITY        := name_var9; 
:STATE       := name_var10; 
:ZIPCODE     := name_var11;
:COUNTRY     := name_var12;  
:PHONE       := name_var13; 
:FAX         := name_var14; 
:EMAIL       := name_var15; 
:WEB         := name_var16; 
:CATEGORY    := name_var17; 
END IF; 
EXIT WHEN length(name_var2) = 0 or length(name_var2) is null; 
ctr := ctr + 1; 
cols := 1;
--MESSAGE('1');MESSAGE('1');
END LOOP; 
OLE2.INVOKE(application,'Quit'); 
OLE2.RELEASE_OBJ(cell); 
OLE2.RELEASE_OBJ(worksheet); 
OLE2.RELEASE_OBJ(worksheets); 
OLE2.RELEASE_OBJ(workbook); 
OLE2.RELEASE_OBJ(workbooks); 
OLE2.RELEASE_OBJ(application); 
ENd;

Regards,,,



Thank you ,,, problem resolved...

[Updated on: Sun, 23 November 2008 23:55]

Report message to a moderator

Re: Loading XLS file into forms [message #360783 is a reply to message #360717] Mon, 24 November 2008 00:46 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
And the problem was ...?

David
Re: Loading XLS file into forms [message #360810 is a reply to message #360717] Mon, 24 November 2008 01:26 Go to previous message
AHMADF124
Messages: 30
Registered: January 2008
Location: KSA
Member

It was dealing with null values in the blank cells, also the row width was 2 digit so once its reaching 99 it will stop processing the remain cells.
Previous Topic: Blobs Datatypes
Next Topic: how to select all check boxes with single click
Goto Forum:
  


Current Time: Mon Dec 05 05:11:33 CST 2016

Total time taken to generate the page: 0.08478 seconds