Skip to Content
Author's profile photo Kiran Kumar Valluru

Multi Copy/Paste from excel to Web Dynpro ABAP

Introduction

This document helps how to use multi copy/paste from excel to Web Dynpro ABAP. In earlier versions of Netweaver ( before Netweaver 7.0 Ehp3 ), there is no provision to paste the clip board data to Web Dynpro ABAP. When you copy data from excel and paste in Web Dynpro ABAP Table, the data will be pasted only in the pasted cell.

From Netweaver 7.0 Ehp3, we have an event onTablePaste for input field, where we can handle the Pasted clipboard data.

Prerequisites

SAP NetWeaver 700 Ehp3.


Step by Step Process

Step 1: Create a Web Dynpro Component.

Go to the SE80 transaction and create a Web Dynpro Component.

/wp-content/uploads/2013/02/1_190086.jpg

Enter Description and click on OK.

/wp-content/uploads/2013/02/2_190087.jpg

Save and Activate the Component.

/wp-content/uploads/2013/02/3_190090.jpg

Step 2: Data Binding

Go to the Context tab of Main View and create a node STUDENT.

/wp-content/uploads/2013/02/4_190095.jpg

Enter Node name, select cardinality 0..n and click on OK.

/wp-content/uploads/2013/02/51_190100.jpg

Now right click on Context node STUDENT and create an attribute ID of type NUMC10.

/wp-content/uploads/2013/02/6_190117.jpg

/wp-content/uploads/2013/02/7_190121.jpg

Similarly create attributes: FNAME of type STRING, LNAME of type STRING and DOB of type DATS.

/wp-content/uploads/2013/02/8_190122.jpg

Step 3: Layout Design.

   Now Go to Layout tab, and click on Web Dynpro Code Wizard( magic symbol button).

/wp-content/uploads/2013/02/91_190123.jpg

Now double click on Table to create and Bind Table UI.

/wp-content/uploads/2013/02/101_190127.jpg

Click on context and select the STUDENT node.

/wp-content/uploads/2013/02/102_190128.png

Change the Standard cell editor to ‘Input Filed’ .and click on OK.

/wp-content/uploads/2013/02/11_190129.jpg

Now we can see the Table UI in the layout.

/wp-content/uploads/2013/02/121_190563.png

Now select the ID input filed table cell editor, under properties click on create event for onTablePaste.


Enter Action name and description and click on OK.

/wp-content/uploads/2013/02/14_190569.jpg

Now go to methods Tab of MAIN view, and enter the below code in WDDOINIT method

WDDOINIT
METHOD WDDOINIT .

   DATA lo_nd_student TYPE REF TO if_wd_context_node.
   DATA lt_student    TYPE wd_this->elements_student.

   lo_nd_student = wd_context->get_child_node( name = wd_this->wdctx_student ).
*  Create empty rows to insert data
   DO 10 TIMES.
     APPEND INITIAL LINE TO lt_student.
   ENDDO.

   lo_nd_student->bind_table( new_items = lt_student set_initial_elements = abap_true ).

ENDMETHOD.

Now double click on ONACTIONPASTEDATA method and create the parameters:

1. CONTEXT_ELEMENT   Type REF TO IF_WD_CONTEXT_ELEMENT

2. ID                                       Type                 STRING

3. TABLE                               Type                 WDUI_TABLE_PASTE_DATA

/wp-content/uploads/2013/02/15_190584.jpg

Enter the below code in ONACTIONPASTEDATA method

ONACTIONPASTEDATA

METHOD ONACTIONPASTEDATA .

     DATA lo_nd_student TYPE REF TO if_wd_context_node.
     DATA lt_student    TYPE wd_this->elements_student.
     DATA ls_cell       LIKE LINE OF table.
     DATA lv_row_number TYPE i VALUE 1.
     DATA lv_paste_row  TYPE i.
     DATA lv_cur_column TYPE i.
     DATA lr_rtti       TYPE REF TO cl_abap_typedescr.
     DATA lcx_dyn_check TYPE REF TO cx_dynamic_check.
     DATA lt_attributes TYPE string_table.
     DATA lv_col_name   TYPE string.

     FIELD-SYMBOLS <table_row> LIKE LINE OF lt_student.
     FIELD-SYMBOLS <table_col> TYPE data.

     lo_nd_student = wd_context->get_child_node( name = wd_this->wdctx_student ).
     lo_nd_student->get_static_attributes_table( IMPORTING table = lt_student ).
