Several years ago I was given requirement for excel/word reporting through PL/SQL Oracle Forms 10g application. One catch was that these reports need to fill existing reports with data and I can not use reporting tool of any kind. Because I came from Java and OO word I started developing Java bean that will be inserted into Forms. I had idea to create reports through this bean (using Apache POI) and then I will fuse this bean into Forms applet. I was almost done, but then I find out that Oracle deliver library that can (among other things) handle Forms <-> Word or Excel communication called WEBUTIL. So I start to leverage this library and came up with following PL/SQL package for Word and Excel communication using this package and OLE2. I hope you will find them useful I will not comment them because they are quite complex, but if you have questions please comment (or ask me privately) and I will try to response in shortest time possible.
Excel package
Package specification:
PACKAGE excel
IS
/*
Global excel.Application Object --> this represent excel Object.
*/
appl_id client_ole2.obj_type;
/*
Open file that act as template. Parameter are:
_application_ -- global word parameter that we initialize at
begining.
_file_ -- file name we wish to open --> it can be from database, or filesystem...
*/
FUNCTION file_open (application client_ole2.obj_type, FILE VARCHAR2)
RETURN client_ole2.obj_type;
/*
Close current file.
*/
PROCEDURE file_close (document client_ole2.obj_type);
/*
Saves current file (It is useful if we need to save current
file using another name)
*/
PROCEDURE file_save_as (document client_ole2.obj_type, FILE VARCHAR2);
/*
Isert number (not formated)
x - horizontal axei.
y - vertical axis.
v - value.
*/
PROCEDURE insert_number (
worksheet client_ole2.obj_type,
x NUMBER,
y NUMBER,
v NUMBER
);
/*
Insert number and format it as decimal value.
x - horizontal axei.
y - vertical axis.
v - value.
Napomena: !!!THIS DOES NOT WORK IN EXCEL 2007!!!
*/
PROCEDURE insert_number_decimal (
worksheet client_ole2.obj_type,
x NUMBER,
y NUMBER,
v NUMBER
);
/*
Insert characters (not formated)
x - horizontal axei.
y - vertical axis.
v - value.
*/
PROCEDURE insert_char (
worksheet client_ole2.obj_type,
x NUMBER,
y NUMBER,
v VARCHAR2
);
/*
Insert character - formated
color - numbers (15 for example is gray)
style - BOLD' or 'ITALIC'
x - horizontal axei.
y - vertical axis.
v - value.
*/
PROCEDURE insert_char_formated (
worksheet client_ole2.obj_type,
x NUMBER,
y NUMBER,
v VARCHAR2,
color NUMBER,
style VARCHAR2
);
/*
Set autofit on whole sheet.
*/
PROCEDURE set_auto_fit (worksheet client_ole2.obj_type);
/*
Set autofit for range r. For example. r can be: 'A2:E11'
*/
PROCEDURE set_auto_fit_range (worksheet client_ole2.obj_type, r VARCHAR2);
/*
Put decimal format (0.00) on range r.
*/
PROCEDURE set_decimal_format_range (
worksheet client_ole2.obj_type,
r VARCHAR2
);
/*
Create new workbook.
*/
FUNCTION new_workbook (application client_ole2.obj_type)
RETURN client_ole2.obj_type;
/*
Create new worksheet.
*/
FUNCTION new_worksheet (workbook client_ole2.obj_type)
RETURN client_ole2.obj_type;
/*
Saves file in client tempfolder (It is necessary to save file if edit template).
*/
FUNCTION download_file (
file_name IN VARCHAR2,
table_name IN VARCHAR2,
column_name IN VARCHAR2,
where_condition IN VARCHAR2
)
RETURN VARCHAR2;
/*
Run macro on client excel document.
*/
PROCEDURE run_macro_on_document (
document client_ole2.obj_type,
macro VARCHAR2
);
/*
Limit network load...not important.
*/
PROCEDURE insert_number_array (
worksheet client_ole2.obj_type,
x NUMBER,
y NUMBER,
v VARCHAR2
);
END;
Package body:PACKAGE BODY excel
IS
FUNCTION file_open (application client_ole2.obj_type, FILE VARCHAR2)
RETURN client_ole2.obj_type
IS
arg_list client_ole2.list_type;
document client_ole2.obj_type;
documents client_ole2.obj_type;
BEGIN
arg_list := client_ole2.create_arglist;
documents := client_ole2.invoke_obj (application, 'Workbooks');
client_ole2.add_arg (arg_list, FILE);
document := client_ole2.invoke_obj (documents, 'Open', arg_list);
client_ole2.destroy_arglist (arg_list);
client_ole2.RELEASE_OBJ (documents);
RETURN document;
END file_open;
PROCEDURE file_save_as (document client_ole2.obj_type, FILE VARCHAR2)
IS
arg_list client_ole2.list_type;
BEGIN
arg_list := client_ole2.create_arglist;
client_ole2.add_arg (arg_list, FILE);
client_ole2.invoke (document, 'SaveAs', arg_list);
client_ole2.destroy_arglist (arg_list);
END file_save_as;
FUNCTION new_workbook (application client_ole2.obj_type)
RETURN client_ole2.obj_type
IS
workbook client_ole2.obj_type;
workbooks client_ole2.obj_type;
BEGIN
workbooks := client_ole2.get_obj_property (application, 'Workbooks');
workbook := client_ole2.invoke_obj (workbooks, 'Add');
client_ole2.RELEASE_OBJ (workbooks);
RETURN workbook;
END new_workbook;
FUNCTION new_worksheet (workbook client_ole2.obj_type)
RETURN client_ole2.obj_type
IS
worksheets client_ole2.obj_type;
worksheet client_ole2.obj_type;
BEGIN
worksheets := client_ole2.get_obj_property (workbook, 'Worksheets');
worksheet := client_ole2.invoke_obj (worksheets, 'Add');
client_ole2.RELEASE_OBJ (worksheets);
RETURN worksheet;
END new_worksheet;
PROCEDURE file_close (document client_ole2.obj_type)
IS
BEGIN
client_ole2.invoke (document, 'Close');
END file_close;
/*
Macro: Cells(3, 4).Value = 3
Cells(3, 4).Select
Selection.NumberFormat = "0.00"
*/
PROCEDURE insert_number_decimal (
worksheet client_ole2.obj_type,
x NUMBER,
y NUMBER,
v NUMBER
)
IS
args client_ole2.list_type;
cell client_ole2.obj_type;
selection client_ole2.obj_type;
BEGIN
IF v IS NOT NULL
THEN
args := client_ole2.create_arglist;
client_ole2.add_arg (args, x);
client_ole2.add_arg (args, y);
cell := client_ole2.get_obj_property (worksheet, 'Cells', args);
client_ole2.destroy_arglist (args);
client_ole2.set_property (cell, 'Value', v);
client_ole2.invoke (cell, 'Select');
selection := client_ole2.invoke_obj (appl_id, 'Selection');
client_ole2.set_property (selection, 'Numberformat', '#.##0,00');
client_ole2.RELEASE_OBJ (selection);
client_ole2.RELEASE_OBJ (cell);
END IF;
END;
/* Macro:
Cells(x, y).Value = v
*/
PROCEDURE insert_number (
worksheet client_ole2.obj_type,
x NUMBER,
y NUMBER,
v NUMBER
)
IS
args client_ole2.list_type;
cell ole2.obj_type;
BEGIN
IF v IS NOT NULL
THEN
args := client_ole2.create_arglist;
client_ole2.add_arg (args, x);
client_ole2.add_arg (args, y);
cell := client_ole2.get_obj_property (worksheet, 'Cells', args);
client_ole2.destroy_arglist (args);
client_ole2.set_property (cell, 'Value', v);
client_ole2.RELEASE_OBJ (cell);
END IF;
END;
PROCEDURE insert_char (
worksheet client_ole2.obj_type,
x NUMBER,
y NUMBER,
v VARCHAR2
)
IS
args client_ole2.list_type;
cell client_ole2.obj_type;
BEGIN
IF v IS NOT NULL
THEN
args := client_ole2.create_arglist;
client_ole2.add_arg (args, x);
client_ole2.add_arg (args, y);
cell := client_ole2.get_obj_property (worksheet, 'Cells', args);
client_ole2.destroy_arglist (args);
client_ole2.set_property (cell, 'Value', v);
client_ole2.RELEASE_OBJ (cell);
END IF;
END;
/*
Macro:
Cells(x, y).Value = v
Cells(x, y).Select
Selection.Interior.ColorIndex = color
if (style in 'BOLD')
Selection.Font.Bold = True
else if (style in 'ITALIC')
Selection.Font.Italic = True
*/
PROCEDURE insert_char_formated (
worksheet client_ole2.obj_type,
x NUMBER,
y NUMBER,
v VARCHAR2,
color NUMBER,
style VARCHAR2
)
IS
args client_ole2.list_type;
cell client_ole2.obj_type;
selection client_ole2.obj_type;
font client_ole2.obj_type;
interior client_ole2.obj_type;
BEGIN
IF v IS NOT NULL
THEN
args := client_ole2.create_arglist;
client_ole2.add_arg (args, x);
client_ole2.add_arg (args, y);
cell := client_ole2.get_obj_property (worksheet, 'Cells', args);
client_ole2.destroy_arglist (args);
client_ole2.set_property (cell, 'Value', v);
client_ole2.invoke (cell, 'Select');
selection := client_ole2.invoke_obj (appl_id, 'Selection');
font := client_ole2.invoke_obj (selection, 'Font');
interior := client_ole2.invoke_obj (selection, 'Interior');
IF UPPER (style) IN ('BOLD', 'ITALIC')
THEN
client_ole2.set_property (font, style, TRUE);
END IF;
client_ole2.set_property (interior, 'ColorIndex', color);
client_ole2.RELEASE_OBJ (interior);
client_ole2.RELEASE_OBJ (font);
client_ole2.RELEASE_OBJ (selection);
client_ole2.RELEASE_OBJ (cell);
END IF;
END;
/*
Macro:
Range(r).Select
Selection.Columns.AutoFit
Cells(1,1).Select
*/
PROCEDURE set_auto_fit_range (worksheet client_ole2.obj_type, r VARCHAR2)
IS
args client_ole2.list_type;
--range
rang client_ole2.obj_type;
selection client_ole2.obj_type;
colum client_ole2.obj_type;
cell client_ole2.obj_type;
BEGIN
args := client_ole2.create_arglist;
client_ole2.add_arg (args, r);
rang := client_ole2.get_obj_property (worksheet, 'Range', args);
client_ole2.destroy_arglist (args);
client_ole2.invoke (rang, 'Select');
selection := client_ole2.invoke_obj (appl_id, 'Selection');
colum := client_ole2.invoke_obj (selection, 'Columns');
client_ole2.invoke (colum, 'AutoFit');
--now select upper (1,1) for deselection.
args := client_ole2.create_arglist;
client_ole2.add_arg (args, 1);
client_ole2.add_arg (args, 1);
cell := client_ole2.get_obj_property (worksheet, 'Cells', args);
client_ole2.invoke (cell, 'Select');
client_ole2.destroy_arglist (args);
client_ole2.RELEASE_OBJ (colum);
client_ole2.RELEASE_OBJ (selection);
client_ole2.RELEASE_OBJ (rang);
END set_auto_fit_range;
/*
Macro:
Range(r).Select
Selection.Numberformat = "0.00"
Cells(1,1).Select
*/
PROCEDURE set_decimal_format_range (
worksheet client_ole2.obj_type,
r VARCHAR2
)
IS
args client_ole2.list_type;
--range
rang client_ole2.obj_type;
selection client_ole2.obj_type;
--colum Client_OLE2.Obj_Type;
cell client_ole2.obj_type;
BEGIN
args := client_ole2.create_arglist;
client_ole2.add_arg (args, r);
rang := client_ole2.get_obj_property (worksheet, 'Range', args);
client_ole2.destroy_arglist (args);
client_ole2.invoke (rang, 'Select');
selection := client_ole2.invoke_obj (appl_id, 'Selection');
--colum:= Client_OLE2.invoke_obj(selection, 'Columns');
client_ole2.set_property (selection, 'Numberformat', '#.##0,00');
--Client_OLE2.invoke(colum, 'AutoFit');
--now select upper (1,1) for deselection.
args := client_ole2.create_arglist;
client_ole2.add_arg (args, 1);
client_ole2.add_arg (args, 1);
cell := client_ole2.get_obj_property (worksheet, 'Cells', args);
client_ole2.invoke (cell, 'Select');
client_ole2.destroy_arglist (args);
--Client_OLE2.release_obj(colum);
client_ole2.RELEASE_OBJ (selection);
client_ole2.RELEASE_OBJ (rang);
END set_decimal_format_range;
/*
Macro:Cells.Select
Selection.Columns.AutoFit
Cells(1,1).Select
*/
PROCEDURE set_auto_fit (worksheet client_ole2.obj_type)
IS
args client_ole2.list_type;
cell client_ole2.obj_type;
selection client_ole2.obj_type;
colum client_ole2.obj_type;
BEGIN
cell := client_ole2.get_obj_property (worksheet, 'Cells');
client_ole2.invoke (cell, 'Select');
selection := client_ole2.invoke_obj (appl_id, 'Selection');
colum := client_ole2.invoke_obj (selection, 'Columns');
client_ole2.invoke (colum, 'AutoFit');
--now select upper (1,1) for deselection.
args := client_ole2.create_arglist;
client_ole2.add_arg (args, 1);
client_ole2.add_arg (args, 1);
cell := client_ole2.get_obj_property (worksheet, 'Cells', args);
client_ole2.invoke (cell, 'Select');
client_ole2.destroy_arglist (args);
client_ole2.RELEASE_OBJ (colum);
client_ole2.RELEASE_OBJ (selection);
client_ole2.RELEASE_OBJ (cell);
END set_auto_fit;
PROCEDURE run_macro_on_document (
document client_ole2.obj_type,
macro VARCHAR2
)
IS
arg_list client_ole2.list_type;
bookmarkcollection client_ole2.obj_type;
bookmarkobj client_ole2.obj_type;
selectionobj client_ole2.obj_type;
BEGIN
arg_list := client_ole2.create_arglist;
client_ole2.add_arg (arg_list, macro);
client_ole2.invoke (excel.appl_id, 'Run', arg_list);
client_ole2.destroy_arglist (arg_list);
END;
FUNCTION download_file (
file_name IN VARCHAR2,
table_name IN VARCHAR2,
column_name IN VARCHAR2,
where_condition IN VARCHAR2
)
RETURN VARCHAR2
IS
l_ok BOOLEAN;
c_file_name VARCHAR2 (512);
c_path VARCHAR2 (255);
BEGIN
SYNCHRONIZE;
c_path := client_win_api_environment.get_temp_directory (FALSE);
IF c_path IS NULL
THEN
c_path := 'C:\';
ELSE
c_path := c_path || '\';
END IF;
c_file_name := c_path || file_name;
l_ok :=
webutil_file_transfer.db_to_client_with_progress
(c_file_name,
table_name,
column_name,
where_condition,
'Transfer on file system',
'Progress'
);
SYNCHRONIZE;
IF NOT l_ok
THEN
msg_popup ('File not found in database', 'E', TRUE);
END IF;
RETURN c_path || file_name;
END download_file;
END;
Word packagePackage specification
PACKAGE word
IS
/*
Global Word.Application Object --> represent word object.
*/
appl_id client_ole2.obj_type;
/*
Open file that act as template. Parameter are:
_application_ -- global word parameter that we initialize at
begining.
_file_ -- file name we wish to open --> it can be from database, or filesystem...
*/
FUNCTION file_open (application client_ole2.obj_type, FILE VARCHAR2)
RETURN client_ole2.obj_type;
/*
Close current file.
*/
PROCEDURE file_close (document client_ole2.obj_type);
/*
Saves current file (It is useful if we need to save current
file using another name)
*/
PROCEDURE file_save_as (document client_ole2.obj_type, FILE VARCHAR2);
/*
(Bizniss end of this whole package;) ) Inserts value in specific word bookmark.
_dokcument_ -- Word document.
_bookmark_ -- Name of bookmark that is defined in word template,
_content_ -- Content we wish to insert into bookmark.
*/
PROCEDURE insertafter_bookmark (
document client_ole2.obj_type,
bookmark VARCHAR2,
content VARCHAR2
);
/*
InsertAfter_Bookmark insert after bookmark and then delete that bookmark and this is not
good if you itarate through values, so this one do not delete bookmark after insert.
same paramters as previous one.
*/
PROCEDURE replace_bookmark (
document client_ole2.obj_type,
bookmark VARCHAR2,
content VARCHAR2
);
/*
Saame as previous procedure but it handle next for you.
*/
PROCEDURE insertafter_bookmark_next (
document client_ole2.obj_type,
bookmark VARCHAR2,
content VARCHAR2
);
/*
This one after value insert move itself on next row into table. When I say next I mean next-down.
This is essential for iterating through word table (one row at the time)
We need manualy create new row if it does not exists.!!!
*/
PROCEDURE insertafter_bookmark_down (
document client_ole2.obj_type,
bookmark VARCHAR2,
content VARCHAR2
);
/*
Easy...delete bookmark,
*/
PROCEDURE delete_bookmark (document client_ole2.obj_type, bookmark VARCHAR2);
/*
Create new table row (see InsertAfter_Bookmark_Next)
*/
PROCEDURE insert_new_table_row (
document client_ole2.obj_type,
bookmark VARCHAR2
);
/*
Move bookmakr (ONLY IN TABLE) left, right, up, down.
_direction_ can have following valyes'UP', 'DOWN', 'LEFT', 'RIGHT'
*/
PROCEDURE move_table_bookmark (
document client_ole2.obj_type,
bookmark VARCHAR2,
direction VARCHAR2
);
/*
File download.
parametar _file_name_ -- client file name (name on client)
_table_name_ -- Table name for where BLOB column is.
_column_name_ -- BLOB column name that holds Word template.
-where_condition_ -- filter.
*/
FUNCTION download_file (
file_name IN VARCHAR2,
table_name IN VARCHAR2,
column_name IN VARCHAR2,
where_condition IN VARCHAR2
)
RETURN VARCHAR2;
/*
Calling macro's on bookmarks...only for test.
*/
PROCEDURE run_macro_on_bookmark (
document client_ole2.obj_type,
bookmark VARCHAR2,
macro VARCHAR2
);
PROCEDURE run_macro_on_document (
document client_ole2.obj_type,
macro VARCHAR2
);
END;
Package bodyPACKAGE BODY word
IS
FUNCTION file_open (application client_ole2.obj_type, FILE VARCHAR2)
RETURN client_ole2.obj_type
IS
arg_list client_ole2.list_type;
document client_ole2.obj_type;
documents client_ole2.obj_type;
BEGIN
arg_list := client_ole2.create_arglist;
documents := client_ole2.invoke_obj (application, 'documents');
client_ole2.add_arg (arg_list, FILE);
document := client_ole2.invoke_obj (documents, 'Open', arg_list);
client_ole2.destroy_arglist (arg_list);
client_ole2.RELEASE_OBJ (documents);
RETURN document;
END file_open;
PROCEDURE file_close (document client_ole2.obj_type)
IS
BEGIN
client_ole2.invoke (document, 'Close');
--CLIENT_OLE2.RELEASE_OBJ(document);
END file_close;
PROCEDURE file_save_as (document client_ole2.obj_type, FILE VARCHAR2)
IS
arg_list client_ole2.list_type;
BEGIN
arg_list := client_ole2.create_arglist;
client_ole2.add_arg (arg_list, FILE);
client_ole2.invoke (document, 'SaveAs', arg_list);
client_ole2.destroy_arglist (arg_list);
--CLIENT_OLE2.RELEASE_OBJ(document);
END file_save_as;
PROCEDURE replace_bookmark (
document client_ole2.obj_type,
bookmark VARCHAR2,
content VARCHAR2
)
IS
arg_list client_ole2.list_type;
bookmarkcollection client_ole2.obj_type;
bookmarkobj client_ole2.obj_type;
selectionobj client_ole2.obj_type;
BEGIN
bookmarkcollection :=
client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
arg_list := client_ole2.create_arglist;
client_ole2.add_arg (arg_list, bookmark);
bookmarkobj :=
client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
client_ole2.destroy_arglist (arg_list);
client_ole2.invoke (bookmarkobj, 'Select');
selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
arg_list := client_ole2.create_arglist;
client_ole2.add_arg (arg_list, content);
client_ole2.invoke (selectionobj, 'Delete');
client_ole2.invoke (selectionobj, 'InsertAfter', arg_list);
client_ole2.destroy_arglist (arg_list);
client_ole2.RELEASE_OBJ (selectionobj);
client_ole2.RELEASE_OBJ (bookmarkobj);
client_ole2.RELEASE_OBJ (bookmarkcollection);
END replace_bookmark;
PROCEDURE insertafter_bookmark (
document client_ole2.obj_type,
bookmark VARCHAR2,
content VARCHAR2
)
IS
arg_list client_ole2.list_type;
bookmarkcollection client_ole2.obj_type;
bookmarkobj client_ole2.obj_type;
selectionobj client_ole2.obj_type;
BEGIN
bookmarkcollection :=
client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
arg_list := client_ole2.create_arglist;
client_ole2.add_arg (arg_list, bookmark);
bookmarkobj :=
client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
client_ole2.destroy_arglist (arg_list);
client_ole2.invoke (bookmarkobj, 'Select');
selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
arg_list := client_ole2.create_arglist;
client_ole2.add_arg (arg_list, content);
client_ole2.invoke (selectionobj, 'InsertAfter', arg_list);
client_ole2.destroy_arglist (arg_list);
client_ole2.RELEASE_OBJ (selectionobj);
client_ole2.RELEASE_OBJ (bookmarkobj);
client_ole2.RELEASE_OBJ (bookmarkcollection);
END insertafter_bookmark;
PROCEDURE insertafter_bookmark_next (
document client_ole2.obj_type,
bookmark VARCHAR2,
content VARCHAR2
)
IS
arg_list client_ole2.list_type;
bookmarkcollection client_ole2.obj_type;
bookmarkobj client_ole2.obj_type;
selectionobj client_ole2.obj_type;
BEGIN
bookmarkcollection :=
client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
arg_list := client_ole2.create_arglist;
client_ole2.add_arg (arg_list, bookmark);
bookmarkobj :=
client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
client_ole2.destroy_arglist (arg_list);
client_ole2.invoke (bookmarkobj, 'Select');
selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
arg_list := client_ole2.create_arglist;
client_ole2.add_arg (arg_list, content || CHR (13));
client_ole2.invoke (selectionobj, 'InsertAfter', arg_list);
client_ole2.destroy_arglist (arg_list);
client_ole2.RELEASE_OBJ (selectionobj);
client_ole2.RELEASE_OBJ (bookmarkobj);
client_ole2.RELEASE_OBJ (bookmarkcollection);
END insertafter_bookmark_next;
PROCEDURE insertafter_bookmark_down (
document client_ole2.obj_type,
bookmark VARCHAR2,
content VARCHAR2
)
IS
arg_list client_ole2.list_type;
bookmarkcollection client_ole2.obj_type;
bookmarkobj client_ole2.obj_type;
selectionobj client_ole2.obj_type;
BEGIN
bookmarkcollection :=
client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
arg_list := client_ole2.create_arglist;
client_ole2.add_arg (arg_list, bookmark);
bookmarkobj :=
client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
client_ole2.destroy_arglist (arg_list);
client_ole2.invoke (bookmarkobj, 'Select');
selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
arg_list := client_ole2.create_arglist;
client_ole2.add_arg (arg_list, content);
client_ole2.invoke (selectionobj, 'InsertAfter', arg_list);
client_ole2.invoke (selectionobj, 'Cut');
client_ole2.invoke (selectionobj, 'SelectCell');
client_ole2.invoke (selectionobj, 'MoveDown');
client_ole2.invoke (selectionobj, 'Paste');
client_ole2.destroy_arglist (arg_list);
client_ole2.RELEASE_OBJ (selectionobj);
client_ole2.RELEASE_OBJ (bookmarkobj);
client_ole2.RELEASE_OBJ (bookmarkcollection);
END insertafter_bookmark_down;
PROCEDURE delete_bookmark (document client_ole2.obj_type, bookmark VARCHAR2)
IS
arg_list client_ole2.list_type;
bookmarkcollection client_ole2.obj_type;
bookmarkobj client_ole2.obj_type;
selectionobj client_ole2.obj_type;
BEGIN
bookmarkcollection :=
client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
arg_list := client_ole2.create_arglist;
client_ole2.add_arg (arg_list, bookmark);
bookmarkobj :=
client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
client_ole2.destroy_arglist (arg_list);
client_ole2.invoke (bookmarkobj, 'Select');
selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
client_ole2.invoke (selectionobj, 'Delete');
client_ole2.RELEASE_OBJ (selectionobj);
client_ole2.RELEASE_OBJ (bookmarkobj);
client_ole2.RELEASE_OBJ (bookmarkcollection);
END delete_bookmark;
PROCEDURE run_macro_on_bookmark (
document client_ole2.obj_type,
bookmark VARCHAR2,
macro VARCHAR2
)
IS
arg_list client_ole2.list_type;
bookmarkcollection client_ole2.obj_type;
bookmarkobj client_ole2.obj_type;
selectionobj client_ole2.obj_type;
BEGIN
bookmarkcollection :=
client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
arg_list := client_ole2.create_arglist;
client_ole2.add_arg (arg_list, bookmark);
bookmarkobj :=
client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
client_ole2.destroy_arglist (arg_list);
client_ole2.invoke (bookmarkobj, 'Select');
selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
arg_list := client_ole2.create_arglist;
client_ole2.add_arg (arg_list, macro);
client_ole2.invoke (word.appl_id, 'Run', arg_list);
client_ole2.destroy_arglist (arg_list);
client_ole2.RELEASE_OBJ (selectionobj);
client_ole2.RELEASE_OBJ (bookmarkobj);
client_ole2.RELEASE_OBJ (bookmarkcollection);
END;
PROCEDURE run_macro_on_document (
document client_ole2.obj_type,
macro VARCHAR2
)
IS
arg_list client_ole2.list_type;
bookmarkcollection client_ole2.obj_type;
bookmarkobj client_ole2.obj_type;
selectionobj client_ole2.obj_type;
BEGIN
--bookmarkCollection := CLIENT_OLE2.INVOKE_OBJ(document, 'Bookmarks', arg_list);
--arg_list := CLIENT_OLE2.CREATE_ARGLIST;
--CLIENT_OLE2.ADD_ARG(arg_list, bookmark);
--bookmarkObj := CLIENT_OLE2.INVOKE_OBJ(bookmarkCollection, 'Item',arg_list);
--CLIENT_OLE2.DESTROY_ARGLIST(arg_list);
--CLIENT_OLE2.INVOKE(bookmarkObj, 'Select');
--selectionObj := CLIENT_OLE2.INVOKE_OBJ(appl_id, 'Selection');
arg_list := client_ole2.create_arglist;
client_ole2.add_arg (arg_list, macro);
client_ole2.invoke (word.appl_id, 'Run', arg_list);
client_ole2.destroy_arglist (arg_list);
--CLIENT_OLE2.RELEASE_OBJ(selectionObj);
--CLIENT_OLE2.RELEASE_OBJ(bookmarkObj);
--CLIENT_OLE2.RELEASE_OBJ(bookmarkCollection);
END;
PROCEDURE insert_new_table_row (
document client_ole2.obj_type,
bookmark VARCHAR2
)
IS
arg_list client_ole2.list_type;
bookmarkcollection client_ole2.obj_type;
bookmarkobj client_ole2.obj_type;
selectionobj client_ole2.obj_type;
BEGIN
bookmarkcollection :=
client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
arg_list := client_ole2.create_arglist;
client_ole2.add_arg (arg_list, bookmark);
bookmarkobj :=
client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
client_ole2.destroy_arglist (arg_list);
client_ole2.invoke (bookmarkobj, 'Select');
selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
arg_list := client_ole2.create_arglist;
client_ole2.add_arg (arg_list, 1);
client_ole2.invoke (selectionobj, 'InsertRowsBelow', arg_list);
client_ole2.destroy_arglist (arg_list);
client_ole2.RELEASE_OBJ (selectionobj);
client_ole2.RELEASE_OBJ (bookmarkobj);
client_ole2.RELEASE_OBJ (bookmarkcollection);
END insert_new_table_row;
PROCEDURE move_down_table_bookmark (
document client_ole2.obj_type,
bookmark VARCHAR2
)
IS
arg_list client_ole2.list_type;
bookmarkcollection client_ole2.obj_type;
bookmarkobj client_ole2.obj_type;
selectionobj client_ole2.obj_type;
BEGIN
bookmarkcollection :=
client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
arg_list := client_ole2.create_arglist;
client_ole2.add_arg (arg_list, bookmark);
bookmarkobj :=
client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
client_ole2.destroy_arglist (arg_list);
client_ole2.invoke (bookmarkobj, 'Select');
selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
client_ole2.invoke (selectionobj, 'Cut');
client_ole2.invoke (selectionobj, 'SelectCell');
client_ole2.invoke (selectionobj, 'MoveDown');
client_ole2.invoke (selectionobj, 'Paste');
client_ole2.RELEASE_OBJ (selectionobj);
client_ole2.RELEASE_OBJ (bookmarkobj);
client_ole2.RELEASE_OBJ (bookmarkcollection);
END move_down_table_bookmark;
PROCEDURE move_up_table_bookmark (
document client_ole2.obj_type,
bookmark VARCHAR2
)
IS
arg_list client_ole2.list_type;
bookmarkcollection client_ole2.obj_type;
bookmarkobj client_ole2.obj_type;
selectionobj client_ole2.obj_type;
BEGIN
bookmarkcollection :=
client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
arg_list := client_ole2.create_arglist;
client_ole2.add_arg (arg_list, bookmark);
bookmarkobj :=
client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
client_ole2.destroy_arglist (arg_list);
client_ole2.invoke (bookmarkobj, 'Select');
selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
client_ole2.invoke (selectionobj, 'Cut');
client_ole2.invoke (selectionobj, 'SelectCell');
client_ole2.invoke (selectionobj, 'MoveUp');
client_ole2.invoke (selectionobj, 'Paste');
client_ole2.RELEASE_OBJ (selectionobj);
client_ole2.RELEASE_OBJ (bookmarkobj);
client_ole2.RELEASE_OBJ (bookmarkcollection);
END move_up_table_bookmark;
PROCEDURE move_left_table_bookmark (
document client_ole2.obj_type,
bookmark VARCHAR2
)
IS
arg_list client_ole2.list_type;
bookmarkcollection client_ole2.obj_type;
bookmarkobj client_ole2.obj_type;
selectionobj client_ole2.obj_type;
BEGIN
bookmarkcollection :=
client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
arg_list := client_ole2.create_arglist;
client_ole2.add_arg (arg_list, bookmark);
bookmarkobj :=
client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
client_ole2.destroy_arglist (arg_list);
client_ole2.invoke (bookmarkobj, 'Select');
selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
client_ole2.invoke (selectionobj, 'Cut');
client_ole2.invoke (selectionobj, 'SelectCell');
client_ole2.invoke (selectionobj, 'MoveUp');
client_ole2.invoke (selectionobj, 'Paste');
client_ole2.RELEASE_OBJ (selectionobj);
client_ole2.RELEASE_OBJ (bookmarkobj);
client_ole2.RELEASE_OBJ (bookmarkcollection);
END move_left_table_bookmark;
PROCEDURE move_table_bookmark (
document client_ole2.obj_type,
bookmark VARCHAR2,
direction VARCHAR2
)
IS
arg_list client_ole2.list_type;
bookmarkcollection client_ole2.obj_type;
bookmarkobj client_ole2.obj_type;
selectionobj client_ole2.obj_type;
BEGIN
bookmarkcollection :=
client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
arg_list := client_ole2.create_arglist;
client_ole2.add_arg (arg_list, bookmark);
bookmarkobj :=
client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
client_ole2.destroy_arglist (arg_list);
client_ole2.invoke (bookmarkobj, 'Select');
selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
IF UPPER (direction) IN ('UP', 'DOWN', 'LEFT', 'RIGHT')
THEN
client_ole2.invoke (selectionobj, 'Cut');
client_ole2.invoke (selectionobj, 'SelectCell');
client_ole2.invoke (selectionobj, 'Move' || direction);
client_ole2.invoke (selectionobj, 'Paste');
END IF;
client_ole2.RELEASE_OBJ (selectionobj);
client_ole2.RELEASE_OBJ (bookmarkobj);
client_ole2.RELEASE_OBJ (bookmarkcollection);
END move_table_bookmark;
FUNCTION download_file (
file_name IN VARCHAR2,
table_name IN VARCHAR2,
column_name IN VARCHAR2,
where_condition IN VARCHAR2
)
RETURN VARCHAR2
IS
l_ok BOOLEAN;
c_file_name VARCHAR2 (512);
c_path VARCHAR2 (255);
BEGIN
SYNCHRONIZE;
c_path := client_win_api_environment.get_temp_directory (FALSE);
IF c_path IS NULL
THEN
c_path := 'C:\';
ELSE
c_path := c_path || '\';
END IF;
c_file_name := c_path || file_name;
l_ok :=
webutil_file_transfer.db_to_client_with_progress
(c_file_name,
table_name,
column_name,
where_condition,
'Transfer on file system',
'Progress'
);
SYNCHRONIZE;
RETURN c_path || file_name;
END download_file;
END;
Simple testPROCEDURE Call(c_prog IN VARCHAR2,param1 IN VARCHAR2 DEFAULT NULL,value1 IN VARCHAR2 DEFAULT NULL,
param2 IN VARCHAR2 DEFAULT NULL,value2 IN VARCHAR2 DEFAULT NULL) IS
list_id Paramlist;
BEGIN
--Check if list exists.
list_id := Get_Parameter_List('param_list');
IF NOT Id_Null(list_id) THEN
Destroy_Parameter_List(list_id); -- Ako postoji, unisti je!
END IF;
list_id := Create_Parameter_List('param_list');
Add_Parameter(list_id, 'ps_sif',TEXT_PARAMETER, :Global.ps_sif);
Add_Parameter(list_id, 'frm_sif',TEXT_PARAMETER, :Global.frm_sif);
Add_Parameter(list_id, 'god_sif',TEXT_PARAMETER, :Global.god_sif);
Add_Parameter(list_id, 'ana_id',TEXT_PARAMETER, :Global.ana_id);
Add_Parameter(list_id, 'id_radnik',TEXT_PARAMETER, :Global.id_radnik);
Add_Parameter(list_id, 'forma',TEXT_PARAMETER, UPPER(c_prog));
IF param1 IS NOT NULL THEN
Add_Parameter(list_id, param1,TEXT_PARAMETER, value1);
END IF;
IF param2 IS NOT NULL THEN
Add_Parameter(list_id, param2,TEXT_PARAMETER, value2);
END IF;
CALL_FORM(c_prog || '.FMX', NO_HIDE, DO_REPLACE, NO_QUERY_ONLY, list_id);
END;
do you work with a word template .dot?
ReplyDeleteI have a problem Problem with a template word and client_ole2 in the folowing code:
I have the folowing global variables in the specification package
App CLIENT_OLE2.OBJ_TYPE;
Docs CLIENT_OLE2.OBJ_TYPE;
Doc CLIENT_OLE2.OBJ_TYPE;
Selection CLIENT_OLE2.OBJ_TYPE;
And i have two functions in the body package:
---- This function takes a template word located at
---- NomFich = C:\DOCUME~1\MADX92\CONFIG~1\Temp\MADQ80_CIA.dot
FUNCTION F_NuevWord (a_NomFich VARCHAR2) RETURN BOOLEAN IS
v_Lista CLIENT_OLE2.LIST_TYPE;
v_ObjEx CLIENT_OLE2.OBJ_TYPE;
v_Err_Code BINARY_INTEGER;
v_Err_Text VARCHAR2(255);
BEGIN
App := CLIENT_OLE2.CREATE_OBJ ('Word.Application');
CLIENT_OLE2.SET_PROPERTY (App, 'Visible', 0);
Docs := CLIENT_OLE2.GET_OBJ_PROPERTY (App, 'Documents');
v_Lista := CLIENT_OLE2.CREATE_ARGLIST;
CLIENT_OLE2.ADD_ARG (v_Lista, a_NomFich);
v_ObjEx := CLIENT_OLE2.INVOKE_OBJ (Docs, 'Add', v_Lista);
CLIENT_OLE2.DESTROY_ARGLIST (v_Lista);
CLIENT_OLE2.RELEASE_OBJ (v_ObjEx);
RETURN TRUE;
EXCEPTION
WHEN CLIENT_OLE2.OLE_ERROR THEN
--shows the error
RETURN FALSE;
WHEN OTHERS THEN
P_INFO('Error: SVAGEST01:PCK_WORDDOC.F_NuevWord');
RETURN FALSE;
END F_NuevWord;
---This function saves the word document filled with data using ---the template word by a function WriteText
---a_NomFich = \\ESMADFFS01\PRUEBAS\SVAPRU\WORDDOCS\2011_04---\248070.doc
FUNCTION F_SaveAsWord (a_NomFich VARCHAR2) RETURN BOOLEAN IS
v_Lista CLIENT_OLE2.LIST_TYPE;
v_ObjEx CLIENT_OLE2.OBJ_TYPE;
ActDoc CLIENT_OLE2.OBJ_TYPE;
v_Err_Code BINARY_INTEGER;
V_Err_Text VARCHAR2(255);
BEGIN
v_WdActDoc := CLIENT_OLE2.GET_OBJ_PROPERTY (App, 'ActiveDocument');
v_Lista := CLIENT_OLE2.CREATE_ARGLIST;
CLIENT_OLE2.ADD_ARG (v_Lista, a_NomFich);
CLIENT_OLE2.ADD_ARG (v_Lista, 0);
v_ObjEx := CLIENT_OLE2.INVOKE_OBJ (v_WdActDoc, 'SaveAs', v_Lista);
CLIENT_OLE2.DESTROY_ARGLIST (v_Lista);
CLIENT_OLE2.RELEASE_OBJ (v_WdActDoc);
RETURN TRUE;
EXCEPTION
WHEN CLIENT_OLE2.OLE_ERROR THEN
---SHOWS THE ERROR
RETURN FALSE;
WHEN OTHERS THEN
P_INFO('Error en SVAGEST01:PCK_WORDDOC.F_SaveAsWord');
RETURN TRUE;
END F_SaveAsWord;
I got a error at
v_ObjEx := CLIENT_OLE2.INVOKE_OBJ (v_WdActDoc, 'SaveAs', v_Lista);
WUO-714:Unable to get the last OLE Error details; Exception null.
WUO-705:Unable to invoke Method: SaveAs; Exception com.jacob.com.ComFailException: VariantChangeType failed
The word document was created correctly by the template but i cant´t save the document generated name_file.doc. I don´t know what is the problem.
can you help me please?
Hello.
ReplyDeleteYou said that you can see how Word .dot file is filled with data?
If that is the case, we need to try to find error in save procedure... I didn't go too much deep into your code, is everything initialized properly?
I am a newbie please help me how i can use word package(already created) to change the orientation and run macros in a msword..give code snaps
ReplyDeleteThanks in advance..
plz reply to me
ReplyDeletei want to open the word document using webutil clien_ole2 but it give the error oracle.form.ole.olefunction not found.webutil is configured becuase all the tab of Demo form come with webutil folder is working except the ole tab.what is error and h i correct it.?
ReplyDeleteHello. Can u help with a function to insert image in excel file using ole2 functions in oracle forms
ReplyDelete