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 IS NOT INITIAL.
         clear : lv_domain, lv_convexit,lv_function_name .


         IF  gf_fieldcat-ref_fieldname IS NOT INITIAL.
           SELECT SINGLE rollname
                    FROM dd03l
                    INTO lv_domain
                   WHERE tabname   = gf_fieldcat- ref_tabname
                     AND fieldname = gf_fieldcat- ref_fieldname.
         ELSE .
           SELECT SINGLE rollname
                    FROM dd03l
                    INTO lv_domain
                   WHERE tabname   = gf_fieldcat- ref_tabname
                     AND fieldname = gf_fieldcat- fieldname.
         ENDIF .


         SELECT SINGLE convexit
           INTO lv_convexit
           FROM dd01l
          WHERE domname = lv_domain .


         IF lv_convexit IS NOT INITIAL.
        
          data : lv_field type ref to data.
           create data lv_field type ( lv_domain).
           FIELD-SYMBOLS : <fs_field> type any .
           ASSIGN lv_field->* to <fs_field>. 


           CONCATENATE 'CONVERSION_EXIT_'
                       lv_convexit
                       '_INPUT'
                  INTO lv_function_name .


           CALL FUNCTION lv_function_name
             EXPORTING
               input  = idata-value 
             IMPORTING
               output = <fs_field>.




          move <fs_field> to idata -value.
        ENDIF .
       ENDIF .




     ENDLOOP .


     CONDENSE idata-value .
     ASSIGN COMPONENT col OF STRUCTURE gt_upload TO <fs>. 
     MOVE idata- value TO <fs>. 


     AT END OF row.
       IF gt_upload IS INITIAL. 
         EXIT .
       ELSE .


         APPEND gt_upload.
         CLEAR gt_upload.
       ENDIF .
     ENDAT.


   ENDLOOP.




ENDFORM.                    " CONVERT_XLS_ITAB




*&---------------------------------------------------------------------* 
*&      Form  ORNEK_SABLON 
*&---------------------------------------------------------------------* 
*----------------------------------------------------------------------* 
FORM ornek_sablon . 


* start Excel
   CREATE OBJECT h_excel 'EXCEL.APPLICATION' .
   PERFORM err_hdl. 
   SET PROPERTY OF h_excel  'Visible' = 1. 


* get list of workbooks, initially empty 
   CALL METHOD OF h_excel 'Workbooks' = h_mapl. 


* add a new workbook 
   CALL METHOD OF h_mapl 'Add' = h_map. 


* output column headings to active Excel sheet 


   LOOP AT gt_fieldcat INTO gf_fieldcat.
     PERFORM fill_cell USING 1 
                             gf_fieldcat -col_pos
                             1
                             gf_fieldcat -seltext_l. 
   ENDLOOP.




* disconnect from Excel 
   FREE OBJECT h_excel. 




ENDFORM.                    " ORNEK_SABLON




*&---------------------------------------------------------------------* 
*&      Form  FILL_CELL 
*&---------------------------------------------------------------------* 
FORM fill_cell  USING   i j bold val.
   CALL METHOD OF h_excel 'Cells' = h_zl
   EXPORTING
     #1 = i
     #2 = j. 
   SET PROPERTY OF h_zl 'Value' = val . 
   GET PROPERTY OF h_zl 'Font' = h_f. 
   SET PROPERTY OF h_f 'Bold' = bold . 
ENDFORM.                    " FILL_CELL


*&---------------------------------------------------------------------* 
*&      Form  ERR_HDL 
*&---------------------------------------------------------------------* 
FORM err_hdl . 
   IF sy- subrc <> 0. 
     WRITE: / 'OLE de hata:', sy -subrc. 
     STOP.
   ENDIF.
ENDFORM.                    " ERR_HDL


*&---------------------------------------------------------------------* 
*&      Form  SHOW_DATA 
*&---------------------------------------------------------------------* 
FORM show_data . 




   gl_layout-colwidth_optimize = 'X'.
*  gl_layout-box_fieldname = 'SEL'. 




* Event Tanımları. 
   PERFORM event USING 'PF_STATUS_SET'
         'ALV_STATUS' .
   PERFORM event USING 'USER_COMMAND'
         'ALV_COMMAND' .


   CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
   EXPORTING
     i_callback_program = gc_program_name
     is_layout          = gl_layout
     it_fieldcat        = gt_fieldcat[]
     it_events          = gt_event
   TABLES
     t_outtab           = gt_upload .




ENDFORM.                    " SHOW_DATA


*&--------------------------------------------------------------------- 
*&      Form  EVENT 
*&--------------------------------------------------------------------- 


FORM event  USING    p_event
       p_form. 


   gwa_event-name = p_event.
   gwa_event-form = p_form.
   APPEND gwa_event TO gt_event.


ENDFORM.                    " EVENT


*&--------------------------------------------------------------------- 
*&      Form  ALV_STATUS 
*&--------------------------------------------------------------------- 
FORM alv_status USING x .


   SET PF-STATUS 'ALV_STATUS'. 


ENDFORM.                    "alv_status
*&--------------------------------------------------------------------- 
*&      Form  ALV_COMMAND 
*&--------------------------------------------------------------------- 
FORM alv_command USING p_ucomm    LIKE sy -ucomm
       p_selfield TYPE slis_selfield.




   CASE p_ucomm. 
     WHEN 'KAYDET'. 
       PERFORM save_all.
   ENDCASE.


ENDFORM.                    "alv_command
*&---------------------------------------------------------------------* 
*&      Form  SAVE_ALL 
*&---------------------------------------------------------------------* 
FORM save_all . 




   CLEAR: gt_return ,gt_return[], 
   lt_return,lt_return[]. 








   PERFORM show_messages . 


ENDFORM.                    " SAVE_ALL
*&---------------------------------------------------------------------* 
*&      Form  SHOW_MESSAGES 
*&---------------------------------------------------------------------* 
FORM show_messages . 


   DATA : lt_pop TYPE bapirettab, 
         ls_pop TYPE bapiret2.
   CLEAR : ls_pop , lt_pop[], lt_pop.




   APPEND LINES OF gt_return TO lt_pop.


   CALL FUNCTION 'OXT_MESSAGE_TO_POPUP'
   EXPORTING
     it_message = lt_pop
   EXCEPTIONS
     bal_error  = 1
     OTHERS     = 2. 
   IF sy- subrc <> 0. 
     MESSAGE ID sy-msgid TYPE sy- msgty NUMBER sy -msgno
     WITH sy- msgv1 sy-msgv2 sy -msgv3 sy- msgv4.
   ENDIF.


ENDFORM.                    " SHOW_MESSAGES
*&---------------------------------------------------------------------* 
*&      Form  GET_FCAT 
*&---------------------------------------------------------------------* 
FORM get_fcat . 
   CALL FUNCTION 'REUSE_ALV_FIELDCATALOG_MERGE'
   EXPORTING
     i_program_name         = gc_program_name
     i_internal_tabname     = gc_alv_item_table
     i_client_never_display = 'X'
     i_inclname             = gc_program_name
   CHANGING
     ct_fieldcat            = gt_fieldcat
   EXCEPTIONS
     inconsistent_interface = 1
     program_error          = 2 
     OTHERS                 = 3. 
ENDFORM.                    " GET_FCAT




28 views0 comments

Recent Posts

See All
Post: Blog2_Post
bottom of page