ABAP 导入Excel形成内表

创建导入模板

程序实现

代码

*&---------------------------------------------------------------------*
*& Report Z_EXCEL_UPLOAD_LHY
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT Z_EXCEL_UPLOAD_LHY.
TABLES SSCRFIELDS.

DATA: LS_FUNCTXT TYPE SMP_DYNTXT.
CONSTANTS:COS_TEMPLATE TYPE W3OBJID VALUE 'ZFI001',
          COS_FILENAME TYPE STRING VALUE '财务导入模板.xlsx'.
TYPES: BEGIN OF T_MARC,
         MATNR TYPE MARC-MATNR, "物料编号
         WERKS TYPE MARC-WERKS, "工厂
         MAKTX TYPE MAKT-MAKTX,  "物料描述
         MATKL TYPE MARA-MATKL,  "物料组
       END   OF T_MARC.
DATA GT_DATA TYPE TABLE OF T_MARC.

SELECTION-SCREEN BEGIN OF BLOCK BK1.
  PARAMETERS P_FILE TYPE RLGRAP-FILENAME  MODIF ID M1.
SELECTION-SCREEN END OF BLOCK BK1.
**--按钮定义
SELECTION-SCREEN FUNCTION KEY 1.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR  P_FILE. "按选择帮助<F4>键时触发该事件
  PERFORM FRM_GET_FILE_NAME CHANGING P_FILE.


INITIALIZATION.
**--按钮定义-初始化
  CLEAR LS_FUNCTXT.
  LS_FUNCTXT-ICON_ID   = ICON_IMPORT. "图标，可以通过ICON查找
  LS_FUNCTXT-ICON_TEXT = '下载文件'."描述
  SSCRFIELDS-FUNCTXT_01 = LS_FUNCTXT.

AT SELECTION-SCREEN.
**--按钮响应
  CASE SSCRFIELDS-UCOMM.
    WHEN 'FC01'.
      PERFORM FRM_DOWNLOAD_TEMPLATE USING COS_TEMPLATE COS_FILENAME.
    WHEN OTHERS.
  ENDCASE.

START-OF-SELECTION.
  "解析Excel文件到内表
  PERFORM FILE_DATA_INPUT.


**&---------------------------------------------------------------------*
**& Module STATUS_9000 OUTPUT
**&---------------------------------------------------------------------*
**&
**&---------------------------------------------------------------------*
*MODULE STATUS_9000 OUTPUT.
*  SET PF-STATUS 'STANDARD'.
** SET TITLEBAR 'xxx'.
*ENDMODULE.
*&---------------------------------------------------------------------*
*& Form frm_get_file_name
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*&      <-- P_FILE
*&---------------------------------------------------------------------*
FORM FRM_GET_FILE_NAME  CHANGING CV_FILE.

  DATA: LT_FILETABLE TYPE FILETABLE,  "表类型
        LS_FILETABLE TYPE FILE_TABLE, "结构类型
        LV_RC        TYPE I.
  FREE LT_FILETABLE.
  CLEAR: LV_RC.

  CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_OPEN_DIALOG
    EXPORTING
*     window_title            =
      DEFAULT_EXTENSION       = CL_GUI_FRONTEND_SERVICES=>FILETYPE_EXCEL
*     default_filename        =
      FILE_FILTER             = CL_GUI_FRONTEND_SERVICES=>FILETYPE_EXCEL
*     with_encoding           =
*     initial_directory       =
*     multiselection          =
    CHANGING
      FILE_TABLE              = LT_FILETABLE
      RC                      = LV_RC
*     user_action             =
*     file_encoding           =
    EXCEPTIONS
      FILE_OPEN_DIALOG_FAILED = 1
      CNTL_ERROR              = 2
      ERROR_NO_GUI            = 3
      NOT_SUPPORTED_BY_GUI    = 4
      OTHERS                  = 5.
  READ TABLE LT_FILETABLE INTO LS_FILETABLE INDEX 1.
  IF SY-SUBRC <> 0.
