REPORT ZUPLOAD_DATA_FROM_EXCEL. TYPE-POOLS OLE2. *----------------------------------------------------------------------* * 数据类型定义 *----------------------------------------------------------------------* TYPES: BEGIN OF T_LOG, DB TYPE DCOBJDEF-NAME, MESSAGE TYPE STRING, END OF T_LOG. *----------------------------------------------------------------------* * 数据定义 *----------------------------------------------------------------------* DATA: FLG_EXIT TYPE CHAR1, G_TMP_DAT TYPE STRING, IT_DB TYPE STANDARD TABLE OF DCOBJDEF-NAME WITH HEADER LINE, ST_LOG TYPE T_LOG. FIELD-SYMBOLS: <FS_TEMP> TYPE TABLE, <FS_EXCEL> TYPE TABLE. *----------------------------------------------------------------------* * 选择屏幕 *----------------------------------------------------------------------* SELECTION-SCREEN BEGIN OF BLOCK BLOCK1 WITH FRAME TITLE TEXT-001. PARAMETERS: P_FILE LIKE RLGRAP-FILENAME OBLIGATORY, P_TYPE TYPE CHAR1 AS CHECKBOX DEFAULT 'X', P_DEL TYPE CHAR1 AS CHECKBOX. SELECTION-SCREEN END OF BLOCK BLOCK1. *----------------------------------------------------------------------* * 检索帮助 *----------------------------------------------------------------------* AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE. CALL FUNCTION 'TMP_GUI_BROWSE_FOR_FOLDER' EXPORTING WINDOW_TITLE = '请选择文件夹' IMPORTING SELECTED_FOLDER = P_FILE. *----------------------------------------------------------------------* * 主程序 *----------------------------------------------------------------------* START-OF-SELECTION. G_TMP_DAT = 'C:/TEMP.DAT'. CLEAR: FLG_EXIT. REFRESH: IT_DB. * 获得给定路径下所有表名 PERFORM FRM_GET_FILES. IF FLG_EXIT = 'X'. EXIT. ENDIF. LOOP AT IT_DB. CLEAR: FLG_EXIT, ST_LOG. ST_LOG-DB = IT_DB. * 读文件 PERFORM FRM_READ_FILE. * 处理表名 IF FLG_EXIT IS INITIAL. PERFORM FRM_SELECT_DB. ENDIF. * 检查表存在 IF FLG_EXIT IS INITIAL. PERFORM FRM_CHECK_DB USING IT_DB. ENDIF. * 将数据上传到内表 IF FLG_EXIT IS INITIAL. PERFORM FRM_CREATE_TABLE. ENDIF. * 上传数据到DB IF FLG_EXIT IS INITIAL. PERFORM FRM_UPLOAD_DATA. ENDIF. * 删除临时文件 PERFORM FRM_DELETE_TEMP_DAT. IF FLG_EXIT IS INITIAL. ST_LOG-MESSAGE = '数据上传成功!'. ENDIF. WRITE: /, ST_LOG-DB, ST_LOG-MESSAGE. ENDLOOP. *&---------------------------------------------------------------------* *& Form FRM_GET_FILES *&---------------------------------------------------------------------* * 获得给定路径下所有表名 *----------------------------------------------------------------------* FORM FRM_GET_FILES. DATA: L_DIR TYPE STRING, L_COUNT TYPE I, L_OBJECT TYPE REF TO CL_GUI_FRONTEND_SERVICES, L_WA_DB TYPE STRING, L_IT_DB TYPE STANDARD TABLE OF STRING. * 创建对象 CREATE OBJECT L_OBJECT. * 调用方法 L_DIR = P_FILE. CALL METHOD L_OBJECT->DIRECTORY_LIST_FILES EXPORTING DIRECTORY = L_DIR "路径 FILTER = '*.XLS' "文件过滤 FILES_ONLY = 'X' "只取得文件 CHANGING FILE_TABLE = L_IT_DB COUNT = L_COUNT. IF L_IT_DB IS INITIAL. WRITE '该文件夹下没有文件!'. FLG_EXIT = 'X'. ENDIF. LOOP AT L_IT_DB INTO L_WA_DB. TRANSLATE L_WA_DB TO UPPER CASE. APPEND L_WA_DB TO IT_DB. ENDLOOP. ENDFORM. *&---------------------------------------------------------------------* *& Form FRM_SELECT_DB *&---------------------------------------------------------------------* * 处理表名 *----------------------------------------------------------------------* FORM FRM_SELECT_DB. DATA: L_LEN TYPE I. * 取文件名 L_LEN = STRLEN( IT_DB ) - 4. IT_DB = IT_DB+0(L_LEN). IF P_TYPE = 'X'. SELECT SINGLE ZSTAB FROM ZTBCOMPARE INTO IT_DB WHERE ZCTAB = IT_DB. IF SY-SUBRC = 0. IF IT_DB+0(1) <> 'Z'. ST_LOG-MESSAGE = '这是一个系统表,请确认。'. FLG_EXIT = 'X'. ENDIF. ELSE. ST_LOG-MESSAGE = '这个表在对照表中不存在'. FLG_EXIT = 'X'. ENDIF. ELSE. IF IT_DB+0(1) <> 'Z'. ST_LOG-MESSAGE = '这是一个系统表,请确认。'. FLG_EXIT = 'X'. ENDIF. ENDIF. ENDFORM. *&---------------------------------------------------------------------* *& Form FRM_CHECK_DB *&---------------------------------------------------------------------* * 检查表存在 *----------------------------------------------------------------------* * -->PV_DB 表名 *----------------------------------------------------------------------* FORM FRM_CHECK_DB USING VALUE(PV_DB) TYPE DCOBJDEF-NAME. CALL FUNCTION 'INTERN_DD_TABL_TYPE' EXPORTING OBJNAME = PV_DB EXCEPTIONS OBJECT_NOT_FOUND = 01. IF SY-SUBRC = 1. ST_LOG-MESSAGE = '这个数据表在SAP中不存在!'. FLG_EXIT = 'X'. ENDIF. ENDFORM. *&---------------------------------------------------------------------* *& Form FRM_READ_FILE *&---------------------------------------------------------------------* * 读文件 *----------------------------------------------------------------------* FORM FRM_READ_FILE. DATA: L_LEN TYPE I, L_CHAR TYPE CHAR1, L_PATH TYPE RLGRAP-FILENAME, OLE_SELSHEET TYPE OLE2_OBJECT, OLE_ITEM TYPE OLE2_OBJECT, OLE_EXCEL TYPE OLE2_OBJECT, OLE_WORKBOOK TYPE OLE2_OBJECT, OLE_SHEET TYPE OLE2_OBJECT. * 编辑文件路径 L_LEN = STRLEN( P_FILE ) - 1. L_CHAR = P_FILE+L_LEN(1). IF L_CHAR = '/'. CONCATENATE P_FILE IT_DB INTO L_PATH. ELSE. CONCATENATE P_FILE '/' IT_DB INTO L_PATH. ENDIF. * 打开文件 CREATE OBJECT OLE_EXCEL 'EXCEL.APPLICATION'. SET PROPERTY OF OLE_EXCEL 'VISIBLE' = 0. CALL METHOD OF OLE_EXCEL 'WORKBOOKS' = OLE_WORKBOOK. CALL METHOD OF OLE_WORKBOOK 'OPEN' = OLE_SHEET EXPORTING #1 = L_PATH #2 = 0 #3 = 1. IF SY-SUBRC <> 0. ST_LOG-MESSAGE = '文件打开失败!'. FLG_EXIT = 'X'. ENDIF. * 上传数据准备 CALL METHOD OF OLE_SHEET 'SHEETS' = OLE_SELSHEET EXPORTING #1 = 1. CALL METHOD OF OLE_SELSHEET 'ROWS' = OLE_ITEM EXPORTING #1 = '1:1'. CALL METHOD OF OLE_ITEM 'DELETE'. CALL METHOD OF OLE_SHEET 'SAVEAS' EXPORTING #1 = G_TMP_DAT #2 = -4158. CALL METHOD OF OLE_SHEET 'CLOSE' EXPORTING #1 = 0. CALL METHOD OF OLE_EXCEL 'QUIT'. FREE: OBJECT OLE_SELSHEET, OBJECT OLE_ITEM, OBJECT OLE_SHEET, OBJECT OLE_WORKBOOK, OBJECT OLE_EXCEL. ENDFORM. *&---------------------------------------------------------------------* *& Form FRM_CREATE_TABLE *&---------------------------------------------------------------------* * 将数据上传到内表 *----------------------------------------------------------------------* FORM FRM_CREATE_TABLE. DATA: GDS_DD40V LIKE DD40V, L_WA_EXCEL TYPE REF TO DATA, L_IT_EXCEL TYPE REF TO DATA, L_IT_TEMP TYPE REF TO DATA, L_TYPE TYPE DCOBJDEF-NAME. FIELD-SYMBOLS: <LFS_COL> TYPE ANY, <LFS_EXCEL> TYPE ANY. CONCATENATE 'ZZ_' IT_DB '_' SY-UNAME INTO L_TYPE. * 表结构的设置 GDS_DD40V-TYPENAME = L_TYPE. GDS_DD40V-ROWTYPE = IT_DB. GDS_DD40V-ROWKIND = 'S'. GDS_DD40V-ACCESSMODE = 'T'. GDS_DD40V-KEYDEF = 'T'. GDS_DD40V-KEYKIND = 'N'. GDS_DD40V-DDLANGUAGE = SY-LANGU. * 表结构的设生成 CALL FUNCTION 'DDIF_TTYP_PUT' EXPORTING NAME = GDS_DD40V-TYPENAME DD40V_WA = GDS_DD40V EXCEPTIONS TTYP_NOT_FOUND = 1 NAME_INCONSISTENT = 2 TTYP_INCONSISTENT = 3 PUT_FAILURE = 4 PUT_REFUSED = 5 OTHERS = 6. IF SY-SUBRC <> 0. ST_LOG-MESSAGE = '表结构生成失败!'. FLG_EXIT = 'X'. EXIT. ENDIF. * 表结构的有效化 CALL FUNCTION 'DDIF_TTYP_ACTIVATE' EXPORTING NAME = GDS_DD40V-TYPENAME EXCEPTIONS NOT_FOUND = 1 PUT_FAILURE = 2 OTHERS = 3. IF SY-SUBRC <> 0. ST_LOG-MESSAGE = '表结构有效化失败!'. FLG_EXIT = 'X'. EXIT. ENDIF. CREATE DATA L_IT_EXCEL TYPE (L_TYPE). ASSIGN L_IT_EXCEL->* TO <FS_EXCEL>. CREATE DATA L_IT_TEMP TYPE (L_TYPE). ASSIGN L_IT_TEMP->* TO <FS_TEMP>. CALL FUNCTION 'GUI_UPLOAD' EXPORTING FILENAME = G_TMP_DAT FILETYPE = 'ASC' HAS_FIELD_SEPARATOR = 'X' TABLES DATA_TAB = <FS_EXCEL> EXCEPTIONS FILE_OPEN_ERROR = 1 FILE_READ_ERROR = 2 NO_BATCH = 3 GUI_REFUSE_FILETRANSFER = 4 INVALID_TYPE = 5 NO_AUTHORITY = 6 UNKNOWN_ERROR = 7 BAD_DATA_FORMAT = 8 HEADER_NOT_ALLOWED = 9 SEPARATOR_NOT_ALLOWED = 10 HEADER_TOO_LONG = 11 UNKNOWN_DP_ERROR = 12 ACCESS_DENIED = 13 DP_OUT_OF_MEMORY = 14 DISK_FULL = 15 DP_TIMEOUT = 16 OTHERS = 17. IF SY-SUBRC = 0. CREATE DATA L_IT_EXCEL TYPE (IT_DB). ASSIGN L_IT_EXCEL->* TO <LFS_EXCEL>. LOOP AT <FS_EXCEL> ASSIGNING <LFS_EXCEL>. ASSIGN COMPONENT 'MANDT' OF STRUCTURE <LFS_EXCEL> TO <LFS_COL>. <LFS_COL> = SY-MANDT. ENDLOOP. ELSE. ST_LOG-MESSAGE = '临时文件打开失败!'. FLG_EXIT = 'X'. ENDIF. * 生成表结构的削除 CALL FUNCTION 'DDIF_OBJECT_DELETE' EXPORTING TYPE = 'TTYP' NAME = L_TYPE. ENDFORM. *&---------------------------------------------------------------------* *& Form FRM_DELETE_TEMP_DAT *&---------------------------------------------------------------------* * 删除临时文件 *----------------------------------------------------------------------* FORM FRM_DELETE_TEMP_DAT. DATA L_RC TYPE I. CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_DELETE EXPORTING FILENAME = G_TMP_DAT CHANGING RC = L_RC EXCEPTIONS FILE_DELETE_FAILED = 1 CNTL_ERROR = 2 ERROR_NO_GUI = 3 FILE_NOT_FOUND = 4 ACCESS_DENIED = 5 UNKNOWN_ERROR = 6 OTHERS = 7. ENDFORM. *&---------------------------------------------------------------------* *& Form FRM_UPLOAD_DATA *&---------------------------------------------------------------------* * 上传数据到DB *----------------------------------------------------------------------* FORM FRM_UPLOAD_DATA. DATA: L_CNT TYPE I, L_CNT1 TYPE I, L_CNT2 TYPE I, L_ADD TYPE I VALUE 1000. L_CNT1 = 1. L_CNT2 = L_ADD. DESCRIBE TABLE <FS_EXCEL> LINES L_CNT. * 删除既存数据 IF P_DEL = 'X'. DELETE FROM (IT_DB) WHERE MANDT = SY-MANDT. ENDIF. DO. REFRESH <FS_TEMP>. APPEND LINES OF <FS_EXCEL> FROM L_CNT1 TO L_CNT2 TO <FS_TEMP>. MODIFY (IT_DB) FROM TABLE <FS_TEMP>. IF L_CNT2 = L_CNT. EXIT. ENDIF. L_CNT1 = L_CNT1 + L_ADD. L_CNT2 = L_CNT2 + L_ADD. IF L_CNT2 > L_CNT. L_CNT2 = L_CNT. ENDIF. ENDDO. IF SY-SUBRC <> 0. ROLLBACK WORK. ST_LOG-MESSAGE = '数据上传失败!'. FLG_EXIT = 'X'. ENDIF. ENDFORM.