这是一个使用ABAP编写的程序，用于从EXECL文件批量导入数据。程序首先检查输入的文件路径，然后通过'ZALSM_EXCEL_TO_INTERNAL_TABLE'函数读取Excel数据，接着将数据与SAP系统中的采购订单行项目进行匹配和验证，处理后的数据在ALV表格中显示。用户可以通过提供的按钮下载模板，并进行数据导入。
************************************************************************
* 程序名称:
* 程序名：
* 开发日期:
* 创建者：
* 申请者:
*----------------------------------------------------------------------*
* 功能/技术文档:
* 描述:
*
*
*----------------------------------------------------------------------*
* 变更记录
*
* 修改日期 开发人员 请求号 描述
*----------------------------------------------------------------------*
*
************************************************************************
REPORT ZPP030.
************************************************************************
* Tables
************************************************************************
TABLES: SSCRFIELDS.
TABLES:ZPPT008.
************************************************************************
* 类型定义
************************************************************************
************************************************************************
* 内表声明
************************************************************************
DATA:GT_FCAT TYPE LVC_T_FCAT.
DATA:GS_LOUT TYPE LVC_S_LAYO.
DATA:BEGIN OF GS_LIST,
EBELN TYPE ZPPT008-EBELN,
EBELP TYPE ZPPT008-EBELP,
NAME1 TYPE LFA1-NAME1,
MAKTX TYPE MAKTX,
ZJHBZ TYPE ZPPT008-ZJHBZ,
LIGHT TYPE C LENGTH 4, "红绿灯
MSG TYPE C LENGTH 128, "消息
END OF GS_LIST.
DATA:GT_LIST LIKE TABLE OF GS_LIST.
************************************************************************
* 全局变量声明
************************************************************************
DATA:GV_ERR TYPE C.
************************************************************************
* 常数声明
************************************************************************
************************************************************************
* 选择屏幕参数
************************************************************************
PARAMETERS:P_FILE TYPE STRING.
SELECTION-SCREEN FUNCTION KEY 1. "制作一个按钮
************************************************************************
* Initialization
************************************************************************
INITIALIZATION.
/THINKWAY/1B_CL_CMARKER=>POPUP_TO_WARNING( 'ZPP030' ) .
P_FILE = 'C:\采购交期维护表导入模板.XLSX'.
SSCRFIELDS-FUNCTXT_01 = '默认模板下载'. "设置按钮的默认名字
************************************************************************
* Event At selection screen
************************************************************************
AT SELECTION-SCREEN.
"下载模板到本地（模板通过SMW0上传）
CASE SSCRFIELDS-UCOMM. "判断按钮的功能代码 当为FC01时 就是前台点击了默认模板下载按钮。
WHEN 'FC01'.
PERFORM FRM_DOWNLOAD_TEMPLATE USING 'ZPP030'
'采购交期维护表导入模板'.
WHEN OTHERS.
ENDCASE.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.
CALL FUNCTION 'WS_FILENAME_GET' "Get file name
EXPORTING
MASK = ',*.* ,*.*.'
MODE = '0'
TITLE = ''
IMPORTING
FILENAME = P_FILE
EXCEPTIONS
INV_WINSYS = 1
NO_BATCH = 2
SELECTION_CANCEL = 3
SELECTION_ERROR = 4
OTHERS = 5.
IF SY-SUBRC <> 0.
ENDIF.
************************************************************************
* Event Top of page
************************************************************************
TOP-OF-PAGE.
************************************************************************
* Event Start of Selection
************************************************************************
START-OF-SELECTION.
IF P_FILE IS INITIAL.
MESSAGE '导入路径必须输入！' TYPE 'S' DISPLAY LIKE 'E'.
STOP.
ENDIF.
PERFORM FRM_GET_DATA.
PERFORM FRM_PROCESS_DATA.
PERFORM FRM_DISPLAY_DATA.
************************************************************************
* Event End of selection
************************************************************************
END-OF-SELECTION.
************************************************************************
* Event End of page
************************************************************************
END-OF-PAGE.
************************************************************************
** forms
************************************************************************
*&---------------------------------------------------------------------*
*& Form FRM_DOWNLOAD_TEMPLATE
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> PV_OBJNAME text
*& --> PV_FILENAME text
*& <-- PV_FULLPATH text
*&---------------------------------------------------------------------*
FORM FRM_DOWNLOAD_TEMPLATE USING PV_OBJNAME TYPE WWWDATA-OBJID
PV_FILENAME TYPE STRING.
DATA: LS_OBJDATA TYPE WWWDATATAB.
"-----弹出选择存放的路径.
DATA: LV_WINDOW_TITLE TYPE STRING VALUE '文件下载',
LV_DEFAULT_EXTENSION TYPE STRING VALUE 'xlsx',
LV_DEFAULT_FILE_NAME TYPE STRING VALUE '',
LV_FILE_FILTER TYPE STRING VALUE 'Excel文件(*.xlsx)|*.xlsx|',
LV_FILENAME TYPE STRING,
LV_PATH TYPE STRING,
LV_FULLPATH TYPE STRING,
LV_USER_ACTION TYPE I.
DATA: LV_KEY TYPE WWWDATATAB,
LV_DESTINATION TYPE RLGRAP-FILENAME,
LV_RC TYPE I,
LV_TEMP TYPE C.
LV_DEFAULT_FILE_NAME = PV_FILENAME.
"****----------------------------------------------
CLEAR:LS_OBJDATA.
*从SAP服务器中下载Excel模板
SELECT SINGLE RELID
OBJID
FROM WWWDATA "存对象模板的表
INTO CORRESPONDING FIELDS OF LS_OBJDATA
WHERE SRTF2 = 0
AND RELID = 'MI' "有三种类型 HT MI IT
AND OBJID = PV_OBJNAME. "MONTLYSTOCK
IF SY-SUBRC <> 0.
MESSAGE 'Excel 模板不存在,请用TCODE：SMW0进行加载' TYPE 'S' DISPLAY LIKE 'E'.
RETURN.
ENDIF.
IF LS_OBJDATA-OBJID = SPACE .
MESSAGE 'Excel 模板不存在,请用TCODE：SMW0进行加载' TYPE 'S' DISPLAY LIKE 'E'.
RETURN.
ENDIF.
CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_SAVE_DIALOG
EXPORTING
WINDOW_TITLE = LV_WINDOW_TITLE
DEFAULT_EXTENSION = LV_DEFAULT_EXTENSION
DEFAULT_FILE_NAME = LV_DEFAULT_FILE_NAME
FILE_FILTER = LV_FILE_FILTER
CHANGING
FILENAME = LV_FILENAME
PATH = LV_PATH
FULLPATH = LV_FULLPATH
USER_ACTION = LV_USER_ACTION
EXCEPTIONS
CNTL_ERROR = 1
ERROR_NO_GUI = 2
NOT_SUPPORTED_BY_GUI = 3
INVALID_DEFAULT_FILE_NAME = 4
OTHERS = 5.
IF SY-SUBRC <> 0.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ELSE.
IF LV_USER_ACTION = ( CL_GUI_FRONTEND_SERVICES=>ACTION_OK ).
* 下载模板
SELECT SINGLE *
INTO CORRESPONDING FIELDS OF LV_KEY
FROM WWWDATA
WHERE OBJID = PV_OBJNAME.
LV_DESTINATION = LV_FULLPATH.
CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
EXPORTING
KEY = LV_KEY
DESTINATION = LV_DESTINATION
IMPORTING
RC = LV_RC
CHANGING
TEMP = LV_TEMP.
IF LV_RC <> 0.
MESSAGE '下载Excel模板出错' TYPE 'S' DISPLAY LIKE 'E'.
RETURN.
ENDIF.
ELSE.
MESSAGE '已取消下载文件！' TYPE 'S'.
ENDIF.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_GET_DATA
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM FRM_GET_DATA .
DATA:LS_UPLOAD TYPE ZALSMEX_TABLINE,
LT_UPLOAD TYPE TABLE OF ZALSMEX_TABLINE.
DATA:LV_INDEX TYPE I,
LV_TYPE TYPE C.
FIELD-SYMBOLS:<FS>.
DATA:LV_FILENAME TYPE RLGRAP-FILENAME.
LV_FILENAME = P_FILE.
CALL FUNCTION 'ZALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
FILENAME = LV_FILENAME
I_BEGIN_COL = 1
I_BEGIN_ROW = 3
I_END_COL = 60
I_END_ROW = 10000
TABLES
INTERN = LT_UPLOAD
EXCEPTIONS
INCONSISTENT_PARAMETERS = 1
UPLOAD_OLE = 2
OTHERS = 3.
IF SY-SUBRC NE 0 .
MESSAGE '从本地文件导入到SAP失败' TYPE 'E'.
ENDIF.
LOOP AT LT_UPLOAD INTO LS_UPLOAD.
LV_INDEX = LS_UPLOAD-COL.
ASSIGN COMPONENT LV_INDEX OF STRUCTURE GS_LIST TO <FS>.
IF SY-SUBRC = 0.
DESCRIBE FIELD <FS> TYPE LV_TYPE.
CASE LV_TYPE.
WHEN 'C'.
<FS> = LS_UPLOAD-VALUE.
CONDENSE <FS>.
WHEN 'I' "数字串
OR 's'
OR 'b'
OR 'N'
OR 'F'
OR 'P'.
CATCH SYSTEM-EXCEPTIONS ARITHMETIC_ERRORS = 5
CONVERSION_ERRORS = 6
BCD_OVERFLOW = 7
BCD_FIELD_OVERFLOW = 8.
<FS> = LS_UPLOAD-VALUE.
ENDCATCH.
IF SY-SUBRC <> 0.
MESSAGE E001(00) WITH LS_UPLOAD-ROW '行' LS_UPLOAD-COL '列数据转换错误'.
ENDIF.
WHEN OTHERS.
<FS> = LS_UPLOAD-VALUE.
ENDCASE.
ENDIF.
AT END OF ROW.
GS_LIST-EBELN = |{ GS_LIST-EBELN ALPHA = IN }|.
APPEND GS_LIST TO GT_LIST.
CLEAR GS_LIST.
ENDAT.
ENDLOOP.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_PROCESS_DATA
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM FRM_PROCESS_DATA .
CHECK GT_LIST IS NOT INITIAL.
SELECT EBELN,
EBELP
FROM EKPO
INTO TABLE @DATA(LT_EKPO)
FOR ALL ENTRIES IN @GT_LIST
WHERE EBELN = @GT_LIST-EBELN
AND EBELP = @GT_LIST-EBELP.
SORT LT_EKPO BY EBELN EBELP.
LOOP AT GT_LIST INTO GS_LIST.
READ TABLE LT_EKPO
WITH KEY EBELN = GS_LIST-EBELN
EBELP = GS_LIST-EBELP
BINARY SEARCH TRANSPORTING NO FIELDS.
IF SY-SUBRC <> 0.
PERFORM FRM_SET_MSG USING ICON_LED_RED
'采购订单行项目不存在'
'E'.
ENDIF.
MODIFY GT_LIST FROM GS_LIST TRANSPORTING LIGHT MSG.
ENDLOOP.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_DISPLAY_DATA
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM FRM_DISPLAY_DATA .
PERFORM FRM_PREPARE_ALV.
PERFORM FRM_CALL_ALV TABLES GT_LIST
USING GT_FCAT
GS_LOUT
'FRM_PF_STATUS'
'FRM_USER_COMMAND'
''.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_FIELD_LVC
*&---------------------------------------------------------------------*
* 设置字段目录
*----------------------------------------------------------------------*
* -->PT_FIELDCAT_LVC text
* -->PV_FIELDNAME text
* -->PV_REF_FIELD text
* -->PV_REF_TABLE text
* -->PV_CFIELDNAME text
* -->PV_QFIELDNAME text
* -->PV_TEXT text
* -->PV_EDIT text
* -->PV_NZERO text
* -->PV_F4 text
* -->PV_NO_OUT text
* -->PV_OUTPUTLEN text
*----------------------------------------------------------------------*
FORM FRM_FIELD_LVC TABLES PT_FIELDCAT_LVC STRUCTURE LVC_S_FCAT
USING PV_FIELDNAME TYPE LVC_RFNAME
PV_REF_FIELD TYPE LVC_RFNAME
PV_REF_TABLE TYPE LVC_TNAME
PV_CFIELDNAME TYPE LVC_CFNAME
PV_QFIELDNAME TYPE LVC_QFNAME
PV_TEXT TYPE STRING
PV_EDIT TYPE C
PV_NZERO TYPE C
PV_F4 TYPE C
PV_NO_OUT TYPE C
PV_OUTPUTLEN TYPE I.
DATA:LS_FIELDCAT_LVC TYPE LVC_S_FCAT.
CLEAR LS_FIELDCAT_LVC.
LS_FIELDCAT_LVC-FIELDNAME = PV_FIELDNAME.
LS_FIELDCAT_LVC-REF_FIELD = PV_REF_FIELD.
LS_FIELDCAT_LVC-REF_TABLE = PV_REF_TABLE.
LS_FIELDCAT_LVC-CFIELDNAME = PV_CFIELDNAME.
LS_FIELDCAT_LVC-QFIELDNAME = PV_QFIELDNAME.
LS_FIELDCAT_LVC-COLDDICTXT = 'L'.
LS_FIELDCAT_LVC-SCRTEXT_L = PV_TEXT.
LS_FIELDCAT_LVC-EDIT = PV_EDIT.
LS_FIELDCAT_LVC-NO_ZERO = PV_NZERO.
LS_FIELDCAT_LVC-F4AVAILABL = PV_F4.
LS_FIELDCAT_LVC-NO_OUT = PV_NO_OUT.
LS_FIELDCAT_LVC-OUTPUTLEN = PV_OUTPUTLEN.
APPEND LS_FIELDCAT_LVC TO PT_FIELDCAT_LVC.
ENDFORM. "FRM_FIELD_LVC
*&---------------------------------------------------------------------*
*& Form FRM_PREPARE_ALV
*&---------------------------------------------------------------------*
* 准备ALV的字段目录和布局
*----------------------------------------------------------------------*
FORM FRM_PREPARE_ALV .
PERFORM FRM_FIELD_LVC TABLES GT_FCAT
USING 'EBELN' 'EBELN' 'EKPO' '' ''
'采购订单号' '' '' '' '' ''.
PERFORM FRM_FIELD_LVC TABLES GT_FCAT
USING 'EBELP' 'EBELP' 'EKPO' '' ''
'行项目' '' '' '' '' ''.
PERFORM FRM_FIELD_LVC TABLES GT_FCAT
USING 'NAME1' 'NAME1' 'LFA1' '' ''
'供应商简称' '' '' '' '' ''.
PERFORM FRM_FIELD_LVC TABLES GT_FCAT
USING 'MAKTX' 'MAKTX' 'MAKT' '' ''
'物料描述' '' '' '' '' ''.
PERFORM FRM_FIELD_LVC TABLES GT_FCAT
USING 'ZJHBZ' 'ZJHBZ' 'ZPPT008' '' ''
'交期备注' '' '' '' '' ''.
PERFORM FRM_FIELD_LVC TABLES GT_FCAT
USING 'LIGHT' '' '' '' ''
'红绿灯' '' '' '' '' ''.
PERFORM FRM_FIELD_LVC TABLES GT_FCAT
USING 'MSG' '' '' '' ''
'消息' '' '' '' '' ''.
GS_LOUT-ZEBRA = 'X'.
GS_LOUT-CWIDTH_OPT = 'X'.
ENDFORM. " FRM_PREPARE_ALV
*&---------------------------------------------------------------------*
*& Form FRM_CALL_ALV
*&---------------------------------------------------------------------*
* 显示ALV
*----------------------------------------------------------------------*
* -->PT_OUTTAB text
* -->PS_FIELDCAT text
* -->PV_LAYOUT text
* -->PV_STATUS text
* -->PV_COMMAND text
* -->PV_TITLE text
*----------------------------------------------------------------------*
FORM FRM_CALL_ALV TABLES PT_OUTTAB
USING PT_FIELDCAT TYPE LVC_T_FCAT
PS_LAYOUT TYPE LVC_S_LAYO
PV_STATUS TYPE SLIS_FORMNAME
PV_COMMAND TYPE SLIS_FORMNAME
PV_TITLE TYPE SLIS_FORMNAME.
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY_LVC'
EXPORTING
I_CALLBACK_PROGRAM = SY-CPROG
I_CALLBACK_PF_STATUS_SET = PV_STATUS
I_CALLBACK_USER_COMMAND = PV_COMMAND
I_CALLBACK_TOP_OF_PAGE = PV_TITLE
IS_LAYOUT_LVC = PS_LAYOUT
IT_FIELDCAT_LVC = PT_FIELDCAT
I_SAVE = 'A'
TABLES
T_OUTTAB = PT_OUTTAB
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_PF_STATUS
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->PT_EXTAB text
*----------------------------------------------------------------------*
FORM FRM_PF_STATUS USING PT_EXTAB TYPE SLIS_T_EXTAB.
DATA:LT_FCODE LIKE TABLE OF SY-UCOMM.
IF GV_ERR = 'X'.
APPEND 'SAVE' TO LT_FCODE.
ENDIF.
SET PF-STATUS 'STATUS' EXCLUDING LT_FCODE.
* IF GC_GRID IS INITIAL.
* CALL FUNCTION 'GET_GLOBALS_FROM_SLVC_FULLSCR'
* IMPORTING
* E_GRID = GC_GRID.
**
** CALL METHOD GC_GRID->REGISTER_EDIT_EVENT
** EXPORTING
** I_EVENT_ID = CL_GUI_ALV_GRID=>MC_EVT_ENTER.
* ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_USER_COMMAND
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->PV_UCOMM text
* -->PS_SELFIELD text
*----------------------------------------------------------------------*
FORM FRM_USER_COMMAND USING PV_UCOMM LIKE SY-UCOMM
PS_SELFIELD TYPE SLIS_SELFIELD.
PS_SELFIELD-COL_STABLE = 'X'.
PS_SELFIELD-ROW_STABLE = 'X'.
PS_SELFIELD-REFRESH = 'X'.
* CALL METHOD GC_GRID->CHECK_CHANGED_DATA.
* READ TABLE GT_SO INTO GS_SO INDEX PS_SELFIELD-TABINDEX.
CASE PV_UCOMM.
WHEN '&IC1'.
WHEN 'SAVE'.
PERFORM FRM_SAVE.
WHEN OTHERS.
ENDCASE.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_SET_MSG
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> PV_TYPE
*& --> PV_MSG
*&---------------------------------------------------------------------*
FORM FRM_SET_MSG USING PV_TYPE
PV_MSG
PV_1.
CASE PV_1.
WHEN 'S'.
GS_LIST-LIGHT = ICON_LED_GREEN.
WHEN 'E'.
GS_LIST-LIGHT = ICON_LED_RED.
GV_ERR = 'X'.
WHEN OTHERS.
CLEAR GS_LIST-LIGHT.
ENDCASE.
IF GS_LIST-MSG IS INITIAL.
GS_LIST-MSG = PV_MSG.
ELSE.
GS_LIST-MSG = GS_LIST-MSG && PV_MSG.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_SAVE
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM FRM_SAVE .
DATA:LS_ZPPT008 TYPE ZPPT008.
DATA:LT_ZPPT008 TYPE TABLE OF ZPPT008.
LOOP AT GT_LIST INTO GS_LIST WHERE LIGHT IS INITIAL.
MOVE-CORRESPONDING GS_LIST TO LS_ZPPT008.
LS_ZPPT008-CRNAM = SY-UNAME.
LS_ZPPT008-CRDAT = SY-DATUM.
LS_ZPPT008-CRTIM = SY-UZEIT.
APPEND LS_ZPPT008 TO LT_ZPPT008.
ENDLOOP.
MODIFY ZPPT008 FROM TABLE LT_ZPPT008.
COMMIT WORK.
GS_LIST-LIGHT = ICON_LED_GREEN.
GS_LIST-MSG = '数据上传成功'.
MODIFY GT_LIST FROM GS_LIST TRANSPORTING LIGHT MSG
WHERE LIGHT IS INITIAL.
MESSAGE '数据上传成功' TYPE 'S'.
ENDFORM.
扫一扫
9047
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
