6i Report to Excel

 

How to push / generate / convert Oracle Report 6i output to excel

In this post, We discuss about Generating Excel Outputs from existing Oracle Reports 6i Output. Most end-users use Excel as a very generic tool, because of its GUI / user-friendly interface and to analyzing / calculating the data.

There is a trick to capture Oracle Reports output into a excel sheet.


Step 1:
First create simple tabular report in Oracle Reports 6i using DEPT Table in scott schema.

Step 2:
Now on heading frame, go properties and click on format trigger and type following pl/sql code:

function M_G_DEPTNO_HDRFormatTrigger return boolean is
begin
--Print Headings First
  RPT2XLS.put_cell(1,'Dept#','Arial','8','1'); 
  RPT2XLS.put_cell(2,'Deptartment','Arial','8','1'); 
  RPT2XLS.put_cell(3,'Location','Arial','8','1'); 
  RPT2XLS.new_line;
  return (TRUE);
end;

Step 3:
Now in columns repeating frame and do the same with following pl/sql code:

function R_G_DEPTNOFormatTrigger return boolean is
begin
--Now Print column values
  RPT2XLS.put_cell(1,:deptno,'Arial','8','1'); 
  RPT2XLS.put_cell(2,:dname,'Arial','8','1');
  RPT2XLS.put_cell(3,:loc,'Arial','8','1');
  RPT2XLS.new_line;
  return (TRUE);
end;

Step 4:
Create a button any where you want with following pl/sql code:

procedure U_1ButtonAction is
begin
  RPT2XLS.run;
  RPT2XLS.release_memory;
  
end;

PACKAGE RPT2XLS IS
-- Font style constants
BOLD constant binary_integer := 1;
ITALIC constant binary_integer := 2;
UNDERLINE constant binary_integer := 4;

-- Horizontal alignment constants
SUBTYPE xlHAlign IS binary_integer;
CENTER CONSTANT xlHAlign := -4108;
CENTERACROSSSELECTION CONSTANT xlHAlign := 7;
DISTRIBUTED CONSTANT xlHAlign := -4117;
FILL CONSTANT xlHAlign := 5;
GENERAL CONSTANT xlHAlign := 1;
JUSTIFY CONSTANT xlHAlign := -4130;
LEFT CONSTANT xlHAlign := -4131;
RIGHT CONSTANT xlHAlign := -4152;

PROCEDURE put_cell(ColNo binary_integer, CellValue in varchar2,
FontName in varchar2 DEFAULT null,
FontSize in binary_integer DEFAULT null,
FontStyle in binary_integer DEFAULT null,
FontColor in binary_integer DEFAULT null,
BgrColor in binary_integer DEFAULT null,
Format in varchar2 DEFAULT null,
Align in xlHAlign DEFAULT null
);
PROCEDURE new_line;
PROCEDURE pre_line;
PROCEDURE move_line(p_line in number);
PROCEDURE run;
PROCEDURE release_memory;

END;

PACKAGE BODY RPT2XLS IS
TYPE ExcelCell IS RECORD(RowNo binary_integer,
ColNo binary_integer,
Val varchar2(2000),
FontName varchar2(20),
FontSize binary_integer,
FontStyle binary_integer,
FontColor binary_integer,
BgrColor binary_integer,
Format varchar2(60),
Align xlHAlign
);
TYPE ExcelCells IS TABLE OF ExcelCell;
Cell ExcelCells := ExcelCells();
CurrentRow binary_integer := 1;
PROCEDURE new_line IS
BEGIN
CurrentRow := CurrentRow + 1;
END;
PROCEDURE pre_line IS
BEGIN
CurrentRow := CurrentRow - 1;
END;
PROCEDURE move_line(p_line number)IS
BEGIN
CurrentRow := CurrentRow + p_line;
END;
PROCEDURE put_cell(ColNo binary_integer, CellValue in varchar2,
FontName in varchar2 DEFAULT null,
FontSize in binary_integer DEFAULT null,
FontStyle in binary_integer DEFAULT null,
FontColor in binary_integer DEFAULT null,
BgrColor in binary_integer DEFAULT null,
Format in varchar2 DEFAULT null,
Align in xlHAlign DEFAULT null
) IS
BEGIN
Cell.Extend;
Cell(Cell.Last).RowNo := CurrentRow;
Cell(Cell.Last).ColNo := ColNo;
Cell(Cell.Last).Val := CellValue;
Cell(Cell.Last).FontName := FontName;
Cell(Cell.Last).FontSize := FontSize;
Cell(Cell.Last).FontStyle := FontStyle;
Cell(Cell.Last).FontColor := FontColor;
Cell(Cell.Last).BgrColor := BgrColor;
Cell(Cell.Last).Format := Format;
Cell(Cell.Last).Align := Align;
END;
PROCEDURE run IS
Application OLE2.OBJ_TYPE;
Workbooks OLE2.OBJ_TYPE;
Workbook OLE2.OBJ_TYPE;
Worksheets OLE2.OBJ_TYPE;
Worksheet OLE2.OBJ_TYPE;
WorkCell OLE2.OBJ_TYPE;
WorkColumn OLE2.OBJ_TYPE;
WorkFont OLE2.OBJ_TYPE;
WorkInterior OLE2.OBJ_TYPE;
ArgList OLE2.LIST_TYPE;
BEGIN
Application := OLE2.create_obj('Excel.Application');
OLE2.set_property(Application, 'Visible', 1);
Workbooks := OLE2.get_obj_property(Application, 'Workbooks');
Workbook := OLE2.invoke_obj(WorkBooks, 'Add');
Worksheets := OLE2.get_obj_property(Workbook, 'Worksheets');
Worksheet := OLE2.get_obj_property(Application, 'ActiveSheet');
for i in Cell.First .. Cell.Last

