在SAP中货源清单是控制物料只能从特定供应商处采购的主数据它定义了物料与供应商、工厂、采购组织之间的合法供应关系相对于逐条使用事务代码ME01维护货源清单开发自定义程序通过Excel批量维护货源清单能够降低用户的使用门槛同时提升用户的工作效率Excel导入模板效果展示用户在Excel导入模板填写相关信息以后进行文件上传操作点击执行按钮效果如下所示提示Tips自定义程序增设了数据校验逻辑当用户执行Excel文件上传以后会进行ALV报表呈现用户上传的结果如果消息灯报红灯表明Excel填写的信息在SAP系统里面不存在或者格式输入不正确点击【批量导入】按钮创建维护货源清单的时候消息灯报红的那一行明细是无法创建或更新货源清单的点击【批量导入】按钮执行结果呈现如下图所示我们可以看到ALV报表的消息灯报绿灯表明Excel指定的那行明细已经成功导入到SAP系统中了我们可以使用ME01 或者 ME03 去查看成功导入的那行明细信息完整代码如下所示主程序(zrs0513)*---------------------------------------------------------------------* * Report ZRS0513 * *---------------------------------------------------------------------* ************************************************************************ * Program ID: ZRS0513 * Program Description: Excel批量維護貨源清單 * Programmer Name : Andy * Create Date: 2026/08/17 * Change History: *----------------------------------------------------------------------* * Ver. SR/Project Date Author Description *------- --------------- ------------ ---------- ----------------------* * V001 2026/08 Andy Excel批量維護貨源清單 *----------------------------------------------------------------------* REPORT zrs0513. INCLUDE zrs0513_top. 數據定義 INCLUDE zrs0513_f01. 子例程 *----------------------------------------------------------------------* * DESC: INITIALIZATION *----------------------------------------------------------------------* INITIALIZATION. *----------------------------------------------------------------------* * DESC: AT SELECTION-SCREEN ON VALUE-REQUEST FOR [field] 事件 *----------------------------------------------------------------------* AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_path. 為選擇屏幕的自定義字段提供搜索幫助 PERFORM frm_get_path. *----------------------------------------------------------------------* * DESC: AT SELECTION-SCREEN *----------------------------------------------------------------------* AT SELECTION-SCREEN. *----------------------------------------------------------------------* * DESC: START-OF-SELECTION *----------------------------------------------------------------------* START-OF-SELECTION. PERFORM frm_upload_excel. 解析Excel文件中的數据 PERFORM frm_get_output. 將Upload數据存放到呈現ALV的相關內表 *----------------------------------------------------------------------* * DESC: END-OF-SELECTION *----------------------------------------------------------------------* END-OF-SELECTION. PERFORM frm_display_alv. ALV呈現數据INCLUDE程序(zrs0513_top)*---------------------------------------------------------------------* * 包含 ZRS0513_TOP *---------------------------------------------------------------------* TYPE-POOLS: slis. TABLES: eord. * 選擇屏幕 SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-001. PARAMETERS: p_path TYPE rlgrap-filename. 上傳文件路徑 SELECTION-SCREEN END OF BLOCK b1. * 自定義數據類型 TYPES: BEGIN OF ty_upload, werks TYPE eord-werks, Plant matnr TYPE eord-matnr, Material lifnr TYPE eord-lifnr, Vendor vdatu TYPE eord-vdatu, Valid From bdatu TYPE eord-bdatu, Valid To ekorg TYPE eord-ekorg, POrg END OF ty_upload. TYPES: BEGIN OF ty_output, werks TYPE eord-werks, Plant matnr TYPE eord-matnr, Material lifnr TYPE eord-lifnr, Vendor vdatu TYPE eord-vdatu, Valid From bdatu TYPE eord-bdatu, Valid To ekorg TYPE eord-ekorg, POrg msg TYPE bapi_msg, 消息 status TYPE icon_d, 消息燈 sel(1) TYPE c, 選擇標志 END OF ty_output. * 定義內表和結構體變量 DATA: gs_upload TYPE ty_upload, gt_upload TYPE STANDARD TABLE OF ty_upload, gt_upload內表用于存儲解析上傳的Excel文件數据 gs_output TYPE ty_output, gt_output TYPE STANDARD TABLE OF ty_output. gt_output內表用于存儲展示在ALV報表的數据 * ALV參數定義 DATA: gs_layout TYPE lvc_s_layo, 用于定義ALV表單的相關格式、屬性 gs_fcat TYPE lvc_s_fcat, 字段目錄工作區 gt_fcat LIKE TABLE OF gs_fcat. 字段目錄內表INCLUDE程序(zrs0513_f01)*---------------------------------------------------------------------* * 包含 ZRS0513_F01 *---------------------------------------------------------------------* *---------------------------------------------------------------------* * Form frm_get_path *---------------------------------------------------------------------* * 獲取文件上傳路徑 *----------------------------------------------------------------------* form frm_get_path. data: lt_file_table type filetable, lv_rc type i, lv_initial_path type string value C:\, ls_file type file_info. ABAP對象模式(調用類的方法)---提供文件選擇對話框的方式獲取文件的完整路徑 call method cl_gui_frontend_servicesfile_open_dialog exporting initial_directory lv_initial_path 默認上傳路徑 changing file_table lt_file_table 上傳文件的完整路徑 rc lv_rc exceptions file_open_dialog_failed 1 cntl_error 2 error_no_gui 3 not_supported_by_gui 4 others 5. if sy-subrc 0. read table lt_file_table into ls_file index 1. if sy-subrc 0. p_path ls_file-filename. 文件路徑賦值 endif. endif. endform. frm_get_path *---------------------------------------------------------------------* * Form frm_upload_excel *---------------------------------------------------------------------* * 解析Excel文件中的數据 *----------------------------------------------------------------------* form frm_upload_excel. data: lv_filename type rlgrap-filename, lt_data type standard table of alsmex_tabline, ls_data type alsmex_tabline. field-symbols: lfv_value type any. 顯式聲明字段符號 if p_path is not initial. 判斷文件路徑是否不為空 lv_filename p_path. 賦值 調用函數讀取Excel文件中的數据存放到內表中 call function ALSM_EXCEL_TO_INTERNAL_TABLE exporting filename lv_filename 導入的文件名稱路徑 i_begin_col 2 導入單元格的開始列 i_begin_row 2 導入單元格的開始行 i_end_col 255 導入單元格的結束列 i_end_row 65536 導入單元格的結束行 tables intern lt_data 內表,存儲單元格中的內容 exceptions inconsistent_parameters 1 upload_ole 2 others 3. 將內表lt_data中的值轉換到內表gt_upload中 loop at lt_data into ls_data. assign component ls_data-col of structure gs_upload to lfv_value. shift ls_data-value left deleting leading space. 去除空格 lfv_value ls_data-value. 賦值 at end of row. append gs_upload to gt_upload. clear gs_upload. endat. endloop. else. message 文件路徑為空請上傳文件! type I. endif. endform. frm_upload_excel *---------------------------------------------------------------------* * Form frm_get_output *---------------------------------------------------------------------* * 將Upload數据存放到呈現ALV的相關內表 *----------------------------------------------------------------------* form frm_get_output. 校驗用的變量 data: lv_plant_check like t001w-werks, lv_matnr_check like mara-matnr, lv_marc_check like mara-matnr, lv_lifnr_check like lfa1-lifnr, lv_lfm1_check like lfa1-lifnr, lv_ekorg_check like t024e-ekorg. constants: lv_icon_red type char4 value 0A. 紅色LED圖標代碼 constants: lv_icon_green type char4 value 08. 綠色LED圖標代碼 refresh gt_output. loop at gt_upload into gs_upload. 供應商增加前導零 call function CONVERSION_EXIT_ALPHA_INPUT exporting input gs_upload-lifnr importing output gs_upload-lifnr. 補0后的結果 move-corresponding gs_upload to gs_output. 賦值 append gs_output to gt_output. clear: gs_upload, gs_output. endloop. field-symbols: lfs_output type ty_output. * 數據校驗 if gt_output is not initial. loop at gt_output assigning lfs_output. * 校驗工廠 if lfs_output-werks is initial. lfs_output-status lv_icon_red. message e000(zrs0513_msg) into lfs_output-msg. Plant 必填 continue. else. select single werks from t001w into lv_plant_check where werks lfs_output-werks. if sy-subrc 0. lfs_output-status lv_icon_red. message e001(zrs0513_msg) with lfs_output-werks into lfs_output-msg. Plant 1不存在 continue. endif. endif. * 校驗物料 if lfs_output-matnr is initial. lfs_output-status lv_icon_red. message e002(zrs0513_msg) into lfs_output-msg. Matnr 必填 continue. else. select single matnr from mara into lv_matnr_check where matnr lfs_output-matnr. if sy-subrc 0. lfs_output-status lv_icon_red. message e003(zrs0513_msg) with lfs_output-matnr into lfs_output-msg. Material 1不存在 continue. else. 檢查物料是否在工厂下維護 select single matnr from marc into lv_marc_check where matnr lfs_output-matnr and werks lfs_output-werks. if sy-subrc 0. lfs_output-status lv_icon_red. message e004(zrs0513_msg) with lfs_output-matnr lfs_output-werks into lfs_output-msg. Material 1 未在廠別 2 下維護 continue. endif. endif. endif. * 校驗供應商 if lfs_output-lifnr is initial. lfs_output-status lv_icon_red. message e005(zrs0513_msg) into lfs_output-msg. Vendor 必填 continue. else. select single lifnr from lfa1 into lv_lifnr_check where lifnr lfs_output-lifnr. if sy-subrc 0. lfs_output-status lv_icon_red. message e006(zrs0513_msg) with lfs_output-lifnr into lfs_output-msg. 供應商 1不存在 continue. else. 檢查供應商是否在采購組織下維護 select single lifnr from lfm1 into lv_lfm1_check where lifnr lfs_output-lifnr and ekorg lfs_output-ekorg. if sy-subrc 0. lfs_output-status lv_icon_red. message e007(zrs0513_msg) with lfs_output-lifnr lfs_output-ekorg into lfs_output-msg. 供應商 1 未在採購組織 2 下維護 continue. endif. endif. endif. * 校驗採購組織 if lfs_output-ekorg is initial. lfs_output-status lv_icon_red. message e008(zrs0513_msg) into lfs_output-msg. POrg 必填 continue. else. select single ekorg from t024e into lv_ekorg_check where ekorg lfs_output-ekorg. if sy-subrc 0. lfs_output-status lv_icon_red. message e009(zrs0513_msg) with lfs_output-ekorg into lfs_output-msg. 採購組織 1不存在 continue. endif. endif. * 校驗日期 if lfs_output-vdatu is initial. lfs_output-status lv_icon_red. message e010(zrs0513_msg) into lfs_output-msg. Valid from 必填 continue. else. call function DATE_CHECK_PLAUSIBILITY exporting date lfs_output-vdatu exceptions plausibility_check_failed 1 others 2. if sy-subrc 0. lfs_output-status lv_icon_red. message e011(zrs0513_msg) with lfs_output-vdatu into lfs_output-msg. 起始日期 1 無效 continue. endif. endif. if lfs_output-bdatu is initial. lfs_output-status lv_icon_red. message e012(zrs0513_msg) into lfs_output-msg. Valid to 必填 continue. else. call function DATE_CHECK_PLAUSIBILITY exporting date lfs_output-bdatu exceptions plausibility_check_failed 1 others 2. if sy-subrc 0. lfs_output-status lv_icon_red. message e013(zrs0513_msg) with lfs_output-bdatu into lfs_output-msg. 截止日期 1 無效 continue. endif. endif. 檢查起始日期是否小于等于截止日期 if lfs_output-vdatu is not initial and lfs_output-bdatu is not initial. if lfs_output-vdatu lfs_output-bdatu. lfs_output-status lv_icon_red. message e014(zrs0513_msg) into lfs_output-msg. 起始日期不能大于截止日期 continue. endif. endif. endloop. endif. endform. frm_get_output *---------------------------------------------------------------------* * Form frm_display_alv *---------------------------------------------------------------------* * ALV呈現數据 *----------------------------------------------------------------------* form frm_display_alv. perform frm_alv_set_fields. 調用函數顯示ALV數据 call function REUSE_ALV_GRID_DISPLAY_LVC exporting i_callback_program sy-cprog i_callback_pf_status_set FRM_SET_PF_STATUS 子例程(設置狀態欄) i_callback_user_command FRM_USER_COMMAND 子例程(用戶指令響應) i_bypassing_buffer abap_true is_layout_lvc gs_layout 設置ALV布局 it_fieldcat_lvc gt_fcat 設置ALV列屬性 i_save A tables t_outtab gt_output 內表數据 exceptions program_error 1 others 2. endform. frm_display_alv *---------------------------------------------------------------------* * Form frm_alv_set_fields *---------------------------------------------------------------------* * 設置ALV樣式和字段屬性 *----------------------------------------------------------------------* form frm_alv_set_fields. * 設置行的屬性(alv界面格式) clear gs_layout. gs_layout-box_fname SEL. 選擇標識 gs_layout-zebra abap_true. 斑馬條紋顯示 gs_layout-cwidth_opt abap_true. 优化列寬設置 gs_layout-col_opt abap_true. * ALV字段處理宏 define catalog. gs_fcat-fieldname 1. 設置要輸出的表格列的值在內表中定義的字段名(必須大寫) gs_fcat-scrtext_l 2. 設置要輸出的表格列的列名即ALV報表顯示的列名 gs_fcat-no_zero 3. 去除前導零 gs_fcat-edit 4. 可編輯 gs_fcat-just 5. 對齊方式 gs_fcat-key 6. 關鍵字段 append gs_fcat to gt_fcat. end-of-definition. * 1 2 3 4 5 6 catalog: STATUS 消息燈 C abap_true, 消息燈 MSG 消息 abap_true, 消息 WERKS 廠別 abap_true, Plant MATNR 物料編碼 abap_true, Material LIFNR 供應商編碼 abap_true, Vendor VDATU 有效期起 abap_true, Valid From BDATU 有效期止 abap_true, Valid To EKORG 採購組織 abap_true. POrg endform. frm_alv_set_fields * 設置狀態欄 form frm_set_pf_status using ct_extab type slis_t_extab. set pf-status ZSTALV excluding ct_extab. endform. frm_set_pf_status * 設置用戶指令響應 form frm_user_command using pv_ucomm like sy-ucomm ps_selfield type slis_selfield. case pv_ucomm. when ZUPLOAD. perform frm_import_data. endcase. ps_selfield-refresh X. 強制刷新 endform. frm_user_command *---------------------------------------------------------------------* * Form frm_import_data *---------------------------------------------------------------------* * 批量導入維護貨源清單 *----------------------------------------------------------------------* form frm_import_data. data: lt_eordu like table of eordu with header line. constants: lv_icon_red type char4 value 0A. 紅色LED圖標代碼 constants: lv_icon_green type char4 value 08. 綠色LED圖標代碼 data: lv_zeord type eord-zeord, lv_infnr type eine-infnr. * 調用函數之前需要對數据進行排序 sort gt_output by werks matnr. 排序 field-symbols: lfs_output type ty_output. * ALV獲取可以進行批導的數据(消息燈不報紅的數据) loop at gt_output assigning lfs_output where status is initial. clear: lt_eordu, lt_eordu[], lv_zeord, lv_infnr. refresh lt_eordu. 檢查貨源清單是否存在 select single zeord from eord into lv_zeord where matnr lfs_output-matnr and lifnr lfs_output-lifnr and werks lfs_output-werks and ekorg lfs_output-ekorg. move-corresponding lfs_output to lt_eordu. 賦值 lt_eordu-erdat sy-datum. lt_eordu-ernam sy-uname. if sy-subrc 0 and lv_zeord is not initial. 記錄已存在,更新 lt_eordu-kz U. lt_eordu-zeord lv_zeord. 更新時傳入系統記錄號 else. 記錄不存在,新增 lt_eordu-kz I. endif. append lt_eordu. call function ME_INITIALIZE_SOURCE_LIST. 源清單維護初始化 call function ME_DIRECT_INPUT_SOURCE_LIST exporting i_matnr lt_eordu-matnr i_werks lt_eordu-werks tables t_eord lt_eordu[] exceptions plant_missing 1 material_missing 2 error_message 4 others 3. if sy-subrc ne 0. call function BAPI_TRANSACTION_ROLLBACK. lfs_output-status lv_icon_red. 消息燈報紅 message id sy-msgid type E number sy-msgno with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 into lfs_output-msg. else. call function ME_POST_SOURCE_LIST_NEW exporting i_matnr lt_eordu-matnr. call function BAPI_TRANSACTION_COMMIT exporting wait X. lfs_output-status lv_icon_green. 消息燈報綠 if lt_eordu-kz U. lfs_output-msg 貨源清單更新成功. else. lfs_output-msg 貨源清單創建成功. endif. endif. endloop. endform. frm_import_data上述代码涉及的消息类以及文本元素如下所示消息类(ZRS0513_MSG)文本内容TEXT-001文本内容GUI STATUS状态栏