*   Get row number at which the data is pasted
     lv_paste_row  = context_element->get_index( ).
*   Get attributes of the Context Node
     lt_attributes = lo_nd_student->get_node_info( )->get_attribute_names( ).

*  Convert Column Data to Row and set to the Context
     LOOP AT lt_student ASSIGNING <table_row> FROM lv_paste_row.
       lv_cur_column = 1.
       LOOP AT table INTO ls_cell WHERE row = lv_row_number.
         IF lv_cur_column > lines( lt_attributes ).
           EXIT.
         ENDIF.
         READ TABLE lt_attributes INTO lv_col_name INDEX lv_cur_column.
         ASSERT sysubrc = 0.
         ASSIGN COMPONENT lv_col_name OF STRUCTURE <table_row> TO <table_col>.
         lr_rtti = cl_abap_typedescr=>describe_by_data( <table_col> ).
         CASE lr_rtti->type_kind.
           WHEN cl_abap_typedescr=>typekind_decfloat16.
             REPLACE ALL OCCURRENCES OF ‘,’ IN ls_celldata WITH ‘.’.
             TRY.
                 <table_col> = ls_celldata.
               CATCH cx_sy_conversion_no_number cx_sy_move_cast_error INTO lcx_dyn_check.
                 ” Paste not possible, report warning
                 wd_this->wd_get_api( )->get_message_manager( )->report_warning

                                                                                             lcx_dyn_check->get_longtext( ) ).
             ENDTRY.
           WHEN cl_abap_typedescr=>typekind_date.
             CALL FUNCTION ‘CONVERT_DATE_TO_INTERNAL’  ” Convert date to internal
               EXPORTING
                 date_external            = ls_celldata
               IMPORTING
                 date_internal            = <table_col>
               EXCEPTIONS
                 date_external_is_invalid = 1
                 OTHERS                  = 2.
             IF sysubrc <> 0.
               wd_this->wd_get_api( )->get_message_manager( )->report_warning( ‘Invalid Date Format’ ).
             ENDIF.
           WHEN OTHERS.
             TRY.
                 <table_col> = ls_celldata.
               CATCH cx_sy_conversion_no_number cx_sy_move_cast_error INTO lcx_dyn_check.
                 ” Paste not possible, report warning
                 wd_this->wd_get_api( )->get_message_manager( )->report_warning(

                                                                                              lcx_dyn_check->get_longtext( ) ).
             ENDTRY.
         ENDCASE.

         ADD 1 TO lv_cur_column. ” Move to next Column
       ENDLOOP.
       ADD 1 TO lv_row_number. ” Move to next Row
     ENDLOOP.

*  Bind the Pasted data to the table
   lo_nd_student->bind_table( lt_student ).

ENDMETHOD.

Now Save and Activate the Web Dynpro Component.

Create Application.

Create Web Dynpro Application and save it.

/wp-content/uploads/2013/02/122_190586.jpg

Enter description, click on OK and save the application.

/wp-content/uploads/2013/02/123_190587.jpg

Now right click on web dynpro application and click on Test.

Now copy the data from Excel and paste it in the table

/wp-content/uploads/2013/02/126_190596.jpg

Paste it in the Table UI

/wp-content/uploads/2013/02/127_190902.jpg

Now you can see all the data got pasted in Table UI ( not only in the visible rows )

/wp-content/uploads/2013/02/128_190903.jpg

/wp-content/uploads/2013/02/129_190910.jpg

Explanation.

When you paste data in the Table UI (Press CTRL +V ), the onActionPasteData method will be called and the Pasted Data will be passed to the Parameter TABLE of the onActionPasteData event handler method in the form of columnar data.

here is the debugger snapshot of the TABLE ( pasted data )

/wp-content/uploads/2013/02/130_190911.jpg

So, we have to convert this column data to Row data. For this I have written the code in onActionPasteData method to convert the column data to row data and set to the table UI.


Here, We have created 10 empty rows in the table UI and pasted 10 records from excel. Now if we paste 15 records from excel, only first 10 records will be pasted to the table UI as we have created only 10 rows.

