Home » Developer & Programmer » Forms » Limit Number of Rows in OLE2 Forms to Excel Export (Oracle Forms 10g)
Limit Number of Rows in OLE2 Forms to Excel Export [message #659092] Fri, 06 January 2017 01:27 Go to next message
bdsalcedo
Messages: 3
Registered: January 2017
Junior Member
Hi Everyone,

I have this issue. I need to put a limit in number of rows per excel file.

For example :

The total rows in my table is 4116 but I only need 998 per excel file. So,

Excel 1 should be 998
Excel 2 should be 998
Excel 3 should be 998
Excel 4 should be 998
Excel 5 should only be 124

I already done the excel 1 to 4. However, the excel 5 still showing 998 rows even if i limit it only to 124.

Below is my code:
PROCEDURE fpr_forms_to_excel(p_block_name in varchar2 ,
														 p_path 			in varchar2 ,
														 p_file_name 	in varchar2 ) IS
-- Declare the 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;
	range 								OLE2.OBJ_TYPE;
	range_col 						OLE2.OBJ_TYPE;
	
	-- Declare handles to OLE argument lists
	args 								  OLE2.LIST_TYPE;
	arglist							  OLE2.LIST_TYPE;
	-- Declare form and block items
	form_name 						VARCHAR2(100);
	f_block 							VARCHAR2(100);
	l_block 							VARCHAR2(100);
	f_item 								VARCHAR2(100);
	l_item 								VARCHAR2(100);
	cur_block 						VARCHAR2(100):= NAME_IN('system.current_block');
	cur_item 							VARCHAR2(100);
	cur_record 						VARCHAR2(100);
	item_name 						VARCHAR2(100);
	baslik 								VARCHAR2(100);
	row_n 								NUMBER;
	col_n 								NUMBER;
	filename 							VARCHAR2(1000) := p_path||p_file_name;
	filepath							VARCHAR2(1000);
	pyCountS  						NUMBER;
	pyCountE  						NUMBER;	
	pyCtr									NUMBER;	
	pyCtr1	    						NUMBER;	
	pyCtr2	    						NUMBER;		
	ExcelFontId 					OLE2.list_type;

	BEGIN
		
		-- Start Excel 
		application:=OLE2.CREATE_OBJ('Excel.Application');
		OLE2.SET_PROPERTY(application, 'Visible', 'False');
		
		-- Return object handle to the Workbooks collection 
		workbooks:=OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');
		
		-- Add a new Workbook object to the Workbooks collection
		workbook:=OLE2.GET_OBJ_PROPERTY(workbooks,'Add');
		
		-- Return object handle to the Worksheets collection for the Workbook
		worksheets:=OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets');
		
		-- Get the first Worksheet in the Worksheets collection
		-- worksheet:=OLE2.GET_OBJ_PROPERTY(worksheets,'Add');
		args:=OLE2.CREATE_ARGLIST; 
		OLE2.ADD_ARG(args, 1);
		worksheet:=OLE2.GET_OBJ_PROPERTY(worksheets,'Item',args);
		OLE2.DESTROY_ARGLIST(args);
		
		-- Return object handle to cell A1 on the new Worksheet
		go_block(p_block_name);
		
		baslik := get_block_property(p_block_name,FIRST_ITEM);										--commented to consider the second item as the first item
		f_item := p_block_name||'.'||get_block_property(p_block_name,FIRST_ITEM); --in order to skip the old filename in the excel file 
		
		
		l_item := p_block_name||'.'||get_block_property(p_block_name,LAST_ITEM);
		first_record;
--pyCtr1 := 1;
--pyCtr2 := 998;
pyCtr := 998;
For ctr1 IN 1..5 LOOP
	if ctr1 = 5 then
			pyCTR := 124;
			--:apsi_q.txt2:=ctr1 || '-' || pyCTR;
			--synchronize;
	end if;
		FOR ctr2 IN 1..pyCtr LOOP 
		    item_name := f_item;		    
				row_n := pyCtr;
		    pyCountE := NAME_IN('SYSTEM.CURSOR_RECORD');
		    col_n := 1;		    
				if ctr1 = 5 and ctr2 = 125 then
					NULL;
				else
				LOOP 
					IF get_item_property(item_name,ITEM_TYPE)<>'BUTTON' AND get_item_property(item_name,VISIBLE)='TRUE' THEN
					-- Set first row with the item names 	
						 IF row_n=1 THEN
								args := OLE2.create_arglist;
								OLE2.add_arg(args, 1);
								OLE2.add_arg(args, col_n);
								cell := OLE2.get_obj_property(worksheet, 'Cells', args);
								OLE2.destroy_arglist(args);
								--cell_value := OLE2.get_char_property(cell, 'Value');
								ExcelFontId := OLE2.get_obj_property(Cell, 'Font');
								OLE2.set_property(ExcelFontId, 'Bold', 'True');
								-------------------------------------------- 
								baslik:=NVL(get_item_property(item_name,PROMPT_TEXT),baslik);
								--:apsi_q.txt1:=baslik;
								args:=OLE2.CREATE_ARGLIST; 
								--:apsi_q.txt2:=args;
								OLE2.ADD_ARG(args, row_n);
								OLE2.ADD_ARG(args, col_n); 
								cell:=OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
								OLE2.DESTROY_ARGLIST(args);
								OLE2.SET_PROPERTY(cell, 'Value', baslik);
								OLE2.RELEASE_OBJ(cell);
						 END IF;
					-- Set other rows with the item values 
							args:=OLE2.CREATE_ARGLIST; 
							OLE2.ADD_ARG(args, ctr2+1);
							:apsi_q.message:=pyCountE ||' Row(s) Processed.';
							OLE2.ADD_ARG(args, col_n); 
							cell:=OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
							synchronize;
							OLE2.DESTROY_ARGLIST(args);
					
						IF get_item_property(item_name,DATATYPE)<>'NUMBER' THEN
							 OLE2.SET_PROPERTY(cell, 'NumberFormat', '@');
						END IF;
							OLE2.SET_PROPERTY(cell, 'Value', name_in(item_name));
							OLE2.RELEASE_OBJ(cell);
					END IF;
				
					IF item_name = l_item THEN
						 exit;
					END IF;
					baslik := get_item_property(item_name,NEXTITEM);
					item_name := p_block_name||'.'||get_item_property(item_name,NEXTITEM);					
					col_n := col_n + 1;
				END LOOP;
				pyCtr := ctr2;
				end if;
				--:apsi_q.txt1:=ctr1 || '-' || ctr2 || '-' || row_n;
				--synchronize;				
				
		EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE';
		IF :SYSTEM.LAST_RECORD = 'TRUE' THEN
			NULL;
		ELSE
			NEXT_RECORD;
		END IF;
		
		END LOOP;
		
	  --synchronize;
		--Autofit columns
		
		range := OLE2.GET_OBJ_PROPERTY( worksheet,'UsedRange');
		range_col := OLE2.GET_OBJ_PROPERTY( range,'Columns');
		OLE2.INVOKE( range_col,'AutoFit' );
	  OLE2.RELEASE_OBJ( range );
		OLE2.RELEASE_OBJ( range_col );

		-- Save as worksheet with a Specified file path & name.
		
		--IF NVL(filename,'0')<>'0' THEN
			 args := OLE2.CREATE_ARGLIST;
			         OLE2.ADD_ARG(args,'C:\TEMP\aps_finance' || :APSI_Q.PY_YY || :APSI_Q.PY_MM || :APSI_Q.PY_PERIOD || '_' || ctr1);
			 				 OLE2.INVOKE(worksheet,'SaveAs',args );
			 				 OLE2.DESTROY_ARGLIST( args );
		--END IF;
		
		--:apsi_q.txt2:=ctr1 || '-' || pyCTR;
		--synchronize;
END LOOP;	
		--	 Release the OLE objects
		OLE2.RELEASE_OBJ(worksheet);
		OLE2.RELEASE_OBJ(worksheets);
		OLE2.RELEASE_OBJ(workbook);
		OLE2.RELEASE_OBJ(workbooks);
		OLE2.INVOKE     (application,'Quit');
		OLE2.RELEASE_OBJ(application);
		Message('Done Extracting to Excel');
		-- Focus to the original location

exception
	when others then null;
						raise form_trigger_failure;
END;



[Edit MC: add code tags]

[Updated on: Fri, 06 January 2017 01:31] by Moderator

Report message to a moderator

Re: Limit Number of Rows in OLE2 Forms to Excel Export [message #659095 is a reply to message #659092] Fri, 06 January 2017 01:30 Go to previous message
Michel Cadot
Messages: 67146
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

Previous Topic: on change trigger to filter
Next Topic: Custom form LOV is not working properly? (merged 3 by jd)
Goto Forum:
  


Current Time: Tue Jun 02 04:12:13 CDT 2020