Home » Developer & Programmer » Forms » how do i display all excel worksheets name in form
how do i display all excel worksheets name in form [message #269440] Sat, 22 September 2007 05:41 Go to next message
anisam10674
Messages: 18
Registered: March 2007
Junior Member
I have an excel file containing say 3 worksheets in it. What I would like to do now is display names of all 3 worksheets in a datablock multiple text items and allow the user to select any one of those sheets and then import data from the selected excel worksheet onto form's another datablock. any suggestions on how this can be achieved? Thanks in advance.
Re: how do i display all excel worksheets name in form [message #269643 is a reply to message #269440] Sun, 23 September 2007 23:39 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Can you open the spreadsheet? Have you determined the 'ole2' command to get the worksheet names?

David
Re: how do i display all excel worksheets name in form [message #269644 is a reply to message #269643] Mon, 24 September 2007 00:01 Go to previous messageGo to next message
anisam10674
Messages: 18
Registered: March 2007
Junior Member
It would be of great help if anybody can suggest me the code.
Re: how do i display all excel worksheets name in form [message #269662 is a reply to message #269644] Mon, 24 September 2007 00:49 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Have you searched this forum for 'worksheet' or 'ole2'?

The command is part of Microsoft Excel not Oracle.

David
Re: how do i display all excel worksheets name in form [message #269694 is a reply to message #269662] Mon, 24 September 2007 02:24 Go to previous messageGo to next message
anisam10674
Messages: 18
Registered: March 2007
Junior Member
I had used ole2 to write data onto a new worksheet. What I would like to do is open a user specified worksheet from all the worksheets present in an existing excel file. For this, first thing is to get the names of all the worksheets in that file onto a List-Item in a form. The user will select a worksheet from this list-item and then data can be inserted onto that worksheet through ole2. How can this be done?
Re: how do i display all excel worksheets name in form [message #270458 is a reply to message #269694] Thu, 27 September 2007 00:27 Go to previous message
anisam10674
Messages: 18
Registered: March 2007
Junior Member
Thank you friends,

I could finally display all worksheets name of an excel file in a list item of a form, courtesy-some useful posts in this forum.

-----
-- Procedure to get the worksheets name of an excel file and display the names in a list item:
-- v_filename is the excel file name specified by user
-- v_blkitemname is the list item name which will display the names of the excel worksheets present in v_filename

procedure get_xl_ws_names(v_filename in varchar2, v_blkitemname in varchar2) is

--declare handles to OLE object
application ole2.obj_type;
workbooks ole2.obj_type;
workbook ole2.obj_type;
worksheets ole2.obj_type;
worksheet ole2.obj_type;

--declare handles to OLE argument lists
args ole2.list_type;
check_file text_io.file_type;
file_not_found exception;
pragma exception_init(file_not_found,-302000);

--user-defined varibles
v_ws_cnt integer;
v_ws_nam varchar2(100);
v_le_cnt integer;

begin

--delete existing elements from list item
v_le_cnt := get_list_element_count(v_blkitemname);
if v_le_cnt>=1 then
for i in 1..v_le_cnt loop
delete_list_element(v_blkitemname,1);
end loop;
end if;

--check whether file exists, else raise file_not_found exception
check_file := text_io.fopen(v_filename,'R');
text_io.fclose(check_file);

--open excel application
application := ole2.create_obj('excel.application');
ole2.set_property(application,'Visible','False');
workbooks := ole2.get_obj_property(application,'Workbooks');
args := ole2.create_arglist;
ole2.add_arg(args,v_filename);
workbook := ole2.get_obj_property(workbooks,'Open',args);
ole2.destroy_arglist(args);
worksheets := ole2.get_obj_property(workbook,'Worksheets');

--get worksheet count into variable v_ws_cnt
v_ws_cnt := ole2.get_num_property(worksheets,'Count');

--get worksheets name and add it into list element of list item
for i in 1..v_ws_cnt loop
args := ole2.create_arglist;
ole2.add_arg(args,i);
worksheet:=ole2.get_obj_property(worksheets,'Item',args);
v_ws_nam := ole2.get_char_property(worksheet,'Name');
ole2.destroy_arglist(args);
ole2.release_obj(worksheet);
add_list_element(v_blkitemname,1,v_ws_nam, v_ws_nam);
end loop;

--close excel worksheet/workbook/application
ole2.invoke(worksheet,'Close');
ole2.invoke(workbook,'Close');
ole2.invoke(application,'Quit');

--release OLE handles
ole2.release_obj(worksheet);
ole2.release_obj(worksheets);
ole2.release_obj(workbook);
ole2.release_obj(workbooks);
ole2.release_obj(application);

exception
when file_not_found then
message('File not found');

end;
Previous Topic: how to change text, on top of the forms
Next Topic: Getting Started
Goto Forum:
  


Current Time: Thu Dec 08 08:37:05 CST 2016

Total time taken to generate the page: 0.13392 seconds