Home » Developer & Programmer » Forms » Write Data into Excel from Oracle Forms 11g (Oracle Forms 11g, Linux)
Write Data into Excel from Oracle Forms 11g [message #663554] Thu, 08 June 2017 02:41 Go to next message
Mary@kaefer
Messages: 6
Registered: April 2017
Junior Member
Hi,

I have made an excel template and I need to calculate from figures and write into the predefined excel file. The excel file is having 10 worksheet. First sheet called "summary" and rest of sheet names are Sheet1, Sheet2,...Sheet9. I need to write data on second sheet(Sheet1) onwards. I have used following code, but writing still starts from Summary sheet onwards. Please help me.

PROCEDURE r4_export_to_excel
IS
-- Declare handles to OLE objects
   application     client_ole2.obj_type;
   workbooks       client_ole2.obj_type;
   workbook        client_ole2.obj_type;
   worksheets      client_ole2.obj_type;
   worksheet       client_ole2.obj_type;
   v_path          VARCHAR2 (1000);
   -- Declare handles to OLE argument lists
   args            client_ole2.list_type;
   i               NUMBER                := 1;

   CURSOR cp
   IS
      SELECT irdf_filter_value
        FROM inv_rpt_data_filter
       WHERE irdf_org_code = :parameter.global_orgcode
         AND irdf_form_id = :parameter.irdf_form_id
         AND irdf_filter_type = 'PROJECT'
         AND irdf_login_user = :parameter.v_sess_id;
BEGIN
   v_path := 'D:\Project_MIS_R4.xlsx';
   :parameter.v_sess_id := get_session_data ('SESSIONID');
   --Creating new excel
   application := client_ole2.create_obj ('Excel.Application');
   client_ole2.set_property (application, 'Visible', 1);
   workbooks := client_ole2.get_obj_property (application, 'Workbooks');
   --Setting path
   args := client_ole2.create_arglist;
   client_ole2.add_arg (args, v_path);
   workbook := client_ole2.get_obj_property (workbooks, 'Open', args);
   client_ole2.destroy_arglist (args);

   FOR j IN cp                     --For loop added for each multiple project
   LOOP
      i := i + 1;
      :parameter.v_proj := j.irdf_filter_value;
      worksheets := client_ole2.get_obj_property (workbook, 'Worksheets');
      worksheet := client_ole2.get_obj_property (application, 'activesheet');
      --Code newly added for new sheet
      args := client_ole2.create_arglist;
      client_ole2.add_arg (args, i);           -- i represents the sheet no..
      worksheet := client_ole2.get_obj_property (worksheets, 'Item', args);
      client_ole2.set_property (worksheet, 'Name', j.irdf_filter_value);
      --Naming sheets
      client_ole2.destroy_arglist (args);
      proj_mis_costover_rpt_proc_r4 (:GLOBAL.orgcode,
                                     :parameter.v_proj,
                                     :rep_mon,
                                     :fin_year,
                                     :parameter.v_sess_id,
                                     'MCO'
                                    );
      r4_mis_bud_act_forecast_pl (worksheet);
                   /* Budget, Actual & Forecast Details of each budget head */
      r4_mis_aging_details (worksheet);        /* Aging & Retention Details */
      -- Enable user to view the Excel application to see results.
      client_ole2.set_property (application, 'Visible', 'True');
-- Release all OLE object handles
      client_ole2.RELEASE_OBJ (worksheet);
      client_ole2.RELEASE_OBJ (worksheets);
   END LOOP;

   client_ole2.RELEASE_OBJ (workbook);
   client_ole2.RELEASE_OBJ (workbooks);
   client_ole2.RELEASE_OBJ (application);
END;

Thanks in Advance

Mary Yujin

[Updated on: Thu, 08 June 2017 03:16]

Report message to a moderator

Re: Write Data into Excel from Oracle Forms 11g [message #663594 is a reply to message #663554] Sat, 10 June 2017 00:41 Go to previous messageGo to next message
Mary@kaefer
Messages: 6
Registered: April 2017
Junior Member
Please Help !!
Re: Write Data into Excel from Oracle Forms 11g [message #663611 is a reply to message #663594] Sat, 10 June 2017 23:53 Go to previous message
Michel Cadot
Messages: 68598
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
reported message
Hi, I have posted a query and not yet received any response. Please advise me my way of posting is correct. Thanks Mary Yujin

You may not have answer because:
- no one here knows the answer
- the ones that know the answer are not currently there
- the ones that know the answer are not interested in your question

Note that:
- the problem in title in a FAQ, so a search should give you an answer (this may be a reason of having none here)
- this is week-end and many have other things to do than answer in an Oracle forum

I know nothing about Forms and client_ole2 package so I can't help but I'm surprised you didn't find any example about it on the web.

Previous Topic: display values not showing in list items
Next Topic: Adding rows dynamically to the tabular view
Goto Forum:
  


Current Time: Thu Feb 29 09:18:34 CST 2024