PARAMETERS P_XLFIL = EXCEL File name P_XLSHT = EXCEL Sheet name -------------------------------------------------------------------------------- TEXTS TEXT-000 = File Options TEXT-001 = Processing Options TEXT-002 = Post Directly TEXT-003 = Post using BDC TEXT-004 = Test run TEXT-100 = ,*.*,*.XLS,. TEXT-101 = Open EXCEL File TEXT-102 = Opening XL Sheet... TEXT-103 = Reading XL Sheet... TEXT-104 = Header Data TEXT-105 = Detail Data TEXT-106 = Document $BELNR posted in Cocd $BUKRS for year $GJAHR TEXT-107 = Saving XL Sheet... -------------------------------------------------------------------------------- PROGRAM REPORT ZFGLXL01 LINE-SIZE 80. INCLUDE OLE2INCL. CONSTANTS: BATCHINPUT(1) TYPE C VALUE 'B', CALLTRANS(1) TYPE C VALUE 'C', MODE(1) TYPE C VALUE 'N', XL_MAX_COL TYPE I VALUE 256, XL_HDR_ROW TYPE I VALUE 8, XL_DTL_ROW TYPE I VALUE 15, XL_RESERVD TYPE I VALUE 2. DATA: BEGIN OF FTPOST OCCURS 100. INCLUDE STRUCTURE FTPOST. DATA: END OF FTPOST. DATA: BEGIN OF FTTAX OCCURS 0. INCLUDE STRUCTURE FTTAX. DATA: END OF FTTAX. DATA: BEGIN OF XBLNTAB OCCURS 2. INCLUDE STRUCTURE BLNTAB. DATA: END OF XBLNTAB. DATA: BEGIN OF HDR_FLDS OCCURS 5, FNAM LIKE FTPOST-FNAM, END OF HDR_FLDS. DATA: BEGIN OF FLDS OCCURS 200, FNAM LIKE FTPOST-FNAM, END OF FLDS. DATA: BEGIN OF OUT_LINE OCCURS 40, COL_HDR(11) TYPE C, END OF OUT_LINE. DATA: XL_APPL TYPE OLE2_OBJECT, XL_SHEET TYPE OLE2_OBJECT, XL_WKBKS TYPE OLE2_OBJECT. DATA: OPEN(1) TYPE C, FLDLEN TYPE I, DOCL(78) TYPE C, FUNCTION LIKE RFIPI-FUNCT, XBDCC LIKE RFIPI-XBDCC. SELECTION-SCREEN BEGIN OF BLOCK 1 WITH FRAME TITLE TEXT-000. PARAMETERS: P_XLFIL LIKE RLGRAP-FILENAME OBLIGATORY, P_XLSHT LIKE RLGRAP-FILENAME OBLIGATORY. SELECTION-SCREEN END OF BLOCK 1. SELECTION-SCREEN BEGIN OF BLOCK 2 WITH FRAME TITLE TEXT-001. SELECTION-SCREEN BEGIN OF LINE. PARAMETERS: P_POST LIKE FEBPDO-XCALL_EBCK RADIOBUTTON GROUP 1. SELECTION-SCREEN COMMENT 03(29) TEXT-002 FOR FIELD P_POST. PARAMETERS: P_MODE LIKE RFPDO-ALLGAZMD NO-DISPLAY. SELECTION-SCREEN: END OF LINE. SELECTION-SCREEN BEGIN OF LINE. PARAMETERS: P_XBDC LIKE FEBPDO-XBINPT RADIOBUTTON GROUP 1. SELECTION-SCREEN COMMENT 03(29) TEXT-003 FOR FIELD P_XBDC. SELECTION-SCREEN: END OF LINE. SELECTION-SCREEN: BEGIN OF LINE. PARAMETERS: P_TEST LIKE RFPDO1-FEBTESTL RADIOBUTTON GROUP 1. SELECTION-SCREEN COMMENT 03(29) TEXT-004 FOR FIELD P_TEST. SELECTION-SCREEN: END OF LINE. PARAMETERS: GROUP LIKE APQI-GROUPID OBLIGATORY. SELECTION-SCREEN END OF BLOCK 2. *-At Selection-Screen--------------------------------------------------------* AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_XLFIL. PERFORM WS_GET_FILENAME. *-Start-Of-Selection---------------------------------------------------------* START-OF-SELECTION. CLEAR OPEN. IF P_POST = 'X'. FUNCTION = CALLTRANS. XBDCC = 'X'. ENDIF. IF P_XBDC = 'X'. FUNCTION = BATCHINPUT. XBDCC = ' '. ENDIF. PERFORM EXCEL_OPEN_WORKBOOK CHANGING XL_APPL XL_WKBKS XL_SHEET. PERFORM EXCEL_READ_SHEET USING XL_SHEET. PERFORM EXCEL_SAVE_SHEET USING XL_APPL XL_WKBKS. FREE OBJECT: XL_APPL, XL_SHEET, XL_WKBKS. *-End-Of-Selection-----------------------------------------------------------* END-OF-SELECTION. IF P_TEST NE 'X'. PERFORM POSTING_INTERFACE_START. PERFORM POSTING_INTERFACE_DOCUMENT. PERFORM POSTING_INTERFACE_CLOSE. DESCRIBE TABLE XBLNTAB LINES FLDLEN. IF ( FUNCTION = CALLTRANS ) AND ( FLDLEN > 0 ). ULINE. FORMAT COLOR COL_NEGATIVE. LOOP AT XBLNTAB. DOCL = TEXT-106. REPLACE '$BELNR' WITH XBLNTAB-BELNR INTO DOCL. REPLACE '$BUKRS' WITH XBLNTAB-BUKRS INTO DOCL. REPLACE '$GJAHR' WITH XBLNTAB-GJAHR INTO DOCL. WRITE: /1(1) SY-VLINE, DOCL,80(10) SY-VLINE. ENDLOOP. ULINE. ENDIF. ENDIF. LOOP AT FTPOST. AT NEW STYPE. FORMAT COLOR COL_HEADING. IF FTPOST-STYPE = 'K'. WRITE: /1(1) SY-VLINE,TEXT-104,80(1) SY-VLINE. ELSE. ULINE. WRITE: /. ULINE. WRITE: /1(1) SY-VLINE,TEXT-105,80(1) SY-VLINE. ENDIF. ULINE. ENDAT. AT NEW COUNT. ULINE. ENDAT. FORMAT COLOR COL_KEY. COMPUTE FLDLEN = STRLEN( FTPOST-FNAM ). WRITE: /1(1) SY-VLINE. WRITE AT (FLDLEN) FTPOST-FNAM. WRITE 20(1) SY-VLINE. FORMAT COLOR COL_NORMAL. COMPUTE FLDLEN = STRLEN( FTPOST-FVAL ). WRITE AT (FLDLEN) FTPOST-FVAL. WRITE 80(1) SY-VLINE. ENDLOOP. ULINE. *&---------------------------------------------------------------------* *& Form EXCEL_OPEN_WORKBOOK *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * <--P_XL_APPL text * * <--P_XL_WKBKS text * * <--P_XL_SHEET text * *----------------------------------------------------------------------* FORM EXCEL_OPEN_WORKBOOK CHANGING XLAPP TYPE OLE2_OBJECT XLWKBKS TYPE OLE2_OBJECT XLSHEET TYPE OLE2_OBJECT. CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' EXPORTING PERCENTAGE = 0 TEXT = TEXT-102. CREATE OBJECT XLAPP 'EXCEL.APPLICATION'. SET PROPERTY OF XLAPP 'Visible' = 0. CALL METHOD OF XLAPP 'Workbooks' = XLWKBKS. CALL METHOD OF XLWKBKS 'Open' EXPORTING #1 = P_XLFIL. CALL METHOD OF XLAPP 'Worksheets' = XLSHEET EXPORTING #1 = P_XLSHT. CALL METHOD OF XLSHEET 'Activate'. FREE OBJECT XLWKBKS. GET PROPERTY OF XLAPP 'ActiveWorkbook' = XLWKBKS. ENDFORM. " EXCEL_OPEN_WORKBOOK *&---------------------------------------------------------------------* *& Form EXCEL_READ_SHEET *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * -->P_XL_SHEET text * *----------------------------------------------------------------------* FORM EXCEL_READ_SHEET USING XLSHEET TYPE OLE2_OBJECT. DATA: END_OF_DATA TYPE I, XL_CURR_ROW TYPE I, COL_COUNT TYPE I, INDX TYPE I, FVAL(50) TYPE C, COUNT TYPE I VALUE 1. CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' EXPORTING PERCENTAGE = 0 TEXT = TEXT-103. CLEAR: END_OF_DATA, COL_COUNT. PERFORM EXCEL_READ_ROW USING XLSHEET XL_DTL_ROW CHANGING COL_COUNT. WHILE END_OF_DATA IS INITIAL. INDX = XL_MAX_COL * ( XL_DTL_ROW + SY-INDEX + 1 ) + 1. DO COL_COUNT TIMES. PERFORM EXCEL_READ_CELL USING XLSHEET INDX CHANGING FVAL. IF SY-INDEX <= XL_RESERVD. CASE SY-INDEX. WHEN 1. IF FVAL IS INITIAL. END_OF_DATA = 1. EXIT. ENDIF. IF FVAL = 1. PERFORM EXCEL_READ_HEADER USING XLSHEET XL_HDR_ROW. ENDIF. WHEN 2. IF NOT ( FVAL IS INITIAL ). EXIT. ENDIF. ENDCASE. ELSE. IF NOT ( FVAL IS INITIAL ). READ TABLE FLDS INDEX SY-INDEX. PERFORM XL_CONVERT CHANGING FVAL. FTPOST-COUNT = COUNT. FTPOST-STYPE = 'P'. MOVE-CORRESPONDING FLDS TO FTPOST. MOVE FVAL TO FTPOST-FVAL. APPEND FTPOST. ENDIF. ENDIF. INDX = INDX + 1. ENDDO. XL_CURR_ROW = XL_CURR_ROW + 1. COUNT = COUNT + 1. ENDWHILE. ENDFORM. " EXCEL_READ_SHEET *&---------------------------------------------------------------------* *& Form EXCEL_SAVE_SHEET *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * -->P_XL_APPL text * * -->P_XL_WKBK text * *----------------------------------------------------------------------* FORM EXCEL_SAVE_SHEET USING XLAPP TYPE OLE2_OBJECT XLWKBKS TYPE OLE2_OBJECT. CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' EXPORTING PERCENTAGE = 0 TEXT = TEXT-107. CALL METHOD OF XLWKBKS 'Save'. CALL METHOD OF XLAPP 'Quit'. ENDFORM. " EXCEL_SAVE_SHEET *&---------------------------------------------------------------------* *& Form WS_GET_FILENAME *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* FORM WS_GET_FILENAME. CALL FUNCTION 'WS_FILENAME_GET' EXPORTING DEF_PATH = 'C:\' MASK = TEXT-100 MODE = 'O' TITLE = TEXT-101 IMPORTING FILENAME = P_XLFIL EXCEPTIONS INV_WINSYS = 1 NO_BATCH = 2 SELECTION_CANCEL = 3 SELECTION_ERROR = 4 OTHERS = 5. IF SY-SUBRC NE 0. CLEAR P_XLFIL. ENDIF. ENDFORM. " WS_GET_FILENAME *&---------------------------------------------------------------------* *& Form EXCEL_READ_HEADER *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * -->XLS text * * <--CURR text * * <--COL text * *----------------------------------------------------------------------* FORM EXCEL_READ_HEADER USING XLS TYPE OLE2_OBJECT CURR TYPE I. DATA: CELL TYPE OLE2_OBJECT, INDX TYPE I, FNAM LIKE FTPOST-FNAM, COL LIKE SY-INDEX, FVAL(50) TYPE C. CLEAR COL. CLEAR FTPOST. FTPOST-COUNT = 1. FTPOST-STYPE = 'K'. REFRESH HDR_FLDS. INDX = XL_MAX_COL * ( CURR - 1 ) + 1. DO. CLEAR FNAM. CALL METHOD OF XLS 'Cells' = CELL EXPORTING #1 = INDX. GET PROPERTY OF CELL 'Value' = FNAM. CONDENSE FNAM NO-GAPS. IF NOT ( FNAM IS INITIAL ). MOVE FNAM TO HDR_FLDS-FNAM. APPEND HDR_FLDS. INDX = INDX + 1. COL = COL + 1. ELSE. EXIT. ENDIF. ENDDO. INDX = XL_MAX_COL * ( XL_HDR_ROW + 2 ) + 1. DO COL TIMES. PERFORM EXCEL_READ_CELL USING XLS INDX CHANGING FVAL. IF NOT ( FVAL IS INITIAL ). READ TABLE HDR_FLDS INDEX SY-INDEX. MOVE HDR_FLDS-FNAM TO FTPOST-FNAM. MOVE FVAL TO FTPOST-FVAL. APPEND FTPOST. ENDIF. INDX = INDX + 1. ENDDO. FREE: CELL. ENDFORM. " EXCEL_READ_ROW *&---------------------------------------------------------------------* *& Form EXCEL_READ_CELL *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * -->P_XLSHEET text * * -->P_INDX text * * <--P_FVAL text * *----------------------------------------------------------------------* FORM EXCEL_READ_CELL USING XLS TYPE OLE2_OBJECT INDX TYPE I CHANGING FVAL. DATA: CELL TYPE OLE2_OBJECT. CALL METHOD OF XLS 'Cells' = CELL EXPORTING #1 = INDX. GET PROPERTY OF CELL 'Value' = FVAL. ENDFORM. " EXCEL_READ_CELL *&---------------------------------------------------------------------* *& Form EXCEL_READ_ROW *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * -->P_XLSHEET text * * -->P_XL_DTL_ROW text * * <--P_COL_COUNT text * *----------------------------------------------------------------------* FORM EXCEL_READ_ROW USING XLS TYPE OLE2_OBJECT CURR TYPE I CHANGING COL TYPE I. DATA: CELL TYPE OLE2_OBJECT, INDX TYPE I, FNAM LIKE FTPOST-FNAM. REFRESH FLDS. INDX = XL_MAX_COL * ( CURR - 1 ) + 1. DO. CLEAR FNAM. CALL METHOD OF XLS 'Cells' = CELL EXPORTING #1 = INDX. GET PROPERTY OF CELL 'Value' = FNAM. CONDENSE FNAM NO-GAPS. IF NOT ( FNAM IS INITIAL ). IF SY-INDEX > XL_RESERVD. MOVE FNAM TO FLDS-FNAM. ELSE. MOVE 'RESERVED' TO FLDS-FNAM. ENDIF. APPEND FLDS. INDX = INDX + 1. COL = COL + 1. ELSE. EXIT. ENDIF. ENDDO. FREE: CELL. ENDFORM. " EXCEL_READ_ROW *&---------------------------------------------------------------------* *& Form XL_CONVERT *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * <--P_FVAL text * *----------------------------------------------------------------------* FORM XL_CONVERT CHANGING VAL. IF VAL CS '.'. WRITE VAL TO VAL RIGHT-JUSTIFIED. SHIFT VAL RIGHT DELETING TRAILING '0'. SHIFT VAL RIGHT DELETING TRAILING '.'. CONDENSE VAL NO-GAPS. ENDIF. ENDFORM. " XL_CONVERT *&---------------------------------------------------------------------* *& Form POSTING_INTERFACE_START *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* FORM POSTING_INTERFACE_START. DATA: GROUPNAME LIKE APQI-GROUPID. IF FUNCTION = CALLTRANS. GROUPNAME = GROUP. GROUPNAME+8(4) = '-ERR'. CONDENSE GROUPNAME NO-GAPS. ELSE. GROUPNAME = GROUP. GROUPNAME+8(3) = '-OK'. CONDENSE GROUPNAME NO-GAPS. ENDIF. CALL FUNCTION 'POSTING_INTERFACE_START' EXPORTING I_FUNCTION = FUNCTION I_MODE = MODE I_GROUP = GROUPNAME I_USER = SY-UNAME I_XBDCC = XBDCC. ENDFORM. " POSTING_INTERFACE_START *&---------------------------------------------------------------------* *& Form POSTING_INTERFACE_DOCUMENT *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* FORM POSTING_INTERFACE_DOCUMENT. DATA: SUBRC LIKE SY-SUBRC, MSGID LIKE SY-MSGID, MSGTY LIKE SY-MSGTY, MSGNO LIKE SY-MSGNO, MSGV1 LIKE SY-MSGV1, MSGV2 LIKE SY-MSGV2, MSGV3 LIKE SY-MSGV3, MSGV4 LIKE SY-MSGV4. CALL FUNCTION 'POSTING_INTERFACE_DOCUMENT' EXPORTING I_TCODE = 'FB01' IMPORTING E_SUBRC = SUBRC E_MSGID = MSGID E_MSGTY = MSGTY E_MSGNO = MSGNO E_MSGV1 = MSGV1 E_MSGV2 = MSGV2 E_MSGV3 = MSGV3 E_MSGV4 = MSGV4 TABLES T_FTPOST = FTPOST T_FTTAX = FTTAX T_BLNTAB = XBLNTAB. ENDFORM. " POSTING_INTERFACE_DOCUMENT *&---------------------------------------------------------------------* *& Form POSTING_INTERFACE_CLOSE *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* FORM POSTING_INTERFACE_CLOSE. CALL FUNCTION 'POSTING_INTERFACE_END'. ENDFORM. " POSTING_INTERFACE_CLOSE