This discussion is archived
1 Reply Latest reply: Jan 24, 2013 12:38 AM by AlexDiniasi RSS

Open and Save Excel Files

986579 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points