批量处理 Excel 导入模板11支持多 Sheet 页
原创 Tab Zhu [ SAP TAB ](javascript:void\(0\);)
**点击蓝字关注我们**
富强 民主 文明 和谐
自由 平等 公正 法制
爱国 敬业 诚信 友善
_**Title** _
****个人** **Excel** **上载模板** **(** **兼容多个** **Sheet)** **
**不念过** **去**
**START**
**不畏将来**
之前的excel模板使用了很久，但是最近老是遇到多个sheet页面上传的需求，所以自己整理了一个上传多个sheet页excel上载模板，各位客官可以选择性食用(Ctrl+C&V)
参考类型为ZCL_TAB_DOC=>TT_SHEETNAME_INFO,存放了如何输入Excel的信息,
LT_TABC 的类型是参考的ZCL_TAB_DOC=>TT_TABC 自定义的类型请看代码 测试为通过Excel 上传数据，且支持多个Sheet页签
我们准备2个sheet 分别上传物料的不同信息 Sheet1
Sheet2
调用默认类打开需要上传的Excel(其实就是封装了一下cl_gui_frontend_services=>file_open_dialog 给下默认值)
调用Import_excel_by_ole类,获取excel数据，按照Sheet名称对应放在LT_TABC中
调用excel_convert_table 来将excel中每个sheet页数据填入到对应的内表中， 其中：FRM_SPECIAL_PROCESS
为需要执行的特殊处理，比如日期转化(2021/9/8->20210908),单位转化(PC->ST)等操作
最后我们把物料单位和描述合在一起，输出看一下效果
下面是调用的模板程序(Copy 可用）
_**01** _
**调用程序示例
**** **
*&---------------------------------------------------------------------**& Report Z_TAB_TEST04*&---------------------------------------------------------------------*
REPORT z_tab_test04.*&=====================================================================**& CREATE date: 2021.09.08*& created by: tab-zhu (微信公众号：tab_jxzhu)*&=====================================================================*DATA:BEGIN OF gs_excel, matnr TYPE mara-matnr, maktx TYPE makt-maktx, meins TYPE mara-meins, creationdate TYPE datum, creationtime TYPE uzeit, END OF gs_excel.DATA gt_excel LIKE TABLE OF gs_excel.
DATA:BEGIN OF gs_excel_sheet1, matnr TYPE mara-matnr, meins TYPE mara-meins, creationdate TYPE datum, creationtime TYPE uzeit, END OF gs_excel_sheet1.DATA gt_excel_sheet1 LIKE TABLE OF gs_excel_sheet1.
DATA:BEGIN OF gs_excel_sheet2, matnr TYPE mara-matnr, maktx TYPE makt-maktx, END OF gs_excel_sheet2.DATA gt_excel_sheet2 LIKE TABLE OF gs_excel_sheet2.
DATA:lr_excel TYPE REF TO zcl_tab_doc.DATA lt_files TYPE filetable.DATA l_rc TYPE i.DATA l_filename TYPE string.
DATA lt_sheetname TYPE zcl_tab_doc=>tt_sheetname_info."存放了如何输入excel的信息
DATA: lr_functions TYPE REF TO cl_salv_functions_list.
DATA lt_tabc TYPE zcl_tab_doc=>tt_tabc."存放获取sheet页的数据PARAMETERS:p_file TYPE rlgrap-filename OBLIGATORY MEMORY ID mi.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file. " CALL METHOD zcl_tab_doc=>file_open_dialog_default CHANGING ct_file_table = lt_files c_rc = l_rc EXCEPTIONS file_open_dialog_failed = 1 cntl_error = 2 error_no_gui = 3 not_support_by_gui = 4 OTHERS = 5. READ TABLE lt_files INTO DATA(ls_files) INDEX 1. IF sy-subrc EQ 0. p_file = ls_files-filename. ENDIF.
START-OF-SELECTION. lt_sheetname = VALUE #( ( sheetname = 'Sheet1' startrow = 2 startcol = 1 every_copy_lines = 30 endcol = 4 ) ( sheetname = 'Sheet2' startrow = 2 startcol = 1 every_copy_lines = 30 endcol = 2 ) ).
CREATE OBJECT lr_excel. l_filename = p_file. CALL METHOD lr_excel->import_excel_by_ole EXPORTING i_filename = l_filename CHANGING ct_sheetname = lt_sheetname ct_tabc = lt_tabc EXCEPTIONS file_open_error = 1 OTHERS = 2. IF sy-subrc <> 0. MESSAGE e398(00) WITH |File opened failed!| ''. ENDIF.CHECK lt_tabc[] IS NOT INITIAL.
READ TABLE lt_tabc INTO DATA(ls_tabc) WITH KEY sheetname = 'Sheet1'. IF sy-subrc EQ 0. CALL METHOD lr_excel->excel_convert_table EXPORTING* i_separator = CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB is_table = ls_tabc i_repid = 'Z_TAB_TEST04' i_special_process = 'FRM_SPECIAL_PROCESS' CHANGING ct_table = gt_excel_sheet1 EXCEPTIONS context_convert_failed = 1 OTHERS = 2. IF sy-subrc <> 0. MESSAGE e398(00) WITH 'Context converted failed!' ''. ENDIF. ENDIF.CHECK gt_excel_sheet1[] IS NOT INITIAL.
READ TABLE lt_tabc INTO ls_tabc WITH KEY sheetname = 'Sheet2'. IF sy-subrc EQ 0. CALL METHOD lr_excel->excel_convert_table EXPORTING* i_separator = CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB is_table = ls_tabc i_repid = 'Z_TAB_TEST04' i_special_process = 'FRM_SPECIAL_PROCESS' CHANGING ct_table = gt_excel_sheet2 EXCEPTIONS context_convert_failed = 1 OTHERS = 2. IF sy-subrc <> 0. MESSAGE e398(00) WITH 'Context converted failed!' ''. ENDIF. ENDIF.SORT gt_excel_sheet2 BY matnr.
LOOP AT gt_excel_sheet1 INTO gs_excel_sheet1. READ TABLE gt_excel_sheet2 INTO gs_excel_sheet2 WITH KEY matnr = gs_excel_sheet1-matnr BINARY SEARCH. IF sy-subrc EQ 0. gs_excel-maktx = gs_excel_sheet2-maktx. ENDIF. MOVE-CORRESPONDING gs_excel_sheet1 TO gs_excel. APPEND gs_excel TO gt_excel. CLEAR gs_excel. ENDLOOP.DATA go_alv TYPE REF TO cl_salv_table.
TRY. cl_salv_table=>factory( IMPORTING r_salv_table = go_alv CHANGING t_table = gt_excel[] ).
CATCH cx_salv_msg. ENDTRY. lr_functions = go_alv->get_functions( ). lr_functions->set_all( 'X' ). CHECK go_alv IS BOUND. go_alv->display( ).
FORM frm_special_process USING is_componet TYPE abap_compdescr i_row TYPE i i_col TYPE i CHANGING c_cell TYPE string. DATA lv_matnr TYPE mara-matnr. CHECK c_cell IS NOT INITIAL. CASE i_col. WHEN '1'. "内外部转化 lv_matnr = c_cell. CALL FUNCTION 'CONVERSION_EXIT_MATN1_INPUT' EXPORTING input = lv_matnr IMPORTING output = lv_matnr EXCEPTIONS length_error = 1 OTHERS = 2. IF sy-subrc <> 0. CLEAR lv_matnr. ENDIF. c_cell = lv_matnr. WHEN '2'. CHECK is_componet-name = 'MEINS'."转化单位 CALL FUNCTION 'CONVERSION_EXIT_CUNIT_INPUT' EXPORTING input = c_cell language = sy-langu IMPORTING output = c_cell EXCEPTIONS unit_not_found = 1 OTHERS = 2. IF sy-subrc <> 0. MESSAGE e398(00) WITH |Unit convert failed!| ''. ENDIF. WHEN '3'. "日期转换 DATA l_num2(2) TYPE n. SPLIT c_cell AT '/' INTO TABLE DATA(lt_cell). CLEAR c_cell. LOOP AT lt_cell INTO DATA(ls_cell). CASE sy-tabix. WHEN 1. c_cell = ls_cell. WHEN 2 OR 3. l_num2 = ls_cell. c_cell = |{ c_cell }{ l_num2 }|. WHEN OTHERS. ENDCASE. ENDLOOP. REPLACE ALL OCCURRENCES OF '-' IN c_cell WITH ''. REPLACE ALL OCCURRENCES OF '/' IN c_cell WITH ''. CONDENSE c_cell NO-GAPS. WHEN '4'. "时间转换 REPLACE ALL OCCURRENCES OF ':' IN c_cell WITH ''. CONDENSE c_cell NO-GAPS. WHEN OTHERS. ENDCASE.ENDFORM._**02** _
**Excel文档处理类**
...
来源说明：本文内容由「批量处理Excel导入模板11支持多Sheet页.md」整理生成，仅用于内部技术分享与学习交流。