How to Export Oracle data to Excel file from Oracle Forms 6i | Oracle apps

Опубликовано: 10 Март 2025
на канале: Subratkumar123

14,849

This is Oracle forms tutorial exporting data using for cursor loop to excel file invoking excel application with OLE2 forms built in package. OLE2 package contains all the stuff to invoke the property, procedures and function within an excel application. It creates the file, add workbook, add worksheet, put data to cells of worksheet and set properties like font size ,font color and font size of the cells. It saves the file as well.

How to Export Oracle data to CSV file

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 empno,ename,sal from emp;
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.empno);
col_num:=2;
SetCellValue(row_num,col_num,i.ename);
col_num:=3;
SetCellValue(row_num,col_num,i.sal);
end loop;
save_excel('d:\excel_export','emp_data');
OLE2.Release_Obj(workbook);
OLE2.Release_Obj(workbooks);
OLE2.Release_Obj(application);
end;

*************---------------------------***********************
Query Solved below

How to Create excel reports from oracle forms,
oracle forms export to excel,
how to generate excel file from oracle reports,
how to convert oracle report into excel,
oracle reports 10g to excel output,
How to Create excel reports from oracle report,
How to Create EXCEL Report from Oracle forms 10g,
export data from oracle to excel using pl sql,
how to generate excel file from pl sql,
exporting data from oracle sql developer to excel,
How to Create EXCEL Report from Oracle Database