How to update oracle data into excel sheet (merged) [message #355753] |
Mon, 27 October 2008 06:29  |
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 #355865 is a reply to message #355761] |
Tue, 28 October 2008 03:11   |
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
|
|
|
|
|
|
|
|
|