Oracle to MS WORD using OLE2 (table) 2

[jhoann]

hi,

is there someone know how to insert table from oracle forms 6i to MS WORD using OLE2 ?

for example I want a table which has a column header.

please see some of my code :

hSelection OLE2.LIST_TYPE;
hArgs OLE2.LIST_TYPE;
hTables OLE2.OBJ_TYPE;
hTable OLE2.OBJ_TYPE;
hColumns OLE2.OBJ_TYPE;
hColumn OLE2.OBJ_TYPE;
hCells OLE2.OBJ_TYPE;
hCell OLE2.OBJ_TYPE;

Begin
hTables := OLE2.GET_OBJ_PROPERTYhselection,'Table');
hArgs := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(hArgs, hTables);
hTable := OLE2.INVOKE_OBJ(hTables,'Item',hArgs);
OLE2.DESTROY_ARGLIST(hArgs);
end;

It's not complete because I don't know what's next :c
Please help me ....

Thanks in advance!

 

[sfvb]

Can you be more specific about what you want accomplished?

 

[jhoann]

What I want is to code from Oracle to MS WORD using OLE2
.... I want my output like this:

In MS Word (which is my output) it will show like this (in table form:
___________________
| SALES |
-------------------
|PRODUCT | AMOUNT |
--------------------
|Apple | 10.00 |
--------------------
|Banana | 50.00 |
--------------------

Can it also have a color for example the shading of my row 1 (the SALES) is blue ?

I hope you can help me .

Sorry for the drawing of the table, I hope you understand.

Thank you very much!

 

[sfvb]

I think this is what you want. You'll have to change the items in blue to match your table.

DECLARE
hApplication OLE2.OBJ_TYPE;
hDocuments OLE2.OBJ_TYPE;
hDocument OLE2.OBJ_TYPE;
hSelection OLE2.OBJ_TYPE;
hRange OLE2.OBJ_TYPE;
hTables OLE2.OBJ_TYPE;
hTable OLE2.OBJ_TYPE;
hCells OLE2.OBJ_TYPE;
hRows OLE2.OBJ_TYPE;
hShading OLE2.OBJ_TYPE;
hParagraphFormat OLE2.OBJ_TYPE;
hFont OLE2.OBJ_TYPE;

args OLE2.LIST_TYPE;

---- wdDefaultTableBehavior Class members
wdWord8TableBehavior CONSTANT number(5) := 0; --Default
wdWord9TableBehavior CONSTANT number(5) := 1;

---- wdAutoFitBehavior Class members
---- (only works when DefaultTableBehavior = wdWord9TableBehavior)
wdAutoFitContent CONSTANT number(5) := 1;
wdAutoFitFixed CONSTANT number(5) := 0;
wdAutoFitWindow CONSTANT number(5) := 2;

---- wdUnits Class members
wdCell CONSTANT number(5) := 12;
wdCharacter CONSTANT number(5) := 1; --Default
wdWord CONSTANT number(5) := 2;
wdSentence CONSTANT number(5) := 3;
wdLine CONSTANT number(5) := 5;

---- wdMovementType Class members
wdExtend CONSTANT number(5) := 1;
wdMove CONSTANT number(5) := 0; --Default

---- WdParagraphAlignment Class members
wdAlignParagraphCenter CONSTANT number(5) := 1;
wdAlignParagraphLeft CONSTANT number(5) := 0;
wdAlignParagraphRight CONSTANT number(5) := 2;

---- HexColor = BBGGRR
myLightBlue CONSTANT number(8) := 16755370; --FFAAAA

CURSOR c IS
SELECT typ product, amt amount, rownum
FROM myTable
WHERE rownum < 100;

BEGIN
hApplication := OLE2.CREATE_OBJ('Word.Application');
OLE2.SET_PROPERTY(hApplication, 'Visible', 1);

hDocuments := OLE2.GET_OBJ_PROPERTY(hApplication, 'Documents');
hDocument := OLE2.INVOKE_OBJ(hDocuments,'Add');

------------------------------
-------- Create Table --------
------------------------------
hSelection := OLE2.GET_OBJ_PROPERTY(hApplication, 'Selection');
hTables := OLE2.GET_OBJ_PROPERTY(hDocument , 'Tables' );
hRange := OLE2.GET_OBJ_PROPERTY(hSelection, 'Range');
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG_OBJ(args, hRange); --Range
OLE2.ADD_ARG(args, 3); --NumRows
OLE2.ADD_ARG(args, 2); --NumColumns
OLE2.ADD_ARG(args, wdWord9TableBehavior); --DefaultTableBehavior
OLE2.ADD_ARG(args, wdAutoFitContent); --AutoFitBehavior
hTable := OLE2.INVOKE_OBJ(hTables, 'Add', args);
OLE2.DESTROY_ARGLIST(args);
OLE2.RELEASE_OBJ(hTable);
OLE2.RELEASE_OBJ(hRange);
OLE2.RELEASE_OBJ(hTables);

------------------------------------------
-------- Create and Format Header --------
------------------------------------------
---- Select 2 cells at the top of table
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, wdCharacter); --Unit
OLE2.ADD_ARG(args, 2); --Count
OLE2.ADD_ARG(args, wdExtend); --Extend
OLE2.INVOKE(hSelection, 'MoveRight', args);
OLE2.DESTROY_ARGLIST(args);

---- Merge those 2 cells into 1 cell
hcells := OLE2.GET_OBJ_PROPERTY(hSelection, 'Cells');
OLE2.INVOKE(hCells, 'Merge');

