ABAP EXCEL导入与导出（一）

需求场景：

导入和导出EXCEL是日常一个比较常见的需求，本篇博客记录一下EXCEL导入的功能，下一篇会记录EXCEL导出。

1.下载系统模板

PERFORM frm_downexceltemplete USING '国内报价单模板' 'ZMMC001R2'.
FORM frm_downexceltemplete USING p_default p_objid.
  DATA: l_filepath TYPE string,
        l_filename TYPE string,
        l_path     TYPE string,
        l_default  TYPE string,
        lv_title   TYPE string.

  DATA: l_subrc TYPE sy-subrc.
  DATA: w_excel_filename TYPE string,
        w_objid          LIKE wwwdatatab-objid,
        wa_objdata       LIKE wwwdatatab,
        w_dest           LIKE rlgrap-filename.

  CLEAR: w_objid,w_excel_filename.

  MOVE: p_default TO  l_default,
        p_objid TO w_objid.

  lv_title = text-007.

  CALL METHOD cl_gui_frontend_services=>file_save_dialog
    EXPORTING
      window_title         = lv_title
      default_file_name    = l_default
      file_filter          = '.XLS'
      initial_directory    = 'C:\TEMP'
      prompt_on_overwrite  = 'X'
    CHANGING
      filename             = l_filename
      path                 = l_path
      fullpath             = l_filepath
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4.
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
    WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.

  IF l_filename IS NOT INITIAL AND l_filename NS '.XLS'.
    CONCATENATE l_filename '.XLSX' INTO l_filename.
  ENDIF.

* 服务器下载模板表
  IF NOT l_filepath IS INITIAL.

    MOVE: l_filename TO w_excel_filename.


    SELECT SINGLE relid
    objid
    FROM wwwdata
    INTO CORRESPONDING FIELDS OF wa_objdata
    WHERE srtf2 = 0
    AND relid = 'MI'
    AND objid = w_objid.

    IF sy-subrc NE 0.
      MESSAGE e398(00) WITH text-008.
    ENDIF.

* 下载模板文件
    w_dest =  w_excel_filename.

    CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
      EXPORTING
        key         = wa_objdata
        destination = w_dest
      IMPORTING
        rc          = l_subrc.

    IF l_subrc NE 0.
      MESSAGE e398(00) WITH text-009.
    ENDIF.
  ENDIF.
ENDFORM.                    " FRM_DOWNEXCELTEMPLETE

2.读取数据到内部表

FORM frm_upload_data01 USING p_colums.
  DATA:lv_kunnr TYPE kunnr,
       lv_werks TYPE werks_d,
       lv_bjh   LIKE zmmt036-zbjh,
       lv_bukrs TYPE bukrs.

  DATA:len  TYPE int4,
       len1 TYPE int4.

  DATA:lv_zwzc(2).

  DATA:lv_type TYPE dd01v-datatype.

  DATA:lv_num      TYPE i,lv_num_char.
  CLEAR:lv_num.
  REFRESH: gt_excel.

  gv_pfname = p_file.
  CONDENSE gv_pfname.

  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                = gv_pfname
      i_begin_col             = 1
      i_begin_row             = 1
      i_end_col               = p_colums
      i_end_row               = 99999
    TABLES
      intern                  = gt_excel
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.
  IF sy-subrc <> 0.
    WRITE: / text-028,gv_pfname.
    EXIT.
  ELSE.
    LOOP AT gt_excel.
      CONDENSE gt_excel-value.
      MODIFY gt_excel.
    ENDLOOP.
  ENDIF.

  CLEAR:lv_kunnr,lv_bjh,lv_werks,lv_bukrs.
  lv_num = 1.
  LOOP AT gt_excel.
    CASE gt_excel-row.
      WHEN '0001'.
      WHEN '0002'.
        CASE gt_excel-col.
          WHEN '0003'.
          WHEN '0009'.
        ENDCASE.
      WHEN '0003'.
      WHEN '0004'.
      WHEN OTHERS.
        CASE gt_excel-col.
          WHEN '0001'.
          WHEN '0002'.
          WHEN '0003'.
          WHEN '0004'.
          WHEN '0005'.
          WHEN '0006'.
          WHEN '0008'.
          WHEN '0009'.
          WHEN OTHERS.
        ENDCASE.
        AT END OF row."判断读到行数据的最后一列数据
          gs_output01-zbjh = lv_bjh.
          gs_output01-zvendor = lv_kunnr.
          gs_output01-zwerks = lv_werks.
          gs_output01-bukrs = lv_bukrs.
          APPEND gs_output01 TO gt_output01.
          CLEAR:gs_output01.
        ENDAT.
    ENDCASE.
  ENDLOOP.
ENDFORM.                    " FRM_UPLOAD_DATA01

3.数据校验和处理

通过第二步把数据已经读取到内表了，下一步可以对数据进行校验，校验后的数据就可以根据业务需求进行后续处理了。