DDE-Poke

Requirements

1.Create Form DEPT And EMP Table as Master Detail.

2.Create One Push Button have following Code

  IF :dept.deptno IS NOT NULL THEN

   SUMMARY_TO_EXCEL;

  END IF;

3.Create Following Procedure On Program Unit Of Created Forms. 

4.Makesure You Install WEBUTIL Utility in Your Application Server and Developer PC.

PROCEDURE SUMMARY_TO_EXCEL IS

  APPID            PLS_INTEGER;

  CONVID           PLS_INTEGER;

  DOCID            PLS_INTEGER;

  CONV_ESTABLISHED BOOLEAN := FALSE;

  BUFFER           CHAR(100);

  CTR              NUMBER(10) := 0;

  L_CTR            NUMBER(10) := 0;

  ROW_COL1         VARCHAR2(10);

  ROW_COL2         VARCHAR2(10);

  FILENAME         VARCHAR2(100);

  V_DS_CLASSE      VARCHAR2(100);

BEGIN

  V_DS_CLASSE := CLIENT_WIN_API_ENVIRONMENT.READ_REGISTRY('HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Excel\InstallRoot', 'Path', FALSE);

  V_DS_CLASSE := V_DS_CLASSE || 'EXCEL.EXE';

  --APPID := DDE.APP_BEGIN('C:\Program Files\Microsoft Office\Office12\EXCEL.EXE', DDE.APP_MODE_MAXIMIZED);--dde.app_mode_normal);

  APPID := DDE.APP_BEGIN(V_DS_CLASSE, DDE.APP_MODE_MAXIMIZED); --dde.app_mode_normal);

  /* In Application Server Install MSOFFICE Also..*/

  WHILE NOT CONV_ESTABLISHED LOOP

    BEGIN

      CONVID           := DDE.INITIATE('EXCEL', 'SYSTEM');

      CONV_ESTABLISHED := TRUE;

    EXCEPTION

      WHEN DDE.DMLERR_NO_CONV_ESTABLISHED THEN

        CONV_ESTABLISHED := FALSE;

    END; -- LOOP

  END LOOP;

  FILENAME := 'D:\Dept_Summary_' || TO_CHAR(SYSDATE, 'DDMMRRRR') || '.XLSX';

  DDE.EXECUTE(CONVID, '[SAVE.AS("' || FILENAME || '")]', 10000);

  DOCID := DDE.INITIATE('EXCEL', FILENAME);

  CTR   := 2;

  GO_BLOCK('DEPT');

  DDE.POKE(DOCID, 'R1C1', 'Dept Code',  DDE.CF_TEXT, 10000);

  DDE.POKE(DOCID, 'R1C2', :DEPT.DEPTNO, DDE.CF_TEXT, 10000);

  DDE.POKE(DOCID, 'R1C3', :DEPT.DNAME,  DDE.CF_TEXT, 10000);

  DDE.POKE(DOCID, 'R1C4', :DEPT.LOC,    DDE.CF_TEXT, 10000);

  DDE.POKE(DOCID, 'R2C1', 'As On Date', DDE.CF_TEXT, 10000);

  DDE.POKE(DOCID, 'R2C2', SYSDATE,      DDE.CF_TEXT, 10000);

  DDE.POKE(DOCID, 'R3C1', 'EMPNO',      DDE.CF_TEXT, 10000);

  DDE.POKE(DOCID, 'R3C2', 'ENAME',      DDE.CF_TEXT, 10000);

  DDE.POKE(DOCID, 'R3C3', 'JOB',        DDE.CF_TEXT, 10000);

  DDE.POKE(DOCID, 'R3C4', 'MGR',        DDE.CF_TEXT, 10000);

  DDE.POKE(DOCID, 'R3C5', 'HIREDATE',   DDE.CF_TEXT, 10000);

  DDE.POKE(DOCID, 'R3C6', 'SALARY',     DDE.CF_TEXT, 10000);

  DDE.POKE(DOCID, 'R3C7', 'COMMISSION', DDE.CF_TEXT, 10000);

  GO_BLOCK('EMP');

  LAST_RECORD;

  L_CTR := :SYSTEM.CURSOR_RECORD;

  CTR   := 3;

  FIRST_RECORD;

  FOR I IN 1 .. L_CTR LOOP

 

    CTR := CTR + 1;

 

    DDE.POKE(DOCID, 'R' || CTR || 'C1', :EMPNO, DDE.CF_TEXT, 10000);

    DDE.POKE(DOCID,

             'R' || CTR || 'C2',

             NVL(:ENAME, 'NA'),

             DDE.CF_TEXT,

             10000);

    DDE.POKE(DOCID,

             'R' || CTR || 'C3',

             NVL(:JOB, 'NA'),

             DDE.CF_TEXT,

             10000);

    DDE.POKE(DOCID,

             'R' || CTR || 'C4',

             NVL(:MGR, 7839),

             DDE.CF_TEXT,

             10000);

    DDE.POKE(DOCID,

             'R' || CTR || 'C5',

             NVL(:HIREDATE, SYSDATE),

             DDE.CF_TEXT,

             10000);

    DDE.POKE(DOCID, 'R' || CTR || 'C6', NVL(:SAL, 0), DDE.CF_TEXT, 10000);

    DDE.POKE(DOCID, 'R' || CTR || 'C7', NVL(:COMM, 0), DDE.CF_TEXT, 10000);

    NEXT_RECORD;

  END LOOP;

  DDE.TERMINATE(DOCID);

  DDE.APP_END(APPID);

  -- HANDLE EXCEPTIONS 

EXCEPTION

  WHEN DDE.DDE_APP_FAILURE THEN

    MESSAGE('WINDOWS APPLICATION CANNOT START.');

  WHEN DDE.DDE_PARAM_ERR THEN

    MESSAGE('A NULL VALUE WAS PASSED TO DDE');

  WHEN DDE.DMLERR_NO_CONV_ESTABLISHED THEN

    MESSAGE('DDE CANNOT ESTABLISH A CONVERSATION');

  WHEN DDE.DMLERR_NOTPROCESSED THEN

    MESSAGE('A TRANSACTION FAILED');

 

END;