动态上传EXCEL文件内容至SAP

考虑到项目经常会有需要做批导功能，于是封装了一个方法用于动态上载EXCEL数据

类 YCL_UPLOAD

代码如下

*&Date                     Author      Change Req No.     Ver.Description
*&2022.03.07 AM    王浩辉                                    1.Create new version

class YCL_UPLOAD definition
  public
  final
  create public .

public section.

  types:
    BEGIN OF ty_msgt ,
            line  TYPE i,
            mtype TYPE bapi_mtype,
            messg TYPE bapi_msg,
          END OF ty_msgt .
  types:
    tt_msgt TYPE STANDARD TABLE OF ty_msgt .

  class-methods UPLOAD
    importing
      !IV_FILE type LOCALFILE
      !IV_BROW type I
      !IV_EROW type I
      !IV_BCOL type I
    exporting
      !ET_MSGT type TT_MSGT
    changing
      !CT_DATA type DATA .
  class-methods DOWNLOAD
    importing
      !IV_FILENAME type STRING
      !IV_OBJID type W3OBJID .
  class-methods DATE_CONVERTS
    importing
      !IV_DATE type ANY
    returning
      value(RV_DATE) type DATUM .
  class-methods TIME_CONVERTS
    importing
      !IV_TIME type ANY
    returning
      value(RV_TIME) type UZEIT .
protected section.
private section.
ENDCLASS.



CLASS YCL_UPLOAD IMPLEMENTATION.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Public Method YCL_UPLOAD=>DATE_CONVERTS
* +-------------------------------------------------------------------------------------------------+
* | [--->] IV_DATE                        TYPE        ANY
* | [<-()] RV_DATE                        TYPE        DATUM
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD date_converts.
    DATA:lv_char8  TYPE char8,
         lv_char10 TYPE char10,
         lv_year   TYPE char4,
         lv_mon    TYPE char2,
         lv_date   TYPE char2,
         lv_no     TYPE p,
         lv_len1   TYPE i,
         lv_len2   TYPE i.

    CLEAR rv_date.

    lv_char10 = iv_date.

    "只判断是否有 - / . 等三种符号，其它不管

    TRY.
        lv_no   = lv_char10.
        rv_date = lv_char10.
      CATCH cx_root INTO DATA(lo_eor).
        DO 2 TIMES.
          REPLACE '.' WITH '-' INTO lv_char10.
          REPLACE '/' WITH '-' INTO lv_char10.
        ENDDO.

        SPLIT lv_char10 AT '-' INTO lv_year lv_char8.
        SPLIT lv_char8  AT '-' INTO lv_mon  lv_date.

        CONDENSE:lv_mon  NO-GAPS,
                 lv_date NO-GAPS.

        lv_len1 = strlen( lv_mon ).
        IF lv_len1 EQ 1.
          lv_mon = |0{ lv_mon }|.
        ENDIF.

        lv_len2 = strlen( lv_date ).
        IF lv_len2 EQ 1.
          lv_date = |0{ lv_date }|.
        ENDIF.

        rv_date = |{ lv_year }{ lv_mon }{ lv_date }|.
    ENDTRY.
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Public Method YCL_UPLOAD=>DOWNLOAD
* +-------------------------------------------------------------------------------------------------+
* | [--->] IV_FILENAME                    TYPE        STRING
* | [--->] IV_OBJID                       TYPE        W3OBJID
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD download.
    DATA:lv_file     TYPE string,
         lv_result   TYPE char1, "标识: 空表示文件不存在;否则,表示存在
         lv_dest     TYPE localfile, "目标文件
         ls_key      TYPE wwwdatatab,
         lv_subrc    TYPE sy-subrc,
         lv_filename TYPE string,
         lv_path     TYPE string,
         lv_fullpath TYPE string,
         lv_action   TYPE i,
         lv_answer   TYPE char1.

    CALL METHOD cl_gui_frontend_services=>file_save_dialog
      EXPORTING
        default_extension         = 'xlsx'
        default_file_name         = iv_filename
      CHANGING
        filename                  = lv_filename
        path                      = lv_path
        fullpath                  = lv_file
        user_action               = lv_action
      EXCEPTIONS
        cntl_error                = 1
        error_no_gui              = 2
        not_supported_by_gui      = 3
        invalid_default_file_name = 4
        OTHERS                    = 5.
    CHECK sy-subrc = 0 AND lv_action = 0.

    CLEAR lv_result.
    CALL METHOD cl_gui_frontend_services=>file_exist
      EXPORTING
        file                 = lv_file
      RECEIVING
        result               = lv_result
      EXCEPTIONS
        cntl_error           = 1
        error_no_gui         = 2
        wrong_parameter      = 3
        not_supported_by_gui = 4
        OTHERS               = 5.
    IF lv_result IS NOT  INITIAL. "文件已经存在
      CALL FUNCTION 'POPUP_TO_CONFIRM'
        EXPORTING
          titlebar              = '请确认 '
          text_question         = '文件已经存在，是否要覆盖?'
          text_button_1         = '是'
          text_button_2         = '否'
          default_button        = '2' "缺省选中按钮: 1 是; 2 否
          display_cancel_button = ''  "标识: 为空,不显示 取消按钮
        IMPORTING
          answer                = lv_answer "标识： 1 是;
        EXCEPTIONS
          text_not_found        = 1
          OTHERS                = 2.
      IF lv_answer NE '1'.
        EXIT.
      ENDIF.
    ENDIF.