loop
if Cell(i).Val is not null then
ArgList := OLE2.create_arglist;
OLE2.add_arg(ArgList, Cell(i).RowNo);
ole2.add_arg(ArgList, Cell(i).ColNo);
WorkCell := OLE2.get_obj_property(Worksheet, 'Cells', ArgList);
ole2.destroy_arglist(ArgList);
ole2.set_property(WorkCell, 'Value', Cell(i).Val);
ole2.set_property(WorkCell, 'NumberFormat', Cell(i).Format);
if Cell(i).Align is not null then
ole2.set_property(WorkCell, 'HorizontalAlignment', Cell(i).Align);
end if;
WorkFont := OLE2.get_obj_property(WorkCell, 'Font');
WorkInterior := ole2.Get_Obj_Property(WorkCell, 'Interior');
if Cell(i).FontName is not null then
OLE2.set_property(WorkFont, 'Name', Cell(i).FontName);
end if;
if Cell(i).FontSize is not null then
OLE2.set_property(WorkFont, 'Size', Cell(i).FontSize);
end if;
if mod(Cell(i).FontStyle, 2) = 1 then
OLE2.set_property(WorkFont, 'Bold', 1);
end if;
if mod(Cell(i).FontStyle, 4) > 2 then
OLE2.set_property(WorkFont, 'Italic', 1);
end if;
if mod(Cell(i).FontStyle, 8) > 4 then
OLE2.set_property(WorkFont, 'Underline', 2);
end if;
if Cell(i).FontColor is not null then
OLE2.set_property(WorkFont, 'ColorIndex', Cell(i).FontColor);
end if;
if Cell(i).BgrColor is not null then
OLE2.set_property(WorkInterior, 'ColorIndex', Cell(i).BgrColor);
end if;
OLE2.release_obj(WorkInterior);
OLE2.release_obj(WorkFont);
OLE2.release_obj(WorkCell);
end if;
end loop;
ArgList := ole2.create_arglist;
ole2.add_arg(ArgList, 'A:Z');
WorkColumn := ole2.Get_Obj_Property(WorkSheet, 'Columns', ArgList);
ole2.destroy_arglist(ArgList);
ole2.invoke(WorkColumn, 'AutoFit');
ArgList := OLE2.CREATE_ARGLIST;
-- OLE2.ADD_ARG(ArgList, 'C:\'||'aaa');
--OLE2.ADD_ARG(ArgList, 'C:\Documents and Settings\All Users\Start Menu\Programs\Microsoft Office');
--OLE2.INVOKE(Workbook, 'SaveAs', ArgList);
OLE2.DESTROY_ARGLIST(ArgList);
---OLE2.INVOKE(Workbook,'CLOSE'); --comment by aq
--ole2.invoke(Application,'Quit'); --comment by aq
OLE2.release_obj(WorkColumn);
OLE2.release_obj(Worksheet);
OLE2.release_obj(Worksheets);
OLE2.release_obj(Workbook);
OLE2.release_obj(Workbooks);
OLE2.release_obj(Application);
END;
PROCEDURE release_memory IS
BEGIN
Cell := ExcelCells();
SYS.DBMS_SESSION.free_unused_user_memory;
END;
END;
-----------------Package2-----------End------