Copy 15 records from excel

/wp-content/uploads/2013/02/131_190922.jpg

Paste in the Table UI

/wp-content/uploads/2013/02/127_190902.jpg

Now we can see only 10 records got pasted in the Table UI. ( this is because we are looping the table UI and dynamically setting the pasted data)

/wp-content/uploads/2013/02/132_190924.jpg

Inorder to paste all the copied data, we will write a simple code in the onActionPasteData method.

write the below code in the onActionPasteData method to paste all the copied data.

ONACTIONPASTEDATA

METHOD ONACTIONPASTEDATA .

     DATA lo_nd_student      TYPE REF TO if_wd_context_node.
     DATA lt_student         TYPE wd_this->elements_student.
     DATA ls_cell            LIKE LINE OF table.
     DATA lv_row_number      TYPE i VALUE 1.
     DATA lv_paste_row       TYPE i.
     DATA lv_cur_column      TYPE i.
     DATA lr_rtti            TYPE REF TO cl_abap_typedescr.
     DATA lcx_dyn_check      TYPE REF TO cx_dynamic_check.
     DATA lt_attributes      TYPE string_table.
     DATA lv_col_name        TYPE string.
     DATA lv_copied_rows     TYPE i.
     DATA lv_total_tab_rows  TYPE i.
     DATA lv_diff_copy_paste TYPE i.
     DATA ls_table           TYPE wdui_table_line_paste_data.

     FIELD-SYMBOLS <table_row> LIKE LINE OF lt_student.
     FIELD-SYMBOLS <table_col> TYPE data.

     lo_nd_student = wd_context->get_child_node( name = wd_this->wdctx_student ).
     lo_nd_student->get_static_attributes_table( IMPORTING table = lt_student ).
*   Get row number at which the data is pasted
     lv_paste_row  = context_element->get_index( ).
*   Get attributes of the Context Node
     lt_attributes = lo_nd_student->get_node_info( )->get_attribute_names( ).

*   Get total copied rows
     lv_copied_rows = lines( table ).
     READ TABLE table INTO ls_table INDEX lv_copied_rows.
     CLEAR lv_copied_rows.
     lv_copied_rows = ls_tablerow.

*   Get total rows in table UI
     lv_total_tab_rows = lines( lt_student ).

*   Create extra rows in Table UI if pasted records are more that the table records
     IF lv_copied_rows > lv_total_tab_rows.
       lv_diff_copy_paste = lv_copied_rows lv_total_tab_rows.
       DO lv_diff_copy_paste TIMES.
         APPEND INITIAL LINE TO lt_student.
       ENDDO.
     ENDIF.

*  Convert Column Data to Row and set to the Context
     LOOP AT lt_student ASSIGNING <table_row> FROM lv_paste_row.
       lv_cur_column = 1.
       LOOP AT table INTO ls_cell WHERE row = lv_row_number.
         IF lv_cur_column > lines( lt_attributes ).
           EXIT.
         ENDIF.
         READ TABLE lt_attributes INTO lv_col_name INDEX lv_cur_column.
         ASSERT sysubrc = 0.
         ASSIGN COMPONENT lv_col_name OF STRUCTURE <table_row> TO <table_col>.
         lr_rtti = cl_abap_typedescr=>describe_by_data( <table_col> ).
         CASE lr_rtti->type_kind.
           WHEN cl_abap_typedescr=>typekind_decfloat16.
             REPLACE ALL OCCURRENCES OF ‘,’ IN ls_celldata WITH ‘.’.
             TRY.
                 <table_col> = ls_celldata.
               CATCH cx_sy_conversion_no_number cx_sy_move_cast_error INTO lcx_dyn_check.
                 ” Paste not possible, report warning
                 wd_this->wd_get_api( )->get_message_manager( )->report_warning(

                                                                                                lcx_dyn_check->get_longtext( ) ).
             ENDTRY.
           WHEN cl_abap_typedescr=>typekind_date.
             CALL FUNCTION ‘CONVERT_DATE_TO_INTERNAL’  ” Convert date to internal
               EXPORTING
                 date_external            = ls_celldata
               IMPORTING
                 date_internal            = <table_col>
               EXCEPTIONS
                 date_external_is_invalid = 1
                 OTHERS                  = 2.
             IF sysubrc <> 0.
               wd_this->wd_get_api( )->get_message_manager( )->report_warning( ‘Invalid Date Format’ ).
             ENDIF.
           WHEN OTHERS.
             TRY.
                 <table_col> = ls_celldata.
               CATCH cx_sy_conversion_no_number cx_sy_move_cast_error INTO lcx_dyn_check.
                 ” Paste not possible, report warning
                 wd_this->wd_get_api( )->get_message_manager( )->report_warning(

                                                                                                lcx_dyn_check->get_longtext( ) ).
             ENDTRY.
         ENDCASE.

         ADD 1 TO lv_cur_column. ” Move to next Column
       ENDLOOP.
       ADD 1 TO lv_row_number. ” Move to next Row
     ENDLOOP.

