Declare
l_filename varchar2(200);
FLAG BOOLEAN;
directory_name varchar2(200);
application Client_OLE2.Obj_Type;
workbooks Client_OLE2.Obj_Type;
workbook Client_OLE2.Obj_Type;
worksheet3 Client_OLE2.Obj_Type;
--worksheets2 Client_OLE2.Obj_Type;
worksheet Client_OLE2.Obj_Type;
args Client_OLE2.List_Type;
cell Client_OLE2.Obj_Type;
v_row number := 1;
v_col number := 1;
v_hdr number := 1;
v_flag boolean;
v_file_name varchar2(30);
v_cur_item varchar2(20);
wksht_name varchar2(20);
cur_itm varchar2(20);
cur_rec number;
cell_value varchar2(200);
v_infolert     NUMBER;

--darow Client_OLE2.Obj_Type;
--darange Client_OLE2.Obj_Type;
row_cnt INTEGER := 0;
TYPE xcel_array IS TABLE OF VARCHAR2(200) INDEX BY PLS_INTEGER;
c_value xcel_array;

begin
	
--info alert
v_infolert := SHOW_ALERT('INFOALERT');
If v_infolert = 89 Then
   GOTO cancel_export;
End If;

l_filename := WEBUTIL_FILE.FILE_OPEN_DIALOG('c:\','','|Excel 2003(*.xls)|*.xls|Excel 2007(*.xlsx)|*.xlsx|','Upload Excel File');


If l_filename is null Then
	 GOTO cancel_export;
End If;

variables.marker := 'F';

application:= Client_OLE2.create_obj('Excel.Application');
workbooks := Client_OLE2.get_obj_property(application, 'Workbooks');

-- Open the required workbook
args:= Client_OLE2.create_arglist; 
Client_OLE2.add_arg(args,l_filename);
workbook := Client_OLE2.invoke_obj(workbooks, 'Open', args);
Client_OLE2.destroy_arglist(args);

--SET_APPLICATION_PROPERTY(CURSOR_STYLE, 'BUSY');

--get active worksheet name --NO NEED for this one
--args:= ole2.create_arglist; 
--worksheets := Client_OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets');
--worksheet := Client_OLE2.GET_OBJ_PROPERTY(application,'activesheet');
--wksht_name := Client_OLE2.GET_CHAR_PROPERTY(worksheet, 'Name');
--msg('shit name: ' || wksht_name);
--ole2.destroy_arglist(args);


-- Open worksheet Sheet1 of that Workbook
args:= Client_OLE2.create_arglist; 
Client_OLE2.add_arg(args, 1); -- 1 is index for first sheet
--Client_OLE2.add_arg(args,'Sheet4');
worksheet := Client_OLE2.get_obj_property(workbook, 'Worksheets', args);
Client_OLE2.destroy_arglist(args);

variables.marker := 'T';

--Get row count
--Palpak, pati mga dinelete na rows nabibilang niya (used rows)
/*
    args:= Client_OLE2.create_arglist; 
    darange := Client_OLE2.GET_OBJ_PROPERTY(worksheet, 'UsedRange');
    darow := Client_OLE2.GET_OBJ_PROPERTY(darange, 'Rows');
    row_cnt := Client_OLE2.get_num_property(darow,'Count');
		msg('Row count: ' || row_cnt);
		Client_OLE2.destroy_arglist(args);		
		--release--
    Client_OLE2.Release_Obj(darange);
    Client_OLE2.Release_Obj(darow);
*/
--Palpka end


v_row:=1; --row 1 is headers

 Loop
    v_row := v_row + 1;
    row_cnt := row_cnt + 1;
    		    
    For v_col in 1..55 --max column is 55
   	 	Loop
		
				-- Get cell value
				args:= Client_OLE2.create_arglist; 
				Client_OLE2.add_arg(args, v_row);
				Client_OLE2.add_arg(args, v_col);
				cell:= Client_OLE2.get_obj_property(worksheet, 'Cells', args);
				Client_OLE2.destroy_arglist(args);

				--convert value into ordinary format
				args:= Client_OLE2.create_arglist; 
				cell_value := Client_OLE2.get_char_property(cell,'Value');

				--Close excel instance
				--release these to prevent multiple excel instance
				Client_OLE2.release_obj(cell);
				Client_OLE2.destroy_arglist(args);
				--msg('Cell value: ' || cell_value);

				If cell_value = 'null' Then
			 			c_value(v_col) := NULL;
				Else
			 			c_value(v_col) := cell_value;
				End If;

				If v_col IN (2,3) and c_value(1) is NULL and c_value(2) is NULL Then
						EXIT;      	
				End If;

   	 	End Loop;


		If c_value(1) is NULL and c_value(2) is NULL Then
			 EXIT;
		Else	 
				Begin
					UPLOAD_POLICY
								 (
									c_value(1),c_value(2), c_value(3),c_value(4),c_value(5),c_value(6),c_value(7),c_value(8),c_value(9),c_value(10)
									,c_value(11),c_value(12),c_value(13),c_value(14),c_value(15),c_value(16),c_value(17),c_value(18),c_value(19),c_value(20)
									,c_value(21),c_value(22),c_value(23),c_value(24),c_value(25),c_value(26),c_value(27),c_value(28),c_value(29),c_value(30)
									,c_value(31),c_value(32),c_value(33),c_value(34),c_value(35),c_value(36),c_value(37),c_value(38),c_value(39),c_value(40)
									,c_value(41),c_value(42),c_value(43),c_value(44),c_value(45),c_value(46),c_value(47),c_value(48),c_value(49),c_value(50)
									,c_value(51),c_value(52),c_value(53),c_value(54),c_value(55)
								 );
				End;
		End If;
		
 End Loop;

--msg(row_cnt);
forms_ddl('commit');

/* release cells */
Client_OLE2.release_obj(cell);

/* release worksheets */
Client_OLE2.Release_Obj(worksheet);

/* release workbook */
Client_OLE2.Release_Obj(workbook);
Client_OLE2.Release_Obj(workbooks);

/* Release application */
Client_OLE2.Invoke(application, 'Quit');
Client_OLE2.Release_Obj(application);


--SYS.DBMS_SESSION.free_unused_user_memory;

/* Display successfull message */
displaymessage(row_cnt-1 || ' records has been successfully uploaded.');
variables.marker := 'F';

Go_Block('POLICY');
execute_query;

SET_APPLICATION_PROPERTY(CURSOR_STYLE, 'DEFAULT'); 

<<cancel_export>>
null;

END;