ABAP 根据表名创建动态内表并更新

最新推荐文章于 2024-09-18 22:46:47 发布

逍遥游abc

最新推荐文章于 2024-09-18 22:46:47 发布

阅读量1.5k

点赞数 1

分类专栏： SAP ABAP

原文链接：https://www.cnblogs.com/chaguoguo/p/14073912.html

版权

SAP ABAP 专栏收录该内容

77 篇文章 26 订阅

订阅专栏

查询界面

1.SE11 通过数据浏览器，右键将数据导出成 EXCLE 文件

主程序

*&---------------------------------------------------------------------*
*& Report ZIT0003
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zit0003.

INCLUDE zit0003_head.
INCLUDE zit0003_screen.
INCLUDE zit0003_forms.

INITIALIZATION.
  sscrfields-functxt_01 = '下载文档'.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file2.
* 选择文件搜索帮助
  PERFORM frm_set_file_f4 USING p_file2 CHANGING p_file2.

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


START-OF-SELECTION.
  IF sy-uname+0(2) NE 'IT'.
    MESSAGE '仅限IT账号使用' TYPE 'S' DISPLAY LIKE 'E'.
    RETURN.
  ENDIF.
  IF p_name+0(1) NE 'Z'.
    MESSAGE '仅允许更新自建表' TYPE 'S' DISPLAY LIKE 'E'.
    RETURN.
  ENDIF.
  IF p_file2 IS INITIAL.
    MESSAGE '请输入文件名' TYPE 'S' DISPLAY LIKE 'E'.
    RETURN.
  ENDIF.
  PERFORM frm_get_data. "动态创建内表 <dyn_table>
  PERFORM frm_file_data2.
*  PERFORM frm_get_filed. "本人电脑使用 TEXT_CONVERT_XLS_TO_SAP 有未知错误，弃之
  PERFORM frm_save_data.


END-OF-SELECTION.
  PERFORM frm_display_data.

包含文件ZIT0003_HEAD

*&---------------------------------------------------------------------*
*& 包含               ZIT0003_HEAD
*&---------------------------------------------------------------------*
TABLES:sscrfields.
DATA: d_ref      TYPE REF TO data,
      d_ref2     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.


DATA: dyn_table2 TYPE REF TO data.
DATA: dyn_wa2 TYPE REF TO data.


FIELD-SYMBOLS :<dyn_table2> TYPE table,
               <dyn_wa2>    TYPE any,
               <dyn_field2> TYPE any,
               <fs_str2>    TYPE any.
DATA: go_excel    TYPE ole2_object,
      go_workbook TYPE ole2_object,
      go_sheet    TYPE ole2_object.

包含文件ZIT0003_SCREEN

*&---------------------------------------------------------------------*
*& 包含               ZIT0003_SCREEN
*&---------------------------------------------------------------------*
PARAMETERS: p_name TYPE dd03l-tabname OBLIGATORY.
PARAMETERS: p_file2 TYPE string MODIF ID m2.
SELECTION-SCREEN: FUNCTION KEY 1.

包含文件

*&---------------------------------------------------------------------*
*& 包含               ZIT0003_FORMS
*&---------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*& Form FRM_GET_DATA
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_get_data .


*取出表结构的字段目录

  CALL FUNCTION 'NAMETAB_GET'
    EXPORTING
      langu          = sy-langu
      tabname        = p_name
    TABLES
      nametab        = lt_table
    EXCEPTIONS
      no_texts_found = 1.
*根据取出的字段目录生成参考字段目录

  CLEAR lt_alv_cat.
  LOOP AT lt_table INTO ls_table.
    ls_alv_cat-fieldname = ls_table-fieldname.
    ls_alv_cat-ref_table = p_name.
    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>.



**从动态表中取数到动态内表中
*  SELECT * INTO CORRESPONDING FIELDS OF TABLE <dyn_table> UP TO 100
*  ROWS FROM (p_name).
*  BREAK-POINT.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_DOWN_EXCEL
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_down_excel .
  DATA: lv_selected_folder TYPE string,
        lv_fullpath        TYPE rlgrap-filename.
