/* Formatted on 18.6.2014. 12:16:57 (QP5 v5.149.1003.31008) */
PACKAGE RPT2XLS
IS
   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 run;

   PROCEDURE release_memory;
-- CurrentRow :=1
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 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');

      OLE2.RELEASE_OBJ (WorkColumn);
      OLE2.RELEASE_OBJ (Worksheet);
      OLE2.RELEASE_OBJ (Worksheets);
      OLE2.RELEASE_OBJ (Workbook);
      OLE2.RELEASE_OBJ (Workbooks);
      OLE2.RELEASE_OBJ (Application);
      CurrentRow := 1;
   END;

   PROCEDURE release_memory
   IS
   BEGIN
      Cell := ExcelCells ();
      SYS.DBMS_SESSION.free_unused_user_memory;
   END;
END;

RPT2XLS.put_cell(4,TO_date(SYSDATE),'DD/MON/RRRR')

rpt2xls.put_cell(4, sysdate, 'dd/mon/rrrr')

rpt2xls.put_cell(4, to_char(sysdate, 'dd/mon/rrrr'))

RPT2XLS.put_cell(4,'"'||TO_char(SYSDATE),'DD/MON/RRRR')||'"')