Home » Developer & Programmer » Reports & Discoverer » Run Report 6i to Excel ..Date form 4/6/14 but i want 04/Jun/2014
Run Report 6i to Excel ..Date form 4/6/14 but i want 04/Jun/2014 [message #616547] Wed, 18 June 2014 03:22 Go to next message
Rizwi
Messages: 16
Registered: June 2014
Location: DUBAI
Junior Member
Dear Brother,
As salamu alaikum,

I have problem one report..Report 6i run in excel fomat then show date format 17-Jun-14 but brother user want to show 17/Jun/2014. how it possible.
i have use procedure below pls check.


/* 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;


[EDITED by LF: fixed crossposting, reformatted and applied [code] tags]

[Updated on: Wed, 18 June 2014 05:20] by Moderator

Report message to a moderator

Re: HELP-:Run Report 6i to Excel ..Date form 4/6/14 but i want 04/Jun/2014 [message #616552 is a reply to message #616547] Wed, 18 June 2014 04:15 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Welcome to the forum.
Please read and follow How to use [code] tags and make your code easier to read?

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

That's just silly. to_date converts a string to a date. sysdate is a date. Trying to convert a date to a date is just a recipe for bugs.
To_char is what you need if you want the date in a particular format.

I suggest you read this: http://edstevensdba.wordpress.com/2011/04/07/nls_date_format/
Re: HELP-:Run Report 6i to Excel ..Date form 4/6/14 but i want 04/Jun/2014 [message #616606 is a reply to message #616552] Thu, 19 June 2014 00:25 Go to previous messageGo to next message
Rizwi
Messages: 16
Registered: June 2014
Location: DUBAI
Junior Member
Dear brother,

i have used RPT2XLS.put_cell(4,TO_date(SYSDATE),'DD/MON/RRRR') this coding .Report is showing 19/6/14 but boss I need for 19/Jun/2014..please help me..
Re: HELP-:Run Report 6i to Excel ..Date form 4/6/14 but i want 04/Jun/2014 [message #616618 is a reply to message #616606] Thu, 19 June 2014 02:06 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I never used RPT2XLS.

You didn't read what Cookiemonster said previously. TO_DATE(SYSDATE) is stupid. SYSDATE is a function that returns a DATE. Why do you think that you should TO_DATE(DATE)?

Therefore, try with something like (once again: I don't know rpt2xls's syntax)
rpt2xls.put_cell(4, sysdate, 'dd/mon/rrrr')

or
rpt2xls.put_cell(4, to_char(sysdate, 'dd/mon/rrrr'))
Re: HELP-:Run Report 6i to Excel ..Date form 4/6/14 but i want 04/Jun/2014 [message #616628 is a reply to message #616618] Thu, 19 June 2014 03:25 Go to previous messageGo to next message
Rizwi
Messages: 16
Registered: June 2014
Location: DUBAI
Junior Member
Actually brother I have run report from from6i and wrote a code in Report 6i and Run the Report in EXCEL format.I have
used RPT2XLS.PUT_CELL PROCEDURE.I have only problem date format..


RPT2XLS.PUT_CELL procedure used for Run the Report in EXCEL format....Direct run form 6i to excel..

[Updated on: Thu, 19 June 2014 03:31]

Report message to a moderator

Re: HELP-:Run Report 6i to Excel ..Date form 4/6/14 but i want 04/Jun/2014 [message #616633 is a reply to message #616628] Thu, 19 June 2014 03:51 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
And have you tried what I and LF suggested?
Re: HELP-:Run Report 6i to Excel ..Date form 4/6/14 but i want 04/Jun/2014 [message #616644 is a reply to message #616633] Thu, 19 June 2014 04:54 Go to previous messageGo to next message
Rizwi
Messages: 16
Registered: June 2014
Location: DUBAI
Junior Member
Hi cookiemonster,

I have try but report have same problem..i have also used RPT2XLS.put_cell(4,TO_date(SYSDATE),'DD/MON/RRRR') and report display 1/6/14 But I want to diplay 01/Jun/2014 and 1 replace as 01,6 replace as Jun and 14 replace as 2014.

Pls let me know how will write codding..

Thanku

[Updated on: Thu, 19 June 2014 04:55]

Report message to a moderator

Re: HELP-:Run Report 6i to Excel ..Date form 4/6/14 but i want 04/Jun/2014 [message #616666 is a reply to message #616644] Thu, 19 June 2014 06:48 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
First, don't ever write to_Date(sysdate) again. It is always wrong.
Second read the link I posted above so that you understand why the above is always wrong.

Then try something like this:
RPT2XLS.put_cell(4,'"'||TO_char(SYSDATE),'DD/MON/RRRR')||'"')

I suspect that excel is formatting the date for you. Adding "" may stop this.
Re: HELP-:Run Report 6i to Excel ..Date form 4/6/14 but i want 04/Jun/2014 [message #616670 is a reply to message #616666] Thu, 19 June 2014 07:25 Go to previous messageGo to next message
Rizwi
Messages: 16
Registered: June 2014
Location: DUBAI
Junior Member
Thanks cookiemonster its working fine.
Re: Run Report 6i to Excel ..Date form 4/6/14 but i want 04/Jun/2014 [message #667012 is a reply to message #616547] Tue, 05 December 2017 03:35 Go to previous message
chirag1890
Messages: 4
Registered: July 2012
Location: Malaysia
Junior Member
Hello, I Used Above Given Code , But Not Able Export Data In Excel,. Only Header Is Coming .. List Of Table Data is not coming In Excel..

i have attached file RDF File .. Please Help me Out..

Thanks U ..

[Updated on: Tue, 05 December 2017 21:16]

Report message to a moderator

Previous Topic: prefix and suffix symbol in number field (merged 3 CM)
Next Topic: REP-1108 Report error
Goto Forum:
  


Current Time: Thu Mar 28 15:41:33 CDT 2024