1.一、下载SMW0中的模板
使用SMW0上传模板后,可以使用如下函数下载模板到为本地文件;
此函数中附近选择本地目录、文件名功能。使用时只OBJID就可以。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 |
FUNCTION ZFC_EXCEL_DOWNLOAD_FROM_SMW0. *"---------------------------------------------------------------------- *"*"Local interface: *" IMPORTING *" VALUE(I_OBJID) TYPE WWWDATATAB-OBJID *" VALUE(I_FIELDNAME) TYPE STRING *" EXPORTING *" REFERENCE(E_RETURN) TYPE BAPIRET2 *"---------------------------------------------------------------------- *--此函数用于,在SWM0上传后,通过此函数下载到本地; * 相关变量定义 DATA:LO_OBJDATA LIKE WWWDATATAB, LO_MIME LIKE W3MIME, LC_FULLPATH TYPE STRING VALUE 'c:\', LC_PATH TYPE STRING VALUE 'c:\', LS_DESTINATION LIKE RLGRAP-FILENAME, LS_OBJNAM TYPE STRING, L_RC LIKE SY-SUBRC, LS_ERRTXT TYPE STRING, L_DEST LIKE SAPB-SAPPFAD. * 模板下载路径名 CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_SAVE_DIALOG EXPORTING WINDOW_TITLE = '模板下载' DEFAULT_EXTENSION = 'xlsx' DEFAULT_FILE_NAME = I_FIELDNAME CHANGING FILENAME = I_FIELDNAME PATH = LC_PATH FULLPATH = LC_FULLPATH EXCEPTIONS CNTL_ERROR = 1 ERROR_NO_GUI = 2 NOT_SUPPORTED_BY_GUI = 3 OTHERS = 5. * 没有选择路径 IF LC_FULLPATH = ''. E_RETURN-TYPE = 'E'. E_RETURN-MESSAGE = '请选择正确的路径!'. RETURN. ELSE. *- 路径名 L_DEST = LC_FULLPATH. SELECT SINGLE RELID "IMPORT/EXPORT 记录中的区域 OBJID "SAP WWW 网关对象名 FROM WWWDATA INTO CORRESPONDING FIELDS OF LO_OBJDATA WHERE SRTF2 = 0 AND RELID = 'MI' AND OBJID = I_OBJID. IF SY-SUBRC <> 0 OR LO_OBJDATA-OBJID IS INITIAL. E_RETURN-TYPE = 'E'. E_RETURN-MESSAGE = '没有获得模板数据'. RETURN. ENDIF. LS_DESTINATION = L_DEST. CALL FUNCTION 'DOWNLOAD_WEB_OBJECT' EXPORTING KEY = LO_OBJDATA DESTINATION = LS_DESTINATION IMPORTING RC = L_RC. IF L_RC <> 0. E_RETURN-TYPE = 'E'. E_RETURN-MESSAGE = '模板下载失败'. RETURN. ENDIF. ENDIF. ENDFUNCTION. |
使用方法
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
FORM FRM_EXCEL_DOWN . DATA: LV_OBJID TYPE W3OBJID, LS_RETURN TYPE BAPIRET2. LV_OBJID = 'ZFIR_718'. CLEAR LS_RETURN CALL FUNCTION 'ZFC_EXCEL_DOWNLOAD_FROM_SMW0' EXPORTING I_OBJID = LV_OBJID I_FIELDNAME = '代收款费用入账_货款清账批导模板' IMPORTING E_RETURN = LS_RETURN. IF LS_RETURN-TYPE EQ 'E'. MESSAGE LS_RETURN-MESSAGE TYPE 'S' DISPLAY LIKE 'E'. STOP. ENDIF. ENDFORM. "FRM_EXCEL_DOWN |
2.二、上传EXECL得到内表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 |
FUNCTION ZFC_PUB_EXCEL_UPLOAD. *"---------------------------------------------------------------------- *"*"Local interface: *" IMPORTING *" VALUE(I_BECOL) TYPE I DEFAULT 1 *" VALUE(I_ENCOL) TYPE I DEFAULT 20 *" VALUE(I_BEROW) TYPE I DEFAULT 2 *" VALUE(I_ENROW) TYPE I DEFAULT 50000 *" VALUE(I_MOD) TYPE CHAR10 DEFAULT 'FIELD' *" VALUE(I_TITLE_LINE) TYPE I DEFAULT 1 *" VALUE(I_EXCEL_WAY) TYPE LOCALFILE OPTIONAL *" EXPORTING *" VALUE(E_RETURN) TYPE BAPIRET2 *" TABLES *" T_TAB TYPE STANDARD TABLE *"---------------------------------------------------------------------- *局部变量的定义 DATA: LT_EXCEL TYPE TABLE OF ALSMEX_TABLINE. DATA: LS_EXCEL TYPE ALSMEX_TABLINE, LV_COL TYPE I, CL_CX TYPE REF TO CX_ROOT, LT_HEAD TYPE TABLE OF ALSMEX_TABLINE WITH HEADER LINE. CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' EXPORTING TEXT = '正在导入数据'. CHECK I_EXCEL_WAY IS NOT INITIAL. IF I_MOD EQ 'FIELD' AND I_TITLE_LINE IS INITIAL. E_RETURN-TYPE = 'E'. E_RETURN-MESSAGE = '请输入TITLE行'. RETURN. ENDIF. * 把文件的数据变成内表 CLEAR LT_EXCEL. CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE' EXPORTING FILENAME = I_EXCEL_WAY I_BEGIN_COL = I_BECOL I_BEGIN_ROW = I_BEROW I_END_COL = I_ENCOL I_END_ROW = I_ENROW TABLES INTERN = LT_EXCEL EXCEPTIONS INCONSISTENT_PARAMETERS = 1 UPLOAD_OLE = 2 ERROR_MESSAGE = 3 OTHERS = 4. IF SY-SUBRC NE 0. E_RETURN-TYPE = 'E'. IF SY-MSGID IS NOT INITIAL. MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4 INTO E_RETURN-MESSAGE. ELSE. CASE SY-SUBRC. WHEN '1'. E_RETURN-MESSAGE = 'inconsistent_parameters'. WHEN '2'. E_RETURN-MESSAGE = 'upload_ole'. WHEN OTHERS. E_RETURN-MESSAGE = '上传EXCEL未知异常'. ENDCASE. ENDIF. RETURN. ENDIF. DATA: DY_WA TYPE REF TO DATA, DYN_TAB TYPE REF TO DATA. FIELD-SYMBOLS: <ITABLE> TYPE STANDARD TABLE, <F_LINE> TYPE ANY, <F_FIELD> TYPE ANY. CREATE DATA DYN_TAB LIKE TABLE OF T_TAB. ASSIGN DYN_TAB->* TO <ITABLE>. CREATE DATA DY_WA LIKE LINE OF <ITABLE>. ASSIGN DY_WA->* TO <F_LINE>. * 处理内表数据 CLEAR: LS_EXCEL,LT_HEAD,LT_HEAD[]. SORT LT_EXCEL BY ROW COL. LOOP AT LT_EXCEL INTO LS_EXCEL. IF LS_EXCEL-ROW EQ I_TITLE_LINE. APPEND LS_EXCEL TO LT_HEAD. IF I_MOD EQ 'FIELD'. AT END OF ROW. SORT LT_HEAD BY COL. ENDAT. CLEAR LS_EXCEL. CONTINUE. ENDIF. ENDIF. IF I_MOD EQ 'FIELD'. CLEAR LT_HEAD. READ TABLE LT_HEAD WITH KEY COL = LS_EXCEL-COL BINARY SEARCH. ASSIGN COMPONENT LT_HEAD-VALUE OF STRUCTURE <F_LINE> TO <F_FIELD>. ELSE. LV_COL = LS_EXCEL-COL. ASSIGN COMPONENT LV_COL OF STRUCTURE <F_LINE> TO <F_FIELD>. ENDIF. IF SY-SUBRC EQ 0. TRY. MOVE LS_EXCEL-VALUE TO <F_FIELD>. CATCH CX_ROOT INTO CL_CX. ASSIGN COMPONENT 'MESSAGE' OF STRUCTURE <F_LINE> TO <F_FIELD>. IF SY-SUBRC EQ 0. <F_FIELD> = CL_CX->GET_TEXT( ). ENDIF. ASSIGN COMPONENT 'MTYPE' OF STRUCTURE <F_LINE> TO <F_FIELD>. IF SY-SUBRC EQ 0. <F_FIELD> = 'E'. ENDIF. ASSIGN COMPONENT 'STATU' OF STRUCTURE <F_LINE> TO <F_FIELD>. IF SY-SUBRC EQ 0. <F_FIELD> = ICON_LED_RED. ENDIF. ENDTRY. ENDIF. IF LT_HEAD-VALUE = 'MATNR'. CALL FUNCTION 'CONVERSION_EXIT_MATN1_INPUT' EXPORTING INPUT = <F_FIELD> IMPORTING OUTPUT = <F_FIELD>. ELSEIF LT_HEAD-VALUE(5) = 'VBELN'. CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT' EXPORTING INPUT = <F_FIELD> IMPORTING OUTPUT = <F_FIELD>. ENDIF. AT END OF ROW. APPEND <F_LINE> TO <ITABLE>. CLEAR <F_LINE>. ENDAT. CLEAR LS_EXCEL. ENDLOOP. T_TAB[] = <ITABLE>[]. IF T_TAB[] IS INITIAL. E_RETURN-TYPE = 'E'. E_RETURN-MESSAGE = '没有需要上传的数据'. ENDIF. ENDFUNCTION. |
使用时,指定本地EXECL文件名,直接调用行
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
FORM PF_UPLOAD_DATA . DATA: LS_RETURN TYPE BAPIRET2. CLEAR LS_RETURN. CALL FUNCTION 'ZFC_PUB_EXCEL_UPLOAD' EXPORTING I_BECOL = 1 I_ENCOL = 27 I_BEROW = 2 " 含标题 * I_ENROW = 50000 * I_MOD = 'FIELD' * I_TITLE_LINE = 1 I_EXCEL_WAY = P_FILE IMPORTING E_RETURN = LS_RETURN TABLES T_TAB = GT_ALV[]. IF LS_RETURN-TYPE EQ 'E'. MESSAGE LS_RETURN-MESSAGE TYPE 'S' DISPLAY LIKE 'E'. STOP. ENDIF. ENDFORM. "UPLOAD_DATA |