ABAP-读取EXCEL中不同的SHEET函数
标准的函数alsm_excel_to_internal_table1只能读取默认的SHEET，可以复制标准函数进行修改，达到可以动态的读取不同的SHEET
1 FUNCTION zalsm_excel_to_internal_table1 . 2 *"---------------------------------------------------------------------- 3 *"*"本地接口： 4 *" IMPORTING 5 *" VALUE(FILENAME) LIKE RLGRAP-FILENAME 6 *" VALUE(I_BEGIN_COL) TYPE I 7 *" VALUE(I_BEGIN_ROW) TYPE I 8 *" VALUE(I_END_COL) TYPE I 9 *" VALUE(I_END_ROW) TYPE I 10 *" VALUE(I_SHEETINDEX) TYPE I 11 *" TABLES 12 *" INTERN 13 *" EXCEPTIONS 14 *" INCONSISTENT_PARAMETERS 15 *" UPLOAD_OLE 16 *"---------------------------------------------------------------------- 17 18 DATA: excel_tab TYPE ty_t_sender. 19 DATA: ld_separator TYPE c. 20 DATA: application TYPE ole2_object, 21 workbook TYPE ole2_object, 22 range TYPE ole2_object, 23 worksheet TYPE ole2_object. 24 DATA: h_cell TYPE ole2_object, 25 h_cell1 TYPE ole2_object. 26 DATA: 27 ld_rc TYPE i. 28 * Rückgabewert der Methode "clipboard_export " 29 30 * Makro für Fehlerbehandlung der Methods 31 DEFINE m_message. 32 CASE sy-subrc. 33 WHEN 0. 34 WHEN 1. 35 MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno 36 WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4. 37 WHEN OTHERS. RAISE upload_ole. 38 ENDCASE. 39 END-OF-DEFINITION. 40 41 * check parameters 42 IF i_begin_row > i_end_row. RAISE inconsistent_parameters. ENDIF. 43 IF i_begin_col > i_end_col. RAISE inconsistent_parameters. ENDIF. 44 45 * Get TAB-sign for separation of fields 46 CLASS cl_abap_char_utilities DEFINITION LOAD. 47 ld_separator = cl_abap_char_utilities=>horizontal_tab. 48 49 * open file in Excel 50 IF application-header = space OR application-handle = -1. 51 CREATE OBJECT application 'Excel.Application'. 52 m_message. 53 ENDIF. 54 CALL METHOD OF application 'Workbooks' = workbook. 55 m_message. 56 CALL METHOD OF workbook 'Open' EXPORTING #1 = filename. 57 m_message. 58 59 SET PROPERTY OF application 'DISPLAYALERTS' = 0. 60 * set property of application 'Visible' = 1. 61 * m_message. 62 63 ********************* CHANGE ZLP-- 64 IF i_sheetindex = space."用默认模式 65 GET PROPERTY OF application 'ACTIVESHEET' = worksheet. 66 m_message. 67 ELSE. 68 69 *-->可以实现读取多个sheet 70 CALL METHOD OF application 71 'WORKSHEETS' = worksheet 72 EXPORTING 73 #1 = i_sheetindex. 74 CALL METHOD OF worksheet 'Activate'. 75 m_message. 76 ENDIF. 77 *************** CHANGE ZLP-- 78 79 m_message. 80 81 * mark whole spread sheet 82 CALL METHOD OF worksheet 'Cells' = h_cell 83 EXPORTING #1 = i_begin_row #2 = i_begin_col. 84 m_message. 85 CALL METHOD OF worksheet 'Cells' = h_cell1 86 EXPORTING #1 = i_end_row #2 = i_end_col. 87 m_message. 88 89 CALL METHOD OF worksheet 'RANGE' = range 90 EXPORTING #1 = h_cell #2 = h_cell1. 91 m_message. 92 CALL METHOD OF range 'SELECT'. 93 m_message. 94 95 * copy marked area (whole spread sheet) into Clippboard 96 CALL METHOD OF range 'COPY'. 97 m_message. 98 99 * read clipboard into ABAP 100 CALL METHOD cl_gui_frontend_services=>clipboard_import 101 IMPORTING 102 data = excel_tab 103 EXCEPTIONS 104 cntl_error = 1 105 * ERROR_NO_GUI = 2 106 * NOT_SUPPORTED_BY_GUI = 3 107 OTHERS = 4. 108 IF sy-subrc <> 0. 109 MESSAGE a037(alsmex). 110 ENDIF. 111 112 *begin ADD by ZHANGLP 添加了注释 113 * PERFORM separated_to_intern_convert TABLES excel_tab intern 114 * USING ld_separator. 115 *end 116 ************ BEGIN 修改为以下的代码：********************* 117 FIELD-SYMBOLS:<dyn_field>. 118 DATA: f_wa LIKE LINE OF excel_tab, 119 it_data TYPE string OCCURS 0 WITH HEADER LINE. 120 121 LOOP AT excel_tab INTO f_wa. 122 SPLIT f_wa AT ld_separator INTO TABLE it_data . 123 LOOP AT it_data . 124 ASSIGN COMPONENT sy-tabix OF STRUCTURE intern TO <dyn_field>. 125 <dyn_field> = it_data. 126 ENDLOOP. 127 APPEND intern. 128 CLEAR intern. 129 ENDLOOP. 130 ************ END 修改为以下的代码：********************* 131 132 * clear clipboard 133 REFRESH excel_tab. 134 CALL METHOD cl_gui_frontend_services=>clipboard_export 135 IMPORTING 136 data = excel_tab 137 CHANGING 138 rc = ld_rc 139 EXCEPTIONS 140 cntl_error = 1 141 * ERROR_NO_GUI = 2 142 * NOT_SUPPORTED_BY_GUI = 3 143 OTHERS = 4. 144 145 * quit Excel and free ABAP Object - unfortunately, this does not kill 146 * the Excel process 147 CALL METHOD OF application 'QUIT'. 148 m_message. 149 150 * >>>>> Begin of change note 575877 151 * to kill the Excel process it's necessary to free all used objects 152 FREE OBJECT h_cell. m_message. 153 FREE OBJECT h_cell1. m_message. 154 FREE OBJECT range. m_message. 155 FREE OBJECT worksheet. m_message. 156 FREE OBJECT workbook. m_message. 157 FREE OBJECT application. m_message. 158 * <<<<< End of change note 575877 159 ENDFUNCTION.
