Oracle, Web, Script, SQLserver, Tips & Trick

December 16, 2010

Example Data Block Forms to Excel

Filed under: dev6i,oracle — sikathabis @ 5:39 am
Tags:

PROCEDURE pr_Forms_to_Excel(p_block_name IN VARCHAR2 DEFAULT NAME_IN(‘system.current_block’)) IS
— Declare the 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;
range OLE2.OBJ_TYPE;
range_col OLE2.OBJ_TYPE;
— Declare handles to OLE argument lists
args OLE2.LIST_TYPE;
— Declare form and block items
form_name VARCHAR2(100);
f_block VARCHAR2(100);
l_block VARCHAR2(100);
f_item VARCHAR2(100);
l_item VARCHAR2(100);
cur_block VARCHAR2(100) := NAME_IN(‘system.current_block’);
cur_item VARCHAR2(100) := NAME_IN(‘system.current_item’);
cur_record VARCHAR2(100) := NAME_IN(‘system.cursor_record’);
item_name VARCHAR2(100);
baslik VARCHAR2(100);
row_n NUMBER;
col_n NUMBER;
filename VARCHAR2(100);

BEGIN
— Start Excel
application:=OLE2.CREATE_OBJ(‘Excel.Application’);
OLE2.SET_PROPERTY(application, ‘Visible’, ‘True’);
— Return object handle to the Workbooks collection
workbooks:=OLE2.GET_OBJ_PROPERTY(application, ‘Workbooks’);
— Add a new Workbook object to the Workbooks collection
workbook:=OLE2.GET_OBJ_PROPERTY(workbooks,’Add’);
— Return object handle to the Worksheets collection for the Workbook
worksheets:=OLE2.GET_OBJ_PROPERTY(workbook, ‘Worksheets’);
— Get the first Worksheet in the Worksheets collection
— worksheet:=OLE2.GET_OBJ_PROPERTY(worksheets,’Add’);
args:=OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 1);
worksheet:=OLE2.GET_OBJ_PROPERTY(worksheets,’Item’,args);
OLE2.DESTROY_ARGLIST(args);
— Return object handle to cell A1 on the new Worksheet
go_block(p_block_name);
baslik := get_block_property(p_block_name,FIRST_ITEM);
f_item := p_block_name||’.’||get_block_property(p_block_name,FIRST_ITEM);
l_item := p_block_name||’.’||get_block_property(p_block_name,LAST_ITEM);
first_record;
LOOP
item_name := f_item;
row_n := NAME_IN(‘SYSTEM.CURSOR_RECORD’);
col_n := 0;
LOOP
IF get_item_property(item_name,ITEM_TYPE)<>’BUTTON’ AND
get_item_property(item_name,VISIBLE)=’TRUE’
THEN
— Set first row with the item names
IF row_n=1 THEN
baslik:=NVL(get_item_property(item_name,PROMPT_TEXT),baslik);
args:=OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, row_n);
OLE2.ADD_ARG(args, col_n);
cell:=OLE2.GET_OBJ_PROPERTY(worksheet, ‘Cells’, args);
OLE2.DESTROY_ARGLIST(args);
OLE2.SET_PROPERTY(cell, ‘Value’, baslik);
OLE2.RELEASE_OBJ(cell);
END IF;
— Set other rows with the item values
args:=OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, row_n+1);
OLE2.ADD_ARG(args, col_n);
cell:=OLE2.GET_OBJ_PROPERTY(worksheet, ‘Cells’, args);
OLE2.DESTROY_ARGLIST(args);
IF get_item_property(item_name,DATATYPE)<>’NUMBER’ THEN
OLE2.SET_PROPERTY(cell, ‘NumberFormat’, ‘@’);
END IF;
OLE2.SET_PROPERTY(cell, ‘Value’, name_in(item_name));
OLE2.RELEASE_OBJ(cell);
END IF;
IF item_name = l_item THEN
exit;
END IF;
baslik := get_item_property(item_name,NEXTITEM);
item_name := p_block_name||’.’||get_item_property(item_name,NEXTITEM);
if get_item_property(item_name,VISIBLE)=’TRUE’ then col_n := col_n + 1; end if;
END LOOP;
EXIT WHEN NAME_IN(‘system.last_record’) = ‘TRUE’;
NEXT_RECORD;
END LOOP;

item_name:= ‘TOTOUTSTANDING’;
row_n:=row_n+2;
col_n:=3;
go_block(‘BLK_SUM’);
baslik := get_block_property(‘BLK_SUM’,FIRST_ITEM);
–f_item := ‘BLK_SUM’||’.’||get_block_property(‘BLK_SUM’,FIRST_ITEM);
FIRST_RECORD;
baslik:=NVL(get_item_property(item_name,PROMPT_TEXT),baslik);
args:=OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, row_n);
OLE2.ADD_ARG(args, col_n);
cell:=OLE2.GET_OBJ_PROPERTY(worksheet, ‘Cells’, args);
OLE2.DESTROY_ARGLIST(args);
OLE2.SET_PROPERTY(cell, ‘Value’, baslik);

