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.

posted @ 2022-06-20 16:47 TigerMan 阅读(294) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· ABAP-DOI导出EXCEL

· ABAP-读取长文本函数

· ABAP 自定义简易读取EXCEL函数

· SAP ABAP ALSM_EXCEL_TO_INTERNAL_TABLE 导入excel 和 GUI_UPLOAD 导入 txt

· ABAP 操作Excel

公告

昵称： TigerMan
园龄： 5年4个月
粉丝： 18
关注： 2

+加关注

2025年3月

日

一

二

三

四

五

六

TigerMan

ABAP-读取EXCEL中不同的SHEET函数

公告

搜索

常用链接

随笔分类

随笔档案

阅读排行榜

推荐排行榜