ABAP根据表名创建动态内表并更新(通用自建表批导)
查询界面
执行结果
SE11
主
*&---------------------------------------------------------------------* *& 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
*&---------------------------------------------------------------------* *& 包含 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 zit0001. *创建动态工作区 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. DATA lv_line TYPE i. lv_line = lines( lt_table[] ). CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE' "读取excel文件中的内容 EXPORTING filename = lv_filename i_begin_col = '1' i_begin_row = '2' i_end_col = lv_line "'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.