D2K - Generating excel report from oracle developer.

mansur_mca
mansur_mca used Ask the Experts™
on
Is there any way we can generate a excel report from Oracle forms-D2K. Please advice the steps.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can try webutil ( pdf file is attached ) or

Here is code that  call from a button on a form. It uses the DDEbuilt-in package. It also uses the D2KWUTIL library for theWIN_API_UTILITY.DELETE_FILE package.

 
 PROCEDURE LOADINVOICEDATA IS BEGIN   DECLARE     AppID  PLS_INTEGER;     ConvID  PLS_INTEGER;     RCount Number := 0;     CURSOR c1 IS SELECT col1, col2, col3 from table;     PROCEDURE INSERTROW (invalue VARCHAR2, incol NUMBER) IS        BEGIN          DDE.POKE(ConvID, 'R'||TO_CHAR(RCount)||'C'||TO_CHAR(incol), invalue, DDE.CF_TEXT, 1000);        END INSERTROW;   BEGIN --:WORKSHEETPATH is a field on the form that contains the path and name of the Excel worksheet--:EXCELFILEPATH is a field on the form that contains the name of theExcel.exe ex. c:\program files\microsoft office\office\excel.exe     WIN_API_UTILITY.DELETE_FILE(:WORKSHEETPATH,False);     RCount := 1;     AppID := DDE.APP_BEGIN(:EXCELFILEPATH, DDE.APP_MODE_MAXIMIZED);     ConvID := DDE.INITIATE('EXCEL', 'system');     DDE.EXECUTE(ConvID, '[Save.As("' || :WORKSHEETPATH || '",1)]',10000);     ConvID := DDE.INITIATE('EXCEL', :WORKSHEETPATH); --Insert row for headings     INSERTROW('Col1', 1);     INSERTROW('Col2', 2);     INSERTROW('Col3', 3);     FOR INV IN c1 LOOP       RCount := RCount + 1;       INSERTROW(NVL(TO_CHAR(INV.col1,'mm/dd/yyyy'),' '), 1);  --inserting a date.  if null, return a spaceINSERTROW(NVL(TO_CHAR(INV.col2),' '), 2); --if a numeric column,convert to text and if null, return a space       INSERTROW(NVL(INV.col3,' '), 3);                        --if text column, just check for null     END LOOP;   END;   EXCEPTION     WHEN DDE.DDE_APP_FAILURE THEN       MESSAGE('Could not start EXCEL (' || :EXCELFILEPATH || ').');     WHEN DDE.DDE_INIT_FAILED THEN       MESSAGE('Could not initialize conversation with ' || :WORKSHEETPATH || '.');     WHEN DDE.DMLERR_NO_CONV_ESTABLISHED THEN       MESSAGE('Could not initialize conversation with ' || :WORKSHEETPATH || '.');     WHEN DDE.DMLERR_SYS_ERROR THEN       MESSAGE('An internal error has occurred.');     WHEN OTHERS THEN       MESSAGE(TO_CHAR(MESSAGE_CODE) || ' ' || MESSAGE_TEXT || TO_CHAR(ERROR_CODE) || ' ' || ERROR_TEXT); END;ere is code that I call from a button on a form. It uses the DDE built-in package. It also uses the D2KWUTIL library for the WIN_API_UTILITY.DELETE_FILE package.
 
 PROCEDURE LOADINVOICEDATA IS
 BEGIN
   DECLARE
     AppID  PLS_INTEGER;
     ConvID  PLS_INTEGER;
     RCount Number := 0;
     CURSOR c1 IS SELECT col1, col2, col3 from table;
     PROCEDURE INSERTROW (invalue VARCHAR2, incol NUMBER) IS
        BEGIN
          DDE.POKE(ConvID, 'R'||TO_CHAR(RCount)||'C'||TO_CHAR(incol), invalue, DDE.CF_TEXT, 1000);
        END INSERTROW;
   BEGIN
 --:WORKSHEETPATH is a field on the form that contains the path and name of the Excel worksheet
 --:EXCELFILEPATH is a field on the form that contains the name of the Excel.exe ex. c:\program files\microsoft office\office\excel.exe
     WIN_API_UTILITY.DELETE_FILE(:WORKSHEETPATH,False);
     RCount := 1;
     AppID := DDE.APP_BEGIN(:EXCELFILEPATH, DDE.APP_MODE_MAXIMIZED);
     ConvID := DDE.INITIATE('EXCEL', 'system');
     DDE.EXECUTE(ConvID, '[Save.As("' || :WORKSHEETPATH || '",1)]',10000);
     ConvID := DDE.INITIATE('EXCEL', :WORKSHEETPATH);
 --Insert row for headings
     INSERTROW('Col1', 1);
     INSERTROW('Col2', 2);
     INSERTROW('Col3', 3);
     FOR INV IN c1 LOOP
       RCount := RCount + 1;
       INSERTROW(NVL(TO_CHAR(INV.col1,'mm/dd/yyyy'),' '), 1);  --inserting a date.  if null, return a space
 INSERTROW(NVL(TO_CHAR(INV.col2),' '), 2); --if a numeric column, convert to text and if null, return a space
       INSERTROW(NVL(INV.col3,' '), 3);                        --if text column, just check for null
     END LOOP;
   END;
   EXCEPTION
     WHEN DDE.DDE_APP_FAILURE THEN
       MESSAGE('Could not start EXCEL (' || :EXCELFILEPATH || ').');
     WHEN DDE.DDE_INIT_FAILED THEN
       MESSAGE('Could not initialize conversation with ' || :WORKSHEETPATH || '.');
     WHEN DDE.DMLERR_NO_CONV_ESTABLISHED THEN
       MESSAGE('Could not initialize conversation with ' || :WORKSHEETPATH || '.');
     WHEN DDE.DMLERR_SYS_ERROR THEN
       MESSAGE('An internal error has occurred.');
     WHEN OTHERS THEN
       MESSAGE(TO_CHAR(MESSAGE_CODE) || ' ' || MESSAGE_TEXT || TO_CHAR(ERROR_CODE) || ' ' || ERROR_TEXT);
 END;
 
Attached file :<input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">
web-util.pdf

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial