brzak
Član broj: 66407 Poruke: 126
|
Evo procedure koja prebacuje sadrzaj forme u Excel. Radi sa Forms 6i, i koristi d2kwutil biblioteku.
PROCEDURE export_2_excel (p_block IN VARCHAR2)
IS
application ole2.obj_type;
workbooks ole2.obj_type;
workbook ole2.obj_type;
worksheets ole2.obj_type;
worksheet ole2.obj_type;
args ole2.list_type;
cell ole2.obj_type;
j INTEGER;
k INTEGER;
file_name_cl VARCHAR2 (32767);
item_prompt VARCHAR2 (32767);
user_cancel EXCEPTION;
item_name VARCHAR2 (250);
item_last VARCHAR2 (250);
item_count NUMBER := 0;
itm_type NUMBER;
fst_blkname VARCHAR2 (250);
blk_row_count NUMBER;
fst_item VARCHAR2 (250);
row_sep NUMBER := 1;
selection ole2.obj_type;
colum ole2.obj_type;
item_last_nav VARCHAR2 (5);
item_first_nav VARCHAR2 (5);
itmnext VARCHAR2 (100);
itm VARCHAR2 (100);
BEGIN
--file_name_cl := get_file_name('C:','','|Excel Documents(*.xls)|*.xls|','Select output document...',SAVE_FILE,true);
--file_name_cl := SUBSTR(file_name_cl,1,LENGTH(file_name_cl));
/*file_name_cl is not returning any value so the following lines raised a internal PLSQL Error since the Exception
user_cancel is not assigned to any error number ?
--IF file_name_cl IS NULL THEN
--RAISE user_cancel;
--END IF; */
application := ole2.create_obj ('Excel.Application');
ole2.set_property (application, 'Visible', 1);
workbooks := ole2.get_obj_property (application, 'Workbooks');
workbook := ole2.invoke_obj (workbooks, 'Add');
worksheets := ole2.get_obj_property (workbook, 'Worksheets');
worksheet := ole2.invoke_obj (worksheets, 'Add');
/* Get the first and last block name */
/* Make last_blkname = NULL If both are same so that the loop executes once */
/* Loop through all the Blocks in the form */
GO_BLOCK (p_block);
item_count := 0;
fst_blkname := :SYSTEM.current_block;
blk_row_count := GET_BLOCK_PROPERTY (fst_blkname, records_displayed);
/* Get the first and last items in the particular block */
item_name := GET_BLOCK_PROPERTY (fst_blkname, first_item);
fst_item := item_name;
item_last := GET_BLOCK_PROPERTY (fst_blkname, last_item);
-- item_last_nav := get_item_property(fst_blkname||'.'||item_last,navigable);
-- set_item_property(fst_blkname||'.'||item_last,navigable,property_true);
itm := GET_BLOCK_PROPERTY (fst_blkname, first_item);
/* Loop through all the items in the block */
LOOP
EXIT WHEN itm IS NULL;
/* Get item count */
IF f_visible_item (fst_blkname || '.' || itm)
THEN
item_count := item_count + 1;
END IF;
itm := GET_ITEM_PROPERTY (fst_blkname || '.' || itm, nextitem);
END LOOP;
IF blk_row_count > 1
THEN
FIRST_RECORD;
END IF;
-- GO_ITEM (fst_blkname || '.' || fst_item);
GO_BLOCK (fst_blkname);
FIRST_RECORD;
itm := GET_BLOCK_PROPERTY (fst_blkname, first_item);
k := 1; /* Represemts column number */
j := row_sep + 1; /* Represents row number */
/* Add the column headings using item prompts */
-- FOR k IN 1 .. item_count + 1 /* Block has n visible columns */
LOOP
EXIT WHEN itm IS NULL;
IF f_visible_item (fst_blkname || '.' || itm)
THEN
item_prompt := GET_ITEM_PROPERTY (fst_blkname || '.' || itm, prompt_text);
args := ole2.create_arglist;
ole2.add_arg (args, j);
ole2.add_arg (args, k);
cell := ole2.get_obj_property (worksheet, 'Cells', args);
ole2.destroy_arglist (args);
ole2.set_property (cell, 'Value', item_prompt);
ole2.RELEASE_OBJ (cell);
k := k + 1;
END IF;
itm := GET_ITEM_PROPERTY (fst_blkname || '.' || itm, nextitem);
-- NEXT_ITEM;
END LOOP;
j := j + 1; /* Add to rowcount so that data won't overwrite column headings! */
itm := GET_BLOCK_PROPERTY (fst_blkname, first_item);
LOOP
/* Add in all the data */
-- FOR k IN 1 .. item_count + 1 /* Block has n visible columns */
k := 1;
LOOP
EXIT WHEN itm IS NULL;
IF f_visible_item (fst_blkname || '.' || itm)
THEN
args := ole2.create_arglist;
ole2.add_arg (args, j);
ole2.add_arg (args, k);
cell := ole2.get_obj_property (worksheet, 'Cells', args);
ole2.destroy_arglist (args);
ole2.set_property (cell, 'Value', NAME_IN (fst_blkname || '.' || itm));
ole2.RELEASE_OBJ (cell);
k := k + 1;
END IF;
itm := GET_ITEM_PROPERTY (fst_blkname || '.' || itm, nextitem);
END LOOP;
j := j + 1;
/* Check for last record */
IF :SYSTEM.LAST_RECORD = 'TRUE'
THEN
EXIT;
ELSE
/* If Records_displayed is more than 1 then only move to the next record specifically for Master-detail blocks */
IF blk_row_count > 1
THEN
NEXT_RECORD;
itm := GET_BLOCK_PROPERTY (fst_blkname, first_item);
ELSE
EXIT;
END IF;
END IF;
END LOOP;
-- if item_last_nav='FALSE' then
-- set_item_property(item_last,navigable,property_false);
-- end if;
FIRST_RECORD;
--AutoFit
cell := ole2.get_obj_property (worksheet, 'UsedRange');
colum := ole2.get_obj_property (cell, 'Columns');
ole2.invoke (colum, 'AutoFit');
--i selektuj 1,1!
args := ole2.create_arglist;
ole2.add_arg (args, 1);
ole2.add_arg (args, 1);
cell := ole2.get_obj_property (worksheet, 'Cells', args);
ole2.invoke (cell, 'Select');
ole2.destroy_arglist (args);
ole2.RELEASE_OBJ (colum);
ole2.RELEASE_OBJ (cell);
--kraj AutoFit
ole2.RELEASE_OBJ (worksheet);
ole2.RELEASE_OBJ (worksheets);
/* Save the Excel file created */
args := ole2.create_arglist;
ole2.add_arg (args, file_name_cl);
ole2.invoke (workbook, 'Save', args);
ole2.destroy_arglist (args);
/* release workbook */
ole2.RELEASE_OBJ (workbook);
ole2.RELEASE_OBJ (workbooks);
/* Release application */
ole2.invoke (application, 'Quit');
ole2.RELEASE_OBJ (application);
--EXCEPTION
--WHEN user_cancel THEN
--RAISE;
END;
Kod mene je to zakaceno na meni, i ovo je poziv
DECLARE
cur_blk VARCHAR2 (40) := :SYSTEM.cursor_block;
cur_rec NUMBER;
bk_id BLOCK;
BEGIN
bk_id := FIND_BLOCK (cur_blk);
cur_rec := GET_BLOCK_PROPERTY (bk_id, records_displayed);
IF cur_rec > 1
THEN
export_2_excel (:SYSTEM.current_block);
ELSE
message ('Operacija moguca samo za multi record blok!');
END IF;
END;
Eto, mozda ce nekom zatrebati.
|