ABAP 批量导入excel程序模板

程序模板

TABLES sscrfields.
DATA:functxt      TYPE smp_dyntxt,
     lv_initfname TYPE string,
     lv_w3objid   TYPE w3objid,
     gs_layout    TYPE lvc_s_layo,
     gt_fieldcat TYPE lvc_t_fcat.
     
SELECTION-SCREEN FUNCTION KEY 1.
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-001.
PARAMETERS p_path TYPE localfile MODIF ID gp0.
SELECTION-SCREEN END OF BLOCK b1.

INITIALIZATION.

  functxt-icon_id   = icon_toggle_display_change.
  functxt-icon_text = '下载模板'.
  sscrfields-functxt_01 = functxt.
  
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_path.
  p_path = zcl_batch_tool=>get_file_address( EXPORTING im_path = p_path ).  
AT SELECTION-SCREEN.
  CASE sscrfields-ucomm.
    WHEN 'FC01'.
        lv_w3objid = 'ZPPR050'.
        lv_initfname = 'SOP销售预测模板'.      
      zcl_batch_tool=>download_template( EXPORTING im_filename = lv_w3objid  im_init_filename = lv_initfname ).
  ENDCASE.

START-OF-SELECTION.

perform upload.

perform display.

form upload.
zcl_batch_tool=>upload_excel_data( EXPORTING im_path      = p_file
                                             im_begin_col = 1
                                             im_begin_row = 2
                                             im_end_col   = 4
                                             im_end_row   = 50000
                                   IMPORTING ex_tab       = gt_upload_data  ).  
endform.

form display.
  gs_layout-zebra      =   'X'.
  gs_layout-sel_mode   = 'D'.
PERFORM fill_fcat TABLES gt_fieldcat USING: 'BUKRS' '公司' '' '' '' '' '' '' '' '4'.  
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY_LVC'
    EXPORTING
      i_callback_program       = sy-repid
      is_layout_lvc            = gs_layout
      it_fieldcat_lvc          = gt_head_fieldcat[]
      i_save                   = 'A'
      i_callback_user_command  = 'USER_COMMAND'
      i_callback_pf_status_set = 'SET_PF_STATUS'
*     is_variant               = g_variant
*     it_events                = i_events
*     it_sort_lvc              = gt_sort[]
*     is_print_lvc             = gs_print
*     i_grid_title             = l_title   " 报表TITLE
    TABLES
      t_outtab                 = gt_head
    EXCEPTIONS
      program_error            = 1
      OTHERS                   = 2.
endform.
FORM set_pf_status USING rt_extab TYPE slis_t_extab.
  SET PF-STATUS 'ALV_STATUS' EXCLUDING rt_extab.
ENDFORM.
FORM user_command  USING r_ucomm LIKE sy-ucomm
                          rs_selfield TYPE slis_selfield.
DATA lr_grid       TYPE REF TO cl_gui_alv_grid.
CALL FUNCTION 'GET_GLOBALS_FROM_SLVC_FULLSCR'
    IMPORTING
      e_grid = lr_grid.
  CALL METHOD lr_grid->check_changed_data.
CASE R_UCOMM.
WHEN ''.
ENDCASE.
  rs_selfield-row_stable = 'X'.
  rs_selfield-col_stable = 'X'.
  rs_selfield-refresh = 'X'.
ENDFORM.  

FORM fill_fcat TABLES pt_fieldcat TYPE lvc_t_fcat
                     USING VALUE(ps1)
                     VALUE(ps2)
                     VALUE(ps3)
                     VALUE(ps4)
                     VALUE(ps5)
                     VALUE(ps6)
                     VALUE(ps7)
                     VALUE(ps8)
                     VALUE(ps9)
                     VALUE(ps10).
  DATA ls_fieldcat TYPE lvc_s_fcat.
  ls_fieldcat-fieldname = ps1.
  ls_fieldcat-scrtext_s = ps2.
  ls_fieldcat-scrtext_m = ps2.
  ls_fieldcat-scrtext_l = ps2.
  ls_fieldcat-ref_field = ps3.
  ls_fieldcat-ref_table = ps4.
  ls_fieldcat-edit = ps5.
  ls_fieldcat-convexit = ps6.
  ls_fieldcat-checkbox = ps7.
  ls_fieldcat-outputlen = ps10.
  ls_fieldcat-no_zero = ps8.
  ls_fieldcat-key = ps9.
  APPEND ls_fieldcat TO pt_fieldcat.

