在SAP中，货源清单是控制物料只能从特定供应商处采购的主数据，它定义了物料与供应商、工厂、采购组织之间的合法供应关系
相对于逐条使用事务代码 ME01 维护货源清单，开发自定义程序，通过Excel批量维护货源清单，能够降低用户的使用门槛，同时提升用户的工作效率
Excel导入模板
效果展示
用户在Excel导入模板填写相关信息以后，进行文件上传操作
点击
执行按钮，效果如下所示：
提示Tips：自定义程序，增设了数据校验逻辑，当用户执行Excel文件上传以后，会进行ALV报表呈现用户上传的结果，如果消息灯报红灯，表明Excel填写的信息在SAP系统里面不存在，或者格式输入不正确，点击【批量导入】按钮创建维护货源清单的时候，消息灯报红的那一行明细是无法创建或更新货源清单的
点击【批量导入】按钮，执行结果呈现如下图所示
我们可以看到ALV报表的消息灯报绿灯，表明Excel指定的那行明细已经成功导入到SAP系统中了，我们可以使用ME01 或者 ME03 去查看成功导入的那行明细信息
完整代码如下所示
主程序(zrs0513)
1*&---------------------------------------------------------------------* 2*& Report ZRS0513 * 3*&---------------------------------------------------------------------* 4 5************************************************************************ 6* Program ID: ZRS0513 7* Program Description: Excel批量維護貨源清單 8* Programmer Name : Andy 9* Create Date: 2026/08/17 10* Change History: 11*----------------------------------------------------------------------* 12* Ver. SR/Project Date Author Description 13*------- --------------- ------------ ---------- ----------------------* 14* V001 2026/08 Andy Excel批量維護貨源清單 15*----------------------------------------------------------------------* 16 17REPORT zrs0513. 18 19INCLUDE zrs0513_top. " 數據定義 20 21INCLUDE zrs0513_f01. " 子例程 22 23 24*----------------------------------------------------------------------* 25* DESC: INITIALIZATION 26*----------------------------------------------------------------------* 27INITIALIZATION. 28 29 30*----------------------------------------------------------------------* 31* DESC: AT SELECTION-SCREEN ON VALUE-REQUEST FOR [field] 事件 32*----------------------------------------------------------------------* 33AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_path. " 為選擇屏幕的自定義字段提供搜索幫助 34 PERFORM frm_get_path. 35 36 37*----------------------------------------------------------------------* 38* DESC: AT SELECTION-SCREEN 39*----------------------------------------------------------------------* 40AT SELECTION-SCREEN. 41 42 43*----------------------------------------------------------------------* 44* DESC: START-OF-SELECTION 45*----------------------------------------------------------------------* 46START-OF-SELECTION. 47 PERFORM frm_upload_excel. " 解析Excel文件中的數据 48 PERFORM frm_get_output. " 將Upload數据存放到呈現ALV的相關內表 49 50 51*----------------------------------------------------------------------* 52* DESC: END-OF-SELECTION 53*----------------------------------------------------------------------* 54END-OF-SELECTION. 55 PERFORM frm_display_alv. " ALV呈現數据
INCLUDE程序(zrs0513_top)
1*&---------------------------------------------------------------------* 2*& 包含 ZRS0513_TOP 3*&---------------------------------------------------------------------* 4 5TYPE-POOLS: slis. 6 7TABLES: eord. 8 9* 選擇屏幕 10SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-001. 11 12 PARAMETERS: p_path TYPE rlgrap-filename. " 上傳文件路徑 13 14SELECTION-SCREEN END OF BLOCK b1. 15 16 17* 自定義數據類型 18TYPES: BEGIN OF ty_upload, 19 werks TYPE eord-werks, " Plant 20 matnr TYPE eord-matnr, " Material 21 lifnr TYPE eord-lifnr, " Vendor 22 vdatu TYPE eord-vdatu, " Valid From 23 bdatu TYPE eord-bdatu, " Valid To 24 ekorg TYPE eord-ekorg, " POrg 25 END OF ty_upload. 26 27TYPES: BEGIN OF ty_output, 28 werks TYPE eord-werks, " Plant 29 matnr TYPE eord-matnr, " Material 30 lifnr TYPE eord-lifnr, " Vendor 31 vdatu TYPE eord-vdatu, " Valid From 32 bdatu TYPE eord-bdatu, " Valid To 33 ekorg TYPE eord-ekorg, " POrg 34 msg TYPE bapi_msg, " 消息 35 status TYPE icon_d, " 消息燈 36 sel(1) TYPE c, " 選擇標志 37 END OF ty_output. 38 39 40* 定義內表和結構體變量 41DATA: gs_upload TYPE ty_upload, 42 gt_upload TYPE STANDARD TABLE OF ty_upload, " gt_upload內表用于存儲解析上傳的Excel文件數据 43 gs_output TYPE ty_output, 44 gt_output TYPE STANDARD TABLE OF ty_output. " gt_output內表用于存儲展示在ALV報表的數据 45 46 47* ALV參數定義 48DATA: gs_layout TYPE lvc_s_layo, " 用于定義ALV表單的相關格式、屬性 49 gs_fcat TYPE lvc_s_fcat, " 字段目錄工作區 50 gt_fcat LIKE TABLE OF gs_fcat. " 字段目錄內表
INCLUDE程序(zrs0513_f01)
1*&---------------------------------------------------------------------* 2*& 包含 ZRS0513_F01 3*&---------------------------------------------------------------------* 4 5*&---------------------------------------------------------------------* 6*& Form frm_get_path 7*&---------------------------------------------------------------------* 8* 獲取文件上傳路徑 9*----------------------------------------------------------------------* 10form frm_get_path. 11 data: lt_file_table type filetable, 12 lv_rc type i, 13 lv_initial_path type string value 'C:\', 14 ls_file type file_info. 15 16 " ABAP對象模式(調用類的方法)---提供文件選擇對話框的方式獲取文件的完整路徑 17 call method cl_gui_frontend_services=>file_open_dialog 18 exporting 19 initial_directory = lv_initial_path " 默認上傳路徑 20 changing 21 file_table = lt_file_table " 上傳文件的完整路徑 22 rc = lv_rc 23 exceptions 24 file_open_dialog_failed = 1 25 cntl_error = 2 26 error_no_gui = 3 27 not_supported_by_gui = 4 28 others = 5. 29 if sy-subrc = 0. 30 read table lt_file_table into ls_file index 1. 31 if sy-subrc = 0. 32 p_path = ls_file-filename. " 文件路徑賦值 33 endif. 34 endif. 35 36endform. "frm_get_path 37 38 39*&---------------------------------------------------------------------* 40*& Form frm_upload_excel 41*&---------------------------------------------------------------------* 42* 解析Excel文件中的數据 43*----------------------------------------------------------------------* 44form frm_upload_excel. 45 data: lv_filename type rlgrap-filename, 46 lt_data type standard table of alsmex_tabline, 47 ls_data type alsmex_tabline. 48 49 field-symbols: <lfv_value> type any. " 顯式聲明字段符號 50 51 if p_path is not initial. " 判斷文件路徑是否不為空 52 lv_filename = p_path. " 賦值 53 54 " 調用函數，讀取Excel文件中的數据存放到內表中 55 call function 'ALSM_EXCEL_TO_INTERNAL_TABLE' 56 exporting 57 filename = lv_filename " 導入的文件名稱路徑 58 i_begin_col = 2 " 導入單元格的開始列 59 i_begin_row = 2 " 導入單元格的開始行 60 i_end_col = 255 " 導入單元格的結束列 61 i_end_row = 65536 " 導入單元格的結束行 62 tables 63 intern = lt_data " 內表,存儲單元格中的內容 64 exceptions 65 inconsistent_parameters = 1 66 upload_ole = 2 67 others = 3. 68 69 " 將內表lt_data中的值轉換到內表gt_upload中 70 loop at lt_data into ls_data. 71 assign component ls_data-col of structure gs_upload to <lfv_value>. 72 shift ls_data-value left deleting leading space. " 去除空格 73 <lfv_value> = ls_data-value. " 賦值 74 at end of row. 75 append gs_upload to gt_upload. 76 clear gs_upload. 77 endat. 78 endloop. 79 else. 80 message '文件路徑為空，請上傳文件!' type 'I'. 81 endif. 82endform. " frm_upload_excel 83 84 85*&---------------------------------------------------------------------* 86*& Form frm_get_output 87*&---------------------------------------------------------------------* 88* 將Upload數据存放到呈現ALV的相關內表 89*----------------------------------------------------------------------* 90form frm_get_output. 91 92 " 校驗用的變量 93 data: lv_plant_check like t001w-werks, 94 lv_matnr_check like mara-matnr, 95 lv_marc_check like mara-matnr, 96 lv_lifnr_check like lfa1-lifnr, 97 lv_lfm1_check like lfa1-lifnr, 98 lv_ekorg_check like t024e-ekorg. 99 100 constants: lv_icon_red type char4 value '@0A@'. " 紅色LED圖標代碼 101 constants: lv_icon_green type char4 value '@08@'. " 綠色LED圖標代碼 102 103 refresh gt_output. 104 105 loop at gt_upload into gs_upload. 106 " 供應商增加前導零 107 call function 'CONVERSION_EXIT_ALPHA_INPUT' 108 exporting 109 input = gs_upload-lifnr 110 importing 111 output = gs_upload-lifnr. " 補0后的結果 112 113 move-corresponding gs_upload to gs_output. " 賦值 114 append gs_output to gt_output. 115 clear: gs_upload, gs_output. 116 endloop. 117 118 field-symbols: <lfs_output> type ty_output. 119 120* 數據校驗 121 if gt_output is not initial. 122 loop at gt_output assigning <lfs_output>. 123* 校驗工廠 124 if <lfs_output>-werks is initial. 125 <lfs_output>-status = lv_icon_red. 126 message e000(zrs0513_msg) into <lfs_output>-msg. " Plant 必填 127 continue. 128 else. 129 select single werks from t001w 130 into lv_plant_check 131 where werks = <lfs_output>-werks. 132 if sy-subrc <> 0. 133 <lfs_output>-status = lv_icon_red. 134 message e001(zrs0513_msg) with <lfs_output>-werks into <lfs_output>-msg. " Plant &1不存在 135 continue. 136 endif. 137 endif. 138 139* 校驗物料 140 if <lfs_output>-matnr is initial. 141 <lfs_output>-status = lv_icon_red. 142 message e002(zrs0513_msg) into <lfs_output>-msg. " Matnr 必填 143 continue. 144 else. 145 select single matnr from mara 146 into lv_matnr_check 147 where matnr = <lfs_output>-matnr. 148 if sy-subrc <> 0. 149 <lfs_output>-status = lv_icon_red. 150 message e003(zrs0513_msg) with <lfs_output>-matnr into <lfs_output>-msg. " Material &1不存在 151 continue. 152 else. 153 " 檢查物料是否在工厂下維護 154 select single matnr from marc 155 into lv_marc_check 156 where matnr = <lfs_output>-matnr 157 and werks = <lfs_output>-werks. 158 if sy-subrc <> 0. 159 <lfs_output>-status = lv_icon_red. 160 message e004(zrs0513_msg) with <lfs_output>-matnr <lfs_output>-werks into <lfs_output>-msg. " Material &1 未在廠別 &2 下維護 161 continue. 162 endif. 163 endif. 164 endif. 165 166* 校驗供應商 167 if <lfs_output>-lifnr is initial. 168 <lfs_output>-status = lv_icon_red. 169 message e005(zrs0513_msg) into <lfs_output>-msg. " Vendor 必填 170 continue. 171 else. 172 select single lifnr from lfa1 173 into lv_lifnr_check 174 where lifnr = <lfs_output>-lifnr. 175 if sy-subrc <> 0. 176 <lfs_output>-status = lv_icon_red. 177 message e006(zrs0513_msg) with <lfs_output>-lifnr into <lfs_output>-msg. " 供應商 &1不存在 178 continue. 179 else. 180 " 檢查供應商是否在采購組織下維護 181 select single lifnr from lfm1 182 into lv_lfm1_check 183 where lifnr = <lfs_output>-lifnr 184 and ekorg = <lfs_output>-ekorg. 185 if sy-subrc <> 0. 186 <lfs_output>-status = lv_icon_red. 187 message e007(zrs0513_msg) with <lfs_output>-lifnr <lfs_output>-ekorg into <lfs_output>-msg. " 供應商 &1 未在採購組織 &2 下維護 188 continue. 189 endif. 190 endif. 191 endif. 192 193* 校驗採購組織 194 if <lfs_output>-ekorg is initial. 195 <lfs_output>-status = lv_icon_red. 196 message e008(zrs0513_msg) into <lfs_output>-msg. " POrg 必填 197 continue. 198 else. 199 select single ekorg from t024e 200 into lv_ekorg_check 201 where ekorg = <lfs_output>-ekorg. 202 if sy-subrc <> 0. 203 <lfs_output>-status = lv_icon_red. 204 message e009(zrs0513_msg) with <lfs_output>-ekorg into <lfs_output>-msg. " 採購組織 &1不存在 205 continue. 206 endif. 207 endif. 208 209* 校驗日期 210 if <lfs_output>-vdatu is initial. 211 <lfs_output>-status = lv_icon_red. 212 message e010(zrs0513_msg) into <lfs_output>-msg. " Valid from 必填 213 continue. 214 else. 215 call function 'DATE_CHECK_PLAUSIBILITY' 216 exporting 217 date = <lfs_output>-vdatu 218 exceptions 219 plausibility_check_failed = 1 220 others = 2. 221 if sy-subrc <> 0. 222 <lfs_output>-status = lv_icon_red. 223 message e011(zrs0513_msg) with <lfs_output>-vdatu into <lfs_output>-msg. " 起始日期 &1 無效 224 continue. 225 endif. 226 endif. 227 228 if <lfs_output>-bdatu is initial. 229 <lfs_output>-status = lv_icon_red. 230 message e012(zrs0513_msg) into <lfs_output>-msg. " Valid to 必填 231 continue. 232 else. 233 call function 'DATE_CHECK_PLAUSIBILITY' 234 exporting 235 date = <lfs_output>-bdatu 236 exceptions 237 plausibility_check_failed = 1 238 others = 2. 239 if sy-subrc <> 0. 240 <lfs_output>-status = lv_icon_red. 241 message e013(zrs0513_msg) with <lfs_output>-bdatu into <lfs_output>-msg. " 截止日期 &1 無效 242 continue. 243 endif. 244 endif. 245 246 " 檢查起始日期是否小于等于截止日期 247 if <lfs_output>-vdatu is not initial and <lfs_output>-bdatu is not initial. 248 if <lfs_output>-vdatu > <lfs_output>-bdatu. 249 <lfs_output>-status = lv_icon_red. 250 message e014(zrs0513_msg) into <lfs_output>-msg. " 起始日期不能大于截止日期 251 continue. 252 endif. 253 endif. 254 255 endloop. 256 endif. 257 258 259endform. " frm_get_output 260 261 262*&---------------------------------------------------------------------* 263*& Form frm_display_alv 264*&---------------------------------------------------------------------* 265* ALV呈現數据 266*----------------------------------------------------------------------* 267form frm_display_alv. 268 perform frm_alv_set_fields. 269 270 " 調用函數顯示ALV數据 271 call function 'REUSE_ALV_GRID_DISPLAY_LVC' 272 exporting 273 i_callback_program = sy-cprog 274 i_callback_pf_status_set = 'FRM_SET_PF_STATUS' " 子例程(設置狀態欄) 275 i_callback_user_command = 'FRM_USER_COMMAND' " 子例程(用戶指令響應) 276 i_bypassing_buffer = abap_true 277 is_layout_lvc = gs_layout " 設置ALV布局 278 it_fieldcat_lvc = gt_fcat " 設置ALV列屬性 279 i_save = 'A' 280 tables 281 t_outtab = gt_output " 內表數据 282 exceptions 283 program_error = 1 284 others = 2. 285endform. " frm_display_alv 286 287 288*&---------------------------------------------------------------------* 289*& Form frm_alv_set_fields 290*&---------------------------------------------------------------------* 291* 設置ALV樣式和字段屬性 292*----------------------------------------------------------------------* 293form frm_alv_set_fields. 294 295* 設置行的屬性(alv界面格式) 296 clear gs_layout. 297 gs_layout-box_fname = 'SEL'. " 選擇標識 298 gs_layout-zebra = abap_true. " 斑馬條紋顯示 299 gs_layout-cwidth_opt = abap_true. " 优化列寬設置 300 gs_layout-col_opt = abap_true. 301 302* ALV字段處理宏 303 define catalog. 304 gs_fcat-fieldname = &1. " 設置要輸出的表格列的值，在內表中定義的字段名(必須大寫) 305 gs_fcat-scrtext_l = &2. " 設置要輸出的表格列的列名，即ALV報表顯示的列名 306 gs_fcat-no_zero = &3. " 去除前導零 307 gs_fcat-edit = &4. " 可編輯 308 gs_fcat-just = &5. " 對齊方式 309 gs_fcat-key = &6. " 關鍵字段 310 311 append gs_fcat to gt_fcat. 312 end-of-definition. 313 314* &1 &2 &3 &4 &5 &6 315 catalog: 316 'STATUS' '消息燈' '' '' 'C' 'abap_true', " 消息燈 317 'MSG' '消息' '' '' '' 'abap_true', " 消息 318 'WERKS' '廠別' '' '' '' 'abap_true', " Plant 319 'MATNR' '物料編碼' '' '' '' 'abap_true', " Material 320 'LIFNR' '供應商編碼' '' '' '' 'abap_true', " Vendor 321 'VDATU' '有效期起' '' '' '' 'abap_true', " Valid From 322 'BDATU' '有效期止' '' '' '' 'abap_true', " Valid To 323 'EKORG' '採購組織' '' '' '' 'abap_true'. " POrg 324endform. " frm_alv_set_fields 325 326 327* 設置狀態欄 328form frm_set_pf_status using ct_extab type slis_t_extab. 329 set pf-status 'ZSTALV' excluding ct_extab. 330endform. "frm_set_pf_status 331 332 333* 設置用戶指令響應 334form frm_user_command using pv_ucomm like sy-ucomm 335 ps_selfield type slis_selfield. 336 337 case pv_ucomm. 338 when '&ZUPLOAD'. 339 perform frm_import_data. 340 endcase. 341 342 ps_selfield-refresh = 'X'. " 強制刷新 343 344endform. "frm_user_command 345 346 347*&---------------------------------------------------------------------* 348*& Form frm_import_data 349*&---------------------------------------------------------------------* 350* 批量導入維護貨源清單 351*----------------------------------------------------------------------* 352form frm_import_data. 353 354 data: lt_eordu like table of eordu with header line. 355 356 constants: lv_icon_red type char4 value '@0A@'. " 紅色LED圖標代碼 357 constants: lv_icon_green type char4 value '@08@'. " 綠色LED圖標代碼 358 359 data: lv_zeord type eord-zeord, 360 lv_infnr type eine-infnr. 361 362* 調用函數之前需要對數据進行排序 363 sort gt_output by werks matnr. " 排序 364 365 field-symbols: <lfs_output> type ty_output. 366 367* ALV獲取可以進行批導的數据(消息燈不報紅的數据) 368 loop at gt_output assigning <lfs_output> where status is initial. 369 370 clear: lt_eordu, lt_eordu[], lv_zeord, lv_infnr. 371 refresh lt_eordu. 372 373 " 檢查貨源清單是否存在 374 select single zeord 375 from eord 376 into lv_zeord 377 where matnr = <lfs_output>-matnr 378 and lifnr = <lfs_output>-lifnr 379 and werks = <lfs_output>-werks 380 and ekorg = <lfs_output>-ekorg. 381 382 move-corresponding <lfs_output> to lt_eordu. " 賦值 383 lt_eordu-erdat = sy-datum. 384 lt_eordu-ernam = sy-uname. 385 386 if sy-subrc = 0 and lv_zeord is not initial. 387 " 記錄已存在,更新 388 lt_eordu-kz = 'U'. 389 lt_eordu-zeord = lv_zeord. " 更新時傳入系統記錄號 390 else. 391 " 記錄不存在,新增 392 lt_eordu-kz = 'I'. 393 endif. 394 395 append lt_eordu. 396 397 call function 'ME_INITIALIZE_SOURCE_LIST'. " 源清單維護初始化 398 399 call function 'ME_DIRECT_INPUT_SOURCE_LIST' 400 exporting 401 i_matnr = lt_eordu-matnr 402 i_werks = lt_eordu-werks 403 tables 404 t_eord = lt_eordu[] 405 exceptions 406 plant_missing = 1 407 material_missing = 2 408 error_message = 4 409 others = 3. 410 if sy-subrc ne 0. 411 call function 'BAPI_TRANSACTION_ROLLBACK'. 412 <lfs_output>-status = lv_icon_red. " 消息燈報紅 413 message id sy-msgid type 'E' number sy-msgno with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 into <lfs_output>-msg. 414 else. 415 call function 'ME_POST_SOURCE_LIST_NEW' 416 exporting 417 i_matnr = lt_eordu-matnr. 418 419 call function 'BAPI_TRANSACTION_COMMIT' 420 exporting 421 wait = 'X'. 422 <lfs_output>-status = lv_icon_green. " 消息燈報綠 423 424 if lt_eordu-kz = 'U'. 425 <lfs_output>-msg = '貨源清單更新成功'. 426 else. 427 <lfs_output>-msg = '貨源清單創建成功'. 428 endif. 429 endif. 430 endloop. 431endform. " frm_import_data
上述代码涉及的消息类以及文本元素如下所示
消息类(ZRS0513_MSG)文本内容
TEXT-001文本内容
GUI STATUS状态栏
《[SAP ABAP] Excel批量维护货源清单》 是转载文章，点击查看原文。