convert d2k report in excel

How to convert d2k report in excel

first create a  RPT2XLS.pll  as under below :

first step :-

/start****************RPT2XLS(package space)@***********************/

PACKAGE RPT2XLS IS

BOLD constant binary_integer := 1;

ITALIC constant binary_integer := 2;

UNDERLINE constant binary_integer := 4;

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  );

PROCEDURE new_line;

PROCEDURE run;

PROCEDURE release_memory;

END;

/end****************RPT2XLS(package space)@***********************/

/start****************RPT2XLS(package body)@***********************/

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) DEFAULT null );

TYPE ExcelCells IS TABLE OF ExcelCell;

Cell ExcelCells := ExcelCells();

CurrentRow binary_integer := 1;

PROCEDURE new_line IS

BEGIN

CurrentRow := CurrentRow + 1;

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) IS

EGIN

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;

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);

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');

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;

/end****************RPT2XLS(package body)@***********************/

Second Step:

Use RPT2XLZ.PLL in report builder 

attach lib fiel in reort builder 

attach lib file in reort builder

After report trigger 

function AfterReport return boolean is

begin

rpt2xls.release_memory;

return (TRUE);

end;

format trigger 

function F_21FormatTrigger return boolean is

begin

--  RPT2XLS.PUT_CELL(1,:countasper);--,null,null,03,null,null,null);

--RPT2XLS.PUT_CELL(2,:p_date);

RPT2XLS.PUT_CELL(3,'Grand Total',null,null,03,null,null,null);

RPT2XLS.PUT_CELL(4,:sumoftotalweight1);

RPT2XLS.PUT_CELL(5,:sumofoutcenwtsoc);

RPT2XLS.PUT_CELL(6,:sumtotalwt);

--RPT2XLS.PUT_CELL(7,:govt_rule);

RPT2XLS.PUT_CELL(8,:sum_soc_comm_tot);

RPT2XLS.PUT_CELL(9,:sum_cf_4);

RPT2XLS.PUT_CELL(10,:sum_cf_4);

RPT2XLS.PUT_CELL(11,:sum_cf_4);

RPT2XLS.PUT_CELL(12,:sum_cf_4);

rpt2xls.new_line;

return (TRUE);

end;

when button pressed

procedure U_1ButtonAction is

begin

rpt2xls.run;

end;

then compile report and run properly 

Welcome your suggestion