ABAP 通用批导EXCLE数据到数据库表

*&---------------------------------------------------------------------*
*& Report  ZTY_001
*&
*&---------------------------------------------------------------------*
REPORT zty_001.

*TYPE-POOLS:slis,abap.

DATA: BEGIN OF iexcel OCCURS 0.
        INCLUDE STRUCTURE alsmex_tabline.
DATA: END OF iexcel.

DATA: i_index TYPE i.
FIELD-SYMBOLS: <fs>.



*-----------
DATA: d_ref      TYPE REF TO data,

      lt_alv_cat TYPE TABLE OF lvc_s_fcat,

      ls_alv_cat LIKE LINE OF lt_alv_cat.


DATA: lt_table LIKE TABLE OF dntab.

DATA: ls_table TYPE dntab.


DATA: dyn_table TYPE REF TO data.

DATA: dyn_wa TYPE REF TO data.


FIELD-SYMBOLS :

  <dyn_table> TYPE table,

  <dyn_wa>    TYPE any,

  <dyn_field> TYPE any,

  <fs_str>    TYPE any.


*-------------------------------------------
PARAMETERS: p_fname LIKE rlgrap-filename MEMORY ID m01. " 获取文件路径
PARAMETERS: p_table  TYPE tabname,
            p_begrow TYPE i DEFAULT 2, "开始行
            p_begcol TYPE i DEFAULT 1. "开始列

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_fname.
  PERFORM get_field.


START-OF-SELECTION.
  PERFORM get_table.
  PERFORM upload.
  PERFORM alv_show.

*&---------------------------------------------------------------------*
*&      Form  get_field
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM get_field.


  CALL FUNCTION 'WS_FILENAME_GET'
    EXPORTING
*     DEF_FILENAME     = ' '
*     DEF_PATH         = ' '
      mask             = 'Excel Files,*.xls,All Files,*.csv,*.*.'
*     MODE             = ' '
      title            = '请选择要导入的格式'
    IMPORTING
      filename         = p_fname
*     RC               =
    EXCEPTIONS
      inv_winsys       = 1
      no_batch         = 2
      selection_cancel = 3
      selection_error  = 4
      OTHERS           = 5.
  IF sy-subrc <> 0.
    WRITE: '导入数据出错'.
  ENDIF.

ENDFORM.                    "get_field


*&---------------------------------------------------------------------*
*&      Form  upLOAD
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM upload.

  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                = p_fname
      i_begin_col             = 1
      i_begin_row             = p_begrow
      i_end_col               = 100
      i_end_row               = 10000
    TABLES
      intern                  = iexcel
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.
  IF sy-subrc <> 0.
    WRITE:sy-subrc.
  ELSE.
    SELECT SINGLE dcpfm INTO @DATA(l_dcpfm)
      FROM usr01
      WHERE bname = @sy-uname.

    p_begcol = 1 - ( p_begcol - 1 ).

    LOOP AT iexcel.

      " WRITE:IEXCEL-COL.
      MOVE iexcel-col TO i_index.
      i_index = i_index + p_begcol.
      ASSIGN COMPONENT i_index OF STRUCTURE <dyn_wa> TO <fs> .
      IF <fs> IS ASSIGNED.
        MOVE iexcel-value TO <fs>.
        READ TABLE lt_table INTO ls_table INDEX i_index.
        IF sy-subrc = 0.
          CASE ls_table-inttype.
            WHEN 'D'.
