Hello,
This is on Oracle Forms 12c using WebUtils. I have following code and I am trying to download an excel file from a Forms - Database block using a WHEN-BUTTON-PRESSED event.
The problem: code is not doing anything. There is no response when I click the button. What am I missing?
I am not seeing any errors either.
Please help!.
Thanks,
Darsh
-- Declare the OLE objects
DECLARE
application CLIENT_OLE2.OBJ_TYPE;
workbooks CLIENT_OLE2.OBJ_TYPE;
workbook CLIENT_OLE2.OBJ_TYPE;
worksheets CLIENT_OLE2.OBJ_TYPE;
worksheet CLIENT_OLE2.OBJ_TYPE;
cell CLIENT_OLE2.OBJ_TYPE;
range CLIENT_OLE2.OBJ_TYPE;
range_col CLIENT_OLE2.OBJ_TYPE;
-- Declare handles to OLE argument lists
args CLIENT_OLE2.LIST_TYPE;
-- Declare form and block items
p_block_name VARCHAR2(200) := 'HISTORY';
form_name VARCHAR2(100);
f_block VARCHAR2(100);
l_block VARCHAR2(100);
f_item VARCHAR2(100);
l_item VARCHAR2(100);
cur_block VARCHAR2(100) := :system.current_block;
cur_item VARCHAR2(100) := :system.current_item;
cur_record VARCHAR2(100) := :system.cursor_record;
item_name VARCHAR2(100);
baslik VARCHAR2(100);
row_n NUMBER;
col_n NUMBER;
filename VARCHAR2(100);
BEGIN
-- Start Excel
application:=CLIENT_OLE2.CREATE_OBJ('Excel.Application');
CLIENT_OLE2.SET_PROPERTY(application, 'Visible', 'true');
-- Return object handle to the Workbooks collection
workbooks:=CLIENT_OLE2.INVOKE_OBJ(application, 'Workbooks');
-- Add a new Workbook object to the Workbooks collection
workbook:=CLIENT_OLE2.INVOKE_OBJ(workbooks,'Add');
-- Return object handle to the Worksheets collection for the Workbook
worksheets:=CLIENT_OLE2.INVOKE_OBJ(workbook, 'Worksheets');
-- Get the first Worksheet in the Worksheets collection
-- worksheet:=CLIENT_OLE2.GET_OBJ_PROPERTY(worksheets,'Add');
args:=CLIENT_OLE2.CREATE_ARGLIST;
CLIENT_OLE2.ADD_ARG(args, 1);
worksheet:=CLIENT_OLE2.INVOKE_OBJ(worksheets,'Item',args);
CLIENT_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 := 1;
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:=CLIENT_OLE2.CREATE_ARGLIST;
CLIENT_OLE2.ADD_ARG(args, row_n);
CLIENT_OLE2.ADD_ARG(args, col_n);
cell:=CLIENT_OLE2.INVOKE_OBJ(worksheet, 'Cells', args);
CLIENT_OLE2.DESTROY_ARGLIST(args);
CLIENT_OLE2.SET_PROPERTY(cell, 'Value', baslik);
CLIENT_OLE2.RELEASE_OBJ(cell);
END IF;
-- Set other rows with the item values
args:=CLIENT_OLE2.CREATE_ARGLIST;
CLIENT_OLE2.ADD_ARG(args, row_n+1);
CLIENT_OLE2.ADD_ARG(args, col_n);
cell:=CLIENT_OLE2.INVOKE_OBJ(worksheet, 'Cells', args);
CLIENT_OLE2.DESTROY_ARGLIST(args);
IF get_item_property(item_name,DATATYPE)<>'NUMBER' THEN
CLIENT_OLE2.SET_PROPERTY(cell, 'NumberFormat', '@');
END IF;
CLIENT_OLE2.SET_PROPERTY(cell, 'Value', NVL(name_in(item_name)
,'0'));
CLIENT_OLE2.RELEASE_OBJ(cell);
--next_item;
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);
col_n := col_n + 1;
END LOOP;
EXIT WHEN NAME_IN('system.last_record') = 'TRUE';
NEXT_RECORD;
END LOOP;
-- Autofit columns
range := CLIENT_OLE2.INVOKE_OBJ( worksheet,'UsedRange');
range_col := CLIENT_OLE2.INVOKE_OBJ( range,'Columns');
CLIENT_OLE2.INVOKE( range_col,'AutoFit' );
CLIENT_OLE2.RELEASE_OBJ( range );
CLIENT_OLE2.RELEASE_OBJ( range_col );
-- Get filename and path
args := CLIENT_OLE2.CREATE_ARGLIST;
CLIENT_OLE2.ADD_ARG( args, p_block_name );
CLIENT_OLE2.ADD_ARG( args,'Excel Workbooks (*.xls, *.xls');
filename := CLIENT_OLE2.INVOKE_CHAR( application,'GetSaveAsFilename',args
);
CLIENT_OLE2.DESTROY_ARGLIST( args );
-- Save as worksheet
IF NVL(filename,'0')<>'0' THEN
args := CLIENT_OLE2.CREATE_ARGLIST;
CLIENT_OLE2.ADD_ARG( args,filename );
CLIENT_OLE2.INVOKE( worksheet,'SaveAs',args );
CLIENT_OLE2.DESTROY_ARGLIST( args );
END IF;
-- Close workbook
CLIENT_OLE2.INVOKE( workbook ,'Close');
-- Release the OLE objects
CLIENT_OLE2.RELEASE_OBJ(worksheet);
CLIENT_OLE2.RELEASE_OBJ(worksheets);
CLIENT_OLE2.RELEASE_OBJ(workbook);
CLIENT_OLE2.RELEASE_OBJ(workbooks);
CLIENT_OLE2.INVOKE(application, 'Quit');
CLIENT_OLE2.RELEASE_OBJ(application);
-- Focus to the original location
go_block(cur_block);
go_record(cur_record);
go_item(cur_block
||'.'
||cur_item);
END;