ENDFORM.

zcl_batch_tool

CLASS zcl_batch_tool DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .

  PUBLIC SECTION.

    TYPES:
      ty_excel_table TYPE TABLE OF alsmex_tabline .

    CLASS-METHODS download_template
      IMPORTING
        VALUE(im_filename)       TYPE w3objid
        VALUE(im_init_directory) TYPE string OPTIONAL
        VALUE(im_init_filename)  TYPE string OPTIONAL .
    CLASS-METHODS get_file_address
      IMPORTING
        VALUE(im_path) TYPE localfile OPTIONAL
      RETURNING
        VALUE(ex_path) TYPE localfile .
    CLASS-METHODS confirm_screen
      IMPORTING
        VALUE(im_title)    TYPE char35 OPTIONAL
        VALUE(im_textline) TYPE char35 OPTIONAL
      RETURNING
        VALUE(ex_flag)     TYPE abap_bool .
    CLASS-METHODS convert_date_to_internal
      IMPORTING
        VALUE(i_date_external) TYPE any
      RETURNING
        VALUE(o_date_internal) TYPE datum .
    CLASS-METHODS upload_excel_data
      IMPORTING
        VALUE(im_path)      TYPE localfile OPTIONAL
        VALUE(im_begin_col) TYPE i
        VALUE(im_begin_row) TYPE i
        VALUE(im_end_col)   TYPE i
        VALUE(im_end_row)   TYPE i
        VALUE(im_convert)   TYPE c OPTIONAL
      EXPORTING
        VALUE(ex_tab)       TYPE data .

  PROTECTED SECTION.
  PRIVATE SECTION.
ENDCLASS.



CLASS zcl_batch_tool IMPLEMENTATION.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Public Method ZCL_BATCH_TOOL=>CONFIRM_SCREEN
*弹出确认框
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_TITLE                       TYPE        CHAR35(optional)
* | [--->] IM_TEXTLINE                    TYPE        CHAR35(optional)
* | [<-()] EX_FLAG                        TYPE        abap_bool
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD confirm_screen.

    DATA:lv_answer TYPE c.
    CLEAR ex_flag.
    CALL FUNCTION 'POPUP_TO_CONFIRM_STEP'
      EXPORTING
        titel        = im_title
        start_column = 20             "设置开始的列
        start_row    = 7
        textline1    = im_textline
*       cancel_display = abap_true
      IMPORTING
        answer       = lv_answer.

    CHECK lv_answer EQ 'J'.

    ex_flag = abap_true.

  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Public Method ZCL_BATCH_TOOL=>CONVERT_DATE_TO_INTERNAL
*外部日期格式转内部日期，如2023/4/11-->20240411
* +-------------------------------------------------------------------------------------------------+
* | [--->] I_DATE_EXTERNAL                TYPE        ANY
* | [<-()] O_DATE_INTERNAL                TYPE        DATUM
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD convert_date_to_internal.
    DATA:lt_dd07v TYPE STANDARD TABLE OF dd07v.
    DATA:ls_dd07v TYPE dd07v.

*核心代码参考：CL_ABAP_DATFM
*    *获取日期格式
    CALL FUNCTION 'DD_DOMVALUES_GET'
      EXPORTING
        domname        = 'XUDATFM'
      TABLES
        dd07v_tab      = lt_dd07v
      EXCEPTIONS
        wrong_textflag = 1
        OTHERS         = 2.
    IF sy-subrc <> 0.
      RETURN.
    ENDIF.

    LOOP AT lt_dd07v INTO ls_dd07v.
      CALL 'DATE_CONV_EXT_TO_INT'
           ID 'DATINT' FIELD o_date_internal
           ID 'DATEXT' FIELD i_date_external
           ID 'DATFORMDES' FIELD ls_dd07v-domvalue_l.
      IF sy-subrc = 0.
        EXIT.
      ELSE.
        CLEAR o_date_internal.
      ENDIF.
    ENDLOOP.

  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Public Method ZCL_BATCH_TOOL=>DOWNLOAD_TEMPLATE