*              CALL FUNCTION 'ZCONVERT_DATE_TO_INPUT'
*                EXPORTING
*                  im_datext = iexcel-value
*                IMPORTING
*                  ex_datint = <fs>
**                 EX_DATFMUSED       =
**                 EV_MSGTY  =
**                 EV_MSGTXT =
*                .

            WHEN 'T'.
              cl_abap_timefm=>conv_time_ext_to_int(
                EXPORTING
                  time_ext      = iexcel-value                " External Represenation of Time
                  is_24_allowed = abap_false       " Is 24:00 permitted?
                IMPORTING
                  time_int      = <fs>                " Internal Represenation of Time
              ).

            WHEN 'P'.
              CASE l_dcpfm.
                WHEN ''.
                  REPLACE ALL OCCURRENCES OF '.' IN iexcel-value WITH '' .
                  REPLACE ',' IN iexcel-value WITH '.' .
                  CONDENSE iexcel-value NO-GAPS.
                WHEN 'Y'."小数点是逗号
                  REPLACE ',' IN iexcel-value WITH '.' .
                  CONDENSE iexcel-value NO-GAPS.
                WHEN 'X'."小数点是逗号
                  REPLACE ALL OCCURRENCES OF ',' IN iexcel-value WITH '' .
                  CONDENSE iexcel-value NO-GAPS.
                WHEN OTHERS.
              ENDCASE.
              <fs> = iexcel-value.
            WHEN OTHERS.
          ENDCASE.
        ENDIF.
      ENDIF.
      AT END OF row.
        APPEND <dyn_wa> TO <dyn_table>.
        CLEAR: <dyn_wa> .
        " APPEND IM_TAB.  "在每一行结束时
        " CLEAR IM_TAB.
      ENDAT.

    ENDLOOP.

  ENDIF.


ENDFORM.                    "upLOAD



*&---------------------------------------------------------------------*
*&      Form  get_table
*&---------------------------------------------------------------------*
*       text  获取内表结构
*----------------------------------------------------------------------*
FORM get_table.
*取出表结构的字段目录
  CALL FUNCTION 'NAMETAB_GET'
    EXPORTING
      langu          = sy-langu
      tabname        = p_table
    TABLES
      nametab        = lt_table
    EXCEPTIONS
      no_texts_found = 1.


*根据取出的字段目录生成参考字段目录

  LOOP AT lt_table INTO ls_table.

    ls_alv_cat-fieldname = ls_table-fieldname.

    ls_alv_cat-ref_table = p_table.

    ls_alv_cat-ref_field = ls_table-fieldname.

    APPEND ls_alv_cat TO lt_alv_cat.

    CLEAR ls_alv_cat.

  ENDLOOP.

*    内表创建

  CALL METHOD cl_alv_table_create=>create_dynamic_table
    EXPORTING
      it_fieldcatalog = lt_alv_cat
    IMPORTING
      ep_table        = d_ref.

*    指定生成的内表到字段符号
  ASSIGN d_ref->* TO <dyn_table>.
*    创建动态工作区结构
  CREATE DATA dyn_wa LIKE LINE OF <dyn_table>.
*    创建动态工作区
  ASSIGN dyn_wa->* TO <dyn_wa>.

ENDFORM.                    "get_table




*&---------------------------------------------------------------------*
*&      Form  ALV_SHOW
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM alv_show.
*显示内表中的数据
  CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
    EXPORTING
      i_structure_name         = p_table
      i_callback_program       = sy-repid
      i_callback_pf_status_set = 'SET_PF_STATUS'
      i_callback_user_command  = 'USER_COMMAND'
    TABLES
      t_outtab                 = <dyn_table>
    EXCEPTIONS
      program_error            = 1
      OTHERS                   = 2.

  IF sy-subrc <> 0.

    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

    WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

  ENDIF.
ENDFORM.                    "ALV_SHOW
FORM set_pf_status USING rt_extab TYPE slis_t_extab.
  SET PF-STATUS 'STANDARD' EXCLUDING rt_extab.
ENDFORM.
FORM user_command  USING r_ucomm LIKE sy-ucomm
                                   rs_selfield TYPE slis_selfield.
  CASE r_ucomm.
    WHEN 'ZSAV'.
      MODIFY (p_table) FROM TABLE <dyn_table>.
      IF sy-subrc EQ 0.
        COMMIT WORK AND WAIT.
        MESSAGE '保存成功' TYPE 'S'.
      ELSE.
        ROLLBACK WORK.
        MESSAGE '保存失败，请检查数据是否有误' TYPE 'S' DISPLAY LIKE 'E'.
      ENDIF.
    WHEN OTHERS.
  ENDCASE.
ENDFORM.