Potongan program berikut ini berguna untuk melakukan upload file dalam format excel untuk disimpan dalam internal table
TYPES: BEGIN OF gty_data,
icon TYPE icon_d.
INCLUDE STRUCTURE OF zstructure_excel.
TYPES: END OF gty_data,
gtt_data TYPE TABLE OF gty_data.Penjelasan: deklarasi type data gty_data disarankan include structure excel untuk mempermudah pemindahan data dalam proses yang akan dipaparkan di bawah.
FORM f_ac_xl_to_sap USING p_file TYPE string
CHANGING pct_data TYPE gtt_data.
DATA:
lv_filelength TYPE i,
lv_headerxstring TYPE xstring,
lt_records TYPE solix_tab.
PERFORM f_xlsx2itab USING p_file CHANGING lv_filelength lv_headerxstring lt_records.
PERFORM f_itab2xstring USING lv_filelength lt_records CHANGING lv_headerxstring .
PERFORM f_xstring2data USING lv_headerxstring CHANGING pct_data.
PERFORM f_validation_data CHANGING pct_data.
ENDFORM.penjelasan:
p_file = filename -> silakan merujuk ke post berikut untuk mendapatkan cara membaca filename KLIK DI SINI
gtt_data = tipe data internal table hasil
FORM f_xlsx2itab USING p_file TYPE string
CHANGING pc_filelength TYPE i
pc_headerxstring TYPE xstring
pct_records TYPE solix_tab.
CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
filename = p_file
filetype = 'BIN'
IMPORTING
filelength = pc_filelength
header = pc_headerxstring
TABLES
data_tab = pct_records
EXCEPTIONS
file_open_error = 1
file_read_error = 2
no_batch = 3
gui_refuse_filetransfer = 4
invalid_type = 5
no_authority = 6
unknown_error = 7
bad_data_format = 8
header_not_allowed = 9
separator_not_allowed = 10
header_too_long = 11
unknown_dp_error = 12
access_denied = 13
dp_out_of_memory = 14
disk_full = 15
dp_timeout = 16
OTHERS = 17.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE 'S' NUMBER sy-msgno DISPLAY LIKE 'E'
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
LEAVE LIST-PROCESSING.
ENDIF.
ENDFORM.Penjelasan:
BIN = file bertipe binary
pada bagian ini hasil yang akan digunakan pada proses selanjutnya adalah pc_filelength dan internal table pct_records
FORM f_itab2xstring USING p_filelength TYPE i
pt_records TYPE solix_tab
CHANGING pc_headerxstring TYPE xstring.
CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'
EXPORTING
input_length = p_filelength
IMPORTING
buffer = pc_headerxstring
TABLES
binary_tab = pt_records
EXCEPTIONS
failed = 1
OTHERS = 2.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE 'S' NUMBER sy-msgno DISPLAY LIKE 'E'
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
LEAVE LIST-PROCESSING.
ENDIF.
ENDFORM.Penjelasan: SCMS_BINARY_TO_XSTRING berguna untuk meng-convert data binary yang berada dalam internal table pt_records menjadi format xstring
FORM f_xstring2data USING p_headerxstring TYPE xstring
CHANGING pct_data TYPE gtt_data.
DATA:
lv_filename_str TYPE string,
lo_excel_ref TYPE REF TO cl_fdt_xl_spreadsheet,
lt_worksheets TYPE STANDARD TABLE OF string,
lv_err_msg TYPE string,
lv_idx TYPE sy-index,
lv_waers TYPE waers,
lo_exc TYPE REF TO cx_fdt_excel_core,
lo_data_raw TYPE REF TO data,
ls_data TYPE gty_data,
ls_xlsx TYPE zstructure_excel.
FIELD-SYMBOLS:
<lft_data_raw> TYPE STANDARD TABLE.
TRY .
CREATE OBJECT lo_excel_ref
EXPORTING
document_name = lv_filename_str
xdocument = p_headerxstring.
IF lo_excel_ref IS NOT INITIAL.
lo_excel_ref->if_fdt_doc_spreadsheet~get_worksheet_names(
IMPORTING
worksheet_names = lt_worksheets
).
ENDIF.
IF lt_worksheets IS NOT INITIAL.
" reading all worksheets
LOOP AT lt_worksheets INTO DATA(lv_worksheet).
lv_idx = 5. " initial row
lo_data_raw = lo_excel_ref->if_fdt_doc_spreadsheet~get_itab_from_worksheet( lv_worksheet ).
ASSIGN lo_data_raw->* TO <lft_data_raw>.
IF sy-subrc EQ 0.
TRANSLATE lv_worksheet TO UPPER CASE.
LOOP AT <lft_data_raw> ASSIGNING FIELD-SYMBOL(<lfs_data_raw>) FROM lv_idx.
APPEND INITIAL LINE TO pct_data ASSIGNING FIELD-SYMBOL(<lfs_data>).
DO.
ASSIGN COMPONENT sy-index OF STRUCTURE <lfs_data_raw> TO FIELD-SYMBOL(<lfs_raw>).
IF sy-subrc NE 0.
EXIT.
ELSE.
ASSIGN COMPONENT sy-index OF STRUCTURE ls_xlsx TO FIELD-SYMBOL(<lfs_dest>).
" converting value
CASE sy-index.
WHEN 4. " insert leading zero
<lfs_dest> = |{ <lfs_raw> ALPHA = IN }|.
WHEN 11 OR 12. " date
PERFORM f_convert_date USING <lfs_raw> CHANGING <lfs_dest>.
WHEN 21. " UoM
PERFORM f_convert_uom_in USING <lfs_raw> CHANGING <lfs_dest>.
WHEN 31 OR 32. " Currency
ASSIGN COMPONENT 7 OF STRUCTURE ls_xlsx TO FIELD-SYMBOL(<lfs_waers>). " column 7 of excel is currency key
IF <lfs_waers> IS INITIAL.
<lfs_waers> = lv_waers.
ELSE.
lv_waers = <lfs_waers>.
ENDIF.
PERFORM f_convert_curr_in USING <lfs_raw> <lfs_waers> CHANGING <lfs_dest>.
WHEN OTHERS.
<lfs_dest> = <lfs_raw>.
ENDCASE.
ENDIF.
ENDDO.
MOVE-CORRESPONDING ls_xlsx TO <lfs_data>.
ENDLOOP.
ENDIF.
ENDLOOP.
ENDIF.
CATCH cx_fdt_excel_core INTO lo_exc.
lv_err_msg = lo_exc->get_text( ).
MESSAGE lv_err_msg TYPE 'S' DISPLAY LIKE 'E'.
LEAVE LIST-PROCESSING.
ENDTRY.
ENDFORM.Penjelasan: subroutine ini akan membaca satu per satu sheet dari excel yang diupload untuk disimpan ke dalam internal table pct_data.
NOTES: susunan antara structure workarea excel dan kolom excel file yang di-upload harus sama
FORM f_validation_data CHANGING pct_data TYPE gtt_data.
LOOP AT pct_data ASSIGNING FIELD-SYMBOL(<lfs_data>).
IF <lfs_data>-status = 'ERROR'.
<lfs_data>-remarks = 'ERROR DATA'.
ls_grp_h-icon = icon_red_light.
CONTINUE.
ENDIF.
ENDLOOP.
ENDFORM.Penjelasan: subroutine ini berfungsi untuk memvalidasi data sebelum diolah lebih lanjut
FORM f_convert_date USING p_date_string TYPE string CHANGING pc_date TYPE datum .
DATA: lv_convert_date(10) TYPE c.
CHECK p_date_string IS NOT INITIAL.
lv_convert_date = p_date_string .
"date format YYYY/MM/DD
FIND REGEX '^\d{4}[/|-]\d{1,2}[/|-]\d{1,2}$' IN lv_convert_date ##REGEX_POSIX.
IF sy-subrc = 0.
CALL FUNCTION '/SAPDMC/LSM_DATE_CONVERT'
EXPORTING
date_in = lv_convert_date
date_format_in = 'DYMD'
to_output_format = ' '
to_internal_format = 'X'
IMPORTING
date_out = lv_convert_date
EXCEPTIONS
illegal_date = 1
illegal_date_format = 2
no_user_date_format = 3
OTHERS = 4.
ELSE.
" date format DD/MM/YYYY or DD-MM-YYYY or DD.MM.YYYY
FIND REGEX '^\d{1,2}[/|-|.]\d{1,2}[/|-|.]\d{4}$' IN lv_convert_date ##REGEX_POSIX.
IF sy-subrc = 0.
CALL FUNCTION '/SAPDMC/LSM_DATE_CONVERT'
EXPORTING
date_in = lv_convert_date
date_format_in = 'DDMY'
to_output_format = ' '
to_internal_format = 'X'
IMPORTING
date_out = lv_convert_date
EXCEPTIONS
illegal_date = 1
illegal_date_format = 2
no_user_date_format = 3
OTHERS = 4.
ENDIF.
ENDIF.
IF sy-subrc = 0.
pc_date = lv_convert_date .
ELSE.
CLEAR pc_date.
ENDIF.
ENDFORM.Penjelasan: subroutine ini untuk meng-convert date berformat DD.MM.YYYY menjadi YYYYMMDD (format datum SAP)
FORM f_convert_uom_in USING p_in
CHANGING pc_out.
CALL FUNCTION 'CONVERSION_EXIT_CUNIT_INPUT'
EXPORTING
input = p_in
IMPORTING
output = pc_out
EXCEPTIONS
unit_not_found = 1
OTHERS = 2.
IF sy-subrc <> 0.
* Implement suitable error handling here
ENDIF.
ENDFORM.Penjelasan: subroutine ini berfungsi untuk meng-convert nilai UoM external menjadi nilai UoM internal
FORM f_convert_curr_in USING p_in
p_waers
CHANGING pc_out.
IF p_waers EQ 'IDR'.
pc_out = p_in / 100.
ELSE.
pc_out = p_in.
ENDIF.
ENDFORM.Penjelasan: subroutine ini berfungsi untuk meng-convert nilai amount currency external menjadi nilai amount internal