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