SAP ABAP EXCLE_INCLUDE

*&---------------------------------------------------------------------*
*& Include ZINCLUDE_EXCLE
*&---------------------------------------------------------------------*

TYPE-POOLS: icon,slis.

TABLES: sscrfields.

DATA functxt   TYPE smp_dyntxt.

*----------------------------------------------------------------------*
*           SELECTION-SCREEN                                           *
*----------------------------------------------------------------------*
* 工具栏
SELECTION-SCREEN FUNCTION KEY 1.
* 数据导入
SELECTION-SCREEN BEGIN OF BLOCK blk01 WITH FRAME TITLE TEXT-t01.
* 选择导入文件
PARAMETERS p_file   TYPE rlgrap-filename MODIF ID p1.

SELECTION-SCREEN END OF BLOCK blk01.

*&---文件路径搜索帮助
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

  CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
    CHANGING
      file_name = p_file.

INITIALIZATION.

  PERFORM frm_sub_button.

*&---------------------------------------------------------------------*

*&      Form  frm_sub_button

*&---------------------------------------------------------------------*

*       text

*----------------------------------------------------------------------*

FORM frm_sub_button.

  functxt-icon_id       = icon_export.

  functxt-icon_text     = '下载模板'.

  sscrfields-functxt_01 = functxt.

ENDFORM.                    "frm_sub_button

*&---------------------------------------------------------------------*

*&      Form  frm_dwl_module

*&---------------------------------------------------------------------*

*       text

*----------------------------------------------------------------------*

*      -->GV_NAME    text

*----------------------------------------------------------------------*

FORM frm_dwl_module USING gv_name.

  DATA:

    lv_sheet_number TYPE i, "打开的EXCEL的SHEET的名字

    lv_name         LIKE wwwdatatab,

    lv_mime         LIKE w3mime OCCURS 10,

    lv_filename     TYPE string,    "文件名

    lv_path         TYPE string,    "路径

    lv_fullpath     TYPE string,    "全路径

    lv_title        TYPE string.

  lv_name-relid = 'MI'.

  lv_name-objid = gv_name."pv_mname.       "***************这里给定模板的名字*******************

  lv_name-text = sy-title.

  CALL FUNCTION 'WWWDATA_IMPORT'
    EXPORTING
      key               = lv_name
    TABLES
      mime              = lv_mime
    EXCEPTIONS
      wrong_object_type = 1
      import_error      = 2
      OTHERS            = 3.

  IF sy-subrc <> 0.

    STOP.

  ENDIF.

  lv_title = sy-title.

  CALL METHOD cl_gui_frontend_services=>file_save_dialog
    EXPORTING
      window_title         = lv_title
      default_extension    = 'xlsx'
      default_file_name    = lv_title
      file_filter          = '(电子表格EXCEL)'
    CHANGING
      filename             = lv_filename
      path                 = lv_path
      fullpath             = lv_fullpath
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4.

  IF sy-subrc <> 0.

    STOP.

  ENDIF.

  CALL FUNCTION 'GUI_DOWNLOAD'
    EXPORTING
      filename = lv_fullpath
      filetype = 'BIN'
    TABLES
      data_tab = lv_mime.

  IF sy-subrc = 0.

    MESSAGE '导出模板成功.' TYPE 'I'.

  ENDIF.

ENDFORM.                    "frm_dwl_module

*&---------------------------------------------------------------------*

*&      Form  open_dialog

*&---------------------------------------------------------------------*

*       text

*----------------------------------------------------------------------*

FORM open_dialog USING gv_file."选择文件会话。"

  DATA:lt_file_table TYPE filetable.

  DATA:lw_file_table TYPE file_table.

  DATA:l_rc TYPE i.

  CALL METHOD cl_gui_frontend_services=>file_open_dialog
    EXPORTING
      window_title            = '选择文件'
      default_filename        = '*.XLSX'  "默认excel文件"
*     initial_directory       = 'D:\ '  "默认打开D盘，也可以默认空"
      multiselection          = ''   "文件单选"
    CHANGING
      file_table              = lt_file_table
      rc                      = l_rc
    EXCEPTIONS
      file_open_dialog_failed = 1
      cntl_error              = 2
      error_no_gui            = 3
      not_supported_by_gui    = 4
      OTHERS                  = 5.

  CHECK l_rc EQ 1."因为文件单选，所以这里判断一下选择的数量为1"

  READ TABLE lt_file_table INDEX 1 INTO gv_file.

  "将选择的文件地址写入到地址栏"

ENDFORM.                    "open_dialog

*&---------------------------------------------------------------------*

*&      Form  sec_get_file

*&---------------------------------------------------------------------*

*       text

*----------------------------------------------------------------------*

*      -->GT_TAB     text

*----------------------------------------------------------------------*

FORM sec_get_file TABLES gt_tab USING gv_file gv_line.

  FIELD-SYMBOLS : <fs>.

*  DATA: l_intern LIKE kcde_cells OCCURS 0 WITH HEADER LINE.

  DATA: l_intern LIKE zalsmex_tabline OCCURS 0 WITH HEADER LINE.

  DATA: len_str TYPE i,

        l_index TYPE i,

        lv_line TYPE i.

  TRANSLATE gv_file TO UPPER CASE.

  len_str = strlen( gv_file ).

  len_str = len_str - 4.

  CLEAR gt_tab[].

  lv_line = gv_line.

*  if p_file+len_str(4) ne '.XLS'.

*    concatenate p_file '.XLS' into p_file.

*  endif.

*  CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'
*  CALL FUNCTION 'ZALSM_EXCEL_TO_INTERNAL_TABLE'
  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                = gv_file
      i_begin_col             = 1
      i_begin_row             = 1
      i_end_col               = 256
      i_end_row               = 65535
    TABLES
      intern                  = l_intern
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.

  SORT l_intern BY row col.

  DATA: cwts(30).

**&赋值给内表

  LOOP AT l_intern WHERE row > lv_line.

    MOVE l_intern-col TO l_index.

    ASSIGN COMPONENT l_index OF STRUCTURE gt_tab TO <fs>.

    MOVE l_intern-value TO <fs>.

    AT END OF row.

      APPEND gt_tab.

      CLEAR gt_tab.

    ENDAT.

  ENDLOOP.

ENDFORM.                    "sec_get_file

*********************************************************************
以下是使用方法

INCLUDE zinclude_excle.

AT SELECTION-SCREEN OUTPUT.
  PERFORM frm_set_screen.

*&------------------------------------------------------------------------&
*&创建时间：16.09.2021 09:15:07
*&
*&------------------------------------------------------------------------&
FORM frm_set_screen.
  LOOP AT SCREEN.
    CASE screen-group1.
      WHEN 'S1'.
        IF r_1 EQ 'X'.
          screen-active  = 0.
        ENDIF.
      WHEN 'P1'.
        IF r_2 EQ 'X'.
          screen-active  = 0.
        ENDIF.
      WHEN OTHERS.
    ENDCASE.
    MODIFY SCREEN.
  ENDLOOP.
ENDFORM.

AT SELECTION-SCREEN.
  CASE sscrfields-ucomm.
    WHEN 'FC01'.
      PERFORM frm_dwl_module USING 'ZHRR_9011'.
  ENDCASE.
  
START-OF-SELECTION.
      IF p_file IS INITIAL.
        MESSAGE '请选择导入的文件' TYPE 'E'.
      ENDIF.
      PERFORM sec_get_file TABLES gt_alv USING p_file 2.