【学习】ABAP OLE 对EXCEL的处理
原文：http://blog.sina.com.cn/s/blog_7229b9c00100opx2.html
----------------------------------------------------------------------------
1
2 REPORT ZVR013. 3 INCLUDE OLE2INCL. 4 DATA: GS_EXCEL TYPE OLE2_OBJECT, 5 GS_WBOOKLIST TYPE OLE2_OBJECT, 6 GS_APPLICATION TYPE OLE2_OBJECT, 7 GS_WBOOK TYPE OLE2_OBJECT, 8 GS_ACTIVESHEET TYPE OLE2_OBJECT, 9 GS_SHEETS TYPE OLE2_OBJECT, 10 GS_NEWSHEET TYPE OLE2_OBJECT, 11 GS_CELL1 TYPE OLE2_OBJECT, 12 GS_CELL2 TYPE OLE2_OBJECT, 13 GS_CELLS TYPE OLE2_OBJECT, 14 GS_FONT TYPE OLE2_OBJECT, 15 GS_PAGE TYPE OLE2_OBJECT, 16 GS_RANGE TYPE OLE2_OBJECT, 17 GS_BORDERS TYPE OLE2_OBJECT. 18 DATA: GV_SHEET_NAME(20) TYPE C. 19 DATA: GV_OUTER_INDEX LIKE SY-INDEX. 20 DATA: GV_INTEX(2) TYPE C. 21 DATA: GV_LINE_CNTR TYPE I. "LINE COUNTER 22 DATA: GV_LINNO TYPE I. "LINE NUMBER 23 DATA: GV_COLNO TYPE I. "COLUMN NUMBER 24 DATA: GV_VALUE TYPE I. "DATA 25 DATA: X1 TYPE P, X2 TYPE P, Y1 TYPE P, Y2 TYPE P. 26 DATA: VALUE(100),VALUE1(100). 27 DATA: NAME(20),SIZE(2). 28 DATA: BOLD TYPE P, UNDERLINE TYPE P, ALIGNMENT TYPE P. 29 DATA: ROW TYPE P VALUE 11, LINE TYPE P VALUE 1. "循环次数 30 DATA: ROW1 TYPE P,LINE1 TYPE P. 31 DATA: QUANTITY(20),AMOUNT(20). 32 DATA: ROWHEIGHT(10),COLUMNWIDTH(10). 33 DATA: PAGE TYPE P, PG_INDEX TYPE P. 34 *----------------------------------------------------------- 35 *----------------------------------------------------------- 36 CREATE OBJECT GS_EXCEL 'EXCEL.APPLICATION'. 37 SET PROPERTY OF GS_EXCEL 'VISIBLE' = 1. 38 GET PROPERTY OF GS_EXCEL 'WORKBOOKS' = GS_WBOOKLIST. 39 GET PROPERTY OF GS_WBOOKLIST 'APPLICATION' = GS_APPLICATION. 40 SET PROPERTY OF GS_APPLICATION 'SHEETSINNEWWORKBOOK' = 1. 41 CALL METHOD OF GS_WBOOKLIST 'ADD' = GS_WBOOK. 42 GET PROPERTY OF GS_APPLICATION 'ACTIVESHEET' = GS_ACTIVESHEET. 43 SET PROPERTY OF GS_ACTIVESHEET 'NAME' = GV_SHEET_NAME. 44 GET PROPERTY OF GS_ACTIVESHEET 'PAGESETUP' = GS_PAGE. 45 SET PROPERTY OF GS_PAGE 'TOPMARGIN' = '25'. 46 SET PROPERTY OF GS_PAGE 'BOTTOMMARGIN' = '25'. 47 SET PROPERTY OF GS_PAGE 'LEFTMARGIN' = '25'. 48 SET PROPERTY OF GS_PAGE 'RIGHTMARGIN' = '25'. 49 50 *--FORMATTING THE TITLE 51 FORM FORMAT USING GS_CELL1 VALUE NAME SIZE BOLD ALIGNMENT UNDERLINE. 52 CONDENSE VALUE. 53 CONDENSE NAME. 54 CONDENSE SIZE. 55 GET PROPERTY OF GS_CELL1 'FONT' = GS_FONT . 56 SET PROPERTY OF GS_FONT 'NAME' = NAME. 57 SET PROPERTY OF GS_FONT 'SIZE' = SIZE. 58 SET PROPERTY OF GS_FONT 'BOLD' = BOLD. "NOT BOLD 59 SET PROPERTY OF GS_FONT 'ITALIC' = '1'. "NOT ITALIC 60 SET PROPERTY OF GS_FONT 'UNDERLINE' = UNDERLINE. "NOT UNDERLINED 61 SET PROPERTY OF GS_CELL1 'WRAPTEXT' = 1. 62 SET PROPERTY OF GS_CELL1 'HORIZONTALALIGNMENT' = ALIGNMENT. 63 " && 水平方向 2左对齐，3居中，4右对齐 64 SET PROPERTY OF GS_CELL1 'VERTICALALIGNMENT' = 2. 65 "&& 垂直方向 1靠上 ，2居中，3靠下 66 SET PROPERTY OF GS_CELL1 'NUMBERFORMATLOCAL' = '@'. 67 "&& 设置数据格式 68 SET PROPERTY OF GS_CELL1 'VALUE' = VALUE. 69 ENDFORM. "FORMAT 70 *------------------------------ 71 *该函数用来合并EXCEL单元格 72 *------------------------------ 73 FORM MERGED USING X1 Y1 X2 Y2. 74 *--SELECTING CELL AREA TO BE MERGED. 75 CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL1 76 EXPORTING 77 #1 = X1 78 #2 = Y1. 79 CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL2 80 EXPORTING 81 #1 = X2 82 #2 = Y2. 83 CALL METHOD OF GS_EXCEL 'RANGE' = GS_CELLS 84 EXPORTING 85 #1 = GS_CELL1 86 #2 = GS_CELL2. 87 CALL METHOD OF GS_CELLS 'SELECT'. 88 *--MERGING 89 CALL METHOD OF GS_CELLS 'MERGE' . 90 ENDFORM. "MERGED 91 *--------------------------------- 92 *生成四周有边框的单元格 93 *--------------------------------- 94 FORM MERGED_BORDERS USING X1 Y1 X2 Y2. 95 *--SELECTING CELL AREA TO BE MERGED. 96 CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL1 97 EXPORTING 98 #1 = X1 99 #2 = Y1. 100 CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL2 101 EXPORTING 102 #1 = X2 103 #2 = Y2. 104 CALL METHOD OF GS_EXCEL 'RANGE' = GS_CELLS 105 EXPORTING 106 #1 = GS_CELL1 107 #2 = GS_CELL2. 108 CALL METHOD OF GS_CELLS 'SELECT'. 109 *--MERGING 110 CALL METHOD OF GS_CELLS 'MERGE' . 111 GET PROPERTY OF GS_CELLS 'BORDERS' = GS_RANGE . 112 SET PROPERTY OF GS_RANGE 'WEIGHT' = '2'. 113 SET PROPERTY OF GS_RANGE 'LINESTYLE' = '1'. 114 FREE OBJECT GS_RANGE. 115 ENDFORM. "MERGED 116 *-------------------------------------- 117 *--------------------------------- 118 *生成左右有边框的单元格 119 *--------------------------------- 120 FORM SIDES_BORDERS USING X1 Y1 X2 Y2. 121 *--SELECTING CELL AREA TO BE MERGED. 122 CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL1 123 EXPORTING 124 #1 = X1 125 #2 = Y1. 126 CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL2 127 EXPORTING 128 #1 = X2 129 #2 = Y2. 130 CALL METHOD OF GS_EXCEL 'RANGE' = GS_CELLS 131 EXPORTING 132 #1 = GS_CELL1 133 #2 = GS_CELL2. 134 CALL METHOD OF GS_CELLS 'SELECT'. 135 *--MERGING 136 CALL METHOD OF GS_CELLS 'MERGE' . 137 CALL METHOD OF GS_CELLS 'BORDERS' = GS_BORDERS 138 EXPORTING 139 #1 = '1'. 140 SET PROPERTY OF GS_BORDERS 'LINESTYLE' = '1'. 141 SET PROPERTY OF GS_BORDERS 'WEIGHT' = 2. 142 FREE OBJECT GS_BORDERS. 143 144 CALL METHOD OF GS_CELLS 'BORDERS' = GS_BORDERS 145 EXPORTING 146 #1 = '2'. 147 SET PROPERTY OF GS_BORDERS 'LINESTYLE' = '7'. 148 *& (其中BORDERS参数：1－左、2－右、3－顶、4－底、5－斜、6－斜/； 149 150 *&LINESTYLE值：1与7－细实、2－细虚、4－点虚、9－双细实线) 151 152 SET PROPERTY OF GS_BORDERS 'WEIGHT' = 2. 153 FREE OBJECT GS_BORDERS. 154 ENDFORM. "MERGED 155 *--------------------------------- 156 FORM LEFT_BORDERS USING X1 Y1 X2 Y2. 157 *--SELECTING CELL AREA TO BE MERGED. 158 CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL1 159 EXPORTING 160 #1 = X1 161 #2 = Y1. 162 CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL2 163 EXPORTING 164 #1 = X2 165 #2 = Y2. 166 CALL METHOD OF GS_EXCEL 'RANGE' = GS_CELLS 167 EXPORTING 168 #1 = GS_CELL1 169 #2 = GS_CELL2. 170 CALL METHOD OF GS_CELLS 'SELECT'. 171 *--MERGING 172 CALL METHOD OF GS_CELLS 'MERGE' . 173 CALL METHOD OF GS_CELLS 'BORDERS' = GS_BORDERS 174 EXPORTING 175 #1 = '1'. 176 SET PROPERTY OF GS_BORDERS 'LINESTYLE' = '1'. 177 SET PROPERTY OF GS_BORDERS 'WEIGHT' = 2. 178 FREE OBJECT GS_BORDERS. 179 ENDFORM. "MERGED 180 FORM RIGHT_BORDERS USING X1 Y1 X2 Y2. 181 *--SELECTING CELL AREA TO BE MERGED. 182 CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL1 183 EXPORTING 184 #1 = X1 185 #2 = Y1. 186 CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL2 187 EXPORTING 188 #1 = X2 189 #2 = Y2. 190 CALL METHOD OF GS_EXCEL 'RANGE' = GS_CELLS 191 EXPORTING 192 #1 = GS_CELL1 193 #2 = GS_CELL2. 194 CALL METHOD OF GS_CELLS 'SELECT'. 195 *--MERGING 196 CALL METHOD OF GS_CELLS 'MERGE' . 197 CALL METHOD OF GS_CELLS 'BORDERS' = GS_BORDERS 198 EXPORTING 199 #1 = '2'. 200 SET PROPERTY OF GS_BORDERS 'LINESTYLE' = '1'. 201 SET PROPERTY OF GS_BORDERS 'WEIGHT' = 2. 202 FREE OBJECT GS_BORDERS. 203 ENDFORM. "MERGED 204 205 *--------------------------------- 206 *生成BOTTOM有边框的单元格 207 *--------------------------------- 208 FORM BOTTOM_BORDERS USING X1 Y1 X2 Y2. 209 *--SELECTING CELL AREA TO BE MERGED. 210 CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL1 211 EXPORTING 212 #1 = X1 213 #2 = Y1. 214 CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL2 215 EXPORTING 216 #1 = X2 217 #2 = Y2. 218 CALL METHOD OF GS_EXCEL 'RANGE' = GS_CELLS 219 EXPORTING 220 #1 = GS_CELL1 221 #2 = GS_CELL2. 222 CALL METHOD OF GS_CELLS 'SELECT'. 223 *--MERGING 224 CALL METHOD OF GS_CELLS 'MERGE' . 225 CALL METHOD OF GS_CELLS 'BORDERS' = GS_BORDERS 226 EXPORTING 227 #1 = '4'. 228 SET PROPERTY OF GS_BORDERS 'LINESTYLE' = '7'. 229 SET PROPERTY OF GS_BORDERS 'WEIGHT' = 2. 230 FREE OBJECT GS_BORDERS. 231 ENDFORM. "MERGED 232 233 FORM BOTTOM_LINES USING X1 Y1 X2 Y2. 234 *--SELECTING CELL AREA TO BE MERGED. 235 CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL1 236 EXPORTING 237 #1 = X1 238 #2 = Y1. 239 CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL2 240 EXPORTING 241 #1 = X2 242 #2 = Y2. 243 CALL METHOD OF GS_EXCEL 'RANGE' = GS_CELLS 244 EXPORTING 245 #1 = GS_CELL1 246 #2 = GS_CELL2. 247 CALL METHOD OF GS_CELLS 'SELECT'. 248 *--MERGING 249 CALL METHOD OF GS_CELLS 'MERGE' . 250 CALL METHOD OF GS_CELLS 'BORDERS' = GS_BORDERS 251 EXPORTING 252 #1 = '4'. 253 SET PROPERTY OF GS_BORDERS 'LINESTYLE' = '9'. 254 SET PROPERTY OF GS_BORDERS 'WEIGHT' = 5. 255 FREE OBJECT GS_BORDERS. 256 ENDFORM. "MERGED 257 258 *------------------------------------------------- 259 *设置行高和列宽。 260 FORM ROW_COLUMN USING X1 Y1 X2 Y2 ROWHEIGHT COLUMNWIDTH. 261 *--SELECTING CELL AREA TO BE MERGED. 262 CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL1 263 EXPORTING 264 #1 = X1 265 #2 = Y1. 266 CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL2 267 EXPORTING 268 #1 = X2 269 #2 = Y2. 270 CALL METHOD OF GS_EXCEL 'RANGE' = GS_CELLS 271 EXPORTING 272 #1 = GS_CELL1 273 #2 = GS_CELL2. 274 CALL METHOD OF GS_CELLS 'SELECT'. 275 *--MERGING 276 CALL METHOD OF GS_CELLS 'MERGE' . 277 GET PROPERTY OF GS_CELLS 'ROWS' = GS_BORDERS. 278 SET PROPERTY OF GS_BORDERS 'ROWHEIGHT' = ROWHEIGHT. 279 GET PROPERTY OF GS_CELLS 'COLUMNS' = GS_RANGE . 280 SET PROPERTY OF GS_RANGE 'COLUMNWIDTH' = COLUMNWIDTH. 281 FREE OBJECT GS_BORDERS. 282 FREE OBJECT GS_RANGE. 283 ENDFORM. "MERGED 284 FORM ROWHEIGHT USING X1 Y1 X2 Y2 ROWHEIGHT. 285 *--SELECTING CELL AREA TO BE MERGED. 286 CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL1 287 EXPORTING 288 #1 = X1 289 #2 = Y1. 290 CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL2 291 EXPORTING 292 #1 = X2 293 #2 = Y2. 294 CALL METHOD OF GS_EXCEL 'RANGE' = GS_CELLS 295 EXPORTING 296 #1 = GS_CELL1 297 #2 = GS_CELL2. 298 CALL METHOD OF GS_CELLS 'SELECT'. 299 *--MERGING 300 CALL METHOD OF GS_CELLS 'MERGE' . 301 GET PROPERTY OF GS_CELLS 'ROWS' = GS_BORDERS. 302 SET PROPERTY OF GS_BORDERS 'ROWHEIGHT' = ROWHEIGHT. 303 * GET PROPERTY OF GS_CELLS 'COLUMNS' = GS_RANGE . 304 * SET PROPERTY OF GS_RANGE 'COLUMNWIDTH' = COLUMNWIDTH. 305 FREE OBJECT GS_BORDERS. 306 FREE OBJECT GS_RANGE. 307 ENDFORM. "MERGED 308 309 310 FORM COLUMNWIDTH USING X1 Y1 X2 Y2 COLUMNWIDTH. 311 *--SELECTING CELL AREA TO BE MERGED. 312 CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL1 313 EXPORTING 314 #1 = X1 315 #2 = Y1. 316 CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL2 317 EXPORTING 318 #1 = X2 319 #2 = Y2. 320 CALL METHOD OF GS_EXCEL 'RANGE' = GS_CELLS 321 EXPORTING 322 #1 = GS_CELL1 323 #2 = GS_CELL2. 324 CALL METHOD OF GS_CELLS 'SELECT'. 325 *--MERGING 326 CALL METHOD OF GS_CELLS 'MERGE' . 327 * GET PROPERTY OF GS_CELLS 'ROWS' = GS_BORDERS. 328 * SET PROPERTY OF GS_BORDERS 'ROWHEIGHT' = ROWHEIGHT. 329 GET PROPERTY OF GS_CELLS 'COLUMNS' = GS_RANGE . 330 SET PROPERTY OF GS_RANGE 'COLUMNWIDTH' = COLUMNWIDTH. 331 FREE OBJECT GS_BORDERS. 332 FREE OBJECT GS_RANGE. 333 ENDFORM. "MERGED 334 ******************************************************** 335 FORM PAGES USING L_FILE X Y. 336 X1 = 1. 337 Y1 = 1. 338 X2 = X. 339 Y2 = Y. 340 *DO PAGE TIMES. 341 *_-CREATE EXCEL 342 CONCATENATE 'SHEET' '1' INTO GV_SHEET_NAME. 343 CREATE OBJECT GS_EXCEL 'EXCEL.APPLICATION'. 344 SET PROPERTY OF GS_EXCEL 'VISIBLE' = 1. 345 GET PROPERTY OF GS_EXCEL 'WORKBOOKS' = GS_WBOOKLIST. 346 *----COPY 347 CALL METHOD OF GS_WBOOKLIST 'OPEN' = GS_RANGE 348 EXPORTING 349 #1 = L_FILE. 350 CALL METHOD OF GS_EXCEL 'WORKSHEETS' = GS_BORDERS 351 EXPORTING #1 = 1. 352 CALL METHOD OF GS_BORDERS 'ACTIVATE'. 353 CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL1 354 EXPORTING 355 #1 = 1 356 #2 = 1. 357 CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL2 358 EXPORTING 359 #1 = X 360 #2 = Y. 361 CALL METHOD OF GS_EXCEL 'RANGE' = GS_CELLS 362 EXPORTING 363 #1 = GS_CELL1 364 #2 = GS_CELL2. 365 CALL METHOD OF GS_CELLS 'COPY'. 366 *----PASTESPECIAL 367 *-CREATE 368 CALL METHOD OF GS_WBOOKLIST 'ADD' = GS_WBOOK. 369 GET PROPERTY OF GS_APPLICATION 'ACTIVESHEET' = GS_ACTIVESHEET. 370 SET PROPERTY OF GS_ACTIVESHEET 'NAME' = GV_SHEET_NAME. 371 CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL1 372 EXPORTING 373 #1 = 1 374 #2 = 1. 375 CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL2 376 EXPORTING 377 #1 = X 378 #2 = Y. 379 CALL METHOD OF GS_EXCEL 'RANGE' = GS_CELLS 380 EXPORTING 381 #1 = GS_CELL1 382 #2 = GS_CELL2. 383 CALL METHOD OF GS_CELLS 'PASTESPECIAL'. 384 CALL METHOD OF GS_CELLS 'COPY'. 385 CALL METHOD OF GS_RANGE 'ACTIVATE'. 386 CALL METHOD OF GS_RANGE 'CLOSE'. 387 CALL METHOD OF GS_ACTIVESHEET 'ACTIVATE'. 388 ENDFORM. 389 *------------------------------------------------- 390 FORM PAGES1 USING X Y. 391 DO PAGE TIMES. 392 X1 = X1 + X. 393 Y1 = 1. 394 X2 = X2 + X. 395 Y2 = Y. 396 CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL1 397 EXPORTING 398 #1 = 1 399 #2 = 1. 400 CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL2 401 EXPORTING 402 #1 = X 403 #2 = Y. 404 CALL METHOD OF GS_EXCEL 'RANGE' = GS_CELLS 405 EXPORTING 406 #1 = GS_CELL1 407 #2 = GS_CELL2. 408 CALL METHOD OF GS_CELLS 'COPY'. 409 CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL1 410 EXPORTING 411 #1 = X1 412 #2 = Y1. 413 CALL METHOD OF GS_EXCEL 'CELLS' = GS_CELL2 414 EXPORTING 415 #1 = X2 416 #2 = Y2. 417 CALL METHOD OF GS_EXCEL 'RANGE' = GS_CELLS 418 EXPORTING 419 #1 = GS_CELL1 420 #2 = GS_CELL2. 421 CALL METHOD OF GS_CELLS 'PASTESPECIAL'. 422 ENDDO. 423 ENDFORM.
if p_file is initial.
message '请输入导入文件的路径' type 'I'.
leave to transaction sy-tcode.
endif.
data: begin of i_excel occurs 0.
* ROW(4) TYPE N,
* COL(4) TYPE N,
* VALUE(150),
include structure alsmex_tabline.
data: end of i_excel.
data: l_answer(1) type c.
data: l_column type i.
field-symbols: <fs>.
data: l_pathname like rlgrap-filename.
data:
c_begin_row type i value 4, "Beginning row of excel file
c_begin_col type i value 2, "Beginning column of excel file
c_end_row type i value 10000, "Ending row of excel file
c_end_col type i value 11. "Ending column of excel file
move p_file to l_pathname.
call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
exporting
filename = l_pathname
i_begin_col = c_begin_col
i_begin_row = c_begin_row
i_end_col = c_end_col
i_end_row = c_end_row
tables
intern = i_excel
exceptions
inconsistent_parameters = 1
upload_ole = 2
others = 3.
