Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Creating Matrix Report in Excel using OLE2

Status
Not open for further replies.

krindler

Programmer
Jul 16, 2002
39
US
Does anyone know if it is possible to create a Matrix Report in Excel, using OLE2 from Forms 6i?

Any help you could offer me would me much appreciated.

Thanks In Advance!!
 
Hi,

I'm not sure what you mean, if you want create an excel sheet from forms, just create a model sheet and use this code:

This code copy a record group to an excel sheet, it should be modified for your purpose.
Code:
PROCEDURE BUILD_EXCEL_DEMANDE( 
rgName VARCHAR2 -- Record group name of data to write
, vModel VARCHAR2 -- name of the model
, vDest VARCHAR2 -- name of renamed model
) IS

  -- Declare handles to OLE objects
  application ole2.obj_type;
  workbooks   ole2.obj_type;
  workbook    ole2.obj_type;
  worksheet   ole2.obj_type;
--  worksheets  ole2.obj_type;
  cell        ole2.obj_type;
  
  bk_id  			BLOCK;
  col_id 			GroupColumn; 
 
  -- Declare handles to OLE argument lists
  args        ole2.list_type;

  cell_num_value  number;
  Col_number NUMBER;
BEGIN
   	
  --Load d'excel
  application:= ole2.create_obj('Excel.Application');
  Workbooks := OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');
  OLE2.SET_PROPERTY ( application, 'Visible', true );
		
  -- Open model sheet
  begin
    args:= ole2.create_arglist;
    ole2.add_arg(args, vModele);
    workbook := ole2.invoke_obj(workbooks, 'Open', args);
    ole2.destroy_arglist(args);
		
    -- Open the 1st worksheet of that Workbook
    args:= ole2.create_arglist;
    ole2.add_arg(args, 'Sheet1'); -- The name of your sample sheet
    worksheet := OLE2.GET_OBJ_PROPERTY(workbook, 'Sheets', args);
    ole2.destroy_arglist(args);

    -- Work from here
    begin
      -- For each record of the record group
      FOR i IN 1..GET_GROUP_ROW_COUNT(rgName) LOOP 
        Col_number := 1; -- In the sample, begins on 1st column
-- Col1
        args :=    OLE2.CREATE_ARGLIST;
        OLE2.ADD_ARG ( args, 3+i ); -- In the sample, begins on 4th line
        OLE2.ADD_ARG ( args, Col_number ); -- Colonne
        cell :=    OLE2.GET_OBJ_PROPERTY ( worksheet, 'Cells', args ); -- GOTO the cell
        OLE2.DESTROY_ARGLIST ( args ); --
        col_id  := Find_Column(rgName||'.COL1');
        IF id_null(col_id) THEN
	  Message('Error');
          RAISE LOGIN_DENIED;
        END IF;
	OLE2.SET_PROPERTY ( cell, 'Value', GET_GROUP_CHAR_CELL( col_id, i ) ); -- Puts the value
        OLE2.RELEASE_OBJ(cell);
        Col_number := Col_number + 1;
        			
-- do this for each column of your line
       END LOOP;

        -- Release the OLE2 object handles
        ole2.release_obj(worksheet);
        ole2.release_obj(workbook);
        exception
          WHEN LOGIN_DENIED THEN
            RAISE LOGIN_DENIED;
          when others then 
            message('Error opening '||vModel);
        end;

	-- Free and close excel
	ole2.release_obj(workbooks);
	ole2.invoke(application, 'Quit');
	ole2.release_obj(application);
END;
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top