SAP ABAP万能EXCEL批导

前言：

    程序自动根据导入数据生成alv数据，不需要定义内表和字段。

  功能实现： 

     1、导入数据从Excel的第一行，第一列开始。其中第一行取的值为alv的字段描述。

     2、因为标准函数ALSM_EXCEL_TO_INTERNAL_TABLE 里面的结构

          ALSMEX_TABLINE-VALUE字段太短，一般复制一个标准的出来改一下。

     3、次功能单纯的批导，如果需要添加按钮功能等，可以自行添加。

     4、此功能可以自行添加字段，比如可选行，消息文本等。

    效果如下：

 代码如下： 

*&---------------------------------------------------------------------*
*& Report ZABAP_EXCEL_INPUT
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zabap_excel_input.

"选择屏幕
DATA: g_repid     LIKE sy-repid,
      it_fieldcat TYPE lvc_t_fcat WITH HEADER LINE,
      is_layout   TYPE lvc_s_layo.
FIELD-SYMBOLS:
  <dyn_wa>    TYPE any, "工作区
  <dyn_table> TYPE STANDARD TABLE. "内表指针

SELECTION-SCREEN BEGIN OF BLOCK 001 WITH FRAME TITLE title1.
  PARAMETERS: p_file LIKE rlgrap-filename.
  SELECTION-SCREEN SKIP.
  SELECTION-SCREEN COMMENT /1(79) cmt1.
  SELECTION-SCREEN COMMENT /2(79) cmt2.
SELECTION-SCREEN END OF BLOCK 001 .

AT SELECTION-SCREEN  OUTPUT .
  %_p_file_%_app_%-text = '文件导入地址'.

INITIALIZATION.
  cmt1 = '注意：' .
  cmt2 = 'EXCEL导入数据格式：数据导入从第一行，第一列开始。第一行为ALV字段描述' .
*----------------------------------------------------------------------*
*          AT SELECTION-SCREEN)                                        *
*----------------------------------------------------------------------*
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  PERFORM file_input CHANGING p_file .

START-OF-SELECTION.
  PERFORM update_excel.
  PERFORM alv_show.
*&---------------------------------------------------------------------*
*& Form file_input
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*&      <-- P_FILE
*&---------------------------------------------------------------------*
FORM file_input  CHANGING p_p_file.
  DATA:lt_tab         TYPE filetable,         "存放文件名的内表
       lv_file_filter TYPE string,            "file filter
       lv_rc          TYPE i.                 "函数返回值

  REFRESH:lt_tab.
  CLEAR:lv_file_filter,lv_rc.
  lv_file_filter = 'EXCEL文件|*.XLS;*.XLSX'.
  CALL METHOD cl_gui_frontend_services=>file_open_dialog
    EXPORTING
      window_title            = '选择数据文件'
      file_filter             = lv_file_filter
*     initial_directory       = 'C:\'
    CHANGING
      file_table              = lt_tab
      rc                      = lv_rc
    EXCEPTIONS
      file_open_dialog_failed = 1
      cntl_error              = 2
      error_no_gui            = 3
      not_supported_by_gui    = 4
      OTHERS                  = 5.

  IF sy-subrc = 0 AND lv_rc = 1.
    READ TABLE lt_tab INTO p_file INDEX 1.
  ELSE.
    MESSAGE '没有选择文件或是选择文件错误！' TYPE 'S'.
  ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form update_excel
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM update_excel.
  DATA:lt_excel TYPE TABLE OF zalsmex_tabline.
  DATA ls_excel LIKE LINE OF lt_excel.
  DATA sum_row TYPE i.
  DATA sum_tbx TYPE i.

  FIELD-SYMBOLS: "<dyn_table> TYPE STANDARD TABLE, "内表指针
    <dyn_name>  TYPE any, "工作区
    <dyn_field> TYPE any, "字段
    <fs>        TYPE any, "字段
    <fs_name>   TYPE any. "字段
  DATA:dy_table TYPE REF TO data,
       dy_line  TYPE REF TO data,  "行
       wa_fcat  TYPE lvc_s_fcat,   "列结构
       it_fcat  TYPE lvc_t_fcat.
  DATA lt_table TYPE TABLE OF dfies. "字段结构表
  DATA ls_table TYPE dfies.


  DATA:
    dref_tab    TYPE REF TO data,
    itab_type   TYPE REF TO cl_abap_tabledescr,
    struct_type TYPE REF TO cl_abap_structdescr,
    elem_type   TYPE REF TO cl_abap_elemdescr,
    table_type  TYPE REF TO cl_abap_tabledescr,
    comp_tab    TYPE cl_abap_structdescr=>component_table WITH HEADER LINE.

  IF p_file IS INITIAL.
    MESSAGE '导入数据不能为空' TYPE 'S' DISPLAY LIKE 'E'.
    LEAVE LIST-PROCESSING.
  ELSE.