*  Bind the Pasted data to the table
   lo_nd_student->bind_table( lt_student ).

ENDMETHOD.

Now Paste the copied records from excel ( 15 records ).

/wp-content/uploads/2013/02/131_190922.jpg

Paste the copied data to Table UI. Now we can see all the Pasted data(15 records) got paste in Table UI.

/wp-content/uploads/2013/02/133_190935.jpg

Conclusion

Here I demonstrated a simple example on how to use onTablePaste event. In this demo, I created an action for ‘ID’ input field table cell editor. We can create action for all the columns and in which ever column user paste the data, we can set the pasted data to the Table UI. Also this code will paste the data from which row you pasted it. lets say, if you paste the data at 3rd row, from that row the data will be pasted in the Table UI.

We can use this for Forms and Lists also. This makes our life easier.!! no.. no.. SAP makes user’s life easier..

Follow me onGoogle+

Assigned Tags

      34 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Great, thank you very much!

      Author's profile photo manish bisht
      manish bisht

      Nice Article 🙂

      Author's profile photo Aravindan Aravi
      Aravindan Aravi

      Nice Article Kiran !! 🙂

      Author's profile photo Naresh Bammidi
      Naresh Bammidi

      Good article..!

      Author's profile photo Former Member
      Former Member

      Hi Kiran Kumar Valluru

      Can you help me this problem .. 🙁

      http://scn.sap.com/thread/3326808#

      Thank you so much!

      Author's profile photo Former Member
      Former Member

      Hi Kiran,

      We are in EhP5, but i could not see the onTablePaste Method. Is there anything we need to turn on.

      Author's profile photo Kiran Kumar Valluru
      Kiran Kumar Valluru
      Blog Post Author

      Hi siddarth,

      May be you are on ECC 6.0 Ehp5..!! But this is applicable to Netweaver 700 Ehp3 and higher. Please check your SAP Basis release in System -> Status.

      Best Regards,

      Kiran

      Author's profile photo sam p
      sam p

      HI Siddarth,

      i have bellow reqirment , plz help me on this. my mail id : sam15.ibm@gmail.com , plz send a mail .with step by step details,

      Req : if the user enter the date between 20 to 24, webdynpro alv should display with columns : eno,ename,20,21,22,23,24 and shift.

      if the user enter the date 20 to 26 ,webdynpro alv should display with columns : eno,ename,20,21,22,23,24,25,26 and shift.

      i hope u can do . thanks in advance.

      columns display dynamically in webdynpro alv

      sdn.JPG

      Author's profile photo Former Member
      Former Member

      Nice document. Coding part is really good. Thanks for sharing.

      Regards,

      Karthik

      Author's profile photo Chandrashekhar Mahajan
      Chandrashekhar Mahajan

      Nice Document and Thanks to SAP to enable this feature !

      Author's profile photo Guillaume GARCIA
      Guillaume GARCIA

      Hi,

      Is this applicable to ALV? to CTable?

      Or does it only apply to Table?

      Thanks in advance for your answer.

      Best regards,

      Guillaume

      Author's profile photo Kiran Kumar Valluru
      Kiran Kumar Valluru
      Blog Post Author

      Hi,

      As mentioned in the document, it's the property of Input Field. Input field UI has event onTablePaste. You can create input field in Table and also CTable also and this will work.

      Regards,

      Kiran

      Author's profile photo Guillaume GARCIA
      Guillaume GARCIA

      Hi,

      Thx.

      But ALV input field (CL_SALV_WD_UIE_INPUT_FIELD) and "general" input field (CL_WD_INPUT_FIELD) are not the same.

      Best regards,

      Guillaume

      Author's profile photo Matthias Drescher
      Matthias Drescher

      Thank you very much for that doc .... will add it to my favourites 😉

      Author's profile photo Former Member
      Former Member

      Hi Kiran,

      This is really good Document .

      Thanks.

      Uma

      Author's profile photo Former Member
      Former Member

      Hi Kiran,

      Its brilliant, but how did write the above code. Did  you experimented it or i mean did you tried writing the code of you own?

      Thank you,

      Ramesh J.V

      Author's profile photo Kiran Kumar Valluru
      Kiran Kumar Valluru
      Blog Post Author

      Hi,

      Thank You. Yes, I experimented 🙂 Some adopted from standard and I debugged and formed the logic and wrote the code.

      Regards,

      Kiran

      Author's profile photo shitanshu sahai
      shitanshu sahai

      Great article .. Thanks for sharing the knowledge

      Author's profile photo Sumit Naik
      Sumit Naik

      Very Useful tutorial

      Author's profile photo Former Member
      Former Member

      Useful.

      Thanks

      Author's profile photo ABAP Dev
      ABAP Dev

      Hi Kiran,

      I looked into your document. It is very useful for copy paste from excel.

      I m currently working on SAP NetWeaver 7.3 BW system.

      I have a similar requirement in one of my webdynpro.

      But there is no table paste event enabled in the input field . Please advise.

      Thanks,

      ABAP Dev

      Author's profile photo Kiran Kumar Valluru
      Kiran Kumar Valluru
      Blog Post Author

      Hi,

      You should be on Netweaver 7.3 Ehp1 to make use of this. Please import the relevant support pack to use this feature.

      Regards,

      Kiran

      Author's profile photo John K
      John K

      Hi Kiran,

      Nice to know. Thanks for your effort.

      Author's profile photo Former Member
      Former Member

      Hi Kiran,

      thank you very much for sharing this knowledge !

      Regards,

      Saurabh Sharma

      Author's profile photo Tashi Norbu
      Tashi Norbu

      Hi Kiran,

           Is there anything similar for alv?

      Regards,

      Tashi

      Author's profile photo Kiran Kumar Valluru
      Kiran Kumar Valluru
      Blog Post Author

      Unfortunately No. It's the feature for Input field. ALV input field is different and it's not possible for ALV.

      Regards,

      Kiran

      Author's profile photo Amudha Ram
      Amudha Ram

      Hi Kiran,

      Have tried the same..in EHP7 for SAP ERP 6.0. But am unable to get the control to ONACTION paste method while pasting. I am not sure what is causing for this. please help me if you have any input on this..

      Thanks,

      Amudha

      Author's profile photo Kiran Kumar Valluru
      Kiran Kumar Valluru
      Blog Post Author

      Hi,

      You have to create an Event handler method for OnTablePaste action of Input field cell editor.

      Regards,

      Kiran

      Author's profile photo Amudha Ram
      Amudha Ram

      Hi..

      Thanks for your reply..

      have done the same. but the control is not going to that particular method..

      Thanks,

      Amudha

      Author's profile photo Kiran Kumar Valluru
      Kiran Kumar Valluru
      Blog Post Author

      Hi,

      Which browser you are using? Check if the Javascript access is enabled. This event will be called only if the browser enables Javascript access.

      Regards,

      Kiran

      Author's profile photo Former Member
      Former Member

      Superb document. Well explained! you nailed it. Thanks a lot for sharing.

      Cheers,

      Alfred

      Author's profile photo Former Member
      Former Member

      Great! Super article!

      Author's profile photo Former Member
      Former Member

      Brilliant! Thanks for sharing.

      Author's profile photo Agustín Figueredo
      Agustín Figueredo

      Hi,

      I know this is an old thread, but anyway.. We have a similar requisite.

      We have a webdynpro table where users paste some information (numbers) the format should be DEC 16-9 but so many times the info they have contains more decimals. We would like to round the value to the proper format.

      We have added the ontablepaste event for that, and it is working properly but it is only triggered when a table (more than 1 row/column) is pasted.

       

      Is there any other event/option to do the same for only 1 field pasted?

       

      Thanks a lot,

      Kr,

      Agustín.