*  CALL METHOD cl_gui_frontend_services=>directory_browse
*    CHANGING
*      selected_folder      = lv_selected_folder
*    EXCEPTIONS
*      cntl_error           = 1
*      error_no_gui         = 2
*      not_supported_by_gui = 3
*      OTHERS               = 4.
*  IF sy-subrc <> 0.
*  ENDIF.
*
*  lv_fullpath = lv_selected_folder && p_name && '.xls'.

  SELECT COUNT(*) FROM dd03l WHERE tabname EQ p_name.
  IF sy-subrc NE 0.
    MESSAGE '该表不存在' TYPE 'S' DISPLAY LIKE 'E'.
    RETURN.
  ENDIF.

*取出表结构的字段目录
  CALL FUNCTION 'NAMETAB_GET'
    EXPORTING
      langu          = sy-langu
      tabname        = p_name
    TABLES
      nametab        = lt_table
    EXCEPTIONS
      no_texts_found = 1.
*根据取出的字段目录生成参考字段目录
  CLEAR lt_alv_cat.
  LOOP AT lt_table INTO ls_table.
    ls_alv_cat-fieldname = ls_table-fieldname.
    ls_alv_cat-ref_table = p_name.
    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_ref2.
*  BREAK-POINT.
*指定生成的内表到字段符号
  ASSIGN d_ref2->* TO <dyn_table2>.

*创建动态工作区结构
  CREATE DATA dyn_wa2 LIKE LINE OF <dyn_table2>.
  DATA: lt_zit0001 LIKE TABLE OF zwl_mpbc.
*创建动态工作区
  ASSIGN dyn_wa2->* TO <dyn_wa2>.

*  READ TABLE lt_alv_cat INTO ls_alv_cat INDEX 1.
*  IF sy-subrc EQ 0.
*    ASSIGN COMPONENT ls_alv_cat-fieldname OF STRUCTURE <dyn_wa2> TO <fs_str2>.
*    APPEND <dyn_wa2> TO <dyn_table2>.
*  ENDIF.

*  BREAK-POINT.
*  <fs_str> = '9998'.



  DATA: lv_destination  TYPE rlgrap-filename.
  DATA: ls_key          TYPE wwwdatatab.

*  SELECT * INTO CORRESPONDING FIELDS OF TABLE <dyn_table> UP TO 1
*  ROWS FROM (p_name).

* 首先下载模版
*    SELECT SINGLE relid objid FROM wwwdata INTO CORRESPONDING FIELDS OF ls_key WHERE relid = 'MI' AND objid = 'ZFICO049'.
*    CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
*      EXPORTING
*        key         = ls_key
*        destination = lv_destination.

* 打开模版
  CREATE OBJECT go_excel 'EXCEL.APPLICATION'.
  IF sy-subrc NE 0.
  ENDIF.
  SET PROPERTY OF go_excel 'DISPLAYALERTS' = 0.
  SET PROPERTY OF go_excel 'VISIBLE' = 1.
  CALL METHOD OF go_excel 'WORKBOOKS' = go_workbook.
  SET PROPERTY OF go_excel 'SHEETSINNEWWORKBOOK' = 1.
  CALL METHOD OF go_workbook 'ADD'.

*  CALL METHOD OF go_workbook 'OPEN'
*    EXPORTING
*      #1 = lv_fullpath.
  CALL METHOD OF go_excel 'SHEETS' = go_sheet
      EXPORTING
          #1 = 1.
  CALL METHOD OF go_sheet 'ACTIVATE'.
*填值
  PERFORM excel_row_insert USING go_sheet 3 1 1. "复制并插入一行
  LOOP AT lt_alv_cat INTO ls_alv_cat.
    PERFORM fill_range USING 1 sy-tabix ls_alv_cat-fieldname.
  ENDLOOP.
