將Excel file上轉到Internal table的方法

1 定義輸入檔案(Local file for upload/download)

SELECTION-SCREEN BEGIN OF BLOCK b2 WITH FRAME TITLE text-002.
PARAMETERS p_file LIKE rlgrap-filename OBLIGATORY MODIF ID fin.
SELECTION-SCREEN END OF BLOCK b2.

2 Search help for get download path from PC

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
PERFORM get_pc_download_path(zbar00001) USING 'X'
CHANGING p_file.
ENDIF.

*&---------------------------------------------------------------------*
*& Search help for get download path form PC
*----------------------------------------------------------------------*
FORM get_pc_download_path USING value(p_value)
CHANGING p_pcfile LIKE rlgrap-filename.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
EXPORTING
static = p_value
CHANGING
file_name = p_pcfile.
ENDFORM. " get_pc_download_path

3 Transfer Excel record to internal table

FORM transfer_to_it_file .
DATA : lt_intern TYPE alsmex_tabline OCCURS 0 WITH HEADER LINE.
DATA : l_row TYPE i,
l_col TYPE i,
l_tabix TYPE i.
CONSTANTS: c_linesize TYPE i VALUE 132.

DATA: l_row_st TYPE i VALUE '2',
l_row_ed TYPE i VALUE '65535',
l_col_st TYPE i VALUE '1',
l_col_ed TYPE i VALUE '15'.

*&fuction to upload excel data into internal table lt_intern
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = p_file
i_begin_col = l_col_st
i_begin_row = l_row_st
i_end_col = l_col_ed
i_end_row = l_row_ed
TABLES
intern = lt_intern
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
OTHERS = 3.

*&if file format is wrong etc., show error message.
IF sy-subrc <> 0.
MESSAGE s240(s#) WITH 'Error upload to internal table!'.
EXIT.
ENDIF.

*&no data in excel file
IF lt_intern[] IS INITIAL.
MESSAGE s240(s#) WITH 'No data be upload!'.
EXIT.
ELSE.
SORT lt_intern BY row col.
LOOP AT lt_intern.
l_row = lt_intern-row.
l_col = lt_intern-col + 1.
l_tabix = sy-tabix.

ASSIGN COMPONENT l_col OF STRUCTURE it_file TO .

= lt_intern-value.

AT END OF row.
* when kpein = 0 have error
IF it_tab-kpein = 0.
it_tab-kpein = 1.
ENDIF.

it_file-zzsite = p_site.
it_file-zzerdat = g_erdat.
it_file-erdat = sy-datum.
it_file-erzet = sy-uzeit.
it_file-zzusdid = g_userid.

* Check DN number and item exists or not.
PERFORM convert_alpha_input USING it_file-vbeln.
PERFORM convert_alpha_input USING it_file-posnr.
PERFORM check_dn USING it_file-vbeln it_file-posnr l_row.
* Check date format
PERFORM check_date USING it_file-exdat l_row.
* Check currency
TRANSLATE it_file-waers TO UPPER CASE.
PERFORM check_currency USING it_file-waers l_row.
* Check amount format
PERFORM check_amount USING it_file-zzamont l_row.
* Check price unit value
PERFORM check_unit USING it_file-kpein l_row.

MOVE-CORRESPONDING it_file TO it_duplicate.
it_duplicate-count = 1.
COLLECT it_duplicate.

IF it_err[] IS INITIAL.
MOVE-CORRESPONDING it_file TO it_tab.
APPEND it_tab.
ENDIF.

CLEAR: it_tab, it_file.
ENDAT.

ENDLOOP.
ENDIF.

*&Check Record duplicate or not
PERFORM check_duplicate_record.

ENDFORM. " transfer_to_it_file

4 Check Record duplicate or not

FORM check_duplicate_record .
DATA : l_count TYPE i,
l_message(50).
CLEAR: l_message.

LOOP AT it_duplicate.
IF it_duplicate-count <> 1.
l_message = 'Records are duplicate'.
PERFORM write_error USING l_message sy-tabix.
ENDIF.
ENDLOOP.

ENDFORM. " check_duplicate_record

[@more@]