2019.06.05 ABAP EXCEL 操作类代码 OLE方式（模板下载，excel上传，内表下载）
一般使用标准的excel导入方法9999行，修改了标准的excel导入FM 整合出类：excel的 模板下载，excel上传，ALV内表下载功能。
在项目一开始可以SE24创建一个类来供整体开发使用，或者直接复制代码到程序里来使用。
ZCHAR30000 这个字段给了excel每一行30000字符的限定。
1 *&---------------------------------------------------------------------* 2 *& 包含 ZBP_CREATE_CHANGE_CLS 3 *&---------------------------------------------------------------------* 4 CLASS cl_bc_excel DEFINITION. 5 6 PUBLIC SECTION. 7 TYPES:BEGIN OF ty_excel_tabline, 8 row TYPE numc06, 9 col TYPE numc06, 10 value TYPE text4096, 11 END OF ty_excel_tabline, 12 ty_t_excel_tabline TYPE TABLE OF ty_excel_tabline. 13 DATA lt_excel_tabline TYPE ty_t_excel_tabline. 14 TYPES: zchar30000 TYPE c LENGTH 30000."每一行数据为30000字符 上限 15 16 CLASS-METHODS f4_file_excel "获取 excel 路径 17 IMPORTING 18 VALUE(iv_window_title) TYPE string OPTIONAL 19 VALUE(iv_file_filter) TYPE string OPTIONAL 20 VALUE(iv_multiselection) TYPE char01 OPTIONAL 21 EXPORTING 22 !ev_rc TYPE i 23 !et_file_table TYPE filetable 24 CHANGING 25 VALUE(cv_file_name) TYPE localfile . 26 CLASS-METHODS download_web_object "下载对象 SMW0 27 IMPORTING 28 VALUE(iv_relid) TYPE indx_relid DEFAULT 'MI' 29 VALUE(iv_objid) TYPE w3objid 30 VALUE(iv_window_title) TYPE string OPTIONAL 31 VALUE(iv_default_extension) TYPE string OPTIONAL 32 VALUE(iv_file_filter) TYPE string OPTIONAL 33 VALUE(iv_default_file_name) TYPE string OPTIONAL 34 EXPORTING 35 !ev_rc TYPE sy-subrc 36 !ev_filepath TYPE string . 37 CLASS-METHODS download_template "下载模板 38 IMPORTING 39 VALUE(name_of_template) TYPE char100 40 VALUE(iv_relid) TYPE indx_relid DEFAULT 'MI' 41 VALUE(iv_objid) TYPE w3objid 42 EXPORTING 43 VALUE(rv_filepath) TYPE string 44 EXCEPTIONS 45 download_error . 46 47 CLASS-METHODS update_excel_ole "上载excel 48 IMPORTING 49 REFERENCE(pi_filename) TYPE string 50 REFERENCE(pi_sheetname) TYPE string DEFAULT 'Sheet1' 51 REFERENCE(pi_startline) TYPE i DEFAULT '1' 52 REFERENCE(pi_startcolumn) TYPE i DEFAULT '1' 53 REFERENCE(pi_every_copy_endline) TYPE i DEFAULT '3000' 54 REFERENCE(pi_endcolumn) TYPE i DEFAULT '256' 55 REFERENCE(pi_skpcl_tbl) TYPE i DEFAULT '0' 56 CHANGING 57 VALUE(pt_tab) TYPE ANY TABLE 58 EXCEPTIONS 59 open_file_err. 60 61 CLASS-METHODS excel_2_inner_table "将excel 导入内表 62 IMPORTING 63 VALUE(pi_filename) TYPE string 64 VALUE(pi_sheetname) TYPE string DEFAULT 'Sheet1' 65 VALUE(pi_every_copy_endline) TYPE i DEFAULT '3000' 66 VALUE(pi_endcolumn) TYPE i DEFAULT '256' 67 CHANGING 68 VALUE(pt_tab) TYPE ANY TABLE 69 EXCEPTIONS 70 file_open_error 71 protected section. 72 73 CLASS-METHODS download_to_excel "下载内表到excel 74 IMPORTING 75 VALUE(sheet_name) TYPE char20 DEFAULT 'DATA' 76 VALUE(excel_id) TYPE w3objid 77 VALUE(path) TYPE localfile 78 VALUE(excel_fname) TYPE char30 79 VALUE(show_excel) TYPE char1 OPTIONAL 80 EXPORTING 81 VALUE(e_message) TYPE char100 82 CHANGING 83 VALUE(i_data) TYPE ty_t_excel_tabline 84 EXCEPTIONS 85 excel_error. 86 87 PRIVATE SECTION. 88 89 ENDCLASS. 90 91 CLASS cl_bc_excel IMPLEMENTATION. 92 93 94 * <SIGNATURE>---------------------------------------------------------------------------------------+ 95 * | Static Public Method ZCL_BC_EXCEL=>DOWNLOAD_TEMPLATE 96 * +-------------------------------------------------------------------------------------------------+ 97 * | [--->] NAME_OF_TEMPLATE TYPE CHAR100 98 * | [--->] IV_RELID TYPE INDX_RELID (default ='MI') 99 * | [--->] IV_OBJID TYPE W3OBJID 100 * | [<---] RV_FILEPATH TYPE STRING 101 * | [EXC!] DOWNLOAD_ERROR 102 * +--------------------------------------------------------------------------------------</SIGNATURE> 103 METHOD download_template. 104 * DATA: lv_objid TYPE wwwdatatab-objid VALUE 'ZMM004'. 105 DATA: lv_default_file_name TYPE string. 106 DATA: lv_rc TYPE sy-subrc. 107 * DATA: lv_filepath TYPE string. 108 109 CONCATENATE name_of_template '-' sy-datum+0(8) sy-timlo+0(4) 110 INTO lv_default_file_name. 111 112 CALL METHOD cl_bc_excel=>download_web_object 113 EXPORTING 114 iv_relid = iv_relid 115 iv_objid = iv_objid 116 iv_default_file_name = lv_default_file_name 117 IMPORTING 118 ev_rc = lv_rc 119 ev_filepath = rv_filepath. 120 IF lv_rc <> 0. 121 RAISE download_error. 122 ENDIF. 123 ENDMETHOD. 124 125 METHOD download_web_object. 126 DATA: ls_key TYPE wwwdatatab, 127 ls_wwwdata TYPE wwwdata, 128 lv_filename TYPE string, 129 lv_path TYPE string, 130 lv_fullpath TYPE string, 131 lv_extension TYPE c LENGTH 100, 132 lv_user_action TYPE i. 133 DATA: lv_offset TYPE i, 134 lv_length TYPE i. 135 CLEAR ev_filepath. 136 IF iv_relid IS INITIAL. 137 SELECT SINGLE relid objid srtf2 checkout checknew chname tdate ttime text 138 INTO CORRESPONDING FIELDS OF ls_wwwdata 139 FROM wwwdata 140 WHERE objid = iv_objid. 141 iv_relid = ls_wwwdata-relid. 142 ELSE. 143 SELECT SINGLE relid objid srtf2 checkout checknew chname tdate ttime text 144 INTO CORRESPONDING FIELDS OF ls_wwwdata 145 FROM wwwdata 146 WHERE relid = iv_relid AND objid = iv_objid. 147 ENDIF. 148 IF iv_window_title IS INITIAL. 149 iv_window_title = '请选择文件存储路径...'(001). 150 ENDIF. 151 * IF iv_file_filter IS INITIAL. 152 * iv_file_filter = '(*.xls)|*.xls'. 153 * ENDIF. 154 * IF iv_default_extension IS INITIAL. 155 * iv_default_extension = '.xls'. 156 * ENDIF. 157 IF iv_default_extension IS INITIAL. 158 CALL FUNCTION 'WWWPARAMS_READ' 159 EXPORTING 160 relid = ls_wwwdata-relid 161 objid = ls_wwwdata-objid 162 name = 'fileextension' "c_extension of include LSHTMTOP 163 IMPORTING 164 value = lv_extension 165 EXCEPTIONS 166 entry_not_exists = 1 167 OTHERS = 2. 168 IF sy-subrc <> 0. 169 ENDIF. 170 iv_default_extension = lv_extension. 171 ENDIF. 172 IF iv_file_filter IS INITIAL. 173 iv_file_filter = '(*' && iv_default_extension && ')|*' && iv_default_extension. 174 ENDIF. 175 IF iv_default_file_name IS INITIAL. 176 iv_default_file_name = ls_wwwdata-text. 177 ELSE. 178 WHILE iv_default_file_name CA './'. 179 lv_offset = sy-fdpos. 180 lv_length = lv_offset. 181 lv_offset = lv_offset + 1. 182 iv_default_file_name = iv_default_file_name+0(lv_length) && iv_default_file_name+lv_offset. 183 ENDWHILE. 184 ENDIF. 185 186 ls_key-relid = iv_relid. 187 ls_key-objid = iv_objid. 188 "File path of selection 189 CALL METHOD cl_gui_frontend_services=>file_save_dialog 190 EXPORTING 191 window_title = iv_window_title 192 default_extension = iv_default_extension 193 default_file_name = iv_default_file_name 194 file_filter = iv_file_filter 195 CHANGING 196 filename = lv_filename 197 path = lv_path 198 fullpath = lv_fullpath 199 user_action = lv_user_action 200 EXCEPTIONS 201 cntl_error = 1 202 error_no_gui = 2 203 not_supported_by_gui = 3 204 OTHERS = 4. 205 IF sy-subrc <> 0. 206 ev_rc = sy-subrc. 207 RETURN. 208 * MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno 209 * WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4. 210 ENDIF. 211 IF lv_user_action <> 0. 212 ev_rc = lv_user_action. 213 RETURN. 214 ENDIF. 215 216 ev_filepath = lv_fullpath. 217 "Download web object 218 DATA: lv_destination TYPE rlgrap-filename. 219 lv_destination = ev_filepath. 220 CALL FUNCTION 'DOWNLOAD_WEB_OBJECT' 221 EXPORTING 222 key = ls_key 223 destination = lv_destination 224 IMPORTING 225 rc = ev_rc 226 CHANGING 227 temp = lv_destination. 228 ENDMETHOD. 229 230 231 * <SIGNATURE>---------------------------------------------------------------------------------------+ 232 * | Static Public Method ZCL_BC_EXCEL=>F4_FILE_EXCEL 233 * +-------------------------------------------------------------------------------------------------+ 234 * | [--->] IV_WINDOW_TITLE TYPE STRING(optional) 235 * | [--->] IV_FILE_FILTER TYPE STRING(optional) 236 * | [--->] IV_MULTISELECTION TYPE CHAR01(optional) 237 * | [<---] EV_RC TYPE I 238 * | [<---] ET_FILE_TABLE TYPE FILETABLE 239 * | [<-->] CV_FILE_NAME TYPE LOCALFILE 240 * +--------------------------------------------------------------------------------------</SIGNATURE> 241 METHOD f4_file_excel. 242 DATA: ls_file TYPE file_table, 243 lv_len_s TYPE i, 244 lv_len_d TYPE i. 245 DATA: lv_default_filename TYPE string. 246 IF iv_window_title IS INITIAL. 247 iv_window_title = '请选择文件上传路径...'(002). 248 ENDIF. 249 IF iv_file_filter IS INITIAL. 250 iv_file_filter = 'Excel(*.xlsx)|*.xlsx|Excel(*.xlsm)|*.xlsm|Excel 97-2003(*.xls)|*.xls'. 251 ENDIF. 252 lv_default_filename = cv_file_name. 253 254 CALL METHOD cl_gui_frontend_services=>file_open_dialog 255 EXPORTING 256 window_title = iv_window_title 257 * default_extension = 258 default_filename = lv_default_filename 259 file_filter = iv_file_filter 260 * with_encoding = 261 * initial_directory = 262 multiselection = iv_multiselection 263 CHANGING 264 file_table = et_file_table 265 rc = ev_rc 266 * user_action = 267 * file_encoding = 268 EXCEPTIONS 269 file_open_dialog_failed = 1 270 cntl_error = 2 271 error_no_gui = 3 272 not_supported_by_gui = 4 273 OTHERS = 5. 274 IF sy-subrc <> 0. 275 MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno 276 WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4. 277 ENDIF. 278 279 IF ev_rc < 1. 280 RETURN. 281 ENDIF. 282 READ TABLE et_file_table INTO ls_file INDEX 1. 283 IF sy-subrc = 0. 284 lv_len_s = strlen( ls_file-filename ). 285 DESCRIBE FIELD cv_file_name LENGTH lv_len_d IN CHARACTER MODE. 286 IF lv_len_s > lv_len_d. 287 MESSAGE i398(00) WITH '文件路径太长'(003). 288 RETURN. 289 ENDIF. 290 cv_file_name = ls_file-filename. 291 ENDIF. 292 ENDMETHOD. 293 294 METHOD update_excel_ole. 295 DATA: 296 lt_tabc TYPE STANDARD TABLE OF zchar30000, 297 lw_tabc TYPE zchar30000, 298 lv_tabix TYPE sy-tabix, 299 lt_cell TYPE STANDARD TABLE OF string, 300 lw_cell TYPE string, 301 lv_cell_tabix TYPE sy-tabix. 302 303 DATA: 304 lv_column_num TYPE i, 305 lv_column_skp TYPE i, 306 lw_tab_ref TYPE REF TO data. 307 308 DATA: 309 lo_cx_root TYPE REF TO cx_root. 310 DATA: 311 lv_message TYPE string. 312 DATA 313 descr_ref TYPE REF TO cl_abap_structdescr. 314 315 FIELD-SYMBOLS: 316 <lw_tab> TYPE any, 317 <lv_value> TYPE any. 318 319 CREATE DATA lw_tab_ref LIKE LINE OF pt_tab. 320 ASSIGN lw_tab_ref->* TO <lw_tab>. 321 322 lv_column_skp = pi_skpcl_tbl. 323 324 CALL METHOD cl_bc_excel=>excel_2_inner_table 325 EXPORTING 326 pi_filename = pi_filename 327 pi_sheetname = pi_sheetname 328 pi_every_copy_endline = pi_every_copy_endline 329 pi_endcolumn = pi_endcolumn 330 CHANGING 331 pt_tab = lt_tabc 332 EXCEPTIONS 333 file_open_error = 1 334 OTHERS = 2. 335 336 *->把数据切割到内表 337 LOOP AT lt_tabc INTO lw_tabc. 338 lv_tabix = sy-tabix. 339 340 CHECK lv_tabix >= pi_startline. 341 CLEAR lt_cell. 342 343 SPLIT lw_tabc AT cl_abap_char_utilities=>horizontal_tab INTO TABLE lt_cell. 344 345 LOOP AT lt_cell INTO lw_cell. 346 lv_cell_tabix = sy-tabix. 347 348 lv_column_num = lv_cell_tabix - pi_startcolumn + 1 + lv_column_skp. 349 350 CHECK lv_column_num > 0. 351 352 ASSIGN COMPONENT lv_column_num OF STRUCTURE <lw_tab> TO <lv_value>. 353 CHECK sy-subrc = 0. 354 TRY. 355 <lv_value> = lw_cell. 356 CATCH cx_root INTO lo_cx_root. 357 descr_ref ?= cl_abap_typedescr=>describe_by_data( pt_tab ). 358 READ TABLE descr_ref->components INTO DATA(ls_name) INDEX lv_column_num. 359 lv_message = lv_tabix && 'Lines' && lv_column_num && 'Name of column is ' && ls_name-name && 'error value:' && lw_cell. 360 MESSAGE e001(00) WITH lv_tabix lv_column_num lv_message RAISING open_file_err. 361 ENDTRY. 362 ENDLOOP. 363 IF <lw_tab> IS NOT INITIAL. 364 INSERT <lw_tab> INTO TABLE pt_tab. 365 CLEAR <lw_tab>. 366 ENDIF. 367 368 ENDLOOP. 369 370 ENDMETHOD. 371 372 METHOD excel_2_inner_table. 373 TYPE-POOLS: 374 ole2. 375 376 DATA: 377 ole_excel TYPE ole2_object, 378 ole_workbooks TYPE ole2_object, 379 ole_workbook TYPE ole2_object, 380 ole_worksheets TYPE ole2_object, 381 ole_worksheet TYPE ole2_object, 382 ole_cell_begin TYPE ole2_object, 383 ole_cell_end TYPE ole2_object, 384 ole_range TYPE ole2_object. 385 386 DATA: 387 lv_subrc TYPE sy-subrc, 388 lv_begin_col TYPE i, 389 lv_end_col TYPE i, 390 lv_begin_row TYPE i, 391 lv_end_row TYPE i, 392 lv_add_rows TYPE i VALUE 3000. 393 394 lv_add_rows = pi_every_copy_endline. 395 396 DATA: 397 lt_excel_tab TYPE STANDARD TABLE OF zchar30000, 398 lw_excel_tab TYPE zchar30000, 399 lw_excel_tab_tmp TYPE zchar30000. 400 401 402 *->生成Excel object 403 CREATE OBJECT ole_excel 'Excel.Application'. 404 IF sy-subrc <> 0. 405 MESSAGE e001(zbc) RAISING file_open_error. 406 ENDIF. 407 408 *-> 409 SET PROPERTY OF ole_excel 'Visible' = 0. 410 411 GET PROPERTY OF ole_excel 'Workbooks' = ole_workbooks. 412 413 CALL METHOD OF 414 ole_workbooks 415 'Open' = ole_workbook 416 EXPORTING 417 #1 = pi_filename. 418 419 *->取得Sheet 420 GET PROPERTY OF ole_workbook 'Worksheets' = ole_worksheets 421 EXPORTING 422 #1 = pi_sheetname. 423 424 IF sy-subrc = 0. 425 CALL METHOD OF 426 ole_worksheets 427 'Activate'. 428 ELSE. 429 CALL METHOD OF 430 ole_excel 431 'QUIT'. 432 MESSAGE e001(00) WITH 'error in open Sheet , please check Sheet name' RAISING file_open_error. 433 ENDIF. 434 435 *->把Sheet上数据Copy 到 ClipBoard 436 lv_begin_col = 1. 437 lv_end_col = pi_endcolumn. 438 lv_begin_row = 0. 439 lv_end_row = 0. 440 441 WHILE lv_subrc IS INITIAL. 442 443 IF lv_begin_row IS INITIAL. 444 lv_begin_row = 1. 445 lv_end_row = lv_add_rows. 446 ELSE. 447 lv_begin_row = lv_begin_row + lv_add_rows. 448 lv_end_row = lv_end_row + lv_add_rows. 449 ENDIF. 450 451 CALL METHOD OF 452 ole_worksheets 453 'Cells' = ole_cell_begin 454 EXPORTING 455 #1 = lv_begin_row 456 #2 = lv_begin_col. 457 458 CALL METHOD OF 459 ole_worksheets 460 'Cells' = ole_cell_end 461 EXPORTING 462 #1 = lv_end_row 463 #2 = lv_end_col. 464 465 CALL METHOD OF 466 ole_worksheets 467 'RANGE' = ole_range 468 EXPORTING 469 #1 = ole_cell_begin 470 #2 = ole_cell_end. 471 472 CALL METHOD OF 473 ole_range 474 'SELECT'. 475 IF sy-subrc <> 0. 476 EXIT. 477 ENDIF. 478 479 CALL METHOD OF 480 ole_range 481 'COPY'. 482 483 * read clipboard into ABAP 484 CALL METHOD cl_gui_frontend_services=>clipboard_import 485 IMPORTING 486 data = lt_excel_tab 487 EXCEPTIONS 488 cntl_error = 1 489 error_no_gui = 2 490 not_supported_by_gui = 3 491 OTHERS = 4. 492 IF sy-subrc <> 0. 493 CALL METHOD OF 494 ole_excel 495 'QUIT'. 496 MESSAGE 'Error during import of clipboard contents' TYPE 'A'. 497 ENDIF. 498 499 lv_subrc = 4. 500 LOOP AT lt_excel_tab INTO lw_excel_tab. 501 lw_excel_tab_tmp = lw_excel_tab. 502 REPLACE ALL OCCURRENCES OF cl_abap_char_utilities=>horizontal_tab IN lw_excel_tab_tmp WITH space. 503 IF NOT ( lw_excel_tab_tmp = space OR lw_excel_tab_tmp IS INITIAL ). 504 INSERT lw_excel_tab INTO TABLE pt_tab. 505 CLEAR lv_subrc. 506 ENDIF. 507 ENDLOOP. 508 509 CLEAR lt_excel_tab. 510 511 ENDWHILE. 512 513 DATA: 514 lv_rc TYPE i. 515 CALL METHOD cl_gui_frontend_services=>clipboard_export 516 IMPORTING 517 data = lt_excel_tab 518 CHANGING 519 rc = lv_rc 520 EXCEPTIONS 521 cntl_error = 1 522 * ERROR_NO_GUI = 2 523 * NOT_SUPPORTED_BY_GUI = 3 524 OTHERS = 4. 525 526 * CALL METHOD OF 527 * ole_worksheets 528 * 'COLSE'. 529 530 * CALL METHOD OF 531 * ole_workbook 532 * 'COLSE'. 533 534 * CALL METHOD OF 535 * ole_workbooks 536 * 'CLOSE'. 537 538 CALL METHOD OF 539 ole_excel 540 'QUIT'. 541 FREE OBJECT: 542 ole_excel , 543 ole_workbooks , 544 ole_workbook , 545 ole_worksheets , 546 ole_worksheet , 547 ole_cell_begin , 548 ole_cell_end , 549 ole_range . 550 ENDMETHOD. 551 552 METHOD download_to_excel. 553 554 DATA: g_excel TYPE ole2_object, 555 g_workbookl TYPE ole2_object, 556 g_workbook TYPE ole2_object, 557 g_sheet TYPE ole2_object, 558 g_cell TYPE ole2_object, 559 g_cell1 TYPE ole2_object, 560 g_range TYPE ole2_object. 561 562 DATA: l_formkey TYPE wwwdatatab. 563 DATA: l_excel_template_path TYPE localfile, 564 l_excel_path TYPE localfile, 565 l_data_path TYPE localfile. 566 DATA: l_msg TYPE char128. 567 568 * Download the excel template from server 569 CONCATENATE 'MI' excel_id INTO l_formkey. 570 CONCATENATE path '\' excel_fname '.XLT' INTO l_excel_template_path. 571 REPLACE ALL OCCURRENCES OF '\\' IN l_excel_template_path WITH '\'. 572 573 * PERFORM DOWNLOAD_TEMPLATE USING L_FORMKEY L_EXCEL_TEMPLATE_PATH . 574 DATA: l_path TYPE localfile. 575 CALL FUNCTION 'DOWNLOAD_WEB_OBJECT' 576 EXPORTING 577 key = l_formkey 578 destination = l_excel_template_path 579 CHANGING 580 temp = l_path. 581 582 * Create EXCEL application 583 * PERFORM CREATE_APPLICATION CHANGING E_MESSAGE. 584 DATA: l_indicator TYPE i VALUE 0. 585 IF g_excel IS INITIAL. 586 CREATE OBJECT g_excel 'EXCEL.APPLICATION'. 587 ENDIF. 588 * PERFORM err_hdl CHANGING e_message. 589 IF sy-subrc NE 0. 590 e_message = 'EXCEL操作没成功执行' . 591 RAISE excel_error. 592 ENDIF. 593 SET PROPERTY OF g_excel 'Visible' = l_indicator. 594 * PERFORM err_hdl CHANGING e_message. 595 IF sy-subrc NE 0. 596 e_message = 'EXCEL操作没成功执行' . 597 RAISE excel_error. 598 ENDIF. 599 SET PROPERTY OF g_excel 'DisplayAlerts' = l_indicator. 600 601 * Download to Excel indicator 602 l_msg = '下载数据到EXCEL,请等待....'. 603 * PERFORM progress_indicator USING l_msg. 604 CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' 605 EXPORTING 606 percentage = 0 607 text = l_msg. 608 * Download data to Front 609 CONCATENATE path '\' excel_fname '.TXT' INTO l_data_path. 610 REPLACE ALL OCCURRENCES OF '\\' IN l_data_path WITH '\'. 611 * PERFORM download_data_file TABLES i_data USING l_data_path. 612 SORT i_data BY row col. 613 CALL FUNCTION 'GUI_DOWNLOAD' 614 EXPORTING 615 filename = l_data_path 616 write_field_separator = cl_abap_char_utilities=>horizontal_tab 617 codepage = '8400' 618 TABLES 619 data_tab = i_data. 620 IF sy-subrc <> 0. 621 ENDIF. 622 CLEAR i_data[]. 623 * Open excel template 624 * PERFORM open_excel_template USING l_excel_template_path 625 * CHANGING e_message . 626 CALL METHOD OF g_excel 'Workbooks' = g_workbookl. 627 * PERFORM err_hdl CHANGING e_message. 628 IF sy-subrc NE 0. 629 e_message = 'EXCEL操作没成功执行' . 630 RAISE excel_error. 631 ENDIF. 632 * OPen template file 633 CALL METHOD OF g_workbookl 'Open' = g_workbook 634 EXPORTING 635 #1 = l_excel_template_path. 636 IF sy-subrc NE 0. 637 e_message = 'EXCEL操作没成功执行' . 638 RAISE excel_error. 639 ENDIF. 640 * Open excel sheet 641 * PERFORM open_excel_sheet USING sheet_name 642 * CHANGING e_message. 643 CALL METHOD OF g_workbook 'WORKSHEETS' = g_sheet 644 EXPORTING 645 #1 = sheet_name. 646 IF sy-subrc NE 0. 647 e_message = 'EXCEL操作没成功执行' . 648 RAISE excel_error. 649 ENDIF. 650 CALL METHOD OF g_sheet 'ACTIVATE'. 651 IF sy-subrc NE 0. 652 e_message = 'EXCEL操作没成功执行' . 653 RAISE excel_error. 654 ENDIF. 655 * Convert data file to excel 656 * PERFORM run_macro_open_text USING l_data_path sheet_name. 657 CALL METHOD OF g_excel 'RUN' 658 EXPORTING 659 #1 = 'Open_text' 660 #2 = l_data_path 661 #3 = sheet_name. 662 * Save excel file 663 CONCATENATE path '\' excel_fname '.XLS' INTO l_excel_path. 664 REPLACE ALL OCCURRENCES OF '\\' IN l_excel_path WITH '\'. 665 * PERFORM run_macro_save_excel USING l_excel_path. 666 CALL METHOD OF g_excel 'RUN' 667 EXPORTING 668 #1 = 'Save_excel' 669 #2 = l_excel_path. 670 671 IF show_excel = 'X'. 672 SET PROPERTY OF g_excel 'VISIBLE' = 1 NO FLUSH. 673 ENDIF. 674 * Close excel 675 * PERFORM close_excel CHANGING show_excel. 676 * Free objects 677 IF show_excel = space. 678 CALL METHOD OF g_excel 'Quit'. 679 ENDIF. 680 FREE OBJECT g_cell. 681 FREE OBJECT g_cell1. 682 FREE OBJECT g_range. 683 FREE OBJECT g_sheet. 684 FREE OBJECT g_workbook. 685 FREE OBJECT g_workbookl. 686 687 * force flush 688 CALL FUNCTION 'FLUSH' 689 EXCEPTIONS 690 OTHERS = 0. 691 692 * Delete data file & template file 693 * PERFORM delete_file USING l_data_path. 694 CALL FUNCTION 'GUI_DELETE_FILE' 695 EXPORTING 696 file_name = l_data_path 697 EXCEPTIONS 698 failed = 1 699 OTHERS = 2. 700 IF sy-subrc <> 0. 701 * Implement suitable error handling here 702 ENDIF. 703 * PERFORM delete_file USING l_excel_template_path. 704 CALL FUNCTION 'GUI_DELETE_FILE' 705 EXPORTING 706 file_name = l_excel_template_path 707 EXCEPTIONS 708 failed = 1 709 OTHERS = 2. 710 IF sy-subrc <> 0. 711 * Implement suitable error handling here 712 ENDIF. 713 ENDMETHOD. 714 715 716 ENDCLASS.
仅供参考
