How to generate Excel Report Oracle Forms and Reports 11gR2

About

We upgraded from 6i to 11gR2 so it took me some time to know how to generate Excel in Oracle Forms and Reports 11gR2. So I wrote it down to help those who couldn't figure it out.In my case I am supposed to generate the Excel file after pressing a certain button,so here is how I did it.

How To

1. Open the form required to generate the Excel report using Oracle Form Builder
2. In the Object Navigator pane go to reports

   

   going to Reports in Object Navigator

3. Select "Create" button

   

   selecting create button

4. A window will appear asking you to choose whether to create new report or use existing one. If you have a source "RDF" file of the report choose "Browse" and select the file

   

   window asking to choose how will you like to add the report

5. After browsing for the report, click "Ok"
6. A new report object will appear in the Object Navigator

   

   here the report appeared automatically named to "REPORT274"

   
   
7. Press F4 to open the Property Palette. In the Report Destination Format type in "ENHANCEDSPREADSHEET"
   
8. Go to the button and add a trigger "WHEN-BUTTON-PRESSED"
   
9. Open the PL/SQL editor to write the trigger function
   DECLARE
   REPORT_ID Report_Object; 
   PARAM_LIST PARAMLIST;
   REPORT_STATUS VARCHAR2(100);
   JOB_ID VARCHAR2(100); 
   REPORT_SERVER_JOB VARCHAR2(254);
   BEGIN
   REPORT_ID := find_report_object('REPORT274');
   
   SET_REPORT_OBJECT_PROPERTY(REPORT_ID,REPORT_SERVER,'report_server_name'); 
   
   -- IN CASE YOU WANT TO CREATE PARAMTER LIST AND PASS IT 
   -- TO THE REPORT
   PARAM_LIST := GET_PARAMETER_LIST('DATA');
   IF NOT ID_NULL(PARAM_LIST) THEN
    DESTROY_PARAMETER_LIST('DATA');
   END IF;
   PARAM_LIST :=CREATE_PARAMETER_LIST('DATA');
    
   ADD_PARAMETER(PARAM_LIST,'PARAMETER',TEXT_PARAMETER,'Y');
   
   REPORT_SERVER_JOB:=RUN_REPORT_OBJECT(REPORT_ID,PARAM_LIST);
   REPORT_STATUS := REPORT_OBJECT_STATUS(REPORT_SERVER_JOB); 
   
   IF REPORT_STATUS = 'FINISHED' THEN 
   JOB_ID := substr(REPORT_SERVER_JOB,instr(REPORT_SERVER_JOB,'_',-1)+1);
   WEB.SHOW_DOCUMENT ('/reports/rwservlet/getjobid'|| JOB_ID ||'?server='||'report_server_name','_blank');
   ELSE 
   MESSAGE ('Report failed, Error message '||REPORT_STATUS); 
   END IF; 
   END;
10. Now Open the Forms from your web browser, and press the excel button which we added the trigger to.
    

    

    the forms open in Internet Explorer, -this is not an original image-

11. The following download window will appear
    
    
12. Save the file and open it with Excel.

Notes

• When opening the file in Excel, Excel may prompt a message asking if you are sure that this is an Excel file or not. you should click yes. However the reason for this error is that the generated file is not ".xls" format rather ".htm" which Excel can open it easily and view it as spread sheet.