實用且常用的FN:Upload Excel File Into Internal Table

呼叫主體:

FORM upload_data_to_it_excel .
CALL FUNCTION 'ZIEB_UPLOAD_EXCEL_INTO_ITAB'
EXPORTING
filename = p_path
i_begin_col = 1
i_begin_row = 4
i_end_col = 17
i_end_row = 30000
TABLES
itab = it_excel.

ENDFORM. " UPLOAD_DATA_TO_IT_EXCEL

FN中的import項引數

FILENAME LIKE RLGRAP-FILENAME Local file for upload/download

I_BEGIN_COL TYPE I

I_BEGIN_ROW TYPE I

I_END_COL TYPE I

I_END_ROW TYPE I

FN中的TABLES項引數

ITAB

FN的Source code如下:

FUNCTION ZIEB_UPLOAD_EXCEL_INTO_ITAB .
*"----------------------------------------------------------------------
*"*"Local Interface:
*" IMPORTING
*" REFERENCE(FILENAME) LIKE RLGRAP-FILENAME
*" REFERENCE(I_BEGIN_COL) TYPE I
*" REFERENCE(I_BEGIN_ROW) TYPE I
*" REFERENCE(I_END_COL) TYPE I
*" REFERENCE(I_END_ROW) TYPE I
*" TABLES
*" ITAB
*"----------------------------------------------------------------------
DATA : T_INTERN LIKE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE.
DATA : L_TABIX LIKE SY-TABIX.
DATA : L_BEGCOL TYPE I,
L_BEGROW TYPE I,
L_ENDCOL TYPE I,
L_ENDROW TYPE I,
L_PATH LIKE RLGRAP-FILENAME.

MOVE : I_BEGIN_COL TO L_BEGCOL,
I_BEGIN_ROW TO L_BEGROW,
I_END_COL TO L_ENDCOL,
I_END_ROW TO L_ENDROW,
FILENAME TO L_PATH.

FIELD-SYMBOLS : TYPE ANY.

** upload excel file by standard function
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
FILENAME = L_PATH
I_BEGIN_COL = L_BEGCOL
I_BEGIN_ROW = L_BEGROW
I_END_COL = L_ENDCOL
I_END_ROW = L_ENDROW
TABLES
INTERN = T_INTERN
EXCEPTIONS
INCONSISTENT_PARAMETERS = 1
UPLOAD_OLE = 2
OTHERS = 3.
IF SY-SUBRC <> 0.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.

** 刪除第一筆
DELETE T_INTERN WHERE ROW = 1.

** standard function output internal table convert to...
** get the same row of value into one row split by 'TAB'
REFRESH : ITAB.
SORT T_INTERN BY ROW COL.
LOOP AT T_INTERN.
AT NEW ROW.
L_TABIX = SY-TABIX.
CLEAR : ITAB.
ENDAT.

AT END OF ROW.
DO.
ASSIGN COMPONENT SY-INDEX OF STRUCTURE ITAB TO .
IF SY-SUBRC = 0.
READ TABLE T_INTERN INDEX L_TABIX.
IF SY-SUBRC = 0 AND T_INTERN-COL = SY-INDEX.
= T_INTERN-VALUE.
ADD 1 TO L_TABIX.
ENDIF.
ELSE.
EXIT.
ENDIF.
ENDDO.

APPEND ITAB.
ENDAT.
ENDLOOP.
ENDFUNCTION.

大家可以試用一下，有問題可以在此提問.

[@more@]