代码语言：javascript复制
TABLES:EINE,EINA,SSCRFIELDS,A017.
INCLUDE OLE2INCL.
TYPES:BEGIN OF TY_EXCEL,
SEL(1),
ICON(4),
MESSAGE(100),
LOHNB(1),"信息记录类型(S:标准；W：委外)
INFNR TYPE EINA-INFNR,
LIFNR TYPE LFA1-LIFNR,
MATNR TYPE MARA-MATNR,
EKORG TYPE EINE-EKORG,
WERKS TYPE EINE-WERKS,
EKGRP TYPE EINE-EKGRP,
MWSKZ TYPE EINE-MWSKZ,
APLFZ(3) TYPE C, " EINE-APLFZ,
WAERS TYPE EINE-WAERS,
MEINS TYPE EINA-MEINS,
UMREZ(5) TYPE C, " TYPE EINA-UMREZ,
UMREN(5) TYPE C, " TYPE EINA-UMREN,
NETPR(11) TYPE C, "EINE-NETPR,
LIFAB TYPE EINA-LIFAB,
LIFBI TYPE EINA-LIFBI,
KPEIN(5) TYPE C, "KONP-KPEIN,
* NORBM TYPE EINE-NORBM,
END OF TY_EXCEL.
TYPES:BEGIN OF TY_CHECK,
INFNR TYPE EINA-INFNR,
LIFNR TYPE LFA1-LIFNR,
MATNR TYPE MARA-MATNR,
LIFAB TYPE EINA-LIFAB,
EKORG TYPE EINE-EKORG,
WERKS TYPE EINE-WERKS,
ESOKZ TYPE EINE-ESOKZ,
END OF TY_CHECK.
DATA:GT_EXCEL TYPE TABLE OF TY_EXCEL,
GW_EXCEL LIKE LINE OF GT_EXCEL,
GT_CHECK TYPE TABLE OF TY_CHECK,
GW_CHECK LIKE LINE OF GT_CHECK.
DATA:GT_FIELDCAT TYPE LVC_T_FCAT.
DATA GS_FIELDCAT LIKE LINE OF GT_FIELDCAT.
DATA LS_LAYOUT TYPE LVC_S_LAYO.
DATA : GT_EXCEL_T TYPE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE,
GS_EXCEL_T LIKE LINE OF GT_EXCEL_T.
DATA: BDCDATA LIKE BDCDATA OCCURS 0 WITH HEADER LINE.
DATA: MESSTAB LIKE BDCMSGCOLL OCCURS 0 WITH HEADER LINE.
DATA: GS_CTU_PARAMS TYPE CTU_PARAMS.
DATA: GS_EXCEL TYPE OLE2_OBJECT,
* gs_wbooklist TYPE ole2_object,
* gs_application TYPE ole2_object,
GS_WORKBOOK TYPE OLE2_OBJECT.
DEFINE APPEND_FIELDCAT .
CLEAR GS_FIELDCAT.
GS_FIELDCAT-FIELDNAME = &1.
GS_FIELDCAT-SCRTEXT_L = &2.
GS_FIELDCAT-OUTPUTLEN = &3.
GS_FIELDCAT-NO_ZERO = &4.
* IF &1 = 'MEINS'.
* GS_FIELDCAT-CONVEXIT = 'ZCONV'.
* ENDIF.
APPEND GS_FIELDCAT TO GT_FIELDCAT.
END-OF-DEFINITION.
SELECTION-SCREEN:BEGIN OF BLOCK BLK01 WITH FRAME TITLE TEXT-001.
PARAMETERS:P_FILE LIKE RLGRAP-FILENAME.
SELECTION-SCREEN END OF BLOCK BLK01.
SELECTION-SCREEN FUNCTION KEY 1.
INITIALIZATION.
MOVE '@49@下载模板' TO SSCRFIELDS-FUNCTXT_01.
AT SELECTION-SCREEN.
IF SSCRFIELDS-UCOMM = 'FC01'.
PERFORM PRM_SAVE_FILE.
ENDIF.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.
PERFORM FRM_GET_FILEPATH.
START-OF-SELECTION.
CHECK P_FILE IS NOT INITIAL.
PERFORM PRM_GET_EXCEL.
PERFORM PRM_SET_FIELD.
PERFORM PRM_CHECK_DATA.
PERFORM PRM_SHOW.
*&---------------------------------------------------------------------*
*& Form FRM_GET_FILEPATH
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM FRM_GET_FILEPATH .
CALL FUNCTION 'WS_FILENAME_GET'
EXPORTING
MASK = ',Excel(*.xls),*.XLS,*.XLSX,'
TITLE = '选择文件'(100)
IMPORTING
FILENAME = P_FILE
EXCEPTIONS
INV_WINSYS = 1
NO_BATCH = 2
SELECTION_CANCEL = 3
SELECTION_ERROR = 4
OTHERS = 5.
IF SY-SUBRC <> 0.
MESSAGE '选择文件出错！' TYPE 'S' DISPLAY LIKE 'E'.
LEAVE LIST-PROCESSING.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form PRM_GET_EXCEL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM PRM_GET_EXCEL .
IF P_FILE IS NOT INITIAL.
*将excel数据传入内表
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
FILENAME = P_FILE
I_BEGIN_COL = '1'
I_BEGIN_ROW = '2'
I_END_COL = '20'
I_END_ROW = '5000'
TABLES
INTERN = GT_EXCEL_T.
ENDIF.
IF GT_EXCEL_T[] IS INITIAL.
MESSAGE '文件内容为空！' TYPE 'S' DISPLAY LIKE 'E'.
LEAVE LIST-PROCESSING.
ENDIF.
LOOP AT GT_EXCEL_T INTO GS_EXCEL_T.
AT NEW ROW.
CLEAR:GW_EXCEL.
ENDAT.
CASE GS_EXCEL_T-COL.
WHEN 1."信息记录类型
GW_EXCEL-LOHNB = GS_EXCEL_T-VALUE.
WHEN 2."供应商
GW_EXCEL-LIFNR = GS_EXCEL_T-VALUE.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
EXPORTING
INPUT = GW_EXCEL-LIFNR
IMPORTING
OUTPUT = GW_EXCEL-LIFNR.
WHEN 3."物料
GW_EXCEL-MATNR = GS_EXCEL_T-VALUE.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
EXPORTING
INPUT = GW_EXCEL-MATNR
IMPORTING
OUTPUT = GW_EXCEL-MATNR.
WHEN 4."采购组织
GW_EXCEL-EKORG = GS_EXCEL_T-VALUE.
WHEN 5.
GW_EXCEL-WERKS = GS_EXCEL_T-VALUE.
WHEN 6.
GW_EXCEL-EKGRP = GS_EXCEL_T-VALUE.
WHEN 7.
GW_EXCEL-APLFZ = GS_EXCEL_T-VALUE.
WHEN 8.
GW_EXCEL-NETPR = GS_EXCEL_T-VALUE.
WHEN 9.
GW_EXCEL-WAERS = GS_EXCEL_T-VALUE.
WHEN 10.
GW_EXCEL-KPEIN = GS_EXCEL_T-VALUE.
WHEN 11.
GW_EXCEL-MEINS = GS_EXCEL_T-VALUE.
CALL FUNCTION 'CONVERSION_EXIT_ZCONV_OUTPUT'
EXPORTING
INPUT = GW_EXCEL-MEINS
IMPORTING
OUTPUT = GW_EXCEL-MEINS
EXCEPTIONS
UNIT_NOT_FOUND = 1.
WHEN 12.
GW_EXCEL-UMREZ = GS_EXCEL_T-VALUE.
WHEN 13.
GW_EXCEL-UMREN = GS_EXCEL_T-VALUE.
WHEN 14.
GW_EXCEL-LIFAB = GS_EXCEL_T-VALUE.
WHEN 15.
GW_EXCEL-LIFBI = GS_EXCEL_T-VALUE.
WHEN 16.
GW_EXCEL-MWSKZ = GS_EXCEL_T-VALUE.
WHEN OTHERS.
ENDCASE.
AT END OF ROW.
APPEND GW_EXCEL TO GT_EXCEL.
ENDAT.
ENDLOOP.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form PRM_CHECK_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM PRM_CHECK_DATA .
SELECT EINA~INFNR" TYPE LFA1-LIFNR,
EINA~LIFNR" TYPE LFA1-LIFNR,
EINA~MATNR" TYPE MARA-MATNR,
EINA~LIFAB" TYPE MARA-MATNR,
EINE~EKORG" TYPE EINE-EKORG,
EINE~WERKS" TYPE EINE-WERKS,
EINE~ESOKZ" TYPE EINE-WERKS,
INTO TABLE GT_CHECK
FROM EINA
INNER JOIN EINE
ON EINA~INFNR = EINE~INFNR
FOR ALL ENTRIES IN GT_EXCEL
WHERE EINA~LIFNR = GT_EXCEL-LIFNR
AND EINA~MATNR = GT_EXCEL-MATNR
AND EINE~EKORG = GT_EXCEL-EKORG
AND EINE~WERKS = GT_EXCEL-WERKS
AND EINA~LOEKZ = ''
AND EINE~LOEKZ = ''.
LOOP AT GT_EXCEL INTO GW_EXCEL.
READ TABLE GT_CHECK INTO GW_CHECK WITH KEY LIFNR = GW_EXCEL-LIFNR MATNR = GW_EXCEL-MATNR
EKORG = GW_EXCEL-EKORG WERKS = GW_EXCEL-WERKS.
IF SY-SUBRC <> 0.
GW_EXCEL-MESSAGE = '数据未建立，请稍后检查！'.
GW_EXCEL-ICON = '@0A@'.
ELSE.
GW_EXCEL-INFNR = GW_CHECK-INFNR.
ENDIF.
CLEAR:A017.
SELECT SINGLE * FROM A017 WHERE LIFNR = GW_EXCEL-LIFNR AND MATNR = GW_EXCEL-MATNR
AND EKORG = GW_EXCEL-EKORG AND WERKS = GW_EXCEL-WERKS AND DATAB >= GW_EXCEL-LIFAB.
IF A017 IS NOT INITIAL.
GW_EXCEL-MESSAGE = '有效开始日期不能小于等于原有日期！'.
GW_EXCEL-ICON = '@0A@'.
ENDIF.
IF GW_EXCEL-LIFAB IS INITIAL OR GW_EXCEL-LIFBI IS INITIAL.
GW_EXCEL-MESSAGE = '新建日期范围必输！'.
GW_EXCEL-ICON = '@0A@'.
ENDIF.
IF GW_EXCEL-LOHNB <> 'S' AND GW_EXCEL-LOHNB <> 'W'.
GW_EXCEL-MESSAGE = '信息类型错误！'.
GW_EXCEL-ICON = '@0A@'.
ELSEIF GW_EXCEL-LOHNB = 'S'.
IF GW_CHECK-ESOKZ <> '0'.
GW_EXCEL-MESSAGE = '信息类型不匹配！'.
GW_EXCEL-ICON = '@0A@'.
ENDIF.
ELSEIF GW_EXCEL-LOHNB = 'W'.
IF GW_CHECK-ESOKZ <> '3'.
GW_EXCEL-MESSAGE = '信息类型不匹配！'.
GW_EXCEL-ICON = '@0A@'.
ENDIF.
ENDIF.
*
* IF GW_EXCEL-LIFAB <= GW_CHECK-LIFAB.
* GW_EXCEL-MESSAGE = '有效开始日期不能小于等于原有日期！'.
* GW_EXCEL-ICON = '@0A@'.
* ENDIF.
MODIFY GT_EXCEL FROM GW_EXCEL.
CLEAR:GW_EXCEL-MESSAGE.
ENDLOOP.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form PRM_SET_FIELD
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM PRM_SET_FIELD .
REFRESH GT_FIELDCAT.
LS_LAYOUT-CWIDTH_OPT = 'X'. "优化列宽选项是否设置
LS_LAYOUT-ZEBRA = 'X'.
LS_LAYOUT-BOX_FNAME = 'SEL'.
APPEND_FIELDCAT:'ICON' '标记' 1 '',
'MESSAGE' '消息' 100 '',
'LOHNB' '信息记录类型' 1 '',
'INFNR' '信息记录编号' 10 'X',
'LIFNR' '供应商编码' 10 'X',
'MATNR' '物料编码' 18 'X',
'EKORG' '采购组织' 4 '',
'EKGRP' '采购组' 3 '',
'MEINS' '订单价格单位' 5 '',
'WERKS' '工厂' 4 '',
'APLFZ' '计划交货期限' 3 '',
'WAERS' '货币码' 5 '',
'NETPR' '价格' 16 '',
'MWSKZ' '税码' 2 '',
'KPEIN' '价格倍数' 6 '',
'UMREZ' '转化分子' 5 '',
'UMREN' '转化分母' 5 '',
'LIFAB' '开始日期' 10 '',
'LIFBI' '截止日期' 10 ''.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form PRM_SHOW
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM PRM_SHOW .
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY_LVC'
EXPORTING
I_CALLBACK_PROGRAM = SY-REPID
IS_LAYOUT_LVC = LS_LAYOUT
IT_FIELDCAT_LVC = GT_FIELDCAT[]
I_CALLBACK_PF_STATUS_SET = 'PRM_SET_STATUS'
I_CALLBACK_USER_COMMAND = 'PRM_USER_COMMAND'
* i_grid_title = lv_title
I_DEFAULT = 'X'
I_SAVE = 'A'
TABLES
T_OUTTAB = GT_EXCEL
EXCEPTIONS
PROGRAM_ERROR = 1
OTHERS = 2.
IF SY-SUBRC <> 0.
ENDIF.
ENDFORM.
FORM PRM_SET_STATUS USING RT_EXTAB TYPE SLIS_T_EXTAB..
SET PF-STATUS 'ZSTAT'.
ENDFORM.
FORM PRM_USER_COMMAND USING P_UCOMM LIKE SY-UCOMM
P_SELFIELD TYPE SLIS_SELFIELD.
DATA:GT_SELECT TYPE TABLE OF TY_EXCEL.
DATA:GW_SELECT LIKE LINE OF GT_EXCEL.
DATA:LR_GRID TYPE REF TO CL_GUI_ALV_GRID.
DATA:LV_LINE TYPE I.
CHECK P_UCOMM = 'GENR'.
APPEND LINES OF GT_EXCEL TO GT_SELECT.
DELETE GT_SELECT WHERE SEL = ''.
DELETE GT_SELECT WHERE MESSAGE <> ''.
CALL FUNCTION 'GET_GLOBALS_FROM_SLVC_FULLSCR'
IMPORTING
E_GRID = LR_GRID.
LOOP AT GT_SELECT INTO GW_EXCEL.
CLEAR BDCDATA[].
PERFORM BDC_DYNPRO USING 'SAPMM06I' '0100'.
PERFORM BDC_FIELD USING 'BDC_CURSOR'
'EINE-WERKS'.
PERFORM BDC_FIELD USING 'BDC_OKCODE'
'/00'.
PERFORM BDC_FIELD USING 'EINA-LIFNR'
GW_EXCEL-LIFNR.
PERFORM BDC_FIELD USING 'EINA-MATNR'
GW_EXCEL-MATNR.
PERFORM BDC_FIELD USING 'EINE-EKORG'
GW_EXCEL-EKORG.
PERFORM BDC_FIELD USING 'EINE-WERKS'
GW_EXCEL-WERKS.
IF GW_EXCEL-LOHNB = 'S'.
PERFORM BDC_FIELD USING 'RM06I-NORMB'
'X'.
ELSEIF GW_EXCEL-LOHNB = 'L'.
PERFORM BDC_FIELD USING 'RM06I-LOHNB'
'X'.
ENDIF.
PERFORM BDC_DYNPRO USING 'SAPMM06I' '0101'.
PERFORM BDC_FIELD USING 'BDC_OKCODE'
'=EINE'.
IF GW_EXCEL-MEINS IS NOT INITIAL.
PERFORM BDC_FIELD USING 'EINA-MEINS'
GW_EXCEL-MEINS.
ENDIF.
IF GW_EXCEL-UMREZ <> 0.
PERFORM BDC_FIELD USING 'EINA-UMREZ'
GW_EXCEL-UMREZ.
ENDIF.
IF GW_EXCEL-UMREN <> 0.
PERFORM BDC_FIELD USING 'EINA-UMREN'
GW_EXCEL-UMREN.
ENDIF.
PERFORM BDC_DYNPRO USING 'SAPMM06I' '0102'.
PERFORM BDC_FIELD USING 'BDC_CURSOR'
'EINE-MWSKZ'.
PERFORM BDC_FIELD USING 'BDC_OKCODE'
'/00'.
IF GW_EXCEL-APLFZ > 0.
PERFORM BDC_FIELD USING 'EINE-APLFZ'
GW_EXCEL-APLFZ.
ENDIF.
IF GW_EXCEL-EKGRP IS NOT INITIAL.
PERFORM BDC_FIELD USING 'EINE-EKGRP'
GW_EXCEL-EKGRP.
ENDIF.
* PERFORM BDC_FIELD USING 'EINE-NORBM'
* RECORD-NORBM_011.
* PERFORM BDC_FIELD USING 'EINE-WEBRE'
* RECORD-WEBRE_012.
IF GW_EXCEL-MWSKZ IS NOT INITIAL.
PERFORM BDC_FIELD USING 'EINE-MWSKZ'
GW_EXCEL-MWSKZ.
ENDIF.
* PERFORM BDC_FIELD USING 'EINE-IPRKZ'
* RECORD-IPRKZ_014.
PERFORM BDC_DYNPRO USING 'SAPMM06I' '0105'.
PERFORM BDC_FIELD USING 'BDC_CURSOR'
'EINE-ANGNR'.
PERFORM BDC_FIELD USING 'BDC_OKCODE'
'=KO'.
PERFORM BDC_DYNPRO USING 'SAPLV14A' '0102'.
PERFORM BDC_FIELD USING 'BDC_CURSOR'
'VAKE-DATAB(01)'.
PERFORM BDC_FIELD USING 'BDC_OKCODE'
'=NEWD'.
PERFORM BDC_DYNPRO USING 'SAPMV13A' '0201'.
PERFORM BDC_FIELD USING 'BDC_CURSOR'
'KONP-KBETR(01)'.
PERFORM BDC_FIELD USING 'BDC_OKCODE'
'=SICH'.
PERFORM BDC_FIELD USING 'RV13A-DATAB'
GW_EXCEL-LIFAB.
PERFORM BDC_FIELD USING 'RV13A-DATBI'
GW_EXCEL-LIFBI.
PERFORM BDC_FIELD USING 'KONP-KBETR(01)'
GW_EXCEL-NETPR.
IF GW_EXCEL-WAERS IS NOT INITIAL.
PERFORM BDC_FIELD USING 'KONP-KONWA(01)'
GW_EXCEL-WAERS.
ENDIF.
IF GW_EXCEL-KPEIN IS NOT INITIAL.
PERFORM BDC_FIELD USING 'KONP-KPEIN(01)'
GW_EXCEL-KPEIN.
ENDIF.
* PERFORM BDC_TRANSACTION USING 'ME12'.
CALL TRANSACTION 'ME12' USING BDCDATA
MODE 'E'
UPDATE 'S'
MESSAGES INTO MESSTAB.
READ TABLE MESSTAB WITH KEY MSGTYP = 'E'.
IF SY-SUBRC <> 0.
GW_EXCEL-MESSAGE = '修改成功！'.
GW_EXCEL-ICON = '@08@'.
ELSE.
CONDENSE MESSTAB-MSGV1 NO-GAPS.
CONDENSE MESSTAB-MSGV2 NO-GAPS.
CONDENSE MESSTAB-MSGV3 NO-GAPS.
CONDENSE MESSTAB-MSGV4 NO-GAPS.
CONCATENATE '修改失败:' MESSTAB-MSGV1 MESSTAB-MSGV2 MESSTAB-MSGV3 MESSTAB-MSGV4 INTO GW_EXCEL-MESSAGE.
GW_EXCEL-ICON = '@0A@'.
ENDIF.
MODIFY GT_SELECT FROM GW_EXCEL.
refresh:MESSTAB.
ENDLOOP.
LOOP AT GT_EXCEL INTO GW_EXCEL.
LV_LINE = SY-TABIX.
READ TABLE GT_SELECT INTO GW_SELECT WITH KEY INFNR = GW_EXCEL-INFNR.
IF SY-SUBRC = 0.
GW_EXCEL-MESSAGE = GW_SELECT-MESSAGE.
GW_EXCEL-ICON = GW_SELECT-ICON.
MODIFY GT_EXCEL FROM GW_EXCEL INDEX LV_LINE TRANSPORTING MESSAGE ICON.
ENDIF.
ENDLOOP.
CALL METHOD LR_GRID->REFRESH_TABLE_DISPLAY.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form BDC_DYNPRO
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_0774 text
* -->P_0775 text
*----------------------------------------------------------------------*
FORM BDC_DYNPRO USING PROGRAM DYNPRO.
CLEAR BDCDATA.
BDCDATA-PROGRAM = PROGRAM.
BDCDATA-DYNPRO = DYNPRO.
BDCDATA-DYNBEGIN = 'X'.
APPEND BDCDATA.
ENDFORM.
*----------------------------------------------------------------------*
* Insert field *
*----------------------------------------------------------------------*
FORM BDC_FIELD USING FNAM FVAL.
CLEAR BDCDATA.
BDCDATA-FNAM = FNAM.
BDCDATA-FVAL = FVAL.
APPEND BDCDATA.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form PRM_SAVE_FILE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM PRM_SAVE_FILE .
DATA:GV_FILE1 TYPE STRING.
DATA:GV_FILE2 TYPE STRING.
DATA:GV_FILE3 TYPE STRING.
DATA:LS_KEY TYPE WWWDATATAB,
LV_DESTINAT LIKE RLGRAP-FILENAME,
LV_RC TYPE I.
CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_SAVE_DIALOG
EXPORTING
WINDOW_TITLE = '保存文件到'
CHANGING
FILENAME = GV_FILE1
PATH = GV_FILE2
FULLPATH = GV_FILE3
EXCEPTIONS
CNTL_ERROR = 1
ERROR_NO_GUI = 2
NOT_SUPPORTED_BY_GUI = 3
INVALID_DEFAULT_FILE_NAME = 4
OTHERS = 5.
IF SY-SUBRC <> 0.
ENDIF.
IF GV_FILE3 IS NOT INITIAL.
LS_KEY-RELID = 'MI'.
LS_KEY-OBJID = 'ZMMF016'.
LV_DESTINAT = GV_FILE3.
"下载模板
CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
EXPORTING
KEY = LS_KEY
DESTINATION = LV_DESTINAT
IMPORTING
RC = LV_RC.
ENDIF.
ENDFORM.
