كود:
PROCEDURE l_print_excel IS
APPLICATION OLE2.OBJ_TYPE;
WORKBOOKS OLE2.OBJ_TYPE;
WORKBOOK OLE2.OBJ_TYPE;
WORKSHEETS OLE2.OBJ_TYPE;
WORKSHEET OLE2.OBJ_TYPE;
Arglist OLE2.LIST_TYPE;
CELL OLE2.OBJ_TYPE;
r INTEGER;
c INTEGER;
file_name_cl VARCHAR2(32767);
user_cancel EXCEPTION;
Workfont OLE2.OBJ_TYPE;
WorkInterior OLE2.OBJ_TYPE;
Cursor c1 is
Select 'EMP_CODE' COL1,'EMP_NAME' COL2
From dual;
Cursor c2 is
Select emp_code,emp_name
From employee_master;
--Inner Proc.
Procedure put_cell (Row_num number,
Col_num number,
put_value varchar2,
font_name varchar2 default null,
font_size binary_integer default null,
font_style varchar2 default null,/*here you can pass BOLD for bold, ITALIC for italic etc*/
font_color binary_integer default null) is
Begin
Arglist := OLE2.create_arglist;
OLE2.add_arg(Arglist,row_num);
OLE2.add_arg(Arglist,col_num);
cell := OLE2.get_obj_property(Worksheet,'Cells',Arglist);
OLE2.destroy_arglist(Arglist);
OLE2.set_property(cell,'Value',put_value);
Workfont := OLE2.get_obj_property(cell,'Font');
WorkInterior := OLE2.get_obj_property(cell,'Interior');
If font_name is not null then
OLE2.set_property(Workfont,'Name',font_name);
End if;
If font_size is not null then
OLE2.set_property(Workfont,'Size',font_size);
End if;
If font_style is not null then OLE2.set_property(Workfont,font_style,1);
End if;
If font_color is not null then OLE2.set_property(Workfont,'ColorIndex',font_color);
End if;
OLE2.release_obj(workinterior);
OLE2.release_obj(workfont);
OLE2.release_obj(cell); End; BEGIN --Open Excel Application and make it visible
APPLICATION := OLE2.CREATE_OBJ('Excel.Application');
OLE2.SET_PROPERTY(APPLICATION,'Visible',True);
-------------------------------------------------------------------------
--Open a specified file where i want to export data
/* Arglist := OLE2.create_arglist;
OLE2.add_arg(Arglist,'C:\temp\test.xls');
WORKBOOKS := OLE2.GET_OBJ_PROPERTY(APPLICATION, 'WORKBOOKS');
WORKBOOK := OLE2.INVOKE_OBJ(WORKBOOKS, 'Open',Arglist);
OLE2.destroy_arglist(Arglist); */
/*open new file*/
WORKBOOKS := OLE2.GET_OBJ_PROPERTY(APPLICATION, 'WORKBOOKS');
WORKBOOK := OLE2.INVOKE_OBJ(WORKBOOKS, 'Add');
-------------------------------------------------------------------------
---Initilize work sheet Worksheet := OLE2.get_obj_property(Application,'Activesheet');
OLE2.set_property(Worksheet,'Name','MySheetNo.1'); /*Print titles*/
r := 1;
put_Cell (r,1,'EMPLOYEE LIST OF THE COMPANY',font_style=>'BOLD');
r := 2;
For r1 in c1 loop
put_Cell (r,1,r1.col1,font_style=>'BOLD');
put_Cell (r,2,r1.col1,font_style=>'BOLD');
r := r+1;--increment row
END LOOP;
/*Print Data*/
For r2 in c2 loop
put_Cell (r,1,r2.emp_code);
put_Cell (r,2,r2.emp_name);
r := r+1;
end loop;
-- Save the Excel file created
Arglist := OLE2.Create_Arglist;
OLE2.Add_Arg(Arglist,'c:\temp\test.xls');
OLE2.Invoke(workbook, 'Save', Arglist);
OLE2.Destroy_Arglist(Arglist);
-- release workbook
OLE2.Release_Obj(worksheet);
OLE2.Release_Obj(workbook);
OLE2.Release_Obj(workbooks);
OLE2.Release_Obj(application);
End;
ضوابط المشاركة
المفضلات