Results 1 to 6 of 6

Thread: read excel file

  1. #1
    Join Date
    Jan 2004
    Posts
    31

    Unanswered: read excel file

    hi,
    i have an excel file and
    I want to read that datas from oracle forms
    how can i read excel datas from oracle forms?
    thanks for helps

  2. #2
    Join Date
    Mar 2004
    Posts
    370
    Export your data into an ODBC datasource.Then use some ETL tool such as Microsoft DTS (built in SQL Server 2000) to read data from that datasource into your Oracle table or form!
    -hope this help

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by Paren
    hi,
    i have an excel file and
    I want to read that datas from oracle forms
    how can i read excel datas from oracle forms?
    thanks for helps
    You can also use OLE2. I have example code if you need it.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jan 2004
    Posts
    31

    sample code

    hi,thanks
    if you send example code i will hope..
    regards

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    DECLARE

    -- Declare handles to OLE objects
    application ole2.obj_type;
    workbooks ole2.obj_type;
    workbook ole2.obj_type;
    worksheet ole2.obj_type;
    cell ole2.obj_type;

    -- Declare handles to OLE argument lists
    args ole2.list_type;

    check_file text_io.file_type;
    no_file exception;
    PRAGMA EXCEPTION_INIT (no_file, -302000);

    x_row number;
    RET_VAL NUMBER;
    cnt number;

    -- Declare holders for returned columns
    seg1 varchar2(10);
    seg2 varchar2(10);
    seg3 varchar2(10);
    seg4 varchar2(10);
    seg5 varchar2(10);
    seg6 varchar2(10);
    seg7 varchar2(10);
    segstr varchar2(10);
    segnum varchar2(10);
    period01 number;
    period02 number;
    period03 number;
    period04 number;
    period05 number;
    period06 number;
    period07 number;
    period08 number;
    period09 number;
    period10 number;
    period11 number;
    period12 number;
    period13 number;
    total number;
    totalper number;
    CREDIT VARCHAR2(1);
    ACCTTYPE VARCHAR2(1);
    BEGIN
    if :block_main.budget_name is null then
    ret_val := show_alert('NO_BUDGET');
    GO_ITEM('BUDGET_NAME');
    RETURN;
    END IF;
    -- Check the file can be found, if not exception no_file will be raised
    check_file := TEXT_IO.FOPEN(rtrim(:block_main.file_name),'R');
    TEXT_IO.FCLOSE(check_file);
    SET_APPLICATION_PROPERTY(CURSOR_STYLE, 'BUSY');
    -- Clear out the interface table of any old records
    delete from gl.gl_budget_interface;
    -- Start up Excel
    application:= ole2.create_obj('Excel.Application');
    -- ole2.set_property (application, 'Visible', 'True');
    workbooks := ole2.get_obj_property(application, 'Workbooks');
    -- Open the required workbook
    args:= ole2.create_arglist;
    ole2.add_arg(args, rtrim(:block_main.file_name));
    workbook := ole2.get_obj_property(workbooks, 'Open', args);
    ole2.destroy_arglist(args);

    -- Open worksheet Sheet1 of that Workbook
    args:= ole2.create_arglist;
    ole2.add_arg(args, 'Sheet1');
    worksheet := ole2.get_obj_property(workbook, 'Worksheets', args);
    ole2.destroy_arglist(args);


    -- Get the values
    x_row := 7;
    loop
    :block_main.status := 'Scanning row '||to_char(x_row);
    SYNCHRONIZE;
    args:= ole2.create_arglist;
    ole2.add_arg(args, x_row);
    ole2.add_arg(args, 1);
    cell:= ole2.get_obj_property(worksheet, 'Cells', args);
    ole2.destroy_arglist(args);
    segstr :=ole2.get_char_property(cell, 'Value');
    segnum := to_char(ole2.get_num_property(cell, 'Value'),'00');
    if NVL(length(segstr), 0) = 2 then
    seg1 := RTRIM(segstr);
    else
    seg1 := RTRIM(segnum);
    end if;
    if rtrim(segstr) is null then
    seg1 := null;
    end if;
    args:= ole2.create_arglist;
    ole2.add_arg(args, x_row);
    ole2.add_arg(args, 2);
    cell:= ole2.get_obj_property(worksheet, 'Cells', args);
    ole2.destroy_arglist(args);
    segstr :=ole2.get_char_property(cell, 'Value');
    segnum := to_char(ole2.get_num_property(cell, 'Value'),'00');
    if NVL(length(segstr), 0) = 2 then
    seg2 := RTRIM(segstr);
    else
    seg2 := RTRIM(segnum);
    end if;
    args:= ole2.create_arglist;
    ole2.add_arg(args, x_row);
    ole2.add_arg(args, 3);
    cell:= ole2.get_obj_property(worksheet, 'Cells', args);
    ole2.destroy_arglist(args);
    segstr :=ole2.get_char_property(cell, 'Value');
    segnum := to_char(ole2.get_num_property(cell, 'Value'),'000');
    if NVL(length(segstr), 0) = 3 then
    seg3 := RTRIM(segstr);
    else
    seg3 := RTRIM(segnum);
    end if;
    args:= ole2.create_arglist;
    ole2.add_arg(args, x_row);
    ole2.add_arg(args, 4);
    cell:= ole2.get_obj_property(worksheet, 'Cells', args);
    ole2.destroy_arglist(args);
    segstr :=ole2.get_char_property(cell, 'Value');
    segnum := to_char(ole2.get_num_property(cell, 'Value'),'000');
    if NVL(length(segstr), 0) = 3 then
    seg4 := RTRIM(segstr);
    else
    seg4 := RTRIM(segnum);
    end if;
    args:= ole2.create_arglist;
    ole2.add_arg(args, x_row);
    ole2.add_arg(args, 5);
    cell:= ole2.get_obj_property(worksheet, 'Cells', args);
    ole2.destroy_arglist(args);
    segstr :=ole2.get_char_property(cell, 'Value');
    segnum := to_char(ole2.get_num_property(cell, 'Value'),'0000');
    if NVL(length(segstr), 0) = 4 then
    seg5 := RTRIM(segstr);
    else
    seg5 := RTRIM(segnum);
    end if;
    args:= ole2.create_arglist;
    ole2.add_arg(args, x_row);
    ole2.add_arg(args, 6);
    cell:= ole2.get_obj_property(worksheet, 'Cells', args);
    ole2.destroy_arglist(args);
    seg6 :=ole2.get_char_property(cell, 'Value');
    args:= ole2.create_arglist;
    ole2.add_arg(args, x_row);
    ole2.add_arg(args, 7);
    cell:= ole2.get_obj_property(worksheet, 'Cells', args);
    ole2.destroy_arglist(args);
    segstr :=ole2.get_char_property(cell, 'Value');
    segnum := to_char(ole2.get_num_property(cell, 'Value'),'000');
    if NVL(length(segstr), 0) = 3 then
    seg7 := RTRIM(segstr);
    else
    seg7 := RTRIM(segnum);
    end if;
    args:= ole2.create_arglist;
    ole2.add_arg(args, x_row);
    ole2.add_arg(args, 9);
    cell:= ole2.get_obj_property(worksheet, 'Cells', args);
    ole2.destroy_arglist(args);
    total := ole2.get_num_property(cell, 'Value');


    args:= ole2.create_arglist;
    ole2.add_arg(args, x_row);
    ole2.add_arg(args, 10);
    cell:= ole2.get_obj_property(worksheet, 'Cells', args);
    ole2.destroy_arglist(args);
    period01 := round(ole2.get_num_property(cell, 'Value'),2);
    args:= ole2.create_arglist;
    ole2.add_arg(args, x_row);
    ole2.add_arg(args, 11);
    cell:= ole2.get_obj_property(worksheet, 'Cells', args);
    ole2.destroy_arglist(args);
    period02 := round(ole2.get_num_property(cell, 'Value'),2);
    args:= ole2.create_arglist;
    ole2.add_arg(args, x_row);
    ole2.add_arg(args, 12);
    cell:= ole2.get_obj_property(worksheet, 'Cells', args);
    ole2.destroy_arglist(args);
    period03 := round(ole2.get_num_property(cell, 'Value'),2);
    args:= ole2.create_arglist;
    ole2.add_arg(args, x_row);
    ole2.add_arg(args, 13);
    cell:= ole2.get_obj_property(worksheet, 'Cells', args);
    ole2.destroy_arglist(args);
    period04 := round(ole2.get_num_property(cell, 'Value'),2);
    args:= ole2.create_arglist;
    ole2.add_arg(args, x_row);
    ole2.add_arg(args, 14);
    cell:= ole2.get_obj_property(worksheet, 'Cells', args);
    ole2.destroy_arglist(args);
    period05 := round(ole2.get_num_property(cell, 'Value'),2);
    args:= ole2.create_arglist;
    ole2.add_arg(args, x_row);
    ole2.add_arg(args, 15);
    cell:= ole2.get_obj_property(worksheet, 'Cells', args);
    ole2.destroy_arglist(args);
    period06 := round(ole2.get_num_property(cell, 'Value'),2);
    args:= ole2.create_arglist;
    ole2.add_arg(args, x_row);
    ole2.add_arg(args, 16);
    cell:= ole2.get_obj_property(worksheet, 'Cells', args);
    ole2.destroy_arglist(args);
    period07 := round(ole2.get_num_property(cell, 'Value'),2);
    args:= ole2.create_arglist;
    ole2.add_arg(args, x_row);
    ole2.add_arg(args, 17);
    cell:= ole2.get_obj_property(worksheet, 'Cells', args);
    ole2.destroy_arglist(args);
    period08 := round(ole2.get_num_property(cell, 'Value'),2);
    args:= ole2.create_arglist;
    ole2.add_arg(args, x_row);
    ole2.add_arg(args, 18);
    cell:= ole2.get_obj_property(worksheet, 'Cells', args);
    ole2.destroy_arglist(args);
    period09 := round(ole2.get_num_property(cell, 'Value'),2);
    args:= ole2.create_arglist;
    ole2.add_arg(args, x_row);
    ole2.add_arg(args, 19);
    cell:= ole2.get_obj_property(worksheet, 'Cells', args);
    ole2.destroy_arglist(args);
    period10 := round(ole2.get_num_property(cell, 'Value'),2);
    args:= ole2.create_arglist;
    ole2.add_arg(args, x_row);
    ole2.add_arg(args, 20);
    cell:= ole2.get_obj_property(worksheet, 'Cells', args);
    ole2.destroy_arglist(args);
    period11 := round(ole2.get_num_property(cell, 'Value'),2);
    args:= ole2.create_arglist;
    ole2.add_arg(args, x_row);
    ole2.add_arg(args, 21);
    cell:= ole2.get_obj_property(worksheet, 'Cells', args);
    ole2.destroy_arglist(args);
    period12 := round(ole2.get_num_property(cell, 'Value'),2);
    args:= ole2.create_arglist;
    ole2.add_arg(args, x_row);
    ole2.add_arg(args, 22);
    cell:= ole2.get_obj_property(worksheet, 'Cells', args);
    ole2.destroy_arglist(args);
    period13 := round(ole2.get_num_property(cell, 'Value'),2);
    -- ******** insert code removed ********
    x_row := x_row + 1;
    end loop;
    :block_main.status := null;
    :block_main.file_name := null;
    SYNCHRONIZE;
    ole2.invoke(application,'Quit');

    -- Release the OLE2 object handles
    ole2.release_obj(cell);
    ole2.release_obj(worksheet);
    ole2.release_obj(workbook);
    ole2.release_obj(workbooks);
    ole2.release_obj(application);
    ystem.Message_Level := 25;
    commit;
    ystem.Message_Level := 0;
    SET_APPLICATION_PROPERTY(CURSOR_STYLE, 'DEFAULT');
    RET_VAL := SHOW_ALERT('COMPLETE');
    go_item('file_name');
    EXCEPTION
    WHEN no_file THEN
    ret_val := show_alert('BADFILE');
    go_item('file_name');
    WHEN OTHERS THEN
    ystem.Message_Level := 25;
    rollback;
    ystem.Message_Level := 0;
    MESSAGE(sqlerrm);
    PAUSE;
    FOR i IN 1 .. tool_err.nerrors LOOP
    MESSAGE(tool_err.message);
    PAUSE;
    tool_err.pop;
    END LOOP;
    END;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Jan 2004
    Posts
    31

    hi

    hi beilstwh ,thanks a lot for helps

Posting Permissions

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