Home » Developer & Programmer » Forms » Populating the data in respective excel columns
Populating the data in respective excel columns [message #400138] Sun, 26 April 2009 09:09 Go to next message
hamdard
Messages: 37
Registered: July 2005
Member
hi,
I have this procedure to populate the data from forms to excel. This is working fine but I have one problem. In the arguments, I'm passing the title, select statement and for columns, I'm passing 'Y' or 'N'. based on 'Y' and 'N', the column will be shown or not shown in the excel sheet. e.g: if I pass 'Y' for empno, 'Y' for ename, 'N' for sal and 'Y' for deptno then in the excel sheet, empno will be showm on first column, ename on 2nd column, 3rd column will be empty and deptno on 4th column.
Now because third column is empty, I want to move deptno column(4th column) to third column.
This can be achieved if I assign a correct values to variable c. But how to get that exact value, I'm not able to do this. I tried a lot but not successful.
Anybody please help. This is the procedure I'm using
PROCEDURE DYNAMIC_CURSOR (PC$REP_HEADING IN VARCHAR2, PC$SEL_STATEMENT IN VARCHAR2, P_EMPNO IN VARCHAR2, P_ENAME IN VARCHAR2,
			      P_SAL IN VARCHAR2, P_DEPTNO IN VARCHAR2)
IS
  cursor_number EXEC_SQL.CursType; 

  -- Variables for the data to be returned into 
	v_empno		number;
	v_ename		varchar2(50);
	v_sal	number;
	v_deptno	number;
	
  -- Control variables 
  LN$count       Number; 
  LC$sql_order   Varchar2(3000);
 
	i_reporttitle VARCHAR2(200) := PC$REP_HEADING;
	r number := 2;
	c number := 1;
	i_orientation VARCHAR2(200) := '2';
	i number :=3;

/* definiton for commonly used variables and constants and exceptions */
	ExcelID client_ole2.obj_type;
	ExcelWorkBooksID client_ole2.obj_type;
	ExcelWorkBookID client_ole2.obj_type;
	ExcelWorkSheetsID client_ole2.obj_type;
	ExcelWorkSheetID client_ole2.obj_type;
	ExcelCellID client_ole2.obj_type;
	ExcelFontID client_ole2.obj_type;
	ExcelPageSetupID client_ole2.obj_type;
	ExcelArgs client_ole2.list_type;
	
	--	Define th ecolors if they are to be used
	colour client_ole2.obj_type;
  myBlue                   CONSTANT number(8) := 16711680; --FF0000
  myGreen                  CONSTANT number(8) := 65280;    --00FF00
  myRed                    CONSTANT number(8) := 255;      --0000FF
  myDkGreen                CONSTANT number(8) := 32768;    --008000
  myBlack                  CONSTANT number(8) := 0;        --000000
  
BEGIN
			
	ExcelID := client_ole2.create_obj('Excel.Application'); 
	ExcelWorkBooksID := client_ole2.get_obj_property(ExcelID, 'Workbooks');
	ExcelWorkBookID := client_ole2.invoke_obj(ExcelWorkBooksID, 'Add');
	ExcelWorkSheetsID := client_ole2.get_obj_property(ExcelWorkBookID, 'Worksheets');
	ExcelWorkSheetID := client_ole2.invoke_obj(ExcelWorkSheetsID, 'Add');
	ExcelPageSetupID := client_ole2.get_obj_property(ExcelWorkSheetID, 'PageSetup');
	
	client_ole2.set_property(ExcelPageSetupID, 'Orientation',i_orientation);
	client_ole2.release_obj(ExcelPageSetupID);


  /************************************************************************************************************
  ***********				Set the Report Title, Color, Cell Font etc																*****************
  ************************************************************************************************************/

	--This is the Report Heading
	ExcelArgs := client_ole2.create_arglist;
	client_ole2.add_arg(ExcelArgs,1);
	client_ole2.add_arg(ExcelArgs,8);
	ExcelCellId := client_ole2.get_obj_property(ExcelWorkSheetId,'cells',ExcelArgs);
	client_ole2.destroy_arglist(ExcelArgs);
	client_ole2.set_property(ExcelCellId, 'Value', i_reporttitle);
	ExcelFontId := client_ole2.get_obj_property(ExcelCellId, 'Font');
	client_ole2.set_property(ExcelFontId, 'Bold', 'True');
	client_ole2.set_property(ExcelFontId, 'Size', '15');
	client_ole2.set_property(ExcelFontId, 'Color', myGreen);
	colour:=client_ole2.get_obj_property(ExcelCellId, 'Interior');
	client_ole2.set_property(colour, 'ColorIndex', 15);
	client_ole2.release_obj(ExcelFontId);
	client_ole2.release_obj(ExcelCellId);

for j in 7..12 loop
	-- Set the Color of Row1, Col7
	ExcelArgs := client_ole2.create_arglist;
	client_ole2.add_arg(ExcelArgs,1);
	client_ole2.add_arg(ExcelArgs,j);
	ExcelCellId := client_ole2.get_obj_property(ExcelWorkSheetId,'cells',ExcelArgs);
	client_ole2.destroy_arglist(ExcelArgs);
	ExcelFontId := client_ole2.get_obj_property(ExcelCellId, 'Font');
	client_ole2.set_property(ExcelFontId, 'Bold', 'True');
	client_ole2.set_property(ExcelFontId, 'Size', '15');
	client_ole2.set_property(ExcelFontId, 'Color', myGreen);	-- 16711680
	colour:=client_ole2.get_obj_property(ExcelCellId, 'Interior');
	client_ole2.set_property(colour, 'ColorIndex', 15);
	client_ole2.release_obj(ExcelFontId);
	client_ole2.release_obj(ExcelCellId);
end loop;

  /************************************************************************************************************
	***********				Report Title Finished Here																								*****************
  ************************************************************************************************************/
  

	-- Open the cursor -- 
	cursor_number := Exec_SQL.Open_cursor; 
	
	-- build the complete SQL order --  
	LC$sql_order := PC$SEL_STATEMENT;
	
	
	-- Parse the SQL order --
	EXEC_SQL.PARSE(cursor_number, LC$sql_order); 
	
	-- Define the columns for the data to be returned --
	
	EXEC_SQL.DEFINE_COLUMN(cursor_number,1,v_empno);
	EXEC_SQL.DEFINE_COLUMN(cursor_number,2, v_ename, 50); 
	EXEC_SQL.DEFINE_COLUMN(cursor_number,3,v_sal);
	EXEC_SQL.DEFINE_COLUMN(cursor_number,4, v_deptno); 
	
	-- Execute the Cursor --
	LN$count := EXEC_SQL.EXECUTE(cursor_number); 

	-- Loop and fetch each row from the result set --

	While EXEC_SQL.FETCH_ROWS(cursor_number) > 0 Loop
		

	
	EXEC_SQL.COLUMN_VALUE(cursor_number,1,v_empno); 
	  EXEC_SQL.COLUMN_VALUE(cursor_number,2,v_ename);
	  EXEC_SQL.COLUMN_VALUE(cursor_number,3,v_sal); 
	  EXEC_SQL.COLUMN_VALUE(cursor_number,4,v_deptno);

	  if P_EMPNO = 'Y' then
	  	
			ExcelArgs := client_ole2.create_arglist;
			client_ole2.add_arg(ExcelArgs,i);
			client_ole2.add_arg(ExcelArgs,c);
			ExcelCellId := client_ole2.get_obj_property(ExcelWorkSheetId,'cells',ExcelArgs);
			client_ole2.destroy_arglist(ExcelArgs);
			client_ole2.set_property(ExcelCellId, 'Value', v_empno);
			ExcelFontId := client_ole2.get_obj_property(ExcelCellId, 'Font');
			client_ole2.set_property(ExcelFontId, 'Bold', 'True');
			client_ole2.set_property(ExcelFontId, 'Size', '10');
			client_ole2.release_obj(ExcelFontId);
			client_ole2.release_obj(ExcelCellId);
	  end if;


		if p_ename = 'Y' then
		
			ExcelArgs := client_ole2.create_arglist;
			client_ole2.add_arg(ExcelArgs,i);
			client_ole2.add_arg(ExcelArgs,c+1);
			ExcelCellId := client_ole2.get_obj_property(ExcelWorkSheetId,'cells',ExcelArgs);
			client_ole2.destroy_arglist(ExcelArgs);
			client_ole2.set_property(ExcelCellId, 'Value', v_ename);
			ExcelFontId := client_ole2.get_obj_property(ExcelCellId, 'Font');
			client_ole2.set_property(ExcelFontId, 'Bold', 'True');
			client_ole2.set_property(ExcelFontId, 'Size', '10');
			client_ole2.release_obj(ExcelFontId);
			client_ole2.release_obj(ExcelCellId);
			
		end if;
		if p_sal = 'Y' then
			
			ExcelArgs := client_ole2.create_arglist;
			client_ole2.add_arg(ExcelArgs,i);
			client_ole2.add_arg(ExcelArgs,c+2);
			ExcelCellId := client_ole2.get_obj_property(ExcelWorkSheetId,'cells',ExcelArgs);
			client_ole2.destroy_arglist(ExcelArgs);
			client_ole2.set_property(ExcelCellId, 'Value', v_sal);
			ExcelFontId := client_ole2.get_obj_property(ExcelCellId, 'Font');
			client_ole2.set_property(ExcelFontId, 'Bold', 'True');
			client_ole2.set_property(ExcelFontId, 'Size', '10');
			client_ole2.release_obj(ExcelFontId);
			client_ole2.release_obj(ExcelCellId);
		end if;
		if p_deptno = 'Y' then
			
			ExcelArgs := client_ole2.create_arglist;
			client_ole2.add_arg(ExcelArgs,i);
			client_ole2.add_arg(ExcelArgs,c+3);
			ExcelCellId := client_ole2.get_obj_property(ExcelWorkSheetId,'cells',ExcelArgs);
			client_ole2.destroy_arglist(ExcelArgs);
			client_ole2.set_property(ExcelCellId, 'Value', v_deptno);
			ExcelFontId := client_ole2.get_obj_property(ExcelCellId, 'Font');
			client_ole2.set_property(ExcelFontId, 'Bold', 'True');
			client_ole2.set_property(ExcelFontId, 'Size', '10');
			client_ole2.release_obj(ExcelFontId);
			client_ole2.release_obj(ExcelCellId);
		end if;
  	  		i := i + 1;
	End Loop ;

	-- Close the cursors 
	EXEC_SQL.CLOSE_CURSOR(cursor_number); 
  
	client_ole2.set_property(ExcelID, 'Visible','TRUE');

	-- ... and release the allocated resources because they are no longer used by forms

	client_ole2.release_obj(ExcelWorkSheetID);
	client_ole2.release_obj(ExcelWorkSheetsID);
	client_ole2.release_obj(ExcelWorkBookID);
	client_ole2.release_obj(ExcelWorkBooksID);
	
	client_ole2.release_obj(ExcelID);

EXCEPTION 
  When EXEC_SQL.INVALID_CONNECTION Then
     message('Unexpected Invalid Connection error from EXEC_SQL'); 

  When EXEC_SQL.PACKAGE_ERROR Then
     message('Unexpected error from EXEC_SQL: '||to_char(EXEC_SQL.LAST_ERROR_CODE)|| EXEC_SQL.LAST_ERROR_MESG); 

  If EXEC_SQL.IS_OPEN(cursor_number) Then
    EXEC_SQL.CLOSE_CURSOR(cursor_number); 
    message('Exception - Cleaned up Cursor'); 
  End if; 

END;


regards
Re: Populating the data in respective excel columns [message #400185 is a reply to message #400138] Mon, 27 April 2009 01:05 Go to previous message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

cut and paste (move)

       Excelargs := ole2.create_arglist;
       ole2.add_arg(Excelargs,'D:D'); 
       ccol := ole2.get_obj_property(ExcelworksheetID,'Columns', Excelargs);
       ole2.destroy_arglist(Excelargs);
       
       Excelargs := ole2.create_arglist;
       ole2.add_arg(Excelargs,'3:50');
       rrow := ole2.get_obj_property(ccol, 'Rows', Excelargs);       
       ole2.destroy_arglist(Excelargs);
       ole2.invoke(rrow, 'select');
       ole2.invoke(rrow,'Cut');
                               
       Excelargs := ole2.create_arglist;
       ole2.add_arg(Excelargs,'C:C'); 
       ccol := ole2.get_obj_property(ExcelworksheetID,'Columns', Excelargs);
       ole2.destroy_arglist(Excelargs);
       Excelargs := ole2.create_arglist;
       ole2.add_arg(Excelargs,'3:3'); 
       rrow := ole2.get_obj_property(Ccol,'Rows', Excelargs);
       ole2.destroy_arglist(Excelargs);
       ole2.invoke(rrow,'Select');


It is hardcode same but you have to change/generalize as per you requirements.

paste work, you have do your self and i feel u can

kanish
Previous Topic: SSO
Next Topic: Insert record in detail block but not in master block
Goto Forum:
  


Current Time: Wed Dec 07 05:05:47 CST 2016

Total time taken to generate the page: 0.13183 seconds