col_n:=col_n+1;
args:=OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, row_n);
OLE2.ADD_ARG(args, col_n);
cell:=OLE2.GET_OBJ_PROPERTY(worksheet, ‘Cells’, args);
OLE2.DESTROY_ARGLIST(args);
/*
IF get_item_property(item_name,DATATYPE)<>’NUMBER’ THEN
OLE2.SET_PROPERTY(cell, ‘NumberFormat’, ‘@’);
END IF;
*/
OLE2.SET_PROPERTY(cell, ‘Value’,TO_NUMBER(name_in(item_name),’999,999,999,999,999,999,999.00′));
item_name:= ‘TOTSTDDEV’;
col_n:=7;
args:=OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, row_n);
OLE2.ADD_ARG(args, col_n);
cell:=OLE2.GET_OBJ_PROPERTY(worksheet, ‘Cells’, args);
OLE2.DESTROY_ARGLIST(args);
/*
IF get_item_property(item_name,DATATYPE)<>’NUMBER’ THEN
OLE2.SET_PROPERTY(cell, ‘NumberFormat’, ‘@’);
END IF;
*/
OLE2.SET_PROPERTY(cell, ‘Value’, TO_NUMBER(name_in(item_name),’999,999,999,999,999.00′));

row_n:=row_n+1;
item_name:= ‘MEAN’;
args:=OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, row_n);
OLE2.ADD_ARG(args, col_n);
cell:=OLE2.GET_OBJ_PROPERTY(worksheet, ‘Cells’, args);
OLE2.DESTROY_ARGLIST(args);
/*
IF get_item_property(item_name,DATATYPE)<>’NUMBER’ THEN
OLE2.SET_PROPERTY(cell, ‘NumberFormat’, ‘@’);
END IF;
*/
OLE2.SET_PROPERTY(cell, ‘Value’, TO_NUMBER(name_in(item_name),’999,999,999,999,999.00′));

row_n:=row_n+1;
item_name:= ‘STDDEVIATION’;
args:=OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, row_n);
OLE2.ADD_ARG(args, col_n);
cell:=OLE2.GET_OBJ_PROPERTY(worksheet, ‘Cells’, args);
OLE2.DESTROY_ARGLIST(args);
/*
IF get_item_property(item_name,DATATYPE)<>’NUMBER’ THEN
OLE2.SET_PROPERTY(cell, ‘NumberFormat’, ‘@’);
END IF;
*/
OLE2.SET_PROPERTY(cell, ‘Value’, TO_NUMBER(name_in(item_name),’999,999,999,999,999.00′));

row_n:=row_n+1;
item_name:= ‘MODE’;
args:=OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, row_n);
OLE2.ADD_ARG(args, col_n);
cell:=OLE2.GET_OBJ_PROPERTY(worksheet, ‘Cells’, args);
OLE2.DESTROY_ARGLIST(args);
/*
IF get_item_property(item_name,DATATYPE)<>’NUMBER’ THEN
OLE2.SET_PROPERTY(cell, ‘NumberFormat’, ‘@’);
END IF;
*/
OLE2.SET_PROPERTY(cell, ‘Value’, TO_NUMBER(name_in(item_name),’999,999,999,999,999.00′));

— Autofit columns
range := OLE2.GET_OBJ_PROPERTY( worksheet,’UsedRange’);
range_col := OLE2.GET_OBJ_PROPERTY( range,’Columns’);
OLE2.INVOKE( range_col,’AutoFit’ );

OLE2.RELEASE_OBJ( range );
OLE2.RELEASE_OBJ( range_col );
/*
— Get filename and path
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG( args, p_block_name );
OLE2.ADD_ARG( args,’Excel Workbooks (*.xls, *.xls’);
filename := OLE2.INVOKE_CHAR( application,’GetSaveAsFilename’,args );
OLE2.DESTROY_ARGLIST( args );
*/
/*
— Save as worksheet
IF NVL(filename,’0′)<>’0′ THEN
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG( args,filename );
OLE2.INVOKE( worksheet,’SaveAs’,args );
OLE2.DESTROY_ARGLIST( args );
END IF;
*/
— Close workbook
–OLE2.INVOKE( workbook ,’Close’);
— Release the OLE objects
OLE2.RELEASE_OBJ(worksheet);
OLE2.RELEASE_OBJ(worksheets);
OLE2.RELEASE_OBJ(workbook);
OLE2.RELEASE_OBJ(workbooks);
–OLE2.INVOKE(application, ‘Quit’);
OLE2.RELEASE_OBJ(application);
— Focus to the original location
go_block(cur_block);
go_record(cur_record);
go_item(cur_block||’.’||cur_item);
END;

1 Comment »

  1. Good Work,very thx.You are best…

    Comment by Sikimkalkti.com — December 17, 2010 @ 12:16 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

Teknologi Pendidikan

Just another WordPress.com weblog

KATAKAMI

Jurnalisme Yang Dapat Dipercaya

Tonk Kosonk Baonk Bunyinya

Penampungan Serapah Tanpa Sumpah

Qey050489's Blog

Ayo Bangkit sobat..!!!!!

B. S. Totoraharjo

Orang Bodoh Yang Tak Kunjung Pandai

culturekitchen foodlab

New Food and Art in Yogyakarta

Wahyu Catur Wibowo

Musafir Dunia Maya

SQL.... Still Learning

My Reference on SQL Server

neinframe

Street Photography is like a box of a chocolates, you're never know what you're gonna get - Thomas Leuthard -

Keluarga Andrivito

-- Andrivito's Journal --

DRK™

This site is about Latest NEWS, Articles, Videos, Books, Course, Presentations, Tips & Tricks related to I.T Pro's

Gaurav Saxena

|| तत् त्वम् असि ||

Be a good Moeslim

Menuju Pribadi Muslim Sejati yang Bertaqwa

TuanSUFI

Manajemen Sholat, renungan spiritual, Teknologi Informasi

Pelangi di khatulistiwa....

Mari berbagi kebaikan, pengalaman dan senyum denganku

angkasa13

Ceritanya saya, Pikirannya saya, Hatinya saya

sikathabis

sikathabis semua informasi disini

%d bloggers like this: