Forum Stats

  • 3,852,900 Users
  • 2,264,146 Discussions
  • 7,905,157 Comments

Discussions

Read Excel file from Oracle Foms

ricardogc
ricardogc Member Posts: 6
edited Oct 18, 2008 11:54PM in Forms
Hi Guys

Im trying to read a excel file from oracle forms 10g using webuti and client_ole2 in order to store the file information in a table.

This is my code.

--------------------------------------------------------------------------------------------------------------

DECLARE
lv_application client_ole2.obj_type;
lv_workbooks client_ole2.obj_type;
lv_workbook client_ole2.obj_type;
lv_worksheet client_ole2.obj_type;
lv_args client_ole2.list_type;
lv_cell client_ole2.obj_type;
lv_cell_list1 client_ole2.list_type;
lv_cell_value VARCHAR2 (100);
lv_row_cntr NUMBER := 1;
lv_col_cntr NUMBER := 1;
lv_bttn NUMBER := 0;
BEGIN
lv_application := client_ole2.create_obj ('EXCEL.APPLICATION');
lv_workbooks := client_ole2.get_obj_property (lv_application, 'WORKBOOKS');
lv_args := client_ole2.create_arglist;
client_ole2.add_arg (lv_args, :bl_control.filename);
lv_workbook := client_ole2.get_obj_property (lv_workbooks, 'OPEN', lv_args);
client_ole2.destroy_arglist (lv_args);
lv_args := client_ole2.create_arglist;
client_ole2.add_arg (lv_args, 'SHEET1');
lv_worksheet :=
client_ole2.get_obj_property (lv_workbook, 'WORKSHEETS', lv_args);
client_ole2.destroy_arglist (lv_args);

FOR i IN 1 .. 10
LOOP
lv_cell_list1 := client_ole2.create_arglist;
client_ole2.add_arg (lv_cell_list1, lv_row_cntr);
client_ole2.add_arg (lv_cell_list1, lv_col_cntr);
lv_cell :=
client_ole2.get_obj_property (lv_worksheet, 'CELLS', lv_cell_list1);
client_ole2.destroy_arglist (lv_cell_list1);
lv_cell_value := client_ole2.get_char_property (lv_cell, 'value');
client_ole2.RELEASE_OBJ (lv_cell);
END LOOP;

lv_args := client_ole2.create_arglist;
client_ole2.add_arg (lv_args, 0);
client_ole2.invoke (lv_workbook, 'CLOSE', lv_args);
client_ole2.destroy_arglist (lv_args);
client_ole2.invoke (lv_application, 'QUIT');
client_ole2.RELEASE_OBJ (lv_worksheet);
client_ole2.RELEASE_OBJ (lv_workbook);
client_ole2.RELEASE_OBJ (lv_workbooks);
client_ole2.RELEASE_OBJ (lv_application);
proc_show_alert ('ALERT_DIALOG',
'Import',
'The import is completed successfully.',
lv_bttn
);
EXCEPTION
WHEN OTHERS
THEN
client_ole2.invoke (lv_application, 'QUIT');
client_ole2.RELEASE_OBJ (lv_worksheet);
client_ole2.RELEASE_OBJ (lv_workbook);
client_ole2.RELEASE_OBJ (lv_workbooks);
client_ole2.RELEASE_OBJ (lv_application);
END;

--------------------------------------------------------------------------------------------------------------

This code is inside a WHEN-BUTTON-PRESSED trigger.

My problem is that my program gets stuck when this line is reached:

lv_workbook := client_ole2.get_obj_property (lv_workbooks, 'OPEN', lv_args);

There's no exception thrown, no errors. It simply does nothing.

I'm running the form from a Windows XP machine, my Office is 2003; both in Spanish.

Hope somebody can help me.

Regards.
Tagged:

