小工具: 以导入数据的方式更新任意透明表

有些时候无论table maintenance或者sql改数据都不方便

就想要把数据导出来

在excel中修改后再导进去

 

针对某一张表的代码比较简单

但是每次有这样的需求都要重新编码

于是扩展一下 做了一个支持任意表导入更新的小程序

(为了安全,限定为仅支持Z*/Y*的Table)

 

界面:

 

其中数据源是SE16N的ALV列表 默认导出的Excel数据

无须修改

直接在Excel中修改内容即可

 

选项一  modify

选项二  先delete 后insert

 

*&---------------------------------------------------------------------*
*& Report  YTOOL_TABLE
*&
*&---------------------------------------------------------------------*
*& 透明表数据更新 V1.0
*& 使用SE16N 默认导出数据模板
*& (支持跨Client的Table)
*&---------------------------------------------------------------------*

REPORT ytool_table.

**********************************************************************
*Screen
**********************************************************************
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
PARAMETERS p_table LIKE dd02l-tabname OBLIGATORY.
PARAMETERS p_file TYPE rlgrap-filename OBLIGATORY.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT 2(40) text-003.
SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN END OF BLOCK b1.

SELECTION-SCREEN BEGIN OF BLOCK b2 WITH FRAME TITLE text-002.
PARAMETERS:
  r1 RADIOBUTTON GROUP 1,
  r2 RADIOBUTTON GROUP 1.
SELECTION-SCREEN END OF BLOCK b2.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

  CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
*   EXPORTING
*     PROGRAM_NAME        = SYST-REPID
*     DYNPRO_NUMBER       = SYST-DYNNR
*     FIELD_NAME          = ' '
*     STATIC              = ' '
*     MASK                = ' '
*     FILEOPERATION       = 'R'
*     PATH                =
    CHANGING
      file_name = p_file
*     LOCATION_FLAG       = 'P'
*   EXCEPTIONS
*     MASK_TOO_LONG       = 1
*     OTHERS    = 2
    .
  IF sy-subrc <> 0.
*   Implement suitable error handling here
  ENDIF.

CLASS lcl_table DEFINITION.
  PUBLIC SECTION.
    METHODS:
      constructor IMPORTING  i_tabname TYPE dd02l-tabname
                  EXCEPTIONS failed,
      update IMPORTING  i_path    TYPE rlgrap-filename
                        i_covered TYPE char1 OPTIONAL
             EXCEPTIONS failed,
      covered IMPORTING  i_path TYPE rlgrap-filename
              EXCEPTIONS failed,
      refresh.
  PRIVATE SECTION.
    METHODS:
      create_dyn_data EXCEPTIONS failed,
      read_file IMPORTING  i_path TYPE rlgrap-filename
                EXCEPTIONS failed,
      save EXCEPTIONS failed,
      delete EXCEPTIONS failed.
    DATA:
      g_tabname         TYPE        dd02l-tabname,
      g_cross_client(1),
      ref_header        TYPE REF TO data,
      ref_table         TYPE REF TO data.


ENDCLASS.

CLASS lcl_table IMPLEMENTATION.
  METHOD create_dyn_data.
    DATA:
      lo_structdescr TYPE REF TO cl_abap_structdescr,
      lo_tabledescr  TYPE REF TO cl_abap_tabledescr.

    lo_structdescr ?= cl_abap_typedescr=>describe_by_name( g_tabname ).


    lo_tabledescr ?= cl_abap_tabledescr=>create( lo_structdescr ) .

    CREATE DATA ref_header TYPE HANDLE lo_structdescr.
    CREATE DATA ref_table TYPE HANDLE lo_tabledescr.
  ENDMETHOD.


  METHOD constructor.

    SELECT SINGLE tabname FROM dd02l INTO g_tabname
      WHERE tabname = i_tabname.
    IF sy-subrc <> 0.
      MESSAGE s000(sap_nwbc) WITH g_tabname '无法识别的表类型' DISPLAY LIKE 'E'.
      RAISE failed.
    ENDIF.

    IF g_tabname+0(1) <> 'Z' AND g_tabname+0(1) <> 'Y'.
      MESSAGE s000(sap_nwbc) WITH g_tabname '系统表不允许修改' DISPLAY LIKE 'E'.
      RAISE failed.
    ENDIF.

    "是否是跨client的表
    SELECT SINGLE  tabname FROM dd03l INTO g_tabname
      WHERE tabname = i_tabname AND fieldname = 'MANDT'.
    IF sy-subrc <> 0.
      g_cross_client = 'X'.
    ENDIF.

  ENDMETHOD.

  METHOD update.
    read_file( i_path ).
    save( EXCEPTIONS failed = 1 ).
    IF sy-subrc = 1.
      RAISE failed.
    ENDIF.
  ENDMETHOD.

  METHOD covered.
    DATA: l_answer(1).
    CALL FUNCTION 'POPUP_TO_CONFIRM'
      EXPORTING
*       TITLEBAR      = ' '
*       DIAGNOSE_OBJECT             = ' '
        text_question = '确认覆盖目标透明表数据?'
