10 Replies Latest reply: Jan 16, 2013 2:45 PM by Nel Marcus RSS

    Paste clipboard content into a multiline block (Forms 6i, EBS 11.5.9)

    Jose Aróstegui
      Hi everyone!

      I'd like to paste the content of the clipboard into a multiline block in forms.

      The problem is that we're using forms6i in a EBS 11.5.9 web environment.

      My main goal is:

      1) Select several values with the mouse from an Excel sheet.
      2) Copy them (CTRL+C)
      3) Paste all the values into a multine block of a custom form in the EBS application.

      I've achieved to create a pop up menu with a paste option, but I only get 1 value pasted at a time!!

      Thanks a lot in advance!!
      Jose.
        • 1. Re: Paste clipboard content into a multiline block (Forms 6i, EBS 11.5.9)
          Jose Aróstegui
          Hi! Any clue about this issue??

          Thanks.
          • 2. Re: Paste clipboard content into a multiline block (Forms 6i, EBS 11.5.9)
            François Degrelle
            What you can do, is to paste in a multi-line TextItem, then take any token from it and paste them in the multi-record table in a loop.

            Francois
            • 3. Re: Paste clipboard content into a multiline block (Forms 6i, EBS 11.5.9)
              Jose Aróstegui
              Hi!

              It's my intention, but when I try to paste the content of the Clipboard into an Item (hidden or not) I get FRM-40218 when I use procedure paste_region!! :-(
              • 4. Re: Paste clipboard content into a multiline block (Forms 6i, EBS 11.5.9)
                François Degrelle
                Maybe because the clipboard don't contain single text data.

                Francois
                • 5. Re: Paste clipboard content into a multiline block (Forms 6i, EBS 11.5.9)
                  François Degrelle
                  I have just made the test. created a form with a multi-line text item (4000 char length), created an Excel file with a column filled from 1 to 30 (A1 -> A30), copied the cells, then pasted them in the TextItem without any problem.

                  Francois
                  • 6. Re: Paste clipboard content into a multiline block (Forms 6i, EBS 11.5.9)
                    Arif Khadas
                    Hi,

                    If you need to import loads of data from Excel sheet you can open the Excel file through forms and import all the cell values from the Excel sheet into the respective fields.

                    The following code might help you :


                    DECLARE
                         app                                        OLE2.OBJ_TYPE;
                         workbooks                         OLE2.OBJ_TYPE;
                         workbook                         OLE2.OBJ_TYPE;
                         worksheets                    OLE2.OBJ_TYPE;
                         worksheet                     OLE2.OBJ_TYPE;
                         cell                                    OLE2.OBJ_TYPE;
                         args                                    OLE2.OBJ_TYPE;
                         ctr                                    NUMBER(12);
                         V_VALUE                          VARCHAR2(100);
                         --rec                                        CALC_ROTANA%ROWTYPE;
                         V_FILENAME                    VARCHAR2(256);
                    BEGIN
                         V_FILENAME     := GET_FILE_NAME(File_Filter=> 'XLS Files (*.XLS)|');
                         IF V_FILENAME IS NULL THEN
                              RAISE FORM_TRIGGER_FAILURE;
                         END IF;     
                         SET_APPLICATION_PROPERTY (CURSOR_STYLE,'BUSY');
                         app := OLE2.CREATE_OBJ('Excel.Application');
                         ole2.set_property(app,'Visible','false');
                         workbooks := OLE2.GET_OBJ_PROPERTY(app, 'Workbooks');
                         
                         args := OLE2.CREATE_ARGLIST;
                         ole2.add_arg(args,V_FILENAME);
                         
                         workbook := ole2.GET_OBJ_PROPERTY(workbooks,'Open',args);

                         ole2.destroy_arglist(args);
                         
                         args := OLE2.CREATE_ARGLIST;
                         
                         ole2.add_arg(args,'calc_rotana'); --CONFIG_STRUCTURE');

                         worksheet := ole2.GET_OBJ_PROPERTY(workbook,'Worksheets',args);
                         ole2.destroy_arglist(args);
                         
                         ctr := 2;

                         FIRST_RECORD;

                         LOOP
                              :BL_IMPORT_CONFIG_STRUCTURE.COMPONENT_PART := GET_CELL_VALUE(WORKSHEET,     ARGS,     CELL, CTR, 1);
                              OLE2.RELEASE_OBJ(cell);

                              :BL_IMPORT_CONFIG_STRUCTURE.QTY_PER_ASSEMBLY := GET_CELL_VALUE(WORKSHEET,     ARGS,     CELL, CTR, 2);      
                              OLE2.RELEASE_OBJ(cell);
                              
                              /*
                              :BL_IMPORT_CONFIG_STRUCTURE.ISSUE_TO_LOC := GET_CELL_VALUE(WORKSHEET,     ARGS,     CELL, CTR, 3);
                              OLE2.RELEASE_OBJ(cell);
                              */
                              
                              NEXT_RECORD;
                              
                              V_VALUE     := GET_CELL_VALUE(WORKSHEET,     ARGS,     CELL, CTR, 1);
                              OLE2.RELEASE_OBJ(cell);
                         
                              EXIT WHEN LENGTH (V_VALUE) = 0 OR V_VALUE IS NULL;
                              CTR := CTR + 1;
                         END LOOP;

                         OLE2.INVOKE(app,'Quit');
                         OLE2.RELEASE_OBJ(cell);
                         OLE2.RELEASE_OBJ(worksheet);
                         --OLE2.RELEASE_OBJ(worksheets);
                         OLE2.RELEASE_OBJ(workbook);
                         OLE2.RELEASE_OBJ(workbooks);
                         OLE2.RELEASE_OBJ(app);
                         SET_APPLICATION_PROPERTY (CURSOR_STYLE,'NORMAL');

                         SET_ITEM_PROPERTY ('BL_IMPORT_CONFIG_STRUCTURE.PB_CREATE_CONFIG_STRUCTURE',ENABLED,PROPERTY_TRUE);
                         GO_ITEM ('BL_IMPORT_CONFIG_STRUCTURE.PB_CREATE_CONFIG_STRUCTURE');
                         SET_ITEM_PROPERTY ('BL_IMPORT_CONFIG_STRUCTURE.PB_IMPORT_CONFIG_STRUCT_FILE',ENABLED,PROPERTY_FALSE);

                         EXCEPTION
                              WHEN OTHERS THEN
                                   OLE2.INVOKE(app,'Quit');
                                   OLE2.RELEASE_OBJ(cell);
                                   OLE2.RELEASE_OBJ(worksheet);
                                   --OLE2.RELEASE_OBJ(worksheets);
                                   OLE2.RELEASE_OBJ(workbook);
                                   OLE2.RELEASE_OBJ(workbooks);
                                   OLE2.RELEASE_OBJ(app);
                                   SET_APPLICATION_PROPERTY (CURSOR_STYLE,'NORMAL');
                                   SET_ITEM_PROPERTY ('BL_IMPORT_CONFIG_STRUCTURE.PB_CREATE_CONFIG_STRUCTURE',ENABLED,PROPERTY_TRUE);
                                   GO_ITEM ('BL_IMPORT_CONFIG_STRUCTURE.PB_CREATE_CONFIG_STRUCTURE');
                                   SET_ITEM_PROPERTY ('BL_IMPORT_CONFIG_STRUCTURE.PB_IMPORT_CONFIG_STRUCT_FILE',ENABLED,PROPERTY_FALSE);
                    END;

                    Best Regards

                    Arif Khadas
                    • 7. Re: Paste clipboard content into a multiline block (Forms 6i, EBS 11.5.9)
                      Jose Aróstegui
                      Hi Francois,

                      Your right, but I'm trying to use the procedure paste_region, as I don't want to display a big text area just to paste the text.

                      My intention is to have a button which pastes the content of the clipboard into a non displayed text item, and after that, process the lines, parse the separator character, and move the data to a multi-line block with a for-loop.

                      So, the user would select and copy several cells (10-15) in an Excel sheet, and after that he'd click a new button in the forms which would paste the cells into the multi-line block. I hope I made myself clear about this.

                      Thanks!!!
                      -----------------

                      Hi Arif,

                      Thanks about your suggestion, but I can't use OLE2 as we're in Jinitiator Web environment of the EBS 11.5.9.

                      Really apreciate your help anyway!
                      • 8. Re: Paste clipboard content into a multiline block (Forms 6i, EBS 11.5.9)
                        François Degrelle
                        If you want to paste into it, the multi-line text item must be visible. What you can do is to set its size to 1x1 pixel. Then, in you button, procedd as follow:
                        Declare
                          c varchar2(4000);
                        Begin     
                          go_item('bl.t1'); -- 1*1 pixel text item
                          paste_region;
                          -- then take parts of the multi-line text --
                          c := split(:bl.t1,1,CHR(10));
                          :bl.t2 := c ; -- to put the token in each record text item
                        End;
                        • 9. Re: Paste clipboard content into a multiline block (Forms 6i, EBS 11.5.9)
                          François Degrelle
                          This is working for me:
                          Declare
                               c varchar2(4000);
                               i pls_integer := 1 ;
                          Begin     
                            go_item('bl.t1'); -- 1*1 pixel item to paste
                            paste_region;
                            -- loop through records --
                            go_block('bl2');
                            loop
                              c := split(:bl.t1,i,CHR(10));
                              exit when c is null ;
                              if :system.record_status <> 'NEW' Then
                                    create_record ;
                              end if ;      
                              :bl2.ti := c ;
                              i := i + 1 ;
                            end loop;
                          End;
                          the SPLIT() function:
                            --------------------------
                            --  get a string token  --
                            --------------------------
                               Function Split
                               (
                                  PC$Chaine IN VARCHAR2,         -- input string
                                  PN$Pos IN PLS_INTEGER,         -- token number
                                  PC$Sep IN VARCHAR2 DEFAULT ',' -- separator character
                               )
                               RETURN VARCHAR2
                               IS
                                 LC$Chaine VARCHAR2(32767) := PC$Sep || PC$Chaine ;
                                 LI$I      PLS_INTEGER ;
                                 LI$I2     PLS_INTEGER ;
                               BEGIN
                                 LI$I := INSTR( LC$Chaine, PC$Sep, 1, PN$Pos ) ;
                                 IF LI$I > 0 THEN
                                   LI$I2 := INSTR( LC$Chaine, PC$Sep, 1, PN$Pos + 1) ;
                                   IF LI$I2 = 0 THEN LI$I2 := LENGTH( LC$Chaine ) + 1 ; END IF ;
                                   RETURN( Nvl(SUBSTR( LC$Chaine, LI$I+1, LI$I2 - LI$I-1 ),'^') ) ;
                                 ELSE
                                   RETURN NULL ;
                                 END IF ;
                               END Split;
                          Francois
                          • 10. Re: Paste clipboard content into a multiline block (Forms 6i, EBS 11.5.9)
                            Nel Marcus
                            Francois - I used your code to copy excel values into multi-line form columns. I get the sting '^' copied inot the first field. Is there something wrong that I am doing here?