1 Reply Latest reply: Jan 24, 2013 2:38 AM by AlexDiniasi RSS

    Open and Save Excel Files

    986579
      Hi All,

      I need code of How to open and save excel file in local system in Oracle forms.

      With Regards,
      Chandra Shekhar
        • 1. Re: Open and Save Excel Files
          AlexDiniasi
          Hello Chandra,

          Webutil can be used to achieve this functionality.

          STEPS TO FOLLOW
          ================
          1. Install and configure Webutil following instructions in the webutil manual
          and the readme file.
          2. Create a form with a block Eg. DEPT
          3. Create a button, and in that button put the following code -



          DECLARE

          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;
          args Client_OLE2.List_Type;
          cell ole2.Obj_Type;
          j INTEGER;
          k INTEGER;

          BEGIN

          application := Client_OLE2.create_obj('Excel.Application');

          workbooks := Client_OLE2.Get_Obj_Property(application, 'Workbooks');
          workbook := Client_OLE2.Invoke_Obj(workbooks, 'Add');

          worksheets := Client_OLE2.Get_Obj_Property(workbook, 'Worksheets');
          worksheet := Client_OLE2.Invoke_Obj(worksheets, 'Add');

          go_block('dept');
          first_record;

          j:=1;
          k:=1;

          while :system.last_record = 'FALSE'
          loop
          for k in 1..3 /* DEPT has 3 columns */
          loop

          If not name_in(:system.cursor_item) is NULL Then
          args:=Client_OLE2.create_arglist;
          Client_OLE2.add_arg(args, j);
          Client_OLE2.add_arg(args, k);
          cell:=Client_OLE2.get_obj_property(worksheet, 'Cells', args);
          Client_OLE2.destroy_arglist(args);
          Client_OLE2.set_property(cell, 'Value', name_in(:system.cursor_item));
          Client_OLE2.release_obj(cell);

          End If;
          next_item;
          end loop;

          j:=j+1;
          next_record;
          end loop;


          /* For the last record */
          for k in 1..3
          loop
          If not name_in(:system.cursor_item) is NULL Then
          args:=Client_OLE2.create_arglist;
          Client_OLE2.add_arg(args, j);
          Client_OLE2.add_arg(args, k);
          cell:=Client_OLE2.get_obj_property(worksheet, 'Cells', args);
          Client_OLE2.destroy_arglist(args);
          Client_OLE2.set_property(cell, 'Value', name_in(:system.cursor_item));
          Client_OLE2.release_obj(cell);
          End If;
          next_item;
          end loop;

          Client_OLE2.Release_Obj(worksheet);
          Client_OLE2.Release_Obj(worksheets);

          /* Save the Excel file created */
          args := Client_OLE2.Create_Arglist;
          Client_OLE2.Add_Arg(args,'d:\test.xls');
          Client_OLE2.Invoke(workbook, 'SaveAs', args);
          Client_OLE2.Destroy_Arglist(args);

          /* release workbook */
          Client_OLE2.Release_Obj(workbook);
          Client_OLE2.Release_Obj(workbooks);

          /* Release application */
          Client_OLE2.Invoke(application, 'Quit');
          Client_OLE2.Release_Obj(application);

          END;

          4. Save the form and compile it.
          5. Run the form.
          6. Execute the query in the block.
          7. Click on the button.
          8. An excel file will be created in the d:\ directory by the name test.xls.


          Kind regards,
          Alex

          If someone's answer is helpful or correct please mark it accordingly.