top of page

SAP ABAP - Get data from excel to ALV Grid

Updated: Aug 29, 2022





*&---------------------------------------------------------------------* 
*& Report  ZDENEME_SERKAN 
*&
*&---------------------------------------------------------------------* 
*&
*&
*&---------------------------------------------------------------------* 


REPORT zdeneme_serkan. 


TYPE-POOLS: truxs ,slis. 


TABLES : jest , sscrfields. 
CONSTANTS: gc_alv_item_table TYPE slis_tabname
VALUE 'GT_UPLOAD', 
 gc_program_name   LIKE sy-repid
VALUE 'ZDENEME_SERKAN'.


INCLUDE ole2incl. 




DATA: h_excel TYPE ole2_object,        " Excel object
       h_mapl TYPE ole2_object,         " list of workbooks
       h_map TYPE ole2_object,          " workbook
       h_zl TYPE ole2_object,           " cell
       h_f TYPE ole2_object.            " font




DATA : gt_messtab   TYPE TABLE OF bdcmsgcoll WITH HEADER LINE .


DATA: idata      LIKE alsmex_tabline OCCURS 0 WITH HEADER LINE. 
DATA : gt_return TYPE TABLE OF bapiret1 WITH HEADER LINE ,
       lt_return TYPE STANDARD TABLE OF bapiret2
       WITH HEADER LINE .


DATA : BEGIN OF gt_upload OCCURS 0 .
         INCLUDE STRUCTURE likp.
DATA : END OF gt_upload .








DATA : gl_repid     LIKE sy- repid,
       gt_fieldcat  TYPE slis_t_fieldcat_alv,
       gl_layout    TYPE slis_layout_alv,
       gf_fieldcat  LIKE LINE OF gt_fieldcat,
       gwa_flcat    LIKE LINE OF gt_fieldcat,
       gt_event     TYPE slis_t_event,
       gf_event     LIKE LINE OF gt_event,
       gwa_event    LIKE LINE OF gt_event,
       gv_ucomm     LIKE sy-ucomm .








SELECTION-SCREEN BEGIN OF BLOCK b1.
SELECTION-SCREEN SKIP.
PARAMETERS: p_ufile  TYPE  rlgrap- filename,
 p_begcol TYPE i DEFAULT 1 NO -DISPLAY, 
 p_begrow TYPE i DEFAULT 1 NO -DISPLAY, 
 p_endcol TYPE i DEFAULT 100 NO -DISPLAY, 
 p_endrow TYPE i DEFAULT 32000 NO -DISPLAY. 








SELECTION-SCREEN SKIP.
SELECTION-SCREEN SKIP.
SELECTION-SCREEN BEGIN OF LINE .
SELECTION-SCREEN PUSHBUTTON 2 (25) text-137 USER-COMMAND cmd1. 
SELECTION-SCREEN END OF LINE .


SELECTION-SCREEN END OF BLOCK b1.


*&--------------------------------------------------------------------- 
*&             AT  S E L E C T I O N - S C R E E N 
*&--------------------------------------------------------------------- 
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_ufile.
   PERFORM validate_file USING p_ufile.






AT SELECTION-SCREEN.
   IF sscrfields- ucomm = 'CMD1' .
     PERFORM get_fcat.
     PERFORM ornek_sablon.
   ENDIF.






START-OF-SELECTION.
   IF p_ufile IS NOT INITIAL .
     PERFORM get_fcat.
     PERFORM convert_xls_itab.
     PERFORM show_data.
   ELSE.
     MESSAGE 'Excel dosyası seçiniz!' TYPE 'I' .
   ENDIF.




*&---------------------------------------------------------------------* 
*&      Form  VALIDATE_FILE 
*&---------------------------------------------------------------------* 
FORM validate_file  USING    f_file.


   CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
   CHANGING
     file_name     = f_file
   EXCEPTIONS
     mask_too_long = 1
     OTHERS        = 2. 


   IF sy- subrc <> 0. 
     MESSAGE e006(zpstek ). " 'dosya da hata'.
   ENDIF.


ENDFORM.                    " VALIDATE_FILE


*&---------------------------------------------------------------------* 
*&      Form  CONVERT_XLS_ITAB 
*&---------------------------------------------------------------------* 
FORM convert_xls_itab . 


   DATA: col TYPE i.
   FIELD-SYMBOLS <fs> TYPE any .
   DATA : lv_function_name TYPE rs38l_fnam, 
        lv_domain        TYPE dd03l -rollname, 
        lv_convexit      TYPE dd01l -convexit. 




   CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
   EXPORTING
     filename    = p_ufile
     i_begin_col = p_begcol
     i_begin_row = p_begrow
     i_end_col   = p_endcol
     i_end_row   = p_endrow
   TABLES
     intern      = idata.


   DELETE idata WHERE row = '0001' .


   SORT idata BY row col.
*
   LOOP AT idata. 


     MOVE idata- col TO col .




     LOOP AT gt_fieldcat INTO gf_fieldcat WHERE col_pos = col.
       CASE gf_fieldcat-datatype .
         WHEN 'DATS'.
           CONCATENATE idata-value+6 (4) 
                       idata -value+3( 2)
                       idata -value+0( 2)
                  INTO idata -value. 
         WHEN 'TIMS'.
           CONCATENATE idata-value+0 (2) 
                       idata -value+3( 2)
                       idata -value+6( 2)
                  INTO idata -value. 


         WHEN 'CURR' OR 'QUAN' .
           CALL FUNCTION 'HRCM_STRING_TO_AMOUNT_CONVERT'
            EXPORTING 
              string                    = idata- value
              decimal_separator         = ','
              thousands_separator       = '.'
              waers                     = ' '
            IMPORTING 
              betrg                     = idata- value
            EXCEPTIONS 
              convert_error             = 1
              OTHERS                    = 2. 
       ENDCASE .


       IF gf_fieldcat-ref_tabname I