*关闭
  GET PROPERTY OF go_excel 'ACTIVESHEET' = go_sheet. "获取活动SHEET
  FREE OBJECT go_sheet.
  FREE OBJECT go_workbook.

  GET PROPERTY OF go_excel 'ACTIVEWORKBOOK' = go_workbook.


*  SET PROPERTY OF go_excel 'VISIBLE' = 0.  "是否显示EXCEL 此处显示不退出
  CALL METHOD OF go_workbook 'SAVES'.
*  CALL METHOD OF go_workbook 'SAVES'
*    EXPORTING
*      #1 = lv_fullpath
*      #2 = 1.
*  CALL METHOD OF go_workbook 'CLOSE'.
  CALL METHOD OF go_excel 'QUIT'.


  FREE OBJECT go_sheet.
  FREE OBJECT go_workbook.
  FREE OBJECT go_excel.
ENDFORM.


FORM sub_excel_save .
  GET PROPERTY OF go_excel 'ACTIVESHEET' = go_sheet. "获取活动SHEET
  FREE OBJECT go_sheet.
  FREE OBJECT go_workbook.

  GET PROPERTY OF go_excel 'ACTIVEWORKBOOK' = go_workbook.


*  SET PROPERTY OF go_excel 'VISIBLE' = 0.  "是否显示EXCEL 此处显示不退出

  CALL METHOD OF go_workbook 'SAVE'.
  CALL METHOD OF go_excel 'QUIT'.


  FREE OBJECT go_sheet.
  FREE OBJECT go_workbook.
  FREE OBJECT go_excel.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FILL_RANGE
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*      -->P_1      text
*      -->P_1      text
*      -->P_GS_HEAD_BUTXT  text
*&---------------------------------------------------------------------*
FORM fill_range  USING   VALUE(f_row)
                         VALUE(f_col)
                         VALUE(f_value).

  DATA: row     TYPE i,
        col     TYPE i,
        lo_cell TYPE ole2_object.
  row = f_row.
  col = f_col.
  CALL METHOD OF go_excel 'CELLS' = lo_cell
    EXPORTING
    #1 = row
    #2 = col.
  SET PROPERTY OF lo_cell 'VALUE' = f_value.

ENDFORM.
*&---------------------------------------------------------------------*
*& Form EXCEL_ROW_INSERT
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*      -->P_GO_SHEET  text
*      -->P_LV_LOOP2  text
*      -->P_1      text
*&---------------------------------------------------------------------*
FORM excel_row_insert  USING lcobj_sheet
                            lc_row
                            lc_count
                            lv_rowcopy.
  DATA lc_range TYPE ole2_object.
  DATA h_borders  TYPE ole2_object.
  DO lc_count TIMES.
    CALL METHOD OF lcobj_sheet 'ROWS' = lc_range
      EXPORTING #1 = lv_rowcopy.
    CALL METHOD OF lc_range 'COPY'. "COPY第10行插入一个新行
    CALL METHOD OF lcobj_sheet 'ROWS' = lc_range
      EXPORTING #1 = lc_row.
    CALL METHOD OF lc_range 'INSERT'.
    CALL METHOD OF lc_range 'CLEARCONTENTS'. "是否需要清空CELL
  ENDDO.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_SET_FILE_F4
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*      -->P_P_FILE2  text
*      <--P_P_FILE2  text
*&---------------------------------------------------------------------*
FORM frm_set_file_f4  USING i_file CHANGING i_file2.
* 局部变量-文件名定义
  DATA l_filename TYPE rlgrap-filename.
* 选择文件搜索帮助
  CALL FUNCTION 'WS_FILENAME_GET'
    EXPORTING
      def_path         = i_file
      mask             = ',*.xlsx,*.XLSX,*.xls,*.XLS.'
      mode             = '0'
      title            = text-002
    IMPORTING
      filename         = l_filename
    EXCEPTIONS
      inv_winsys       = 1
      no_batch         = 2
      selection_cancel = 3
      selection_error  = 4
      OTHERS           = 5.
* 如果选择了文件
  IF sy-subrc = 0 AND l_filename IS NOT INITIAL.
    i_file2
= l_filename.
  ENDIF.

ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_GET_FILED
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_get_filed .
  DATA: lv_i_tab_raw_data TYPE truxs_t_text_data,         lv_i_filename
  LIKE rlgrap-filename.
*  CLEAR gt_output.
  DATA(lv_platform_code) = cl_gui_frontend_services=>get_platform( ).
  IF lv_platform_code EQ '14'." WINDOWS
    lv_i_filename = p_file2.
    CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
      EXPORTING
        i_line_header        = 'X'
        i_tab_raw_data       = lv_i_tab_raw_data
        i_filename           = lv_i_filename
      TABLES
        i_tab_converted_data = <dyn_table>
      EXCEPTIONS
        conversion_failed    = 1
        OTHERS               = 2.
    IF sy-subrc <> 0.
    ENDIF.
  ELSEIF lv_platform_code EQ '13'." MAC
    cl_gui_frontend_services=>gui_upload(
  EXPORTING         filename
  = p_file2         has_field_separator
  = 'X'         filetype
  = 'ASC'
  CHANGING         data_tab
  = <dyn_table>
  EXCEPTIONS         file_open_error
  = 1         file_read_error
  = 2         no_batch
  = 3         gui_refuse_filetransfer
  = 4         invalid_type
  = 5         no_authority
  = 6         unknown_error
  = 7         bad_data_format
  = 8         header_not_allowed
  = 9         separator_not_allowed
  = 10         header_too_long
  = 11         unknown_dp_error
  = 12         access_denied
  = 13         dp_out_of_memory
  = 14         disk_full
  = 15         dp_timeout
  = 16         not_supported_by_gui
  = 17         error_no_gui
  = 18
  OTHERS = 19 ).
    IF sy-subrc <> 0.
    ENDIF.
  ELSE.
    MESSAGE e008(zfico039).
  ENDIF.

ENDFORM.

FORM frm_file_data2 .
  DATA: lv_end TYPE c.
  DATA: lt_data  TYPE alsmex_tabline OCCURS 0 WITH HEADER LINE.
  DATA  lv_filename TYPE rlgrap-filename.
  DATA lv_tabix TYPE sy-tabix.   lv_filename
  = p_file2.


  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'    "读取excel文件中的内容
    EXPORTING
      filename                = lv_filename
      i_begin_col             = '1'
      i_begin_row             = '2'
      i_end_col               = '28'
      i_end_row               = '99999'
    TABLES
      intern                  = lt_data
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.

  LOOP AT lt_data.
*    CLEAR lv_end.
*    AT END OF row.
*      lv_end = 'X'.
*    ENDAT.
    lv_tabix = lt_data-col.
    READ TABLE lt_alv_cat INTO ls_alv_cat INDEX lv_tabix.
    IF sy-subrc EQ 0.
      ASSIGN COMPONENT ls_alv_cat-fieldname OF STRUCTURE <dyn_wa> TO <fs_str>.       <fs_str>
      = lt_data-value.
    ENDIF.

    AT END OF row.
      APPEND <dyn_wa> TO <dyn_table>.
    ENDAT.

  ENDLOOP.

ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_DISPLAY_DATA
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_display_data .
*显示内表中的数据

  CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
    EXPORTING
      i_structure_name = p_name
    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.
*&---------------------------------------------------------------------*
*& Form FRM_SAVE_DATA
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_save_data .
  IF <dyn_table>[] IS NOT INITIAL.
    MODIFY (p_name) FROM TABLE <dyn_table>.
    IF sy-subrc EQ 0.
      COMMIT WORK AND WAIT.
    ELSE.
      ROLLBACK WORK.
    ENDIF.
  ENDIF.

ENDFORM.