Z表數據EXCEL導入

很多項目都有這種需求，雖然別人用的各有不同，不過閑來無事，還是自己搞了一個出來。基于EXCEL的導入。

?

?

*&---------------------------------------------------------------------*
*& Report  ZLY_UPLOAD_TABLE
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*REPORT ZUPLOAD_TABLE.
TABLES:RSRD1.
TYPE-POOLS:ABAP.FIELD-SYMBOLS: <DYN_TABLE> TYPE STANDARD TABLE,<DYN_WA>,<DYN_FIELD>.
DATA: DY_TABLE TYPE REF TO DATA,DY_LINE  TYPE REF TO DATA,XFC      TYPE        LVC_S_FCAT,IFC      TYPE        LVC_T_FCAT.DATA : GT_EXCEL_T TYPE         ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE,GS_EXCEL_T LIKE LINE OF GT_EXCEL_T.
DATA: GC_FIELDVALUE TYPE FIELDVALUE,  "字符值GC_FLDNAME    TYPE FIELDNAME.   "字段名SELECTION-SCREEN:BEGIN OF BLOCK BLK01 WITH FRAME TITLE TEXT-001.
PARAMETERS:P_TBMA TYPE RSRD1-TBMA_VAL,P_FILE LIKE RLGRAP-FILENAME.
SELECTION-SCREEN END OF BLOCK BLK01.AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.PERFORM FRM_GET_FILEPATH.START-OF-SELECTION.CHECK P_TBMA IS NOT INITIAL.CHECK P_FILE IS NOT INITIAL.PERFORM PRM_GET_TAB_STRC.PERFORM PRM_GET_EXCEL.PERFORM PRM_UPDATA_TAB.
*&---------------------------------------------------------------------*
*&      Form  FRM_GET_FILEPATH
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM FRM_GET_FILEPATH .CALL FUNCTION 'WS_FILENAME_GET'EXPORTINGMASK             = ',Excel(*.xls),*.XLS,*.XLSX,'TITLE            = '選擇文件'(100)IMPORTINGFILENAME         = P_FILEEXCEPTIONSINV_WINSYS       = 1NO_BATCH         = 2SELECTION_CANCEL = 3SELECTION_ERROR  = 4OTHERS           = 5.IF SY-SUBRC <> 0."      MESSAGE e100(zdev) WITH '選擇文件出錯！'(007).ENDIF.
ENDFORM.                    " FRM_GET_FILEPATH
*&---------------------------------------------------------------------*
*&      Form  PRM_GET_TAB_STRC
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM PRM_GET_TAB_STRC .DATA:GT_TABLE TYPE DDFIELDS,GS_TABLE TYPE DFIES.CALL FUNCTION 'CATSXT_GET_DDIC_FIELDINFO'EXPORTINGIM_STRUCTURE_NAME = P_TBMAIMPORTINGEX_DDIC_INFO      = GT_TABLEEXCEPTIONSFAILED            = 1OTHERS            = 2.IF SY-SUBRC <> 0.ENDIF.LOOP AT GT_TABLE INTO GS_TABLE.CLEAR XFC.XFC-FIELDNAME = GS_TABLE-FIELDNAME .XFC-INTTYPE = GS_TABLE-INTTYPE.XFC-INTLEN = GS_TABLE-LENG.XFC-DECIMALS = GS_TABLE-DECIMALS.APPEND XFC TO IFC.ENDLOOP.CALL METHOD CL_ALV_TABLE_CREATE=>CREATE_DYNAMIC_TABLEEXPORTINGIT_FIELDCATALOG = IFCIMPORTINGEP_TABLE        = DY_TABLE.ASSIGN DY_TABLE->* TO <DYN_TABLE>.CREATE DATA DY_LINE LIKE LINE OF <DYN_TABLE>.ASSIGN DY_LINE->* TO <DYN_WA>.
ENDFORM.                    " PRM_GET_TAB_STRC
*&---------------------------------------------------------------------*
*&      Form  PRM_GET_EXCEL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM PRM_GET_EXCEL .IF P_FILE IS NOT INITIAL.
*將excel數據傳入內表CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'EXPORTINGFILENAME    = P_FILEI_BEGIN_COL = '1'I_BEGIN_ROW = '2'I_END_COL   = '300'I_END_ROW   = '50000'TABLESINTERN      = GT_EXCEL_T.ENDIF.IF GT_EXCEL_T[] IS NOT INITIAL.LOOP AT GT_EXCEL_T INTO GS_EXCEL_T.AT NEW ROW.CLEAR <DYN_WA>.ENDAT.IF <DYN_WA> IS INITIAL.ASSIGN COMPONENT GS_EXCEL_T-COL OF STRUCTURE <DYN_WA> TO <DYN_FIELD>.IF SY-SUBRC = 0.<DYN_FIELD> = SY-MANDT.ENDIF.ENDIF.GS_EXCEL_T-COL = GS_EXCEL_T-COL + 1.GC_FIELDVALUE = GS_EXCEL_T-VALUE.CONDENSE GC_FIELDVALUE.ASSIGN COMPONENT GS_EXCEL_T-COL OF STRUCTURE <DYN_WA> TO <DYN_FIELD>.IF SY-SUBRC = 0.<DYN_FIELD> = GC_FIELDVALUE.ENDIF.AT END OF ROW.APPEND <DYN_WA> TO <DYN_TABLE>.ENDAT.ENDLOOP.ENDIF.
ENDFORM.                    " PRM_GET_EXCEL
*&---------------------------------------------------------------------*
*&      Form  PRM_UPDATA_TAB
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM PRM_UPDATA_TAB .MODIFY (P_TBMA) FROM TABLE <DYN_TABLE>.IF SY-SUBRC = 0.MESSAGE '導入成功' TYPE 'S'.ELSE.MESSAGE '導入失敗' TYPE 'S' DISPLAY LIKE 'E'.EXIT.ENDIF.
ENDFORM.                    " PRM_UPDATA_TAB

?

最簡單的一種方式，輸入參數：表名，EXXCEL（EXCEL里數據按表的數據元素順序，可不加客戶端）

?