Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2005
    Posts
    1

    Exclamation Unanswered: Displaying data in Excel via Oracle Forms6i

    I have the following code behind a button on an Oracle Form 6i to display the data in an Excel spreadsheet.

    However when the button is pressed to activate the code the whole process is taking too long. So long that the

    program actually aborts half way through the process.

    Please can someone have a look at the following code and explain how I can make the process work faster.

    DECLARE

    -- Declare handles to 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;


    -- Declare handles to OLE argument lists
    args OLE2.LIST_TYPE;
    row_num number := 2;



    CURSOR C1 IS select e.service_unit EngServUnit, st.system_group_code SysGpCode, sgc.meaning Modality,

    e.plan_date PlanDate, sum(am_hours+pm_hours) SumHours from eng_planning e, systems s, system_types st,

    system_group_codes sgc where e.config_id = s.config_id and s.system_type_corporate = st.system_type and

    st.system_group_code = sgc.system_group_code and e.act_type = '20' and e.plan_date >= sysdate group by

    e.service_unit,st.system_group_code,sgc.meaning,e. plan_date;

    -- Declare the PL/SQL variables which will hold the data
    -- returned from the database.
    EngServUnit varchar2(10);
    SysGpCode varchar2(10);
    Modality varchar2(40);
    PlanDate eng_planning.plan_date%TYPE;
    SumHours number;


    BEGIN

    -- Create handle to application object
    application:=OLE2.CREATE_OBJ('Excel.Application');

    -- Create a Workbooks collection and add new Workbook to
    -- Workbooks collection
    workbooks:=OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');
    workbook:=OLE2.INVOKE_OBJ(workbooks,'Add');

    -- Create a Worksheets collection and add new Worksheet to
    -- Worksheets collection
    worksheets:=OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets');
    worksheet:=OLE2.INVOKE_OBJ(worksheets,'Add');

    -- Insert Column Headings

    -- write_column_header;

    args:=ole2.create_arglist;
    ole2.add_arg(args,1);
    ole2.add_arg(args,1);
    cell:=ole2.get_obj_property(worksheet,'Cells',args );
    ole2.destroy_arglist(args);
    ole2.set_property(cell,'Value','ServiceUnit');
    ole2.release_obj(cell);


    -- Fetch each employee record and pass values of employee name
    -- and salary into Excel (employee names in first column of
    -- worksheet and salaries in second column).

    FOR ctr IN C1 LOOP
    -- Create handle to cell in column 1 of appropriate row in
    -- worksheet. (The arguments to the Cells method are the row
    -- number and column number of the cell).
    args:=OLE2.CREATE_ARGLIST;
    OLE2.ADD_ARG(args, row_num);
    OLE2.ADD_ARG(args, 1);
    cell:=OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
    OLE2.DESTROY_ARGLIST(args);

    -- Put value of employee name into this cell
    OLE2.SET_PROPERTY(cell, 'Value', ctr.EngServUnit);
    OLE2.RELEASE_OBJ(cell);

    args:=OLE2.CREATE_ARGLIST;
    OLE2.ADD_ARG(args, row_num);
    OLE2.ADD_ARG(args, 2);
    cell:=OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
    OLE2.DESTROY_ARGLIST(args);
    OLE2.SET_PROPERTY(cell, 'Value', ctr.SysGpCode);
    OLE2.RELEASE_OBJ(cell);

    args:=OLE2.CREATE_ARGLIST;
    OLE2.ADD_ARG(args, row_num);
    OLE2.ADD_ARG(args, 3);
    cell:=OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
    OLE2.DESTROY_ARGLIST(args);
    OLE2.SET_PROPERTY(cell, 'Value', ctr.Modality);
    OLE2.RELEASE_OBJ(cell);

    args:=OLE2.CREATE_ARGLIST;
    OLE2.ADD_ARG(args, row_num);
    OLE2.ADD_ARG(args, 4);
    cell:=OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
    OLE2.DESTROY_ARGLIST(args);
    OLE2.SET_PROPERTY(cell, 'Value', to_char(ctr.PlanDate));
    OLE2.RELEASE_OBJ(cell);

    args:=OLE2.CREATE_ARGLIST;
    OLE2.ADD_ARG(args, row_num);
    OLE2.ADD_ARG(args, 5);
    cell:=OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
    OLE2.DESTROY_ARGLIST(args);
    OLE2.SET_PROPERTY(cell, 'Value', SumHours);
    OLE2.RELEASE_OBJ(cell);

    row_num := row_num + 1;

    END LOOP;

    -- Enable user to view the Excel application to see results.
    OLE2.SET_PROPERTY(application, 'Visible', 'True');

    -- Release all OLE object handles
    OLE2.RELEASE_OBJ(worksheet);
    OLE2.RELEASE_OBJ(worksheets);
    OLE2.RELEASE_OBJ(workbook);
    OLE2.RELEASE_OBJ(workbooks);
    OLE2.RELEASE_OBJ(application);

    END;

    Any help would be appreciated.

    Many thanks

  2. #2
    Join Date
    Nov 2002
    Posts
    272
    What you do looks pretty much the same as how I remember handling things a couple of years ago.
    I'll try and get hold of my source code by contacting some people who still work for that client. May take some time though.

  3. #3
    Join Date
    Nov 2002
    Posts
    272
    I got my old code back, and you're doing it correctly: get a reference to a particular cell, set its value, release it; repeat.

    Do any errors occur when the program aborts?
    How many rows of data do you expect?

  4. #4
    Join Date
    Mar 2009
    Posts
    2
    hi,

    i am using the exact code but when i copy the .fmx to the app server and run using the url i dont get anything. excel doesnt show or anything. what could be the problem. app server is 9i. using forms 6i for the code.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Is excel on the app server. OLE2 runs the EXCEL application be remote control through the OLE engine in excel, it does not write an excel spreadsheet.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Mar 2009
    Posts
    2
    so there is no way to display data in excel? let alone allowing users to save the sheet?

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > what could be the problem. app server is 9i. using forms 6i for the code.
    I could be answering from faulty memory, but I thought that 6i was client/server based forms & 9i was web based forms.

    Even if you can get the file created in the desired format on the 9iAS server,
    you need to get it back to the client PC where EXCEL is installed.

    Some folks run 9iAS on UNIX servers where EXCEL does not exist.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •