Data Transfer from Oracle Developer to Excel

PROCEDURE fpr_forms_to_excel(p_block_name in varchar2 default NAME_IN('system.current_block'),
  p_path in varchar2 default 'C:\',
  p_file_name in varchar2 default 'Temp') 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;
 arglist 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);
 cur_record VARCHAR2(100);
 item_name VARCHAR2(100);
 baslik VARCHAR2(100);
 row_n NUMBER;
 col_n NUMBER;
 filename VARCHAR2(1000):= p_path||p_file_name;


 ExcelFontId OLE2.list_type;

BEGIN
  -- Start Excel
  application:=OLE2.CREATE_OBJ('Excel.Application');
  OLE2.SET_PROPERTY(application, 'Visible', 'False');

  -- 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 := 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
  args := OLE2.create_arglist;
  OLE2.add_arg(args, 1);
  OLE2.add_arg(args, col_n);
  cell := OLE2.get_obj_property(worksheet, 'Cells', args);
  OLE2.destroy_arglist(args);
  --cell_value := OLE2.get_char_property(cell, 'Value');
  ExcelFontId := OLE2.get_obj_property(Cell, 'Font');
  OLE2.set_property(ExcelFontId, 'Bold', 'True');
  --------------------------------------------
  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);
  col_n := col_n + 1;
  END LOOP;

  EXIT WHEN NAME_IN('system.last_record') = 'TRUE';
  NEXT_RECORD;
  END LOOP;

  -- 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 );

  -- Save as worksheet with a Specified file path & name.
  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;

  -- 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

exception
 when others then null;
  raise form_trigger_failure;
END;