Read Excel file from Oracle Foms

ricardogc

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.

653881

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

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

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

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.

ricardogc

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

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

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

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

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