*     Implement suitable error handling here
  ENDIF.
  IF SY-SUBRC EQ 0.
    CV_FILE = LS_FILETABLE-FILENAME.
  ENDIF.

ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_DOWNLOAD_TEMPLATE
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*&      --> COS_TEMPLATE
*&      --> COS_FILENAME
*&---------------------------------------------------------------------*
FORM FRM_DOWNLOAD_TEMPLATE  USING   U_OBJID TYPE W3OBJID
                                     U_FILE_NAME TYPE STRING.
  DATA: LS_KEY      TYPE WWWDATATAB,
        LV_FILENAME TYPE STRING,
        LV_PATH     TYPE STRING,
        LV_FULLPATH TYPE STRING.

  "判断模板是否存在
  SELECT SINGLE * FROM WWWDATA
    WHERE RELID EQ 'MI'
    AND OBJID EQ @U_OBJID
    INTO CORRESPONDING FIELDS OF @LS_KEY.
  IF SY-SUBRC NE 0.
*   模板&1不存在
    MESSAGE 'ZFI001下载模板不存在' TYPE 'E'."ZFI001
    RETURN.
  ENDIF.
*   调用函数打开文件选择框

  CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_SAVE_DIALOG
    EXPORTING
*     window_title              =
      DEFAULT_EXTENSION         = CL_GUI_FRONTEND_SERVICES=>FILETYPE_EXCEL
      DEFAULT_FILE_NAME         = U_FILE_NAME
*     with_encoding             =
*     file_filter               =
*     initial_directory         =
*     prompt_on_overwrite       = 'X'
    CHANGING
      FILENAME                  = LV_FILENAME
      PATH                      = LV_PATH
      FULLPATH                  = LV_FULLPATH
*     user_action               =
*     file_encoding             =
    EXCEPTIONS
      CNTL_ERROR                = 1
      ERROR_NO_GUI              = 2
      NOT_SUPPORTED_BY_GUI      = 3
      INVALID_DEFAULT_FILE_NAME = 4
      OTHERS                    = 5.
  IF SY-SUBRC <> 0.
*   Implement suitable error handling here
  ENDIF.
  CHECK LV_FULLPATH NE ''.
*   下载SMW0模版
  CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
    EXPORTING
      KEY         = LS_KEY
      DESTINATION = CONV LOCALFILE( LV_FULLPATH ).

ENDFORM.
*&---------------------------------------------------------------------*
*& Form FILE_DATA_INPUT
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM FILE_DATA_INPUT .
  CLEAR GT_DATA. "清空内表
  DATA: LT_INTERN TYPE STANDARD TABLE OF ALSMEX_TABLINE.
  DATA: ERROR_STR TYPE STRING.
  FREE: LT_INTERN.

  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      FILENAME                = P_FILE
      I_BEGIN_COL             = 1
      I_BEGIN_ROW             = 2
      I_END_COL               = 99
      I_END_ROW               = 65536
    TABLES
      INTERN                  = LT_INTERN
    EXCEPTIONS
      INCONSISTENT_PARAMETERS = 1
      UPLOAD_OLE              = 2
      OTHERS                  = 3.
  IF SY-SUBRC NE 0.
  ENDIF.
  SORT LT_INTERN BY ROW COL.
  LOOP AT LT_INTERN INTO DATA(LS_INTERN).
    AT NEW ROW. "如果字段ROW及ROW的左侧全部字段的数据，与上一行数据不一致，则执行代码。
      APPEND INITIAL LINE TO GT_DATA ASSIGNING FIELD-SYMBOL(<FS_DATA>).
    ENDAT.
    "按照现在的LS_INTERN的COL的值，分配结构<FS_DATA>的对应下标的地址给指针<FS_VAL>，
    ASSIGN COMPONENT LS_INTERN-COL OF STRUCTURE <FS_DATA> TO FIELD-SYMBOL(<FS_VAL>).
    <FS_VAL> = LS_INTERN-VALUE.
    CLEAR LS_INTERN.
  ENDLOOP.
  CL_DEMO_OUTPUT=>DISPLAY( GT_DATA ).
ENDFORM.

代码解析

运行结果