Excel批量导入存自定义表程序

REPORT yxgrcr002.


INCLUDE YXGRCR002_top.

INCLUDE yxgrcr002_sel.

INCLUDE yxgrcr002_fm01.



SELECTION-SCREEN: FUNCTION KEY 1.


INITIALIZATION.
  gs_functxt-icon_id    = icon_export .
  gs_functxt-quickinfo  = '模板下载'.
  gs_functxt-icon_text  = '模板下载'.
  sscrfields-functxt_01 = gs_functxt.

AT SELECTION-SCREEN.
  CASE sscrfields-ucomm.
    WHEN 'FC01'.
      PERFORM frm_download_temp.
  ENDCASE.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

  PERFORM get_file_excel CHANGING p_file.


*&---------------------------------------------------------------------*
*&                        START-OF-SELECTION
*&---------------------------------------------------------------------*
START-OF-SELECTION .

  PERFORM prm_upload_data.

YXGRCR002_TOP

TYPE-POOLS: slis,
            icon.
TABLES: sscrfields.

DATA: gs_functxt  TYPE smp_dyntxt.

TYPES:BEGIN OF ty_excel,
        profit    TYPE zprofit,
        bukrs     TYPE bukrs,
        werks     TYPE werks_d,
        company   TYPE zcompany,
        pro_des   TYPE zpro_des,
        werks_des TYPE zwerks_des,
      END OF ty_excel.

DATA:gs_excel TYPE ty_excel,
     gt_excel TYPE TABLE OF ty_excel.

YXGRCR002_SEL

SELECTION-SCREEN BEGIN OF BLOCK bk1 WITH FRAME TITLE TEXT-001.

  PARAMETERS:          p_file TYPE rlgrap-filename  .

SELECTION-SCREEN END OF  BLOCK bk1.

YXGRCR002_FM01

*&---------------------------------------------------------------------*
*& 包含               YXGRCR002_FM01
*&---------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*& Form get_file_excel
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*&      <-- P_FILE
*&---------------------------------------------------------------------*
FORM get_file_excel  CHANGING p_file.

  CALL FUNCTION 'WS_FILENAME_GET'
    EXPORTING
      def_path         = 'D:/temp/'
      mask             = ',Microsoft Office Excel工作薄(*.xlsx),*.XLSX, Microsoft Office Excel工作薄(*.xls),*.XLS, 所有文件(*.*),*.*,'
      title            = 'Select File'(100)
    IMPORTING
      filename         = p_file
    EXCEPTIONS
      inv_winsys       = 1
      no_batch         = 2
      selection_cancel = 3
      selection_error  = 4
      OTHERS           = 5.
  IF sy-subrc <> 0 .

  ENDIF.

ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_download_temp
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_download_temp .

  DATA: lw_objdata     LIKE wwwdatatab,
        lw_mime        LIKE w3mime,
        "w_filename    TYPE string,
        lv_fullpath    TYPE string VALUE 'C:/TEMP/',
        lv_path        TYPE string VALUE 'C:/TEMP/',
        lv_destination LIKE rlgrap-filename,
        lv_objnam      TYPE string,
        lv_rc          LIKE sy-subrc,
        lv_errtxt      TYPE string,
        lv_dest        LIKE sapb-sappfad,
        lv_action      TYPE i.

  DATA: pv_fname TYPE string.
  pv_fname = '利润中心与公司代码关系 '.
  DATA(pv_objid) = 'YXGRCR002'.
  CALL METHOD cl_gui_frontend_services=>file_save_dialog
    EXPORTING
      window_title         = '下载导入模板'
      default_extension    = 'XLS;XLSX'
      default_file_name    = pv_fname
      file_filter          = 'EXCEL文件|*.XLSX;*.XLS'
    CHANGING
      filename             = pv_fname
      path                 = lv_path
      fullpath             = lv_fullpath
      user_action          = lv_action
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4.

  IF sy-subrc = 0 AND lv_action EQ 0.
    lv_dest = lv_fullpath.
    CONDENSE lv_objnam NO-GAPS.

    SELECT SINGLE relid
                  objid
    INTO CORRESPONDING FIELDS OF lw_objdata
    FROM wwwdata
    WHERE srtf2 = 0
      AND relid = 'MI'
      AND objid = pv_objid.
    IF sy-subrc NE 0 OR lw_objdata-objid EQ space.
      CONCATENATE  '模板文件'
                   lv_objnam
                   '不存在'
              INTO lv_errtxt.
      MESSAGE lv_errtxt TYPE 'I'.
    ENDIF.

    lv_destination = lv_dest.
    CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
      EXPORTING
        key         = lw_objdata
        destination = lv_destination
      IMPORTING
        rc          = lv_rc.
    IF lv_rc NE 0.
      CONCATENATE '模板文件：'
                  lv_objnam
                  '下载失败'
             INTO lv_errtxt.
      MESSAGE lv_errtxt TYPE 'E'.
    ENDIF.
  ENDIF.

ENDFORM.
*&---------------------------------------------------------------------*
*& Form prm_upload_data
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM prm_upload_data .

  DATA:lt_intern TYPE TABLE OF zalsmex_tabline,
       lt_commap TYPE TABLE OF ytgrc_com_map,
       ls_commap TYPE ytgrc_com_map,
       l_msg     TYPE string.
  IF p_file IS INITIAL.
    MESSAGE s000(yxa_fi) WITH  '请选择上传文件'  DISPLAY LIKE 'E'.
    LEAVE LIST-PROCESSING.
  ENDIF.

  CALL FUNCTION 'ZALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                = p_file
      i_begin_col             = 1
      i_begin_row             = 2
      i_end_col               = 6
      i_end_row               = 5000
    TABLES
      intern                  = lt_intern
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.

  IF sy-subrc <> 0.
    MESSAGE 'Excel解析失败!' TYPE 'I' DISPLAY LIKE 'E'.
    RETURN.
  ENDIF.

  IF lt_intern IS INITIAL..
    MESSAGE '导入数据为空，请检查' TYPE 'S' DISPLAY LIKE 'E'.
    EXIT.
  ENDIF.
  SORT lt_intern BY row col.

  LOOP AT lt_intern INTO DATA(ls_initern).
    ASSIGN COMPONENT ls_initern-col OF STRUCTURE gs_excel TO FIELD-SYMBOL(<fs_data>).
    IF sy-subrc EQ 0.
      MOVE ls_initern-value TO <fs_data>.
      AT END OF row.
        APPEND gs_excel TO gt_excel.
        CLEAR: gs_excel,<fs_data>.
      ENDAT.
    ENDIF.
  ENDLOOP.

  IF gt_excel[] IS NOT INITIAL.
    LOOP AT gt_excel INTO gs_excel.
      MOVE-CORRESPONDING gs_excel TO ls_commap.
      APPEND ls_commap TO lt_commap.
    ENDLOOP.
    IF lt_commap[] IS NOT INITIAL.
*      DESCRIBE TABLE lt_commap LINES l_lins.
      DATA(l_lins) = lines( lt_commap ).
      MODIFY ytgrc_com_map FROM TABLE lt_commap.
      IF sy-subrc EQ 0.
        COMMIT WORK.
        l_msg = '成功导入(' && l_lins && ')条数据'.
      ELSE.
        ROLLBACK WORK.
        l_msg = '导入失败(' && l_lins && ')条数据'.
      ENDIF.
      MESSAGE l_msg TYPE 'S' DISPLAY LIKE 'S'.
    ENDIF.
  ENDIF.


ENDFORM.