我这个是比较不规则的数据填充
1.程序开头，定义一个工作区，存对应单元格的值：
BEGIN OF TY_EXCEL, C031(40) TYPE C, C032(40) TYPE C, C033(40) TYPE C, C034(40) TYPE C, C035(40) TYPE C, C036(40) TYPE C, C052(40) TYPE C, C054(40) TYPE C, C056(40) TYPE C, C058(40) TYPE C, C091(40) TYPE C, C151(40) TYPE C, C152(40) TYPE C, C153(40) TYPE C, C154(40) TYPE C, C155(40) TYPE C, C156(40) TYPE C, C157(40) TYPE C, C158(40) TYPE C, C181(40) TYPE C, C182(40) TYPE C, C183(40) TYPE C, C184(40) TYPE C, C185(40) TYPE C, C186(40) TYPE C, C187(40) TYPE C, C188(40) TYPE C, END OF TY_EXCEL.
DOI一些变量定义：
DATA: DOCUMENT_NAME(30) VALUE 'ZHRR010', ACTIVE_SHEET(50) VALUE 'Sheet1'. CONSTANTS INPLACE VALUE 'X'. DATA: CONTAINER TYPE REF TO CL_GUI_CUSTOM_CONTAINER, CONTROL TYPE REF TO I_OI_CONTAINER_CONTROL, DOCUMENT TYPE REF TO I_OI_DOCUMENT_PROXY, SPREADSHEET TYPE REF TO I_OI_SPREADSHEET, ERROR TYPE REF TO I_OI_ERROR, ERRORS TYPE REF TO I_OI_ERROR OCCURS 0 WITH HEADER LINE. DATA: RANGEITEM TYPE SOI_RANGE_ITEM. DATA: RANGES TYPE SOI_RANGE_LIST. DATA: EXCEL_INPUT TYPE SOI_GENERIC_TABLE. DATA: EXCEL_INPUT_WA TYPE SOI_GENERIC_ITEM. DATA: INITIALIZED(1), RETCODE TYPE SOI_RET_STRING. DATA: ITEM_URL(256), ALREADY_DONE, NEWNAME(40). DATA DOCUMENT_TYPE(80). DATA: EXCEL(80) VALUE 'Excel.Sheet'. DATA: LINE_COUNT TYPE I, COLUMN_COUNT TYPE I. DATA: OK_CODE LIKE SY-UCOMM.
主屏幕上画个CONTAINER。
单元格填充
FORM FILL_CELL_01 USING I J VAL. DATA: COLUMNS_NUMBER TYPE I, ROWS_NUMBER TYPE I. COLUMNS_NUMBER = 1. ROWS_NUMBER = 1. CALL METHOD SPREADSHEET->INSERT_RANGE_DIM EXPORTING NAME = 'cell' NO_FLUSH = 'X' TOP = I LEFT = J ROWS = ROWS_NUMBER COLUMNS = COLUMNS_NUMBER IMPORTING ERROR = ERRORS. APPEND ERRORS. REFRESH: RANGES, EXCEL_INPUT. RANGEITEM-NAME = 'cell'. RANGEITEM-COLUMNS = 1. RANGEITEM-ROWS = 1. APPEND RANGEITEM TO RANGES. EXCEL_INPUT_WA-COLUMN = 1. EXCEL_INPUT_WA-ROW = 1. EXCEL_INPUT_WA-VALUE = VAL. APPEND EXCEL_INPUT_WA TO EXCEL_INPUT. * set data CALL METHOD SPREADSHEET->SET_RANGES_DATA EXPORTING RANGES = RANGES CONTENTS = EXCEL_INPUT NO_FLUSH = 'X' IMPORTING ERROR = ERRORS. APPEND ERRORS. CALL METHOD SPREADSHEET->FIT_WIDEST EXPORTING NAME = SPACE NO_FLUSH = 'X'. REFRESH: RANGES, EXCEL_INPUT. ENDFORM. "fill_cell
PBO
MODULE STATUS_9000 OUTPUT. SET PF-STATUS 'STATUS_9000'. * SET TITLEBAR 'xxx'. * 创建EXCEL模板 PERFORM CREATE_BASIC_OBJECTS USING '' '' '' '' DOCUMENT_NAME. * 输出EXCEL PERFORM OUTPUT_TO_EXCEL. ENDMODULE.
FORM CREATE_BASIC_OBJECTS USING P_APP_NAME P_CLASSNAME P_CLASSTYPE P_OBJ_KEY P_DOCNAME. DATA L_APP_NAME(200). DATA: BDS_INSTANCE TYPE REF TO CL_BDS_DOCUMENT_SET. DATA: DOC_SIGNATURE TYPE SBDST_SIGNATURE, WA_DOC_SIGNATURE LIKE LINE OF DOC_SIGNATURE, DOC_COMPONENTS TYPE SBDST_COMPONENTS, DOC_URIS TYPE SBDST_URI, WA_DOC_URIS LIKE LINE OF DOC_URIS. DATA: DOC_CLASSNAME TYPE SBDST_CLASSNAME VALUE 'PICTURES', DOC_CLASSTYPE TYPE SBDST_CLASSTYPE VALUE 'OT', DOC_OBJECT_KEY TYPE SBDST_OBJECT_KEY VALUE 'EXCEL'. CHECK INITIALIZED IS INITIAL. * 处理参数 IF P_APP_NAME IS INITIAL. L_APP_NAME = 'R/3 Reporter'. ELSE. L_APP_NAME = P_APP_NAME. ENDIF. IF NOT ( P_CLASSNAME IS INITIAL OR P_CLASSTYPE IS INITIAL OR P_OBJ_KEY IS INITIAL ). DOC_CLASSNAME = P_CLASSNAME. DOC_CLASSTYPE = P_CLASSTYPE. DOC_OBJECT_KEY = P_OBJ_KEY. ENDIF. * 创建容器控制器实例 CALL METHOD C_OI_CONTAINER_CONTROL_CREATOR=>GET_CONTAINER_CONTROL IMPORTING CONTROL = CONTROL ERROR = ERROR. CALL METHOD ERROR->RAISE_MESSAGE EXPORTING TYPE = 'E'. * 创建屏幕上的容器对象 CREATE OBJECT CONTAINER EXPORTING CONTAINER_NAME = 'CONTAINER'. * 初始化容器控制器 CALL METHOD CONTROL->INIT_CONTROL EXPORTING R3_APPLICATION_NAME = L_APP_NAME INPLACE_ENABLED = INPLACE INPLACE_SCROLL_DOCUMENTS = 'X' PARENT = CONTAINER REGISTER_ON_CLOSE_EVENT = 'X' REGISTER_ON_CUSTOM_EVENT = 'X' NO_FLUSH = 'X' IMPORTING ERROR = ERRORS. APPEND ERRORS. CLEAR ITEM_URL. WA_DOC_SIGNATURE-PROP_NAME = 'DESCRIPTION'. DOCUMENT_TYPE = EXCEL. WA_DOC_SIGNATURE-PROP_VALUE = DOCUMENT_TYPE. APPEND WA_DOC_SIGNATURE TO DOC_SIGNATURE. * BDS对象实例化 CREATE OBJECT BDS_INSTANCE. * 读取BDS内容（模板） CALL METHOD BDS_INSTANCE->GET_INFO EXPORTING CLASSNAME = DOC_CLASSNAME CLASSTYPE = DOC_CLASSTYPE OBJECT_KEY = DOC_OBJECT_KEY CHANGING COMPONENTS = DOC_COMPONENTS SIGNATURE = DOC_SIGNATURE. * 读取BDS的URL CALL METHOD BDS_INSTANCE->GET_WITH_URL EXPORTING CLASSNAME = DOC_CLASSNAME CLASSTYPE = DOC_CLASSTYPE OBJECT_KEY = DOC_OBJECT_KEY CHANGING URIS = DOC_URIS SIGNATURE = DOC_SIGNATURE. FREE BDS_INSTANCE. LOOP AT DOC_URIS INTO WA_DOC_URIS. SEARCH WA_DOC_URIS-URI FOR 'ZHRR010'. IF SY-SUBRC = 0. ITEM_URL = WA_DOC_URIS-URI. ENDIF. ENDLOOP. * READ TABLE DOC_URIS INTO WA_DOC_URIS INDEX 3. * ITEM_URL = WA_DOC_URIS-URI. * ask the SAP DOI container for a i_oi_document_proxy for Excel * 容器控制器获得一个EXCEL文档代理 CALL METHOD CONTROL->GET_DOCUMENT_PROXY EXPORTING DOCUMENT_TYPE = 'Excel.Sheet1' NO_FLUSH = 'X' IMPORTING DOCUMENT_PROXY = DOCUMENT ERROR = ERRORS. APPEND ERRORS. * open a document saved in business document service. * 容器控制器中打开指定BDS返回的文档 CALL METHOD DOCUMENT->OPEN_DOCUMENT EXPORTING OPEN_INPLACE = INPLACE DOCUMENT_URL = ITEM_URL. DATA: HAS TYPE I. CALL METHOD DOCUMENT->HAS_SPREADSHEET_INTERFACE EXPORTING NO_FLUSH = '' IMPORTING IS_AVAILABLE = HAS ERROR = ERRORS. APPEND ERRORS. CALL METHOD DOCUMENT->GET_SPREADSHEET_INTERFACE EXPORTING NO_FLUSH = ' ' IMPORTING SHEET_INTERFACE = SPREADSHEET ERROR = ERRORS. APPEND ERRORS. * Activate sheet CALL METHOD SPREADSHEET->SELECT_SHEET EXPORTING NAME = ACTIVE_SHEET NO_FLUSH = '' IMPORTING ERROR = ERRORS. APPEND ERRORS. *错误处理 LOOP AT ERRORS. CALL METHOD ERRORS->RAISE_MESSAGE EXPORTING TYPE = 'S'. ENDLOOP. FREE ERRORS. INITIALIZED = 'X'. ENDFORM.
FORM OUTPUT_TO_EXCEL . DATA:LV_LINE TYPE I. DATA:LV_YD LIKE LINE OF GT_PA0001. DATA:LV_COUNT TYPE I. DATA:LT_YD TYPE TABLE OF TY_0000. DATA:LV_GZ1 TYPE I. DATA:LV_GZ2 TYPE I. PERFORM FILL_CELL_01 USING 3 1 GW_EXCEL-C031 ."工号 PERFORM FILL_CELL_01 USING 3 2 GW_EXCEL-C032 ."工号 PERFORM FILL_CELL_01 USING 3 4 GW_EXCEL-C033 ."工号 PERFORM FILL_CELL_01 USING 3 5 GW_EXCEL-C034 ."工号 PERFORM FILL_CELL_01 USING 3 6 GW_EXCEL-C035 ."工号 PERFORM FILL_CELL_01 USING 3 7 GW_EXCEL-C036 ."工号 IF GW_EXCEL-C054 > 0. PERFORM FILL_CELL_01 USING 5 2 GW_EXCEL-C052 ."工号 PERFORM FILL_CELL_01 USING 5 4 GW_EXCEL-C054 ."工号 ENDIF. PERFORM FILL_CELL_01 USING 5 6 GW_EXCEL-C056 ."工号 PERFORM FILL_CELL_01 USING 5 8 GW_EXCEL-C058 ."工号 AUTHORITY-CHECK OBJECT 'ZHR01' ID 'INFOTYP' FIELD '9004'. IF SY-SUBRC = 0. PERFORM FILL_CELL_01 USING 9 2 GW_EXCEL-C091 ."工号 ENDIF. * AUTHORITY-CHECK OBJECT 'ZHR02' * ID 'INFOTYP' FIELD '9004'. * IF SY-SUBRC = 0. * PERFORM FILL_CELL_01 USING 9 2 GW_EXCEL-C091 ."工号 * ENDIF. PERFORM FILL_CELL_01 USING 15 1 GW_EXCEL-C151 ."工号 PERFORM FILL_CELL_01 USING 15 2 GW_EXCEL-C152 ."工号 PERFORM FILL_CELL_01 USING 15 3 GW_EXCEL-C153 ."工号 PERFORM FILL_CELL_01 USING 15 4 GW_EXCEL-C154 ."工号 PERFORM FILL_CELL_01 USING 15 5 GW_EXCEL-C155 ."工号 PERFORM FILL_CELL_01 USING 15 6 GW_EXCEL-C156 ."工号 * PERFORM FILL_CELL_01 USING 15 7 GW_EXCEL-C157 ."工号 PERFORM FILL_CELL_01 USING 15 8 GW_EXCEL-C158 ."工号 PERFORM FILL_CELL_01 USING 18 1 GW_EXCEL-C181 ."工号 PERFORM FILL_CELL_01 USING 18 2 GW_EXCEL-C182 ."工号 PERFORM FILL_CELL_01 USING 18 3 GW_EXCEL-C183 ."工号 PERFORM FILL_CELL_01 USING 18 4 GW_EXCEL-C184 ."工号 PERFORM FILL_CELL_01 USING 18 5 GW_EXCEL-C185 ."工号 PERFORM FILL_CELL_01 USING 18 6 GW_EXCEL-C186 ."工号 PERFORM FILL_CELL_01 USING 18 7 GW_EXCEL-C187 ."工号 PERFORM FILL_CELL_01 USING 18 8 GW_EXCEL-C188 ."工号 LV_LINE = 8. "异动情况 SORT GT_PA0000 BY PERNR BEGDA ENDDA ASCENDING. "绩效明细 LV_COUNT = LINES( GT_PA9002 ) - 1. DO LV_COUNT TIMES. CALL METHOD DOCUMENT->EXECUTE_MACRO EXPORTING MACRO_STRING = '模块4.Macro2' IMPORTING ERROR = ERRORS. ENDDO. "异动明细 APPEND LINES OF GT_PA0000 TO LT_YD. DELETE LT_YD WHERE MASSN <> 'Z3'. LV_COUNT = LINES( LT_YD ) - 1. DO LV_COUNT TIMES. CALL METHOD DOCUMENT->EXECUTE_MACRO EXPORTING MACRO_STRING = '模块4.Macro1' IMPORTING ERROR = ERRORS. ENDDO. "************************************工资 SORT GT_PA9004[] BY PERNR BEGDA DESCENDING. DELETE GT_PA9004 WHERE ZJBGZ = 0 AND ZJXGZ = 0 AND ZZWJT = 0 AND ZFDJJ = 0 AND ZTSGWJT = 0 AND ZQTJT = 0. LOOP AT GT_PA0000 INTO GW_PA0000." WHERE MASSN = 'Z3'. READ TABLE GT_PA0001 INTO GW_PA0001 WITH KEY PERNR = GW_PA0000-PERNR BEGDA = GW_PA0000-BEGDA. * ENDDA = GW_PA0000-ENDDA. IF SY-SUBRC = 0. IF GW_PA0000-MASSN = 'Z1' OR GW_PA0000-MASSN = 'Z2'. MOVE-CORRESPONDING GW_PA0001 TO LV_YD."将异动前数据保存 ENDIF. IF GW_PA0000-MASSN = 'Z3'."异动 CLEAR:LV_GZ1,LV_GZ1. READ TABLE GT_PA9004 INTO GW_PA9004 WITH KEY PERNR = GW_PA0000-PERNR BEGDA = GW_PA0000-BEGDA. * ENDDA = GW_PA0000-ENDDA. IF SY-SUBRC = 0. LV_GZ2 = GW_PA9004-ZJBGZ + GW_PA9004-ZJXGZ + GW_PA9004-ZZWJT + GW_PA9004-ZFDJJ + GW_PA9004-ZTSGWJT + GW_PA9004-ZQTJT. ENDIF. LOOP AT GT_PA9004 INTO GW_PA9004 WHERE PERNR = GW_PA0000-PERNR. IF GW_PA9004-BEGDA = GW_PA0000-BEGDA."当前异动后工资 LV_GZ2 = GW_PA9004-ZJBGZ + GW_PA9004-ZJXGZ + GW_PA9004-ZZWJT + GW_PA9004-ZFDJJ + GW_PA9004-ZTSGWJT + GW_PA9004-ZQTJT. ELSEIF GW_PA9004-BEGDA < GW_PA0000-BEGDA."当前异动前工资 IF LV_GZ1 <> 0. LV_GZ1 = GW_PA9004-ZJBGZ + GW_PA9004-ZJXGZ + GW_PA9004-ZZWJT + GW_PA9004-ZFDJJ + GW_PA9004-ZTSGWJT + GW_PA9004-ZQTJT. ENDIF. ENDIF. ENDLOOP. PERFORM FILL_CELL_01 USING LV_LINE 1 GW_PA0001-BEGDA ."异动时间 PERFORM FILL_CELL_01 USING LV_LINE 2 LV_YD-ORGTX ."异动前部门 PERFORM FILL_CELL_01 USING LV_LINE 4 LV_YD-PLSTX ."异动前岗位 AUTHORITY-CHECK OBJECT 'ZHR01' ID 'INFOTYP' FIELD '9004'. IF SY-SUBRC = 0. PERFORM FILL_CELL_01 USING LV_LINE 5 LV_GZ1 ."异动时间 PERFORM FILL_CELL_01 USING LV_LINE 8 LV_GZ2 ."异动后工资 ENDIF. PERFORM FILL_CELL_01 USING LV_LINE 6 GW_PA0001-ORGTX ."异动后部门 PERFORM FILL_CELL_01 USING LV_LINE 7 GW_PA0001-PLSTX ."异动后岗位 MOVE-CORRESPONDING GW_PA0001 TO LV_YD."将异动前数据保存 LV_LINE = LV_LINE + 1. ENDIF. ENDIF. ENDLOOP. "无异动，行数下移一位 IF LV_COUNT < 0. LV_LINE = LV_LINE + 4. ELSE."IF LV_COUNT > 1. LV_LINE = LV_LINE + 3. * ELSEIF LV_COUNT = 1. * LV_LINE = LV_LINE + 2. ENDIF. LOOP AT GT_PA9002 INTO GW_PA9002. PERFORM FILL_CELL_01 USING LV_LINE 1 GW_PA9002-ZJXNY ."绩效考核年月份 PERFORM FILL_CELL_01 USING LV_LINE 2 GW_PA9002-ZJXZF ."绩效总分 AUTHORITY-CHECK OBJECT 'ZHR01' ID 'INFOTYP' FIELD '9004'. IF SY-SUBRC = 0. PERFORM FILL_CELL_01 USING LV_LINE 3 GW_PA9002-ZJXJJ ."奖惩金额 ENDIF. LV_LINE = LV_LINE + 1. ENDLOOP. "证书 DATA:LV_ZS(100) TYPE C. LV_LINE = LV_LINE + 7. LOOP AT GT_PA9003 INTO GW_PA9003. IF GW_PA9003-ZZCZG IS NOT INITIAL. CONCATENATE LV_ZS GW_PA9003-ZZCZG INTO LV_ZS SEPARATED BY '/'. ENDIF. ENDLOOP. PERFORM FILL_CELL_01 USING LV_LINE 2 LV_ZS ."绩效考核年月份 ENDFORM.
PAI
MODULE USER_COMMAND_9000 INPUT. IF OK_CODE = 'BACK' . CALL METHOD DOCUMENT->CLOSE_DOCUMENT. FREE DOCUMENT. CALL METHOD CONTROL->DESTROY_CONTROL. FREE CONTROL. SET SCREEN 0 . * ELSEIF OK_CODE = 'EXCEL' . * PERFORM SUB_OUT_EXCEL . ENDIF . ENDMODULE.