*       TEXT_BUTTON_1 = 'Ja'(001)
*       ICON_BUTTON_1 = ' '
*       TEXT_BUTTON_2 = 'Nein'(002)
*       ICON_BUTTON_2 = ' '
*       DEFAULT_BUTTON              = '1'
*       DISPLAY_CANCEL_BUTTON       = 'X'
*       USERDEFINED_F1_HELP         = ' '
*       START_COLUMN  = 25
*       START_ROW     = 6
*       POPUP_TYPE    =
*       IV_QUICKINFO_BUTTON_1       = ' '
*       IV_QUICKINFO_BUTTON_2       = ' '
      IMPORTING
        answer        = l_answer
*   TABLES
*       PARAMETER     =
*   EXCEPTIONS
*       TEXT_NOT_FOUND              = 1
*       OTHERS        = 2
      .
    IF sy-subrc <> 0.
* Implement suitable error handling here
      RETURN.
    ENDIF.

    CHECK l_answer = '1'.

    read_file( i_path ).
    delete( EXCEPTIONS failed = 1 ).
    IF sy-subrc = 1.
      RAISE failed.
    ENDIF.

    save( EXCEPTIONS failed = 1 ).
    IF sy-subrc = 1.
      RAISE failed.
    ENDIF.
  ENDMETHOD.

  METHOD  save.
    FIELD-SYMBOLS:
      <fs_field>  TYPE            any,
      <fs_header> TYPE            any,
      <fs_table>  TYPE ANY TABLE.

    ASSIGN ref_header->* TO <fs_header>.
    ASSIGN ref_table->* TO <fs_table>.

    DATA: lo_exception TYPE REF TO cx_root.
    TRY.
        MODIFY (g_tabname) FROM TABLE <fs_table>.
      CATCH cx_root INTO lo_exception.
        RAISE failed.
    ENDTRY.

    MESSAGE '更新数据成功.' TYPE 'S'.

  ENDMETHOD.

  METHOD delete.
    DATA: lo_exception TYPE REF TO cx_root.
    TRY.
        DELETE FROM (g_tabname).
      CATCH cx_root INTO lo_exception.
        MESSAGE '清空数据时发生错误' TYPE 'S' DISPLAY LIKE 'E'.
        RAISE failed.
    ENDTRY.

  ENDMETHOD.

  METHOD read_file.
    FIELD-SYMBOLS:
      <fs_field>  TYPE                 any,
      <fs_header> TYPE                 any,
      <fs_table>  TYPE STANDARD TABLE.

    create_dyn_data( ).

    DATA:
      lt_excel TYPE TABLE OF alsmex_tabline,
      ls_excel TYPE          alsmex_tabline.

    CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
      EXPORTING
        filename                = i_path
        i_begin_col             = 1
        i_begin_row             = 1
        i_end_col               = 9999
        i_end_row               = 9999
      TABLES
        intern                  = lt_excel
      EXCEPTIONS
        inconsistent_parameters = 1
        upload_ole              = 2
        OTHERS                  = 3.
    IF sy-subrc <> 0.
      RAISE failed.
    ENDIF.

    ASSIGN ref_header->* TO <fs_header>.
    ASSIGN ref_table->* TO <fs_table>.

    DATA: l_off TYPE i,
          l_pos TYPE i.
    IF g_cross_client <> 'X'.
      "非cross_client的table 从第二列开始赋值
      l_off = 1.
    ENDIF.
    LOOP AT lt_excel INTO ls_excel WHERE row > 1.
      l_pos = l_off + ls_excel-col.
      ASSIGN COMPONENT l_pos OF STRUCTURE <fs_header> TO <fs_field>.
      IF sy-subrc <> 0.
        MESSAGE '赋值时发生内部错误,检查模板或程序' TYPE 'E'.
      ENDIF.
      <fs_field> = ls_excel-value.
      AT END OF row.
        APPEND <fs_header> TO <fs_table>.
        CLEAR <fs_header>.
      ENDAT.

    ENDLOOP.

  ENDMETHOD.

  METHOD refresh.
    FREE: ref_header,ref_table.
  ENDMETHOD.
ENDCLASS.

START-OF-SELECTION.
  DATA:
    lo_table TYPE REF TO lcl_table.
  CREATE OBJECT lo_table
    EXPORTING
      i_tabname = p_table
    EXCEPTIONS
      failed    = 1.
  IF sy-subrc = 1.
    LEAVE LIST-PROCESSING.
  ENDIF.

  IF r1 = 'X'.
    lo_table->update( EXPORTING i_path = p_file
                      EXCEPTIONS failed = 1 ).
    IF sy-subrc = 1.
      ROLLBACK WORK.
    ENDIF.
  ELSEIF r2 = 'X'.
    lo_table->covered( EXPORTING i_path = p_file
                      EXCEPTIONS failed = 1 ).
    IF sy-subrc = 1.
      ROLLBACK WORK.
    ENDIF.

  ENDIF.