Home » Developer & Programmer » Forms » Excel generation (Oracle Developer 6i)
Excel generation [message #557376] Tue, 12 June 2012 05:51 Go to next message
shekhar.salunkhe
Messages: 154
Registered: January 2008
Location: Pune
Senior Member
I want to gerate the excel(Office 2010)file using oracle forms 6i.
How we can achieve this with forms 6i?

Report message to a moderator

Re: Excel generation [message #557399 is a reply to message #557376] Tue, 12 June 2012 08:09 Go to previous messageGo to next message
Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I never did that, but I've seen people discussing such problems. Here are the search results; have a look, refine it to narrow the selection.

Report message to a moderator

icon14.gif  Re: Excel generation [message #557719 is a reply to message #557376] Fri, 15 June 2012 01:25 Go to previous messageGo to next message
kartheek
Messages: 17
Registered: February 2012
Location: chennai
Junior Member
here is the sample code which may help you ...
Declare
  application ole2.obj_type;
  workbooks   ole2.obj_type;
  workbook    ole2.obj_type;
  worksheet   ole2.obj_type;
  worksheets  ole2.obj_type;
  WorkColumn  ole2.obj_type;
  Range	      ole2.obj_type;
  Range_col    ole2.obj_type;
  cell         ole2.obj_type;
  WorkFont     ole2.obj_type;
  Column       ole2.obj_type;
  Row 	       ole2.obj_type;
  x_row number;
  x_col number;
  FontName varchar2(100);
  FontSize number;	
begin
for i in 1..20 loop
-- Start up Excel
	application := ole2.create_obj('Excel.Application');
	workbooks   := ole2.get_obj_property(application, 'Workbooks');
-- Open the required workbook
	  DebugMessage:='Error while Opening the File';
	  workbook := ole2.get_obj_property(workbooks, 'Add');
-- Open worksheet 1 of that Workbook	
		worksheets:=ole2.get_obj_property(workbook, 'Worksheets');
		args:=ole2.create_arglist; 
		ole2.add_arg(args, 1);
		worksheet:=ole2.get_obj_property(worksheets,'Item', args);
		ole2.destroy_arglist(args);
 -- Get the values
	  x_row         := 0;
	  x_col         := 1;
	FontName := 'Arial';
	  FontSize := 10;
for j in 1..10 loop
	    x_row := x_row + 1;  	
	  	DebugMessage := 'Error while reading Column='||x_col;
	    args  := ole2.create_arglist;
	    ole2.add_arg(args, x_row);
	    ole2.add_arg(args, x_col);
	    cell := ole2.get_obj_property(worksheet, 'Cells', args);
	    ole2.destroy_arglist(args);
	    ole2.set_property(cell, 'Value',j.1);
	    ole2.set_property(cell, 'HorizontalAlignment', CENTER);
	    ole2.set_property(cell, 'VerticalAlignment', TOP);
	    WorkFont := OLE2.get_obj_property(Cell, 'Font');
	    ole2.set_property(WorkFont, 'Name', FontName);
	    ole2.set_property(WorkFont, 'Size', FontSize);
	    ole2.set_property(WorkFont, 'Bold', BOLD);
	    ole2.release_obj(WorkFont);
	    ole2.release_obj(cell);
--get the columns to merge
		args := ole2.create_arglist;
		ole2.add_arg(args, 'A:F'); --columns A thru F
		column := ole2.get_obj_property(worksheet, 'Columns', args);
		ole2.destroy_arglist(args);
			
--get the rows of the columns to merge
		args := ole2.create_arglist;
		ole2.add_arg(args, x_row||':'||x_row); --only current row
		row := ole2.get_obj_property(column, 'Rows', args);
		ole2.destroy_arglist(args);
		ole2.invoke(row, 'Merge');
--Auto Fit
    	 args := ole2.create_arglist;
	  ole2.add_arg(args, 'A:Z'); 
	  WorkColumn := ole2.Get_Obj_Property(WorkSheet, 'Columns', args); 
	  ole2.destroy_arglist(args);
	  ole2.invoke(WorkColumn, 'AutoFit');

    SYS.DBMS_SESSION.free_unused_user_memory;
  	              
	  --Save the file
		args := ole2.create_arglist;
		ole2.add_arg( args,l_file_name);
		ole2.invoke( worksheet,'SaveAs',args );
		ole2.destroy_arglist( args );
	
		--display the file
		ole2.set_property(application,'Visible','True');
	
		--Close the file and release the objects
	  --ole2.invoke(workbook, 'Close');
	  workbooks:=OLE2.GET_OBJ_PROPERTY(application, 'Workbooks'); 	
		
	  ole2.release_obj(worksheet);
	  ole2.release_obj(worksheets);
	  ole2.release_obj(workbook);
	  ole2.release_obj(workbooks);
	  ole2.invoke(application, 'Quit');
	  ole2.release_obj(application);

Report message to a moderator

Re: Excel generation [message #557730 is a reply to message #557719] Fri, 15 June 2012 04:33 Go to previous messageGo to next message
Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Kartheek, this seems to be an incomplete code. It misses at least two END LOOPs and END for the whole PL/SQL procedure. Did you omit these on purpose, or ...?

Report message to a moderator

Re: Excel generation [message #557739 is a reply to message #557730] Fri, 15 June 2012 06:37 Go to previous message
kartheek
Messages: 17
Registered: February 2012
Location: chennai
Junior Member
oops sorry ....I thought of just giving him a sample idea...by showing the way how we can assign the values..and populate it in excel.....unfortunately I forgot to keep the end for the two loops over there..

Declare
  application ole2.obj_type;
  workbooks   ole2.obj_type;
  workbook    ole2.obj_type;
  worksheet   ole2.obj_type;
  worksheets  ole2.obj_type;
  WorkColumn  ole2.obj_type;
  Range	      ole2.obj_type;
  Range_col    ole2.obj_type;
  cell         ole2.obj_type;
  WorkFont     ole2.obj_type;
  Column       ole2.obj_type;
  Row 	       ole2.obj_type;
  x_row number;
  x_col number;
  FontName varchar2(100);
  FontSize number;	
begin
for i in /*was using a cursor here to fetch the data*/ loop
-- Start up Excel
	application := ole2.create_obj('Excel.Application');
	workbooks   := ole2.get_obj_property(application, 'Workbooks');
-- Open the required workbook
	  DebugMessage:='Error while Opening the File';
	  workbook := ole2.get_obj_property(workbooks, 'Add');
-- Open worksheet 1 of that Workbook	
		worksheets:=ole2.get_obj_property(workbook, 'Worksheets');
		args:=ole2.create_arglist; 
		ole2.add_arg(args, 1);
		worksheet:=ole2.get_obj_property(worksheets,'Item', args);
		ole2.destroy_arglist(args);
 -- Get the values
	  x_row         := 0;
	  x_col         := 1;
	FontName := 'Arial';
	  FontSize := 10;
end loop;--end of the i loop
for j in /*was using a cursor to fetch the data*/ loop
	    x_row := x_row + 1;  	
	  	DebugMessage := 'Error while reading Column='||x_col;
	    args  := ole2.create_arglist;
	    ole2.add_arg(args, x_row);
	    ole2.add_arg(args, x_col);
	    cell := ole2.get_obj_property(worksheet, 'Cells', args);
	    ole2.destroy_arglist(args);
	    ole2.set_property(cell, 'Value',j.cursor name);
	    ole2.set_property(cell, 'HorizontalAlignment', CENTER);
	    ole2.set_property(cell, 'VerticalAlignment', TOP);
	    WorkFont := OLE2.get_obj_property(Cell, 'Font');
	    ole2.set_property(WorkFont, 'Name', FontName);
	    ole2.set_property(WorkFont, 'Size', FontSize);
	    ole2.set_property(WorkFont, 'Bold', BOLD);
	    ole2.release_obj(WorkFont);
	    ole2.release_obj(cell);
--get the columns to merge
		args := ole2.create_arglist;
		ole2.add_arg(args, 'A:F'); --columns A thru F
		column := ole2.get_obj_property(worksheet, 'Columns', args);
		ole2.destroy_arglist(args);
			
--get the rows of the columns to merge
		args := ole2.create_arglist;
		ole2.add_arg(args, x_row||':'||x_row); --only current row
		row := ole2.get_obj_property(column, 'Rows', args);
		ole2.destroy_arglist(args);
		ole2.invoke(row, 'Merge');
end loop;--end of the j loop
--Auto Fit
    	 args := ole2.create_arglist;
	  ole2.add_arg(args, 'A:Z'); 
	  WorkColumn := ole2.Get_Obj_Property(WorkSheet, 'Columns', args); 
	  ole2.destroy_arglist(args);
	  ole2.invoke(WorkColumn, 'AutoFit');

    SYS.DBMS_SESSION.free_unused_user_memory;
  	              
	  --Save the file
		args := ole2.create_arglist;
		ole2.add_arg( args,l_file_name);
		ole2.invoke( worksheet,'SaveAs',args );
		ole2.destroy_arglist( args );
	
		--display the file
		ole2.set_property(application,'Visible','True');
	
		--Close the file and release the objects
	  --ole2.invoke(workbook, 'Close');
	  workbooks:=OLE2.GET_OBJ_PROPERTY(application, 'Workbooks'); 	
		
	  ole2.release_obj(worksheet);
	  ole2.release_obj(worksheets);
	  ole2.release_obj(workbook);
	  ole2.release_obj(workbooks);
	  ole2.invoke(application, 'Quit');
	  ole2.release_obj(application);
end;

[Updated on: Fri, 15 June 2012 06:44]

Report message to a moderator

Previous Topic: Oracle Forms (3 Merged)
Next Topic: USE of JOIN In Forms 6i
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Jul 06 10:13:15 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.