Answers

  • 653881
    653881 Member Posts: 32
    Hi,

    I have done a project just like yours. What i done is convert my xls input file into a flat file with tab separator. You have to set the size for each column . When the flat file is done, i read line by line from this file with substr function to retrieve all data then store them.

    -- store input file into a varchar2
    cFileLineOut := SUBSTR(cFileLine, 1, 21) || ' ' || -- store column
    SUBSTR(cFileLine, 22, 10) || ' ' || -- item column
    SUBSTR(cFileLine, 32, 8) || ' ' || -- sku column
    SUBSTR(cFileLine, 40, 21) || ' ' || -- description column
    SUBSTR(cFileLine, 71, 17) || ' ' || -- upc column
    SUBSTR(cFileLine, 88, 9) || ' ' || -- status column
    SUBSTR(cFileLine, 97, 10) || ' ' || -- fen_qty column
    SUBSTR(cFileLine, 107, 22) || ' ' || -- eoh unit column
    SUBSTR(cFileLine, 129, 23) || ' '; -- eoh cost column

    Hope that could help you.

    Regards.

    Minh Khai.
  • ricardogc
    ricardogc Member Posts: 6
    Hi Minh Khai

    Did u convert your xls file into a flat one inside Forms ??
    How did u do this ???

    Unfortunately I have to do all the process inside Oracle Forms

    Best Regards
    Ricardo
  • 653881
    653881 Member Posts: 32
    I convert my input file manually. I'm not sure that we can do it by form. If yes, somebody can show me?

    Minh Khai.
  • KevinDClarke
    KevinDClarke Member Posts: 2,656
    Yes you can convert it from the form - again using ole2.
    Sorry I don't have access to the code right now but I have done it before.

    Use Ole2 to open the xls. If it has multiple workbooks you can do each one individually.
    Use Ole2 to do a save as csv.

    After you have created a csv you can use either utl_file or host sqlldr to get it into the database, or text_io to get it into the form.

    I found that with sizeable spreadsheets these options perform faster than your original method of looping through the spreadsheet.
    KevinDClarke
  • ricardogc
    ricardogc Member Posts: 6
    Hi Kevin

    Thanks for ur help, but the problem in converting the xls to csv with ole2, is that I can´t open my xls file.

    My program got stuck at this line:

    lv_workbook := client_ole2.get_obj_property (lv_workbooks, 'OPEN', lv_args);

    I got no error or exception raised.

    Hope u can take a look at my code and tell me if something is wrong.

    Best Regards
    Ricardo
  • ricardogc
    ricardogc Member Posts: 6
    Hi guys.

    I tested my form in another machine with the same OS and version of Ms Office and my code is working fine.

    any clue of which configuration setting could be wrong???

    I think my webutil is workin fine because I can use client_text_io with no problem.

    Thanks.
  • Rakin
    Rakin Member Posts: 182
    Hi,

    Could u identify the problem, the same happend to me also, If solved plz share me the idea.

    I am able to write the text file in the client side, but getting stuck when writing or reading a client side excel file using client_ole2. I am using Forms 10q, Client OS is Windows Xp,
    I am developing with OC4J service.

    Thanks
    Rizly
  • Andreas Weiden
    Andreas Weiden Member Posts: 10,871 Gold Crown
    I am able to write the text file in the client side, but getting stuck when writing or reading a client side excel file using client_ole2
    post your code and the error-message you receive..
  • Rakin
    Rakin Member Posts: 182
    Hi

    I am running the common web utilities test form WU_TEST_106.FMB, I that form, there is one optio to write the message to the
    client side text file, that is working fine, But when I try the other feture, to write to the client side word file, I am not getting
    any error or out put also not generated, So I defugg the form, then it was getting stuck at the line
    result := GET_CUSTOM_PROPERTY(bean,1,propertyName);

    That is in GetProperty function of WEBUTIL_CORE package

    FUNCTION GetProperty(packageID in PLS_INTEGER,
    propertyName in VARCHAR2,
    bypassErrorCheck in BOOLEAN DEFAULT true) return VARCHAR2 is
    bean ITEM;
    result VARCHAR2(32000);
    begin
    bean := find_item(getHandlerBean(packageID));
    if id_null(bean) then
    raise WEBUTIL_CORE.BEAN_NOT_REGISTERED;
    end if;
    result := GET_CUSTOM_PROPERTY(bean,1,propertyName);
    if not FORM_SUCCESS then
    raise WEBUTIL_CORE.PROPERTY_ERROR;
    end if;
    if not bypassErrorCheck then
    CheckLastError(bean);
    end if;
    return result;
    exception
    when NO_DATA_FOUND then
    RAISE WEBUTIL_CORE.BEAN_NOT_REGISTERED;
    when OTHERS then
    RAISE;
    end GetProperty;

    Actually the same technique I was using to write to the data in the data block to the client side excel file.


    Forms Version : Forms 10g
    I am developing the application with OC4J, I configured web util, thats why all other features in the test form are working,
    It will be helpful If I could know the configurations in the webutil, that is specific for CLIENT_OLE

    Thanks in advance

    Rizly
This discussion has been closed.