Pasting Excel-data to a forms-block

8. Mai 2011 , Geschrieben von Andreas Weiden Veröffentlicht in #Forms

The task

There was a thread in the Forms-forum these days dealing with Copy&Paste-problems, especially with the question if its possible to copy whole rows into a forms-block. As this is structured
data and. of course, forms doesn't know how do deal with that structured data "automatically". But there is a way to deal with that problem using standard PL/SQL-methods.

Example

Lets say that some manager has entered data about new employees in an excel-sheet. And now the manager wants to transfer that data to the "legay-system"without having to type everything again. So
i use a tabular form based on the EMPLOYEES-table from the demo-schema.

How to do it

When you have a closer look about what gets pasted into the clipboard when you have copied some rows and columns from excel. The data is pure "textdata", each row is delimited by a CHR(10).
In each row, the different cell-values are separated by a CHR(9). So. if we know the stucture of the excel-data, its quite easy to slice it down to the differents rows and columns and transfer
the data to forms-items.

As we have to do some "special work" on pasting, i start the pasting-action using a button.

Here's the code behing the "Paste"-Button

DECLARE
  vcBuffer VARCHAR2(32000);
  vcLine   VARCHAR2(32000);
  VARCHAR2(4000);

  FUNCTION FK_SPLIT(io_vcText IN OUT VARCHAR2, i_vcSplitChar IN VARCHAR2)
  RETURN VARCHAR2 IS
    iPos     PLS_INTEGER:=NVL(INSTR(io_vcText, i_vcSplitChar),0);
    vcResult VARCHAR2(4000);
  BEGIN
    IF iPos>0 THEN    
      vcResult:=SUBSTR(io_vcText, 1, iPos-1);     
      io_vcText:=SUBSTR(io_vcText, iPos+1);  
    ELSE     
      vcResult:=io_vcText;     
      io_vcText:=NULL;  
    END IF;   
    RETURN vcResult; 
  END;
 
  BEGIN     
    GO_ITEM('BL_CONTROL.IT_PASTE'); 
    PASTE_REGION; 
    GO_BLOCK('EMPLOYEES'); 
    vcBuffer:=:BL_CONTROL.IT_PASTE; 
    LOOP
      EXIT WHEN vcBuffer IS NULL;   
      -- Next line   
      vcLine:=FK_SPLIT(vcBuffer, CHR(10));   
      IF :SYSTEM.RECORD_STATUS!='NEW' THEN     
        CREATE_RECORD;   
      END IF;   
      :EMPLOYEES.EMPLOYEE_ID:=TO_NUMBER(FK_SPLIT(vcLine, CHR(9)));   
      :EMPLOYEES.FIRST_NAME:=FK_SPLIT(vcLine,CHR(9));   
      :EMPLOYEES.LAST_NAME:=FK_SPLIT(vcLine, CHR(9));   
      :EMPLOYEES.EMAIL:=FK_SPLIT(vcLine, CHR(9));   
      :EMPLOYEES.HIRE_DATE:=TO_DATE(FK_SPLIT(vcLine, CHR(9)), 'DD.MM.YYYY'); 
    END LOOP;
  END;
END;

Of course, this dummy-Item :BL_CONTROL.IT_PASTE on the canvas could be made to have a size of (0,0) in a "real" application.

Diesen Post teilen

Repost0