1、SAP不支持 后台导入Excel文件数据功能
之所以无法读取相应的文件内容，是因为后台作业实际上运行在ABAP应用服务器层面（Application Servers），而不是表现层（Presentation），当然也就不可能按照给定的路径读取文件了。
2、当程序运行过程需要导入Excel数据，同时程序预计运行时间很长的情况下需要通过导入数据后台运行程序通过Job发送邮件将数据发送给程序运行人员。
实现方案：
1、前台导入EXCEL表 ，转换为內表
2、将內表数据写入服务器文件
3、创建Job
4、通过Job Submit 程序
5、如果是Job运行程序，从服务器文件写入数据到內表
6、处理数据，发送邮件
1、前台导入EXCEL表 ，转换为內表
FORM FRM_EXCEL_TO_TABLE USING VALUE(P_FILENAME).
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
FILENAME = P_FILENAME
I_BEGIN_COL = 1
I_BEGIN_ROW = 1
I_END_COL = 50
I_END_ROW = 9999
TABLES
INTERN = L_INTERN
EXCEPTIONS
INCONSISTENT_PARAMETERS = 1
UPLOAD_OLE = 2
OTHERS = 3.
IF SY-SUBRC <> 0.
* MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
* WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
IF L_INTERN[] IS INITIAL.
MESSAGE '导入本地文件错误，请检查导入文件' TYPE 'E'.
REJECT.
ENDIF.
ENDFORM. " FRM_EXCEL_TO_TABLE
*&---------------------------------------------------------------------*
*& Form FRM_FILL_ITAB
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM FRM_FILL_ITAB .
DATA: L_MESSAGE TYPE CHAR255.
CHECK L_INTERN[] IS NOT INITIAL.
*- 去首行
* IF cb_line EQ 'X'.
DELETE L_INTERN WHERE ROW EQ 1.
* ENDIF.
*- 将EXCEL传到自定义内表
LOOP AT L_INTERN.
CONDENSE L_INTERN-VALUE NO-GAPS.
AT NEW ROW.
IF SY-TABIX NE 1.
APPEND INTAB. CLEAR: INTAB.
ENDIF.
ENDAT.
CASE L_INTERN-COL.
WHEN 1.
MOVE L_INTERN-ROW TO INTAB-LINE.
MOVE L_INTERN-VALUE TO INTAB-WERKS. "工厂
WHEN 2.
MOVE L_INTERN-VALUE TO INTAB-EBELN. "PO no
WHEN 3.
MOVE L_INTERN-VALUE TO INTAB-EBELP. "PO item
WHEN 4.
MOVE L_INTERN-VALUE TO INTAB-MATNR. "物料
*-------检查导入的物料有效性
PERFORM FRM_EXIT_ALPHA_INPUT USING INTAB-MATNR.
PERFORM FRM_CHECK_MATNR_EXIST USING INTAB-MATNR INTAB-LINE.
WHEN OTHERS.
ENDCASE.
AT LAST.
APPEND INTAB. CLEAR INTAB.
ENDAT.
ENDLOOP.
FREE L_INTERN.
ENDFORM. " FRM_FILL_ITAB
*&---------------------------------------------------------------------*
*& Form frm_exit_alpha_input
*&---------------------------------------------------------------------*
* 添前导零
*----------------------------------------------------------------------*
FORM FRM_EXIT_ALPHA_INPUT USING P_STRING.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
EXPORTING
INPUT = P_STRING
IMPORTING
OUTPUT = P_STRING.
ENDFORM. " frm_exit_alpha_input
*&---------------------------------------------------------------------*
*& Form FRM_ALPHA_OUT
*&---------------------------------------------------------------------*
* 去前导零
*----------------------------------------------------------------------*
FORM FRM_ALPHA_OUT USING P_STRING.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
EXPORTING
INPUT = P_STRING
IMPORTING
OUTPUT = P_STRING.
ENDFORM. " FRM_ALPHA_OUT
2、将內表数据写入服务器文件
OPEN DATASET FNAME FOR OUTPUT IN BINARY MODE .
LOOP AT INTAB.
TRANSFER INTAB TO FNAME.
ENDLOOP.
CLOSE DATASET FNAME.
3、创建Job，4、通过Job Submit 程序
FORM EMAIL_JOB. "创建后发邮箱Job
DATA: NUMBER TYPE TBTCJOB-JOBCOUNT,
NAME TYPE TBTCJOB-JOBNAME VALUE 'JOB_TEST',
PRINT_PARAMETERS TYPE PRI_PARAMS.
RANGES R_EMAIL FOR S_EMAIL.
DATA:
RSPAR LIKE RSPARAMS OCCURS 10 WITH HEADER LINE.
LOOP AT S_EMAIL.
MOVE-CORRESPONDING S_EMAIL TO RSPAR.
RSPAR-SELNAME = 'S_EMAIL'.
APPEND RSPAR.
ENDLOOP.
* SUBMIT Z_PP_ATP1
** VIA SELECTION-SCREEN
* WITH P_JOB EQ 'X'
* WITH SELECTION-TABLE RSPAR
* AND RETURN.
CALL FUNCTION 'JOB_OPEN'
EXPORTING
JOBNAME = NAME
IMPORTING
JOBCOUNT = NUMBER
EXCEPTIONS
CANT_CREATE_JOB = 1
INVALID_JOB_DATA = 2
JOBNAME_MISSING = 3
OTHERS = 4.
IF SY-SUBRC = 0.
SUBMIT Z_PP_ATP1
* VIA SELECTION-SCREEN
WITH P_JOB EQ 'X'
WITH SELECTION-TABLE RSPAR
TO SAP-SPOOL
SPOOL PARAMETERS PRINT_PARAMETERS
WITHOUT SPOOL DYNPRO
VIA JOB NAME NUMBER NUMBER
AND RETURN.
IF SY-SUBRC = 0.
CALL FUNCTION 'JOB_CLOSE'
EXPORTING
JOBCOUNT = NUMBER
JOBNAME = NAME
STRTIMMED = 'X'
EXCEPTIONS
CANT_START_IMMEDIATE = 1
INVALID_STARTDATE = 2
JOBNAME_MISSING = 3
JOB_CLOSE_FAILED = 4
JOB_NOSTEPS = 5
JOB_NOTEX = 6
LOCK_FAILED = 7
OTHERS = 8.
IF SY-SUBRC <> 0.
...
ENDIF.
ENDIF.
ENDIF.
ENDFORM.
5、如果是Job运行程序，从服务器文件写入数据到內表
FORM FRM_READ_DATASET.
OPEN DATASET FNAME FOR INPUT IN BINARY MODE.
DO.
READ DATASET FNAME INTO INTAB.
IF SY-SUBRC <> 0.
EXIT.
ENDIF.
APPEND INTAB.
ENDDO.
CLOSE DATASET FNAME.
DELETE DATASET FNAME.
ENDFORM.
5、如果是Job运行程序，从服务器文件写入数据到內表 6、处理数据，发送邮件
FORM BUILD_ITAB_EMAIL.
DATA TIME_STR LIKE SY-UZEIT VALUE '000000' .
DATA : I_TSTAMP LIKE TZONREF-TSTAMPS,
I_TZONE LIKE TZONREF-TZONE.
CLEAR I_TZONE .
I_TZONE = SY-ZONLO .
DATA V_TIME(10) .
CLEAR : ITAB_EMAIL . REFRESH : ITAB_EMAIL .
WRITE : '工厂 'TO ITAB_EMAIL-WERKS,
'PO number 'TO ITAB_EMAIL-EBELN,
'PO item 'TO ITAB_EMAIL-EBELP,
'成品型号'TO ITAB_EMAIL-MATNR.
APPEND ITAB_EMAIL .
CLEAR ITAB_EMAIL .
LOOP AT ITAB .
MOVE-CORRESPONDING ITAB TO ITAB_EMAIL .
APPEND ITAB_EMAIL .
CLEAR ITAB_EMAIL.
ENDLOOP.
ENDFORM . "build_ITAB_EMAIL
*&---------------------------------------------------------------------*
*& Form send_mail
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM SEND_MAIL .
DATA: I_OBJPACK LIKE SOPCKLSTI1 OCCURS 0 WITH HEADER LINE,
I_OBJTXT LIKE SOLISTI1 OCCURS 0 WITH HEADER LINE,
I_OBJBIN LIKE SOLISTI1 OCCURS 0 WITH HEADER LINE,
I_RECLIST LIKE SOMLRECI1 OCCURS 0 WITH HEADER LINE,
I_RECORD LIKE SOLISTI1 OCCURS 0 WITH HEADER LINE,
V_OBJHEAD TYPE SOLI_TAB,
V_LINES_TXT TYPE I,
V_LINES_BIN TYPE I,
V_DOCCHGI TYPE SODOCCHGI1,
V_LINES_BIN_ALL TYPE I,
FILELEN TYPE I.
CLEAR V_DOCCHGI.
V_DOCCHGI-OBJ_NAME = 'Text'.
V_DOCCHGI-EXPIRY_DAT = SY-DATUM + 2 . "过期日期
V_DOCCHGI-SENSITIVTY = 'F'. "Functional object
V_DOCCHGI-DOC_SIZE = V_LINES_TXT * 255.
V_DOCCHGI-OBJ_DESCR = 'TEST报表'.
* clear i_objtxt.
REFRESH I_OBJTXT.
I_OBJTXT = 'Hi all'.
APPEND I_OBJTXT.
I_OBJTXT = ' '.
APPEND I_OBJTXT.
I_OBJTXT = 'Please pay attention to the attached which are from ZTEST.'.
APPEND I_OBJTXT.
I_OBJTXT = ' '.
APPEND I_OBJTXT.
I_OBJTXT = 'Thanks'.
APPEND I_OBJTXT.
I_OBJTXT = ' '.
APPEND I_OBJTXT.
I_OBJTXT = 'This is an auto-generated message. Please DO NOT reply to this message by email.'.
APPEND I_OBJTXT.
DESCRIBE TABLE I_OBJTXT LINES V_LINES_TXT.
CLEAR I_OBJPACK .
REFRESH I_OBJPACK .
I_OBJPACK-TRANSF_BIN = ''.
I_OBJPACK-HEAD_START = 1 .
I_OBJPACK-HEAD_NUM = 0 .
I_OBJPACK-BODY_START = 1 .
I_OBJPACK-BODY_NUM = V_LINES_TXT .
I_OBJPACK-DOC_TYPE = 'RAW'.
APPEND I_OBJPACK .
**内表作为邮件附件
DATA: TMPSTR TYPE STRING .
CLEAR: TMPSTR,I_RECORD,I_RECORD[].
PERFORM ITABTOSTR TABLES ITAB_EMAIL[] USING TMPSTR.
PERFORM STRTORECORD TABLES I_RECORD USING TMPSTR FILELEN.
APPEND LINES OF I_RECORD TO I_OBJBIN.
DESCRIBE TABLE I_RECORD LINES V_LINES_BIN.
DESCRIBE TABLE I_OBJBIN LINES V_LINES_BIN_ALL.
* clear i_objpack.
* refresh i_objpack.
I_OBJPACK-TRANSF_BIN = 'X'.
I_OBJPACK-BODY_START = V_LINES_BIN_ALL - V_LINES_BIN + 1 .
I_OBJPACK-BODY_NUM = V_LINES_BIN.
I_OBJPACK-DOC_TYPE = 'XLS'.
I_OBJPACK-OBJ_NAME = 'text'.
I_OBJPACK-DOC_SIZE = V_LINES_BIN * 255 .
CONCATENATE '模拟备料报表 ' SY-DATUM '.XLS' INTO I_OBJPACK-OBJ_DESCR ."附件名
APPEND I_OBJPACK .
**接收人
* CLEAR i_reclist.
REFRESH I_RECLIST.
CLEAR: G_EMAIL,I_RECLIST-RECEIVER .
LOOP AT S_EMAIL .
I_RECLIST-RECEIVER = S_EMAIL-LOW .
I_RECLIST-EXPRESS = 'X' .
I_RECLIST-REC_TYPE = 'U' .
APPEND I_RECLIST .
CLEAR G_EMAIL .
ENDLOOP .
CALL FUNCTION 'SO_NEW_DOCUMENT_ATT_SEND_API1'
EXPORTING
DOCUMENT_DATA = V_DOCCHGI
PUT_IN_OUTBOX = 'X'
COMMIT_WORK = 'X'
TABLES
PACKING_LIST = I_OBJPACK
OBJECT_HEADER = V_OBJHEAD
CONTENTS_BIN = I_OBJBIN
CONTENTS_TXT = I_OBJTXT
RECEIVERS = I_RECLIST
EXCEPTIONS
TOO_MANY_RECEIVERS = 1
DOCUMENT_NOT_SENT = 2
DOCUMENT_TYPE_NOT_EXIST = 3
OPERATION_NO_AUTHORIZATION = 4
PARAMETER_ERROR = 5
X_ERROR = 6
ENQUEUE_ERROR = 7
OTHERS = 8.
IF SY-SUBRC = 0.
* wait up to 2 seconds.
* submit RSCONN01 with MODE = 'INT' and return.
ENDIF.
ENDFORM . "send_mail
*&---------------------------------------------------------------------*
*& Form itabtostr
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->INTAB text
* -->OUTSTR text
*----------------------------------------------------------------------*
FORM ITABTOSTR TABLES INTAB
USING OUTSTR TYPE STRING.
DATA: TAB TYPE C VALUE CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB,
* tab TYPE c VALUE ',',
ENTER(2) TYPE C VALUE CL_ABAP_CHAR_UTILITIES=>CR_LF,
N TYPE I.
DATA: BEGIN OF HEADTAB OCCURS 0 ,
LENGTH TYPE I,
DECIMALS TYPE I,
TYPE_KIND TYPE C,
NAME(30) TYPE C,
END OF HEADTAB.
DATA DESCR_REF TYPE REF TO CL_ABAP_STRUCTDESCR.
FIELD-SYMBOLS: <COMP_WA> TYPE ABAP_COMPDESCR,
<F_FIELD> ,
<F_INTAB> TYPE ANY.
DATA:STR TYPE STRING,
STR2 TYPE STRING,
TEXT1 TYPE C.
DESCR_REF ?= CL_ABAP_TYPEDESCR=>DESCRIBE_BY_DATA( INTAB ).
LOOP AT DESCR_REF->COMPONENTS ASSIGNING <COMP_WA>.
MOVE-CORRESPONDING <COMP_WA> TO HEADTAB.
APPEND HEADTAB.
ENDLOOP.
DESCRIBE TABLE HEADTAB LINES N.
LOOP AT INTAB ASSIGNING <F_INTAB>.
DO N TIMES.
ASSIGN COMPONENT SY-INDEX OF STRUCTURE <F_INTAB> TO <F_FIELD>.
STR = <F_FIELD>.
READ TABLE HEADTAB INDEX SY-INDEX.
IF HEADTAB-TYPE_KIND = 'I' OR HEADTAB-TYPE_KIND = 'P'
OR HEADTAB-TYPE_KIND = 'F'.
SEARCH STR FOR '-'.
IF SY-SUBRC = 0 AND SY-FDPOS <> 0.
SPLIT STR AT '-' INTO STR TEXT1.
CONDENSE STR.
CONCATENATE '-' STR INTO STR.
ELSE.
CONDENSE STR.
ENDIF.
ELSE.
* SHIFT str LEFT DELETING LEADING '0' .
ENDIF.
CONCATENATE STR2 TAB STR INTO STR2.
ENDDO.
SHIFT STR2.
CONCATENATE OUTSTR STR2 ENTER INTO OUTSTR.
CLEAR STR2.
ENDLOOP.
ENDFORM. "itabtostr
*&---------------------------------------------------------------------*
*& Form strtorecord
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->RECORD text
* -->STR text
* -->LEN text
*----------------------------------------------------------------------*
FORM STRTORECORD TABLES RECORD USING STR LEN.
DATA:TMPBUFFER TYPE XSTRING.
CALL FUNCTION 'SCMS_STRING_TO_XSTRING'
EXPORTING
TEXT = STR
MIMETYPE = '"text/html; charset=gb2312"'
* encoding = '8400'
IMPORTING
BUFFER = TMPBUFFER
EXCEPTIONS
FAILED = 1
OTHERS = 2.
CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
EXPORTING
BUFFER = TMPBUFFER
APPEND_TO_TABLE = ''
IMPORTING
OUTPUT_LENGTH = LEN
TABLES
BINARY_TAB = RECORD.
ENDFORM. "strtorecord
扫一扫
2027
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