---- Add next 2 cells to the selection
---- (next 2 cells are actually row 2)
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, wdCharacter); --Unit
OLE2.ADD_ARG(args, 2); --Count
OLE2.ADD_ARG(args, wdExtend); --Extend
OLE2.INVOKE(hSelection, 'MoveRight', args);
OLE2.DESTROY_ARGLIST(args);

---- Set color of selected cells
hcells := OLE2.GET_OBJ_PROPERTY(hSelection, 'Cells');
hShading := OLE2.GET_OBJ_PROPERTY(hCells, 'Shading');
OLE2.SET_PROPERTY(hShading, 'BackgroundPatternColor', myLightBlue);
OLE2.RELEASE_OBJ(hShading);
OLE2.RELEASE_OBJ(hCells);

---- Set selected cells to be Bold Header
hrows := OLE2.GET_OBJ_PROPERTY(hSelection, 'Rows');
OLE2.SET_PROPERTY(hRows, 'AllowBreakAcrossPages', True);
OLE2.SET_PROPERTY(hRows, 'HeadingFormat', True);
OLE2.RELEASE_OBJ(hRows);
hFont := OLE2.GET_OBJ_PROPERTY(hSelection, 'Font');
OLE2.SET_PROPERTY(hFont, 'Bold', True);
OLE2.RELEASE_OBJ(hFont);

---- Move to Header row 1, set text and center
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, wdCharacter);
OLE2.ADD_ARG(args, 1);
OLE2.ADD_ARG(args, wdMove);
OLE2.INVOKE(hSelection, 'MoveLeft', args);
OLE2.DESTROY_ARGLIST(args);

hParagraphFormat := OLE2.GET_OBJ_PROPERTY(hSelection, 'ParagraphFormat');
OLE2.SET_PROPERTY(hParagraphFormat, 'Alignment', wdAlignParagraphCenter);
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 'Sales');
OLE2.INVOKE(hSelection, 'TypeText', args);
OLE2.DESTROY_ARGLIST(args);

---- Move to Header row 2/cell 1, set text
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, wdCell);
OLE2.ADD_ARG(args, 1);
OLE2.ADD_ARG(args, wdMove);
OLE2.INVOKE(hSelection, 'MoveRight', args);
OLE2.DESTROY_ARGLIST(args);

args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 'Product');
OLE2.INVOKE(hSelection, 'TypeText', args);
OLE2.DESTROY_ARGLIST(args);

---- Move to Header row 2/cell 2, set text and right align
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, wdCell);
OLE2.ADD_ARG(args, 1);
OLE2.ADD_ARG(args, wdMove);
OLE2.INVOKE(hSelection, 'MoveRight', args);
OLE2.DESTROY_ARGLIST(args);

hParagraphFormat := OLE2.GET_OBJ_PROPERTY(hSelection, 'ParagraphFormat');
OLE2.SET_PROPERTY(hParagraphFormat, 'Alignment', wdAlignParagraphRight);
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 'Amount');
OLE2.INVOKE(hSelection, 'TypeText', args);
OLE2.DESTROY_ARGLIST(args);

---- Move row 3/cell 2, right align
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, wdLine);
OLE2.ADD_ARG(args, 1);
OLE2.ADD_ARG(args, wdMove);
OLE2.INVOKE(hSelection, 'MoveDown', args);
OLE2.DESTROY_ARGLIST(args);
hParagraphFormat := OLE2.GET_OBJ_PROPERTY(hSelection, 'ParagraphFormat');
OLE2.SET_PROPERTY(hParagraphFormat, 'Alignment', wdAlignParagraphRight);

---- Move back to first cell
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, wdCell);
OLE2.ADD_ARG(args, 1);
OLE2.ADD_ARG(args, wdMove);
OLE2.INVOKE(hSelection, 'MoveLeft', args);
OLE2.DESTROY_ARGLIST(args);

-------------------------------------------
-------- Populate rows from cursor --------
-------------------------------------------
FOR v IN c
LOOP
IF v.rownum != 1 THEN
---- create new line by moving over 1 cell
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, wdCell);
OLE2.ADD_ARG(args, 1);
OLE2.ADD_ARG(args, wdMove);
OLE2.INVOKE(hSelection, 'MoveRight', args);
OLE2.DESTROY_ARGLIST(args);
END IF;

---- Print product, move over a cell
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, v.product);
OLE2.INVOKE(hSelection, 'TypeText', args);
OLE2.DESTROY_ARGLIST(args);

args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, wdCell);
OLE2.ADD_ARG(args, 1);
OLE2.ADD_ARG(args, wdMove);
OLE2.INVOKE(hSelection, 'MoveRight', args);
OLE2.DESTROY_ARGLIST(args);

---- Print amt
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, to_char(v.amount, '9,999,999.00'));
OLE2.INVOKE(hSelection, 'TypeText', args);
OLE2.DESTROY_ARGLIST(args);
END LOOP;

--------------------------
--------------------------
OLE2.RELEASE_OBJ(hParagraphFormat);
OLE2.RELEASE_OBJ(hSelection);
OLE2.RELEASE_OBJ(hDocument);
OLE2.RELEASE_OBJ(hDocuments);
OLE2.RELEASE_OBJ(hApplication);

message('Task is Done.');
message('Task is Done.');
EXCEPTION
WHEN others THEN
message('Error');
message('Error');
END;

 

[Guest_imported]

you're the BEST ... Thank you so much!

 

[Guest_imported]

I want to open Miscrosft word 97 from Forms 9i. Please write me the solution to sanjeev.goel@igtsolutions.com