**下载EXCEL模板
    lv_dest = lv_file.
    ls_key-relid = 'MI'.
    ls_key-objid = iv_objid.

    CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
      EXPORTING
        key         = ls_key
        destination = lv_dest
      IMPORTING
        rc          = lv_subrc.
    IF lv_subrc <> 0.
      MESSAGE '文件下载失败' TYPE 'E'.
      EXIT.
    ENDIF.
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Public Method YCL_UPLOAD=>TIME_CONVERTS
* +-------------------------------------------------------------------------------------------------+
* | [--->] IV_TIME                        TYPE        ANY
* | [<-()] RV_TIME                        TYPE        UZEIT
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD time_converts.
    DATA:lv_time TYPE char8.

    "只考虑 : 类型

    CLEAR rv_time.
    lv_time = iv_time.

    REPLACE ':' WITH space INTO lv_time.
    REPLACE ':' WITH space INTO lv_time.
    CONDENSE lv_time NO-GAPS.
    rv_time = lv_time.

  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Public Method YCL_UPLOAD=>UPLOAD
* +-------------------------------------------------------------------------------------------------+
* | [--->] IV_FILE                        TYPE        LOCALFILE
* | [--->] IV_BROW                        TYPE        I
* | [--->] IV_EROW                        TYPE        I
* | [--->] IV_BCOL                        TYPE        I
* | [<---] ET_MSGT                        TYPE        TT_MSGT
* | [<-->] CT_DATA                        TYPE        DATA
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD upload.

    DATA: lo_table2 TYPE REF TO cl_abap_tabledescr,
          lo_struc  TYPE REF TO cl_abap_structdescr,
          lo_table  TYPE REF TO cl_abap_tabledescr,
          lo_data   TYPE REF TO data,
          ls_data   TYPE REF TO data,
          lt_comps  TYPE abap_compdescr_tab,
          lv_flag   TYPE char1,
          lv_col    TYPE i,
          lv_row    TYPE i,
          lv_num    TYPE p.

    DATA:lt_excel TYPE STANDARD TABLE OF alsmex_tabline.

    FIELD-SYMBOLS:<fs_tab>   TYPE STANDARD TABLE,
                  <fs_line>  TYPE any,
                  <fs_value> TYPE any,
                  <fs_mtype> TYPE any,
                  <fs_messg> TYPE any.

    CLEAR:et_msgt,ct_data.

    "获取传入参数中内表 字段信息
    lo_table2  ?= cl_abap_typedescr=>describe_by_data( ct_data ).
    lo_struc   ?= lo_table2->get_table_line_type( ).
    lt_comps    = lo_struc->components.

    "根据动态结构创建动态内表类型
    CALL METHOD cl_abap_tabledescr=>create
      EXPORTING
        p_line_type = lo_struc
      RECEIVING
        p_result    = lo_table.

    CREATE DATA lo_data TYPE HANDLE lo_table.

    "判断是否有MTYPE，MESSG字段用于接收消息，没有的话，将消息放到ET_MSGT中
    READ TABLE lt_comps WITH KEY name = 'MTYPE'
                                 TRANSPORTING NO FIELDS.
    IF sy-subrc NE 0.
      lv_flag = abap_true.
    ENDIF.

    READ TABLE lt_comps WITH KEY name = 'MESSG'
                                 TRANSPORTING NO FIELDS.
    IF sy-subrc NE 0.
      lv_flag = abap_true.
    ENDIF.

    DESCRIBE TABLE lt_comps LINES lv_col.
    lv_col = lv_col + iv_bcol - 1.

    CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
      EXPORTING
        filename                = iv_file
        i_begin_col             = iv_bcol
        i_begin_row             = iv_brow
        i_end_col               = lv_col
        i_end_row               = iv_erow
      TABLES
        intern                  = lt_excel
      EXCEPTIONS
        inconsistent_parameters = 1
        upload_ole              = 2
        OTHERS                  = 3.
    IF sy-subrc <> 0.
      MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
            WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
    ENDIF.

    IF lt_excel IS INITIAL.
      MESSAGE '无数据！' TYPE 'E'.
    ENDIF.

    "建立接收数据内表及结构
    ASSIGN lo_data->* TO <fs_tab>.
    CREATE DATA ls_data LIKE LINE OF <fs_tab>.
    ASSIGN ls_data->* TO <fs_line>.

    CLEAR  lv_col.
    LOOP AT lt_excel ASSIGNING FIELD-SYMBOL(<fs_excel>).
      MOVE <fs_excel>-col TO lv_col.
      ASSIGN COMPONENT lv_col OF STRUCTURE <fs_line> TO <fs_value>.
      IF sy-subrc EQ 0.
        TRY.
            "需要将日期和时间的格式转换成后台格式
            READ TABLE lt_comps ASSIGNING FIELD-SYMBOL(<fs_comp>) INDEX lv_col.
            IF sy-subrc EQ 0.
              CASE <fs_comp>-type_kind.
                WHEN 'D'.
                  <fs_value> = ycl_upload=>date_converts( <fs_excel>-value ).
                  "校验日期是否转换成功
                  lv_num     = <fs_value>.
                WHEN 'T'.
                  <fs_value> = ycl_upload=>time_converts( <fs_excel>-value ).
                  "校验时间是否转换成功
                  lv_num     = <fs_value>.
                WHEN OTHERS.
                  <fs_value> = <fs_excel>-value.
              ENDCASE.
            ENDIF.
          CATCH cx_root INTO DATA(lo_eor).
            lv_row = <fs_excel>-row.
            IF lv_flag EQ abap_true.
              READ TABLE et_msgt ASSIGNING FIELD-SYMBOL(<fs_msgt>) WITH KEY line = lv_row.
              IF sy-subrc EQ 0.
                <fs_msgt>-messg = |{ <fs_msgt>-messg }/第{ lv_col }列数据赋值出错！|.
              ELSE.
                APPEND VALUE #( line  = lv_row
                                mtype = 'E'
                                messg = |第{ lv_col }列数据赋值出错| ) TO et_msgt.
              ENDIF.
            ELSE.
              ASSIGN COMPONENT 'MTYPE' OF STRUCTURE <fs_line> TO <fs_mtype>.
              IF sy-subrc EQ 0.
                <fs_mtype> = 'E'.
              ENDIF.

              ASSIGN COMPONENT 'MESSG' OF STRUCTURE <fs_line> TO <fs_messg>.
              IF sy-subrc EQ 0.
                <fs_messg> = |{ <fs_messg> }/第{ lv_row }行 第{ lv_col }列数据赋值出错！|.
              ENDIF.
            ENDIF.
        ENDTRY.
      ENDIF.
      AT END OF row.
        APPEND <fs_line>  TO <fs_tab>.
        CLEAR <fs_line>.
      ENDAT.
    ENDLOOP.

    MOVE-CORRESPONDING <fs_tab> TO ct_data.
  ENDMETHOD.
ENDCLASS.