Home » Developer & Programmer » Forms » How to update oracle data into excel sheet (merged)
How to update oracle data into excel sheet (merged) [message #355753] Mon, 27 October 2008 06:29 Go to next message
dillango
Messages: 145
Registered: January 2008
Senior Member
Hi All,

I have searched extensively in this forum to find the solution how to update Oracle data in to excel sheet (specified row and column). I have yet to find the right solution.

I have been given one excel file (Pre defined and should not modify anything in that except providing the value in a specified column) having multiple sheet.

My requirement is, I have data in Oracle database (version 9i) having two columns. One is value and another is row & Column details of excel where it has to be updated.

Can anyone please have your valid suggestion?

I am good in D2K also.

Regards,
ILANGO


Re: To update Oracle Data into excel sheet [message #355756 is a reply to message #355753] Mon, 27 October 2008 06:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Don't multipost your question.

Regards
Michel
Re: To update Oracle Data into excel sheet [message #355761 is a reply to message #355756] Mon, 27 October 2008 07:57 Go to previous messageGo to next message
dillango
Messages: 145
Registered: January 2008
Senior Member
Michel,

Really I confused where I have to post ("Forms" or "SQL")
I am sorry.

Waiting for your valid feedback

ILANGO
Re: To update Oracle Data into excel sheet [message #355797 is a reply to message #355756] Mon, 27 October 2008 10:38 Go to previous messageGo to next message
dillango
Messages: 145
Registered: January 2008
Senior Member
Any feedback please...?

ILANGO
Re: To update Oracle Data into excel sheet [message #355865 is a reply to message #355761] Tue, 28 October 2008 03:11 Go to previous messageGo to next message
dillango
Messages: 145
Registered: January 2008
Senior Member
Hi,

The following code I have written in When-Button-pressed trigger to send the value from Forms into excel sheet (specific row and sheet).

The problem now is, Instead of opening 'sheet2', It is opening sheet1 and writing the value.

DECLARE
-- Declare handles to OLE objects
  application   ole2.OBJ_TYPE;
  workbooks     ole2.OBJ_TYPE;
  workbook      ole2.OBJ_TYPE;
  worksheets    ole2.OBJ_TYPE;
  worksheet     ole2.OBJ_TYPE;
  cell          ole2.OBJ_TYPE;
  args          ole2.OBJ_TYPE;
  Check_file    text_io.file_type;
  no_file       exception;
  PRAGMA exception_INIT (no_file, -302000);
  cell_value    varchar2 (2000);
  BEGIN
  	
-- Check the file can be found, if not exception no_file will be raised
Check_file := TEXT_IO.FOPEN('c:\test.xls','R');
TEXT_IO.FCLOSE(Check_file);


  application  := ole2.CREATE_OBJ ('Excel.Application');
  workbooks    := ole2.GET_OBJ_PROPERTY (application, 'Workbooks');
  args         := ole2.CREATE_ARGLIST;
  ole2.add_arg (args, 'c:\test.xls');
  workbook     := ole2.GET_OBJ_PROPERTY (workbooks, 'Open', args);
  ole2.destroy_arglist (args);
  worksheets   := ole2.GET_OBJ_PROPERTY (workbook, 'Worksheets');
  worksheet    := ole2.GET_OBJ_PROPERTY (application, 'activesheet');
  ole2.SET_PROPERTY (worksheet, 'Value', 'Sheet2');

-- ***********************************

-- PASS VALUE INTO Excel CELL (ROW 11, COL 11)
args := ole2.CREATE_ARGLIST;
ole2.ADD_ARG(args, 11);
ole2.ADD_ARG(args, 11);
cell := ole2.GET_OBJ_PROPERTY(worksheet,'Cells', args);
ole2.DESTROY_ARGLIST(args);
ole2.SET_PROPERTY(cell, 'Value', 'Hello'); 
-- save document as test.xls
  ole2.INVOKE (worksheet, 'Save');
  ole2.INVOKE (workbook, 'Save');
  ole2.INVOKE (workbook, 'CLOSE');
-- Release the client_ole2 object handles
  ole2.release_obj (cell);
  ole2.release_obj (worksheet);
  ole2.release_obj (worksheets);
  ole2.release_obj (workbook);
  ole2.release_obj (workbooks);
  ole2.invoke (application, 'Quit');
  ole2.release_obj (application);
exception
  WHEN no_file THEN
    MESSAGE ('file not found.');
  WHEN OTHERS THEN
    MESSAGE (sqlerrm);
    PAUSE;
    FOR i IN 1 .. tool_err.nerrors
    LOOP
      MESSAGE (tool_err.message);
      PAUSE;
      tool_err.pop;
    END LOOP;
END;




Can anybody suggest how to write in specific worksheet?

Regards
ILANGO
Re: To update Oracle Data into excel sheet [message #355997 is a reply to message #355865] Tue, 28 October 2008 23:40 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

you already got it. 'Sheet2' is the sheet name specified.
Re: To update Oracle Data into excel sheet [message #356009 is a reply to message #355865] Wed, 29 October 2008 00:50 Go to previous messageGo to next message
dillango
Messages: 145
Registered: January 2008
Senior Member
Mr.Wency,

Even if I specify "Sheet2", It is always writing in first sheet only.

ILANGO
Re: To update Oracle Data into excel sheet [message #356022 is a reply to message #356009] Wed, 29 October 2008 01:32 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

Try this one.

Regards
Re: To update Oracle Data into excel sheet [message #356235 is a reply to message #355865] Thu, 30 October 2008 01:10 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
Have you solved your problem?

David
Re: To update Oracle Data into excel sheet [message #356251 is a reply to message #356235] Thu, 30 October 2008 01:39 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

Sorry I lost those links suppose to be in my post.

try links from here;
http://www.orafaq.com/forum/t/84917/2/
Re: How to update oracle data into excel sheet (merged) [message #356314 is a reply to message #355753] Thu, 30 October 2008 05:11 Go to previous message
dillango
Messages: 145
Registered: January 2008
Senior Member
Yes Mr.David,

It is perfectly working now. What I did was, Instead of writing the worksheet name (say sheet1,sheet2 etc..), I replaced with the number (like Sheet1 with 1, sheet2 with 2 etc.).

My special thanks to Mr.Wency.

ILANGO

Previous Topic: LOGON Block in FOrm
Next Topic: Problem with scroll bar
Goto Forum:
  


Current Time: Tue Feb 11 20:25:50 CST 2025