*下载批导模板
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_FILENAME                    TYPE        W3OBJID
* | [--->] IM_INIT_DIRECTORY              TYPE        STRING(optional)
* | [--->] IM_INIT_FILENAME               TYPE        STRING(optional)
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD download_template.

    DATA:lo_objdata     TYPE wwwdatatab,
         lo_mime        TYPE w3mime,
         ls_destination TYPE rlgrap-filename,
         ls_objnam      TYPE string,
         li_rc          TYPE sy-subrc,
         ls_errtxt      TYPE string.
    DATA:p_objid TYPE wwwdatatab-objid,
         p_dest  TYPE sapb-sappfad.
    DATA:l_path      TYPE string,
         l_fullpath  TYPE string,
         l_filename  TYPE string,
         gc_fullpath TYPE rlgrap-filename.
    DATA:lv_init_directory TYPE string,
         lv_init_filename  TYPE string.

    lv_init_directory = 'D:/' && im_init_directory.
    lv_init_filename = im_init_filename && '.xlsx'.

    CALL METHOD cl_gui_frontend_services=>file_save_dialog
      EXPORTING
        window_title         = 'Select directory'
        initial_directory    = lv_init_directory "'C:/'
        default_file_name    = lv_init_filename
        file_filter          = '.xlsx'
      CHANGING
        filename             = l_filename
        path                 = l_path
        fullpath             = l_fullpath
      EXCEPTIONS
        cntl_error           = 1
        error_no_gui         = 2
        not_supported_by_gui = 3
        OTHERS               = 4.

    p_objid = im_filename.

    CHECK l_fullpath IS NOT INITIAL.

    IF sy-subrc = 0.

      CONCATENATE p_objid '.XLS' INTO ls_objnam.

      CONDENSE ls_objnam NO-GAPS.

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

      IF sy-subrc NE 0 OR lo_objdata-objid EQ space.
        MESSAGE '模板不存在于系统' TYPE 'W'.
        EXIT.
      ENDIF.

      CONCATENATE l_fullpath '.xls' INTO gc_fullpath.

      CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
        EXPORTING
          key         = lo_objdata
          destination = gc_fullpath
        IMPORTING
          rc          = li_rc.

      IF li_rc NE 0.
        MESSAGE '模板下载失败!' TYPE 'W'.
      ENDIF.

    ENDIF.

  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Public Method ZCL_BATCH_TOOL=>GET_FILE_ADDRESS
* 获取文件地址
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_PATH                        TYPE        LOCALFILE(optional)
* | [<-()] EX_PATH                        TYPE        LOCALFILE
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD get_file_address.


    DATA lv_filename TYPE rlgrap-filename.

* 选择文件搜索帮助
    CALL FUNCTION 'WS_FILENAME_GET'
      EXPORTING
        def_path         = im_path
*       mask             = ',EXCEL FILE,*.XLS;*.XLSX;XML;' "',*.xlsx,*.XLSX,*.xls,*.XLS.'
        mask             = ',*.XLS;*.XLSX;*.XML;' "',*.xlsx,*.XLSX,*.xls,*.XLS.'
        mode             = '0'
        title            = '导入文件'
      IMPORTING
        filename         = lv_filename
      EXCEPTIONS
        inv_winsys       = 1
        no_batch         = 2
        selection_cancel = 3
        selection_error  = 4
        OTHERS           = 5.

    IF sy-subrc <> 0 AND sy-subrc <> 3.
      MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
           WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
    ENDIF.

* 如果选择了文件
    IF sy-subrc = 0 AND lv_filename IS NOT INITIAL.
      ex_path = lv_filename.
    ENDIF.


  ENDMETHOD.



* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Public Method ZCL_BATCH_TOOL=>UPLOAD_EXCEL_DATA
* 处理导入的数据到abap内表
* +-------------------------------------------------------------------------------------------------+
* | [--->] IM_PATH                        TYPE        LOCALFILE(optional)
* | [--->] IM_BEGIN_COL                   TYPE        I
* | [--->] IM_BEGIN_ROW                   TYPE        I
* | [--->] IM_END_COL                     TYPE        I
* | [--->] IM_END_ROW                     TYPE        I
* | [--->] IM_CONVERT                     TYPE        C(optional)
* | [<---] EX_TAB                         TYPE        DATA
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD upload_excel_data.

    FIELD-SYMBOLS <fs_excel_value> TYPE any.
    DATA:lt_excel TYPE TABLE OF alsmex_tabline,
         ls_excel TYPE alsmex_tabline.
    DATA:lv_conversion_no_number TYPE REF TO cx_sy_conversion_no_number.
    DATA:ex_tab_cl   TYPE REF TO data,
         ex_tab_area TYPE REF TO data.
    FIELD-SYMBOLS <fs_ex_tab> TYPE STANDARD TABLE.

    DATA:BEGIN OF ls_convert,
           col       TYPE zkcd_ex_col_n,
           component TYPE char30,
           function  TYPE char30.
    DATA END OF ls_convert.
    DATA lt_convert LIKE TABLE OF ls_convert.
    DATA:cl_abap_elemdescr TYPE REF TO cl_abap_elemdescr,
         cl_abap_typedescr TYPE REF TO cl_abap_typedescr.
    IF im_path IS INITIAL.
      im_path = get_file_address( 'C:\' ).
    ENDIF.

    CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
      EXPORTING
        filename                = im_path
        i_begin_col             = im_begin_col
        i_begin_row             = im_begin_row
        i_end_col               = im_end_col
        i_end_row               = im_end_row
      TABLES
        intern                  = lt_excel[]
      EXCEPTIONS
        inconsistent_parameters = 1
        upload_ole              = 2
        OTHERS                  = 3.

    IF sy-subrc <> 0  .
      EXIT.
    ELSE.

*      **  ---获取内表结构
      DATA:lr_table_type  TYPE REF TO cl_abap_tabledescr,
           lr_struct_type TYPE REF TO cl_abap_structdescr,
           lt_comp_table  TYPE cl_abap_structdescr=>component_table.

      lr_table_type ?= cl_abap_tabledescr=>describe_by_data( ex_tab ).
      lr_struct_type ?= lr_table_type->get_table_line_type( ).
      lt_comp_table[] = lr_struct_type->get_components( ).
      LOOP AT lt_comp_table INTO DATA(ls_comp_table).
        cl_abap_elemdescr ?= ls_comp_table-type.
        CHECK cl_abap_elemdescr->edit_mask IS NOT INITIAL.

        ls_convert-component = cl_abap_elemdescr->absolute_name.
        ls_convert-function  = |CONVERSION_EXIT_{ cl_abap_elemdescr->edit_mask+2 }_INPUT|.
        APPEND ls_convert TO lt_convert.
      ENDLOOP.
      CLEAR ls_convert.
      SORT lt_convert BY component ASCENDING.

      CREATE DATA ex_tab_cl TYPE HANDLE lr_table_type.
      ASSIGN ex_tab_cl->* TO <fs_ex_tab>.

      CREATE DATA ex_tab_area TYPE HANDLE lr_struct_type.
      ASSIGN ex_tab_area->* TO FIELD-SYMBOL(<fs_ex_tab_area>).

      LOOP AT lt_excel INTO ls_excel.

*        IF skip_first_row EQ 'X' AND ls_excel-row = 1.
*          CONTINUE.
*        ENDIF.
*
*        IF skip_first_col_int EQ 'X'.
*          ADD 1 TO ls_excel-col.
*        ENDIF.

        ASSIGN COMPONENT ls_excel-col OF STRUCTURE <fs_ex_tab_area> TO <fs_excel_value>.
        TRY .

            <fs_excel_value> = ls_excel-value.
            IF im_convert EQ 'X'.
              CLEAR ls_convert.
              READ TABLE lt_convert INTO ls_convert WITH KEY col = ls_excel-col.
              IF sy-subrc <> 0.
                cl_abap_typedescr = cl_abap_elemdescr=>describe_by_data( <fs_excel_value> ).
                READ TABLE lt_convert INTO ls_convert WITH KEY component = cl_abap_typedescr->absolute_name.
                ls_convert-col = ls_excel-col.
                APPEND ls_convert TO lt_convert.
              ENDIF.
              IF ls_convert-function IS NOT INITIAL.
                CALL FUNCTION ls_convert-function
                  EXPORTING
                    input        = <fs_excel_value>
                  IMPORTING
                    output       = <fs_excel_value>
                  EXCEPTIONS
                    length_error = 1
                    OTHERS       = 2.
              ENDIF.
            ENDIF.
          CATCH cx_sy_conversion_no_number INTO lv_conversion_no_number.

        ENDTRY.

        AT END OF row.
          APPEND <fs_ex_tab_area> TO <fs_ex_tab>.
          CLEAR <fs_ex_tab_area>.
        ENDAT.

      ENDLOOP.

      ex_tab = <fs_ex_tab>.
    ENDIF.


  ENDMETHOD.
ENDCLASS.