2018.04.03 ABAP OLE操作整理
OLE整理:
1.定义，分别对应EXCEL，workbook(工作簿)，sheet(页)，单元格
DATA: EXCEL_OBJ TYPE OLE2_OBJECT, BOOK_OBJ TYPE OLE2_OBJECT, SHEET_OBJ TYPE OLE2_OBJECT, CELL_OBJ TYPE OLE2_OBJECT.
2.方法说明：
CREATE OBJECT name_obj 'app'."创建APP应用类的一个对象obj_name实例 SET PROPERTY OF name_obj 'XXX' = f ."设置对象NAME_obj属性xxx为值f GET PROPERTY OF name_obj 'xxx' = f ."将name_obj的属性xxx的值获取赋给f CALL METHOD OF name_obj 'xxx' = f "由f来接收返回值 EXPORTING #1 = f1. "调用name_obj的方法xxx 传入参数f1…fn FREE OBJECT name_obj . "释放.
3.创建对象
CREATE OBJECT EXCEL_OBJ 'EXCEL.APPLICATION'. SET PROPERTY OF EXCEL_OBJ 'VISIBLE' = 1."使excel可见（1：可见 0：后台运行不可见） SET PROPERTY OF EXCEL_OBJ 'SHEETSINNEWWORKBOOK' = 1."设置 Microsoft Excel 软件打开时，自动插入到新工作簿中的工作表数目（即初始sheet数目，默认名字依次为 Sheet1、Sheet2.....）
CALL METHOD OF EXCEL_OBJ 'WORKBOOKS' = BOOK_OBJ. "由于Workbooks同时为属性，所以可以使用下面语句代替上面语句 *GET PROPERTY OF EXCEL 'Workbooks' = WORKBOOK_OBJ . CALL METHOD OF BOOK_OBJ 'ADD'. CALL METHOD OF BOOK_OBJ 'OPEN'EXPORTING #1 = 'c:\***.xlsx'."开文件
5.增添sheet
CALL METHOD OF EXCEL_OBJ 'sheets' = SHEET_OBJ. CALL METHOD OF SHEET_OBJ 'Add'. SET PROPERTY OF SHEET_OBJ 'Name' = 'NAME'."sheet重命名
6.切换sheet
CALL METHOD OF EXCEL_OBJ 'Worksheets' = SHEET EXPORTING #1 = 'sheet3'. CALL METHOD OF SHEET_OBJ 'Activate'.
7.给单元格赋值
CaLL METHOD OF EXCEL_OBJ 'CELLS' = CELL EXPORTING #1 = row #2 = col. SET PROPERTY OF CELL_OBJ 'value' = xxxx.
8.执行宏
CALL METHOD OF EXCEL_OBJ 'RUN' EXPORTING #1 = 'ZRUNM'.
9.保存和退出
PS:如果遇到程序执行完之后 EXCEL后台未关闭的时候，检查一下 有没有在保存操作前 执行激活
GET PROPERTY OF EXCEL_OBJ 'ACTIVESHEET' = SHEET."激活工作簿 GET PROPERTY OF EXCEL_OBJ 'ACTIVEWORKBOOK' = WORKBOOK."激活工作区 CALL METHOD OF BOOK_OBJ 'SAVEAS'EXPORTING #1 = 'c:\1.xls' #2 = 1. "另存为 "CALL METHOD OF BOOK_OBJ 'SAVE'."保存 CALL METHOD OF BOOK_OBJ 'CLOSE'. "关闭工作区 CALL METHOD OF EXCEL_OBJ 'QUIT'."退出excel
10.释放资源
FREE OBJECT SHEET_OBJ. FREE OBJECT WORKBOOK_OBJ. FREE OBJECT EXCEL_OBJ.
11.例子：
1 REPORT ZZJX_TEST08 MESSAGE-ID ZZJXMSG. 2 3 DATA:C_EXPORT_FILENAME_XLS TYPE STRING VALUE 'ZJX_TEST.XLSX', "导出模板默认文件名 '数据导入模板' 4 C_OBJID_XLS TYPE WWWDATATAB-OBJID VALUE 'ZZJX_TEST08'. "存放模板的对象id 5 6 DATA: LO_OBJDATA LIKE WWWDATATAB, "Excel模板对象 7 LS_DESTINATION LIKE RLGRAP-FILENAME ,"下载保存的目标路径 8 LC_PATH TYPE STRING, "存储路径 9 LC_FULLPATH TYPE STRING, "文件完整路径 10 LI_RC LIKE SY-SUBRC. "返回值 11 12 DATA: EXCEL_OBJ TYPE OLE2_OBJECT, 13 BOOK_OBJ TYPE OLE2_OBJECT, 14 SHEET_OBJ TYPE OLE2_OBJECT, 15 CELL_OBJ TYPE OLE2_OBJECT. 16 17 18 19 LOAD-OF-PROGRAM. 20 21 22 PARAMETERS: P_FILE TYPE RLGRAP-FILENAME OBLIGATORY. 23 24 INITIALIZATION. 25 26 27 AT SELECTION-SCREEN OUTPUT. 28 29 30 AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE. 31 CALL METHOD CL_GUI_FRONTEND_SERVICES=>DIRECTORY_BROWSE 32 EXPORTING 33 WINDOW_TITLE = 'File Directory' 34 INITIAL_FOLDER = 'C:\Users\BRIGHT-SH-002\Desktop' 35 CHANGING 36 SELECTED_FOLDER = LC_PATH 37 * EXCEPTIONS 38 * CNTL_ERROR = 1 39 * ERROR_NO_GUI = 2 40 * NOT_SUPPORTED_BY_GUI = 3 41 * OTHERS = 4 42 . 43 P_FILE = LC_PATH. 44 45 START-OF-SELECTION. 46 *第一步: 下载Excel到本地 47 PERFORM DOWNLOAD_XLS_TEMPLATE. 48 49 *第二步：打开Excel文档 50 PERFORM OPEN_EXCEL. 51 52 *第三步: 写入数据 53 PERFORM WRITE_EXCEL. 54 55 END-OF-SELECTION. 56 *&---------------------------------------------------------------------* 57 *& Form DOWNLOAD_XLS_TEMPLATE 58 *&---------------------------------------------------------------------* 59 * text 60 *----------------------------------------------------------------------* 61 * --> p1 text 62 * <-- p2 text 63 *----------------------------------------------------------------------* 64 FORM DOWNLOAD_XLS_TEMPLATE . 65 * 获取保存路径 66 * LC_PATH = P_FILE. 67 68 * CALL METHOD CL_GUI_FRONTEND_SERVICES=>GET_DESKTOP_DIRECTORY 69 * CHANGING 70 * DESKTOP_DIRECTORY = LC_PATH 71 ** EXCEPTIONS 72 ** CNTL_ERROR = 1 73 ** ERROR_NO_GUI = 2 74 ** NOT_SUPPORTED_BY_GUI = 3 75 ** OTHERS = 4 76 * . 77 * IF LC_PATH IS INITIAL. 78 * LC_PATH = 'C:\Users\BRIGHT-SH-002\Desktop'. 79 * ENDIF. 80 81 CONCATENATE LC_PATH '\' C_EXPORT_FILENAME_XLS INTO LC_FULLPATH. 82 * 检查模板是否存在 83 SELECT SINGLE RELID OBJID FROM WWWDATA INTO CORRESPONDING FIELDS OF LO_OBJDATA 84 WHERE SRTF2 = 0 AND RELID = 'MI' AND OBJID = C_OBJID_XLS. 85 IF SY-SUBRC NE 0 OR LO_OBJDATA-OBJID EQ SPACE. 86 MESSAGE E000(ZZJXMSG) WITH C_EXPORT_FILENAME_XLS. 87 ENDIF. 88 89 * 下载模板 90 LS_DESTINATION = LC_FULLPATH . 91 CALL FUNCTION 'DOWNLOAD_WEB_OBJECT' 92 EXPORTING 93 KEY = LO_OBJDATA 94 DESTINATION = LS_DESTINATION 95 IMPORTING 96 RC = LI_RC 97 * CHANGING 98 * TEMP = 99 . 100 IF LI_RC NE 0. 101 MESSAGE E001(ZZJXMSG) WITH C_EXPORT_FILENAME_XLS. 102 ENDIF. 103 104 ENDFORM. " DOWNLOAD_XLS_TEMPLATE 105 *&---------------------------------------------------------------------* 106 *& Form OPEN_EXCEL 107 *&---------------------------------------------------------------------* 108 * text 109 *----------------------------------------------------------------------* 110 * --> p1 text 111 * <-- p2 text 112 *----------------------------------------------------------------------* 113 FORM OPEN_EXCEL . 114 CREATE OBJECT EXCEL_OBJ 'EXCEL.APPLICATION'. 115 IF SY-SUBRC NE 0. 116 MESSAGE 'EXCEL创建错误' TYPE 'S' DISPLAY LIKE 'E'. 117 STOP. 118 ENDIF. 119 120 CALL METHOD OF 121 EXCEL_OBJ 122 'WORKBOOKS' = BOOK_OBJ. 123 SET PROPERTY OF EXCEL_OBJ 'VISIBLE' = 0. 124 * SET PROPERTY OF EXCEL_OBJ 'SheetInNewWorkbook' = 1. 125 126 * 打开excel文件，（新建使用：call method of book_obj 'Add' = sheet_obj） 127 CALL METHOD OF 128 BOOK_OBJ 129 'Open' = SHEET_OBJ 130 EXPORTING 131 #1 = LS_DESTINATION. 132 133 CALL METHOD OF 134 EXCEL_OBJ 135 'Sheets' = SHEET_OBJ"切换sheets 136 EXPORTING 137 #1 = 1. 138 139 CALL METHOD OF 140 SHEET_OBJ 141 'Select'. 142 143 * FREE OBJECT SHEET_OBJ."ok 144 CALL METHOD OF SHEET_OBJ 'ACTIVATE'."激活 145 SET PROPERTY OF SHEET_OBJ 'NAME' = '库存物料表'. 146 ENDFORM. " OPEN_EXCEL 147 *&---------------------------------------------------------------------* 148 *& Form WRITE_EXCEL 149 *&---------------------------------------------------------------------* 150 * text 151 *----------------------------------------------------------------------* 152 * --> p1 text 153 * <-- p2 text 154 *----------------------------------------------------------------------* 155 FORM WRITE_EXCEL . 156 DATA:BEGIN OF IT_DATA OCCURS 10, 157 MANDT TYPE MANDT, 158 MATNR TYPE MATNR, 159 MAKTX TYPE MAKT-MAKTX, 160 WERKS TYPE WERKS_D, 161 NAME1 TYPE NAME1, 162 LGORT TYPE LGORT_D, 163 LGOBE TYPE T001L-LGOBE, 164 LABST TYPE LABST, 165 MEINS TYPE MEINS, 166 END OF IT_DATA. 167 DATA LV_ROWS TYPE I. 168 RANGES: LR_MATNR FOR MARD-MATNR. 169 CLEAR LR_MATNR. 170 LR_MATNR-SIGN = 'I'. 171 LR_MATNR-OPTION = 'BT'. 172 LR_MATNR-LOW = '000000001000000030'. 173 LR_MATNR-HIGH = '000000001000000197'. 174 APPEND LR_MATNR. 175 176 SELECT A~MATNR B~MAKTX A~WERKS C~NAME1 A~LGORT D~LGOBE A~LABST E~MEINS 177 INTO CORRESPONDING FIELDS OF TABLE IT_DATA 178 FROM MARD AS A 179 INNER JOIN MAKT AS B ON B~MATNR = A~MATNR AND B~SPRAS = 'JA' 180 INNER JOIN T001W AS C ON C~WERKS = A~WERKS 181 INNER JOIN T001L AS D ON D~LGORT = A~LGORT 182 INNER JOIN MARA AS E ON E~MATNR = A~MATNR 183 UP TO 5 ROWS 184 WHERE A~MATNR IN LR_MATNR. 185 186 187 FIELD-SYMBOLS <FS1> LIKE LINE OF IT_DATA. 188 LOOP AT IT_DATA ASSIGNING <FS1> . 189 <FS1>-MANDT = SY-MANDT. 190 ENDLOOP. 191 192 * PERFORM EXCEL_ROW_INSERT USING SHEET_OBJ 3 1. 193 * 输出Excel表头,自定义格式的列等 194 PERFORM FILL_CELL USING 3 1 1 'MANDT'. 195 PERFORM FILL_CELL USING 3 2 1 'MATNR'. 196 PERFORM FILL_CELL USING 3 3 1 'MAKTX'. 197 PERFORM FILL_CELL USING 3 4 1 'WERKS'. 198 PERFORM FILL_CELL USING 3 5 1 'NAME1'. 199 PERFORM FILL_CELL USING 3 6 1 'LGORT'. 200 PERFORM FILL_CELL USING 3 7 1 'LGOBE'. 201 PERFORM FILL_CELL USING 3 8 1 'LABST'. 202 PERFORM FILL_CELL USING 3 9 1 'MEINS'. 203 CLEAR LV_ROWS. 204 LOOP AT IT_DATA. 205 LV_ROWS = SY-TABIX + 3. 206 * PERFORM EXCEL_ROW_INSERT USING SHEET_OBJ LV_ROWS 1. 207 PERFORM FILL_CELL USING: LV_ROWS 1 0 IT_DATA-MANDT, 208 LV_ROWS 2 1 IT_DATA-MATNR, 209 LV_ROWS 3 0 IT_DATA-MAKTX, 210 LV_ROWS 4 0 IT_DATA-WERKS, 211 LV_ROWS 5 0 IT_DATA-NAME1, 212 LV_ROWS 6 0 IT_DATA-LGORT, 213 LV_ROWS 7 0 IT_DATA-LGOBE, 214 LV_ROWS 8 0 IT_DATA-LABST, 215 LV_ROWS 9 0 IT_DATA-MEINS. 216 ENDLOOP. 217 218 FREE OBJECT CELL_OBJ. 219 GET PROPERTY OF EXCEL_OBJ 'ActiveSheet' = SHEET_OBJ. "获取活动SHEET 220 FREE OBJECT SHEET_OBJ. 221 GET PROPERTY OF EXCEL_OBJ 'ACTIVEWORKBOOK' = BOOK_OBJ."激活工作区 222 "save 223 * CALL METHOD OF 224 * BOOK_OBJ 225 * 'SAVEAS' 226 * EXPORTING 227 * #1 = LC_FULLPATH 228 * #2 = 1. 229 CALL METHOD OF 230 BOOK_OBJ 231 'SAVE'. 232 * SET PROPERTY OF excel 'Visible' = 1. "是否显示EXCEL 此处显示不退出 233 CALL METHOD OF 234 BOOK_OBJ 235 'CLOSE'. 236 CALL METHOD OF 237 EXCEL_OBJ 238 'QUIT'. 239 240 FREE OBJECT BOOK_OBJ. 241 FREE OBJECT EXCEL_OBJ. 242 ENDFORM. " WRITE_EXCEL 243 *&---------------------------------------------------------------------* 244 *& Form filL_cell 245 *&---------------------------------------------------------------------* 246 * text 247 *----------------------------------------------------------------------* 248 * -->row 行 249 * -->col 列 250 * -->bold 加粗 251 * -->val 填充值 252 *----------------------------------------------------------------------* 253 FORM FILL_CELL USING VALUE(ROW) 254 VALUE(COL) 255 VALUE(BOLD) 256 VALUE(VAL). 257 CALL METHOD OF 258 EXCEL_OBJ 259 'CELLS' = CELL_OBJ 260 EXPORTING 261 #1 = ROW 262 #2 = COL. 263 * SET PROPERTY OF CELL_OBJ 'BOLD' = BOLD. 264 SET PROPERTY OF CELL_OBJ 'VALUE' = VAL. 265 * FREE OBJECT CELL_OBJ. 266 ENDFORM. " FIL_CELL 267 *&---------------------------------------------------------------------* 268 *& Form EXCEL_ROW_INSERT 269 *&---------------------------------------------------------------------* 270 * text 271 *----------------------------------------------------------------------* 272 * -->P_SHEET text 273 * -->P_TAB text 274 * -->P_1 text 275 *----------------------------------------------------------------------* 276 FORM EXCEL_ROW_INSERT USING LCOBJ_SHEET 277 LC_ROW 278 LC_COUNT. 279 DATA LC_RANGE TYPE OLE2_OBJECT. 280 DATA H_BORDERS TYPE OLE2_OBJECT. 281 DO LC_COUNT TIMES. 282 CALL METHOD OF 283 LCOBJ_SHEET 284 'Rows' = LC_RANGE 285 EXPORTING 286 #1 = 1. 287 CALL METHOD OF LC_RANGE 'Copy'. "COPY第6行插入一个新行 288 CALL METHOD OF 289 LCOBJ_SHEET 290 'Rows' = LC_RANGE 291 EXPORTING 292 #1 = LC_ROW. 293 CALL METHOD OF 294 LC_RANGE 295 'Insert'. 296 CALL METHOD OF LC_RANGE 'ClearContents'. "是否需要清空Cell 297 ENDDO. 298 ENDFORM. " EXCEL_ROW_INSERT
