Export oracle data to Microsoft Excel from Oracle forms.

A few days back I have been requested 

by one of my youtube viewer to make 

an oracle forms tutorial that will 

exports oracle database data to excel 

file with a single button click. 

Alas.. I am not talking about exporting 

data to CSV, but instead exporting 

directly to excel file. This has a 

few advantages. You can place your 

data in your desired format. Format 

your cell, with colors, style, and 

other excel features. So just go 

through this code given below, 

you can also copy paste the code, all you need to change is the column heading 

and sql string as per your requirement. It have nothing but one forms in-build 

package ole2.

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;
 arglist ole2.list_type;
 row_num number;
 col_num number;
 fontObj ole2.obj_type;
 cursor rec is select emp_code,emp_name,to_char(date_of_birth,'dd/mm/rrrr') date_of_birth from employee_master where date_of_birth is not null;
 procedure SetCellValue(rowid number,colid number,cellValue varchar) is
 begin
  arglist := ole2.create_arglist;
  ole2.add_arg(arglist,rowid);
  ole2.add_arg(arglist,colid);
  cell:= ole2.get_obj_property(worksheet,'Cells',arglist);
  fontObj := ole2.get_obj_property(cell,'Font');
  ole2.destroy_arglist(arglist);
  ole2.set_property(cell,'value',cellValue);
  ole2.set_property(fontObj,'Size',16);
  ole2.set_property(fontObj,'BOLD',1);
  ole2.set_property(fontObj,'ColorIndex',7);
  ole2.release_obj(cell);
 end SetCellValue;
 procedure app_init is
  begin
   application := ole2.create_obj('Excel.Application');
   ole2.set_property(application,'Visible',true);
   workbooks := ole2.get_obj_property(application,'workbooks');
   workbook := ole2.invoke_obj(workbooks,'add');
   worksheets := ole2.get_obj_property(application,'worksheets');
   worksheet := ole2.invoke_obj(worksheets,'add');
   ole2.set_property(worksheet,'Name','Emp Sheet');
 end app_init;
 
 procedure save_excel(path varchar,filename varchar) is
  begin
    OLE2.Release_Obj(worksheet);
    OLE2.Release_Obj(worksheets);
    -- Save the Excel file created
    If path is not null then
       Arglist := OLE2.Create_Arglist;
       OLE2.Add_Arg(Arglist,path||'\'||file_name||'.xls');
       OLE2.Invoke(workbook, 'SaveAs', Arglist);
       OLE2.Destroy_Arglist(Arglist);
    end if;
 end save_excel;

 begin
  app_init;
    row_num:=1;
    col_num:=1;
    SetCellValue(row_num,col_num,'Emp Code');
    col_num:=col_num + 1;
    SetCellValue(row_num,col_num,'Emp Name');
    col_num:=col_num + 1;
    SetCellValue(row_num,col_num,'Date of Birth');
    for i in rec loop
     row_num:=row_num + 1;
     col_num:=1;
     SetCellValue(row_num,col_num,i.emp_code);    
     col_num:=2;
     SetCellValue(row_num,col_num,i.emp_name);    
     col_num:=3;
     SetCellValue(row_num,col_num,i.date_of_birth);    
    end loop;    
   save_excel('d:\excel_export','emp_data');       
    OLE2.Release_Obj(workbook);
    OLE2.Release_Obj(workbooks);
    OLE2.Release_Obj(application); 
end;