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