**上传数据
    CALL FUNCTION 'ZALSM_EXCEL_TO_INTERNAL_TABLE'
      EXPORTING
        filename                = p_file
        i_begin_col             = 1
        i_begin_row             = 1
        i_end_col               = 99
        i_end_row               = 19999
        sheet_name              = ''
      TABLES
        intern                  = lt_excel
      EXCEPTIONS
        inconsistent_parameters = 1
        upload_ole              = 2
        OTHERS                  = 3.
    IF sy-subrc <> 0.
* implement suitable error handling here
    ENDIF.

    DATA(lt_data) = lt_excel[].
    DELETE lt_data WHERE row <> '0001'.

    "统计导入字段总数
    CLEAR sum_row.
    DESCRIBE TABLE lt_data LINES sum_row .
    "获取字段名
    IF sum_row > 1.
      CLEAR sum_tbx.
      DO sum_row TIMES.
        sum_tbx = sum_tbx + 1.
* 向结构中动态的新增一个成员
        wa_fcat-fieldname = 'COL' && sum_tbx.
        APPEND wa_fcat TO it_fcat.
        CLEAR wa_fcat.
      ENDDO.
    ENDIF.

    CALL METHOD cl_alv_table_create=>create_dynamic_table
      EXPORTING
        it_fieldcatalog = it_fcat
      IMPORTING
        ep_table        = dy_table.
    ASSIGN dy_table->* TO <dyn_table>.
    CREATE DATA dy_line LIKE LINE OF <dyn_table>.
    ASSIGN dy_line->* TO <dyn_wa>.
    ASSIGN dy_line->* TO <dyn_name>.

    "给现有的内表加选择行
    table_type ?= cl_abap_tabledescr=>describe_by_data( <dyn_table> ).

    struct_type ?= table_type->get_table_line_type( ).

    comp_tab[] = struct_type->get_components( ).

    comp_tab-name = 'SEL'.
    comp_tab-type = cl_abap_elemdescr=>get_c( 1 ).
    INSERT comp_tab INTO comp_tab INDEX sum_row + 1.

    struct_type = cl_abap_structdescr=>create( comp_tab[] ).
    itab_type = cl_abap_tabledescr=>create( struct_type ).
    CREATE DATA dref_tab TYPE HANDLE itab_type.
    ASSIGN dref_tab->* TO <dyn_table>.



    LOOP AT lt_excel INTO ls_excel.
      IF ls_excel-row = '0001'.
        ASSIGN COMPONENT ls_excel-col OF STRUCTURE <dyn_name> TO <fs_name>.
        CONDENSE ls_excel-value NO-GAPS.                  " 去掉空格
        <fs_name> = ls_excel-value.
        SEARCH <fs_name> FOR '日期' .
        IF sy-subrc = 0.
          it_fieldcat-inttype = 'D'.
        ENDIF.
        SEARCH <fs_name> FOR '时间'.
        IF sy-subrc = 0.
          it_fieldcat-inttype = 'T'.
        ENDIF.

        it_fieldcat-fieldname = 'COL' && sy-tabix.
        it_fieldcat-tabname   = '<DYN_TABLE>'.
        it_fieldcat-coltext   = <fs_name>.
        it_fieldcat-just = 'C'.
        APPEND  it_fieldcat.
        CLEAR  it_fieldcat.
      ELSE.
        ASSIGN COMPONENT ls_excel-col OF STRUCTURE <dyn_wa> TO <fs>.
        CONDENSE ls_excel-value NO-GAPS.                  " 去掉空格
        <fs> = ls_excel-value.
      ENDIF.
      AT END OF row.
        IF ls_excel-row <> '0001'.
          APPEND <dyn_wa> TO <dyn_table>.
        ENDIF.
      ENDAT.
    ENDLOOP.
  ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form alv_show
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM alv_show .

  is_layout-zebra      = 'X'.   "明亮显示
  is_layout-cwidth_opt = 'X'.   "自动列宽
  is_layout-box_fname = 'SEL'.

  g_repid = sy-repid.
  CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY_LVC'
    EXPORTING
      i_callback_program = g_repid
*     I_CALLBACK_PF_STATUS_SET          = ' '
*     I_CALLBACK_USER_COMMAND           = ' '
      is_layout_lvc      = is_layout
      it_fieldcat_lvc    = it_fieldcat[]
    TABLES
      t_outtab           = <dyn_table>
    EXCEPTIONS
      program_error      = 1
      OTHERS             = 2.
ENDFORM.

添加字段位置：