SAP中 BDC吧excel数据导入人员培训记录模版
*&---------------------------------**& Report ZHR0050*&*&---------------------------------**&create by zhugx 2013.8.1*&人员培训批量导入*&---------------------------------*REPORT zhr0050.INCLUDE:zsy_xy_tcus.TABLES: t100.DATA: zpa9100 LIKE TABLE OF pa9100 WITH HEADER LINE.DATA: i_list LIKE TABLE OF pa9100 WITH HEADER LINE.DATA: bdcdata1 LIKE bdcdata OCCURS 0 WITH HEADER LINE."定义BDC内表*DATA: msgtab1 LIKE TABLE OF zbdcmsgcoll WITH HEADER LINE.*DATA: zmsgtab LIKE TABLE OF zmsgtab WITH HEADER LINE .*DATA: zmsgtab1 LIKE TABLE OF zmsgtab WITH HEADER LINE .*DATA: zmsgtab LIKE TABLE OF zmsgtab1 WITH HEADER LINE.DATA: BEGIN OF msgtab OCCURS 0. INCLUDE STRUCTURE zbdcmsgcoll.DATA: pernr LIKE pa0001-pernr,* id(3) type c, zid(3) TYPE c,END OF msgtab.DATA: msgtab1 LIKE TABLE OF msgtab WITH HEADER LINE.DATA: zmsgtab LIKE TABLE OF zmsgtab WITH HEADER LINE .DATA: count TYPE i, cg TYPE i, rowts TYPE i, sb TYPE i.DATA: l_mstring(480). "消息字符串rowts = 0.**********模板数据定义*****************************************************TABLES: sscrfields.TYPE-POOLS : tpit.DATA : it_errtab TYPE tpit_t_errdoc WITH HEADER LINE,it_fldtab TYPE tpit_t_fname WITH HEADER LINE,it_buztab TYPE tpit_t_buztab WITH HEADER LINE.DATA: filetable TYPE filetable, rc TYPE i.FIELD-SYMBOLS: <fs> TYPE ANY.DATA: f_path LIKE rlgrap-filename.DATA: file_path TYPE rlgrap-filename.DATA: e_text TYPE string.DATA: itab LIKE alsmex_tabline OCCURS 0 WITH HEADER LINE.DATA: BEGIN OF v_tab OCCURS 10, filename TYPE text100, END OF v_tab.DATA: g_code TYPE sscrfields-ucomm. "FUNCTION CODE****************************************************************************************选择屏幕SELECTION-SCREEN BEGIN OF BLOCK b01 WITH FRAME TITLE text-001.PARAMETERS: p_file(100) MODIF ID sc1 TYPE c .SELECTION-SCREEN END OF BLOCK b01.SELECTION-SCREEN PUSHBUTTON 2(9) p_butt2 USER-COMMAND pb02.*********AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file. PERFORM objclsf4.AT SELECTION-SCREEN. g_code = sscrfields-ucomm. PERFORM user_command USING g_code.INITIALIZATION. p_butt2 = '下载模板'.START-OF-SELECTION. IF f_path IS NOT INITIAL. PERFORM sub_upload_data_xls. ELSE . IF p_file IS NOT INITIAL. f_path = p_file. PERFORM sub_upload_data_xls. ELSE. MESSAGE e996(zfi) WITH f_path . ENDIF. ENDIF.****-----------------模板数据定义结束------------------------***------------------业务方法处理-------------------------------* text 循环导入BDC数据*----------------------------------* IF sy-uname = 'HR000' OR sy-uname = 'HR010' OR sy-uname = 'HR020' OR sy-uname = 'YANGZG'. LOOP AT i_list. PERFORM z_bdc9100 USING i_list. CLEAR bdcdata1[]. "清空BDCDATA的所有数据 ENDLOOP. PERFORM sub_writeout_log. ELSE. MESSAGE '您没有权限执行该操作' TYPE 'I'. ENDIF.***-------------------业务方法处理结束--------------------------*&---------------------------------**& Form user_command*&---------------------------------** text*----------------------------------** -->UCOMM text*----------------------------------*FORM user_command USING ucomm LIKE sy-ucomm. CASE ucomm. WHEN 'PB02'. PERFORM save_the_path_and_download.* PERFORM down_load_template USING file_path. ENDCASE.ENDFORM. "USER_COMMAND*&---------------------------------**& Form save_the_path*&---------------------------------** text 保存文件路径*----------------------------------*FORM save_the_path_and_download . DATA: rc TYPE string, rc1 TYPE string, rc2 TYPE string. DATA: fname LIKE wwwdatatab-objid,"模板的名称 fp_dest LIKE sapb-sappfad."模板路径****Call the file save dialog only showing XML CALL METHOD cl_gui_frontend_services=>file_save_dialog EXPORTING* WINDOW_TITLE = default_extension = '*.xls' default_file_name = '培训导入模板' "下载excel模板的文件名 file_filter = 'xls (*.xls)|*.xls|'* INITIAL_DIRECTORY = CHANGING filename = rc path = rc1 fullpath = rc2 "文件的路径 EXCEPTIONS cntl_error = 1 error_no_gui = 2 OTHERS = 3 . file_path = rc2."模板的路径 fname = 'ZPXJL.XLS'. "服务器模板文件名称 fp_dest = file_path. IF rc2 = space. MESSAGE e001(zxy) WITH e_text. "不能打开excel文件 EXIT. ENDIF. IF sy-subrc = 0. PERFORM download_excel_template USING fname fp_dest ."该函数针对smw0上传的模板进行下载 ENDIF.ENDFORM. " save_the_path*&---------------------------------**& Form DOWNLOAD_EXCEL_TEMPLATE*&---------------------------------** text 下载服务器上的excel文件*----------------------------------** -->FP_OBJID text* -->FP_DEST text* -->ENDFORM text*----------------------------------*FORM download_excel_template USING p_objid LIKE wwwdatatab-objid fp_dest LIKE sapb-sappfad. DATA: lo_objdata LIKE wwwdatatab, ls_errtxt(40) TYPE c, li_rc LIKE sy-subrc, ls_destination LIKE rlgrap-filename. SELECT SINGLE relid objid FROM wwwdata INTO CORRESPONDING FIELDS OF lo_objdata WHERE srtf2 = 0 AND relid = 'MI' AND objid = p_objid.********检查表wwwdata中是否存在所指定的模板文件 IF sy-subrc NE 0 OR lo_objdata-objid EQ space."如果不存在，则给出错误提示 CONCATENATE '模板文件' p_objid '不存在' INTO ls_errtxt. MESSAGE ls_errtxt TYPE 'I'. ENDIF. ls_destination = fp_dest. "保存路径*******如果存在，调用DOWNLOAD_WEB_OBJECT 函数下载模板到路径下，从服务器上下载模板文件 CALL FUNCTION 'DOWNLOAD_WEB_OBJECT' EXPORTING key = lo_objdata destination = ls_destination IMPORTING rc = li_rc. IF li_rc NE 0. CONCATENATE '模板文件：' p_objid '下载失败' INTO ls_errtxt. MESSAGE ls_errtxt TYPE 'E'. ELSE. CONCATENATE '模板文件下载成功' p_objid INTO ls_errtxt. MESSAGE ls_errtxt TYPE 'S'. ENDIF.ENDFORM. "download_excel_template*&---------------------------------**& Form objclsf4*&---------------------------------** text 文件打开对话框*----------------------------------*FORM objclsf4 . CLEAR filetable. CALL METHOD cl_gui_frontend_services=>file_open_dialog EXPORTING* WINDOW_TITLE = default_extension = '*.XLS|*.XLSX'* DEFAULT_FILENAME = file_filter = 'XLS (*.XLS)|*.XLS|XLSX (*.XLSX)|*.XLSX'* INITIAL_DIRECTORY = CHANGING file_table = filetable rc = rc EXCEPTIONS cntl_error = 1 error_no_gui = 2 OTHERS = 3 . READ TABLE filetable INDEX 1 ASSIGNING <fs>. IF sy-subrc = 0. p_file = <fs>. UNASSIGN <fs>. ENDIF. IF NOT p_file IS INITIAL. f_path = p_file. ENDIF.ENDFORM. "objclsf4*&---------------------------------**& Form sub_upload_data_xls*&---------------------------------** text 读取excel文件*----------------------------------*FORM sub_upload_data_xls. DATA: i TYPE i , "开始的行 j TYPE i , "一共多少列 k TYPE i , row TYPE i. DATA: max_row TYPE i. i = 2. j = 17. max_row = 2000. "导入excel最大的行 CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE' EXPORTING filename = f_path "excel文件路径 i_begin_col = 1 "开始的列数 i_begin_row = i "开始的行数 i_end_col = j "读取到的列数 i_end_row = max_row "读取到的行数 TABLES intern = itab "存储的内表 EXCEPTIONS inconsistent_parameters = 1 upload_ole = 2 OTHERS = 3. IF sy-subrc <> 0. MESSAGE e990(zfi) WITH f_path. ELSE. MESSAGE s999(zfi) WITH f_path '成功'. ENDIF. DESCRIBE TABLE itab LINES row. READ TABLE itab INDEX row. row = itab-row. CLEAR itab. count = row.*重置i的值，类似FOR循环 i = 1. WHILE i <= row. LOOP AT itab WHERE row = i. CASE itab-col. WHEN 1. i_list-pernr = itab-value. WHEN 2. i_list-kcbh = itab-value. WHEN 3. i_list-pxjs = itab-value. WHEN 4. i_list-begda = itab-value. WHEN 5. i_list-endda = itab-value. WHEN 6. i_list-pxsj = itab-value. WHEN 7. i_list-pxks = itab-value. WHEN 8. i_list-pxlx = itab-value. WHEN 9. i_list-khfs = itab-value. WHEN 10. i_list-khjg = itab-value. WHEN 11. i_list-khcj = itab-value. WHEN 12. i_list-qdb_num = itab-value. WHEN 13. i_list-cost_accnt = itab-value. WHEN 14. i_list-sfpay = itab-value. WHEN 15. i_list-wxje = itab-value. WHEN 16. i_list-jzrq = itab-value. WHEN 17. i_list-pxxm = itab-value. ENDCASE. ENDLOOP. APPEND i_list. CLEAR i_list. i = i + 1. ENDWHILE. "读取全部EXCEL数ENDFORM. "sub_upload_data_xls*&---------------------------------**& Form sub_writeout_log*&---------------------------------** text BDC写消息*----------------------------------*FORM sub_writeout_log.******返回消息 LOOP AT msgtab. WRITE: / '第',msgtab-id,'行',msgtab-text. IF msgtab-msgtyp1 = 'S'. cg = cg + 1. ENDIF. ENDLOOP. sb = count - cg. WRITE: / '总共导入：',count,'条'. WRITE: / '导入成功：',cg,'条'. WRITE: / '导入失败：',sb,'条'.ENDFORM. "sub_writeout_logINCLUDE zhr0050_z_bdc9100f01.
?
*&---------------------------------**& Form z_bdc9100*&---------------------------------** text*----------------------------------** -->P_I_LIST text*----------------------------------*form z_bdc9100 using p_i_list like i_list.data: ztype type c.ztype = 'N'.perform bdc_dynpro using 'SAPMP50A' '1000'.perform bdc_field using 'BDC_OKCODE' '=INS'.perform bdc_field using 'RP50G-PERNR' p_i_list-PERNR.perform bdc_field using 'RP50G-TIMR6' 'X'. "record-TIMR6_002.perform bdc_field using 'BDC_CURSOR' 'RP50G-CHOIC'.perform bdc_field using 'RP50G-CHOIC' '9100'. "record-CHOIC_003.perform bdc_dynpro using 'MP910000' '2000'.perform bdc_field using 'BDC_CURSOR' 'P9100-PXXM'.perform bdc_field using 'BDC_OKCODE' '=UPD'.perform bdc_field using 'P9100-KCBH' p_i_list-KCBH.perform bdc_field using 'P9100-PXJS' p_i_list-PXJS.perform bdc_field using 'P9100-BEGDA' p_i_list-BEGDA.perform bdc_field using 'P9100-ENDDA' p_i_list-ENDDA.perform bdc_field using 'P9100-PXLX' p_i_list-PXLX.perform bdc_field using 'P9100-PXKS' p_i_list-PXKS.perform bdc_field using 'P9100-KHFS' p_i_list-KHFS.perform bdc_field using 'P9100-KHJG' p_i_list-KHJG.perform bdc_field using 'P9100-KHCJ' p_i_list-KHCJ.perform bdc_field using 'P9100-QDB_NUM' p_i_list-QDB_NUM.perform bdc_field using 'P9100-COST_ACCNT' p_i_list-COST_ACCNT.perform bdc_field using 'P9100-SFPAY' p_i_list-SFPAY.perform bdc_field using 'P9100-WXJE' p_i_list-WXJE.perform bdc_field using 'P9100-JZRQ' p_i_list-JZRQ.perform bdc_field using 'P9100-PXXM' p_i_list-PXXM. CALL TRANSACTION 'PA30' USING bdcdata1 MODE ztype MESSAGES INTO msgtab1. LOOP AT msgtab1. SELECT SINGLE * FROM t100 WHERE sprsl = msgtab1-msgspra AND arbgb = msgtab1-msgid AND msgnr = msgtab1-msgnr. IF sy-subrc = 0. l_mstring = t100-text. IF l_mstring CS '&1'. REPLACE '&1' WITH msgtab1-msgv1 INTO l_mstring. REPLACE '&2' WITH msgtab1-msgv2 INTO l_mstring. REPLACE '&3' WITH msgtab1-msgv3 INTO l_mstring. REPLACE '&4' WITH msgtab1-msgv4 INTO l_mstring. ELSE. REPLACE '&' WITH msgtab1-msgv1 INTO l_mstring. REPLACE '&' WITH msgtab1-msgv2 INTO l_mstring. REPLACE '&' WITH msgtab1-msgv3 INTO l_mstring. REPLACE '&' WITH msgtab1-msgv4 INTO l_mstring. ENDIF. CONDENSE l_mstring.* WRITE: / msgtab1-msgtyp, 20 l_mstring. msgtab-msgtyp1 = msgtab1-msgtyp. msgtab-text = l_mstring. msgtab-pernr = i_list-pernr. rowts = rowts + 1. msgtab-id = rowts. APPEND msgtab. CLEAR msgtab.* ELSE.* WRITE: / msgtab1. ENDIF. ENDLOOP. FREE msgtab1.endform. " z_bdc9100*&---------------------------------**& Form bdc_field*&---------------------------------** text*----------------------------------** -->FNAM text* -->FVAL text*----------------------------------*FORM bdc_field USING fnam fval. CLEAR bdcdata1. bdcdata1-fnam = fnam. bdcdata1-fval = fval. APPEND bdcdata1.ENDFORM. "bdc_field*&---------------------------------**& Form bdc_dynpro*&---------------------------------** text*----------------------------------** -->PROGRAM text* -->DYNPRO text*----------------------------------*FORM bdc_dynpro USING program dynpro. CLEAR bdcdata1. bdcdata1-program = program. bdcdata1-dynpro = dynpro. bdcdata1-dynbegin = 'X'. APPEND bdcdata1.ENDFORM. "bdc_dynpro
?
