从文件中读取数据是项目实现中非常常见的要求。Excel 电子表格对文本文件更简单、更易读。标准 ABAP 功能模块ALSM_EXCEL_TO_INTERNAL_TABLE 将数据从 Excel 文件读取到类型 ALSMEX_TABLINE 的内部表中。
此功能模块仅从活动工作表中读取数据，即，如果 Excel 文件有多个工作表，则可以从任何一个工作表中读取数据。本文介绍一种方法可用于将 Excel 文件内所有工作表中的数据读入内部表。
1.自定义结构
标准 ALSMEX_TABLINE 结构只包括行号（ROW）、列（COL）和值信息（VALUE)。因此需要自定义一个结构用于存储工作表的 Sheet 编号和 Sheet Name，如下：
2.转到 SE80 新建函数组
创建一个新的函数组 FM,命名为 ZFGEXCEL_INT，创建功能模块 ZALSM_EXCEL_TO_INTERNAL_TABLE4，如下图：
Import 参数：
- FILENAME：文件名
- I_BEGIN_COL
- I_BEGIN_ROW
- I_END_COL
- I_END_ROW
- I_SHEETS：Sheet 页数
- I_SHEETNAME：Sheet 名称
Tables 参数：
- IT_DATA：转换后的内表
同时双击下面的 execute 语句并为子例程创建一个单独的包含 （LZFGEXCEL_INTF01），如下所示，并从其中复制标准 FM 的代码。
PERFORM separated_to_intern_convert TABLES excel_tab it_data
USING ld_separator
sheetno
sheetname.
3.源代码：
FUNCTION zalsm_excel_to_internal_table4.
*"----------------------------------------------------------------------
*"*"Local Interface:
*" IMPORTING
*" VALUE(FILENAME) LIKE RLGRAP-FILENAME
*" VALUE(I_BEGIN_COL) TYPE I
*" VALUE(I_BEGIN_ROW) TYPE I
*" VALUE(I_END_COL) TYPE I
*" VALUE(I_END_ROW) TYPE I
*" VALUE(I_SHEETS) TYPE I DEFAULT 10
*" VALUE(I_SHEETNAME) TYPE CHAR50 OPTIONAL
*" TABLES
*" IT_DATA STRUCTURE ZALSMEX_TABLINE4
*" EXCEPTIONS
*" INCONSISTENT_PARAMETERS
*" UPLOAD_OLE
*"----------------------------------------------------------------------
DATA: excel_tab TYPE ty_t_sender.
DATA: ld_separator TYPE c.
DATA: application TYPE ole2_object,
workbook TYPE ole2_object,
range TYPE ole2_object,
worksheet TYPE ole2_object,
sheetno TYPE i,
sheetname TYPE char50.
DATA: h_cell TYPE ole2_object,
h_cell1 TYPE ole2_object.
DATA:
ld_rc TYPE i.
* Rückgabewert der Methode "clipboard_export "
* Makro für Fehlerbehandlung der Methods
DEFINE m_message.
CASE sy-subrc.
WHEN 0.
WHEN 1.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
WHEN OTHERS. RAISE upload_ole.
ENDCASE.
END-OF-DEFINITION.
* check parameters
IF i_begin_row > i_end_row. RAISE inconsistent_parameters. ENDIF.
IF i_begin_col > i_end_col. RAISE inconsistent_parameters. ENDIF.
* Get TAB-sign for separation of fields
CLASS cl_abap_char_utilities DEFINITION LOAD.
ld_separator = cl_abap_char_utilities=>horizontal_tab.
* open file in Excel
IF application-header = space OR application-handle = -1.
CREATE OBJECT application 'Excel.Application'.
m_message.
ENDIF.
CALL METHOD OF application 'Workbooks' = workbook.
m_message.
CALL METHOD OF workbook 'Open' EXPORTING #1 = filename.
m_message.
** set property of application 'Visible' = 1.
** m_message.
* IF sheet_name = space."用默认模式
* GET PROPERTY OF application 'ACTIVESHEET' = worksheet.
* m_message.
* ELSE.
**-->可以实现读取多个sheet
* CALL METHOD OF application 'WORKSHEETS' = worksheet
* EXPORTING
* #1 = sheet_name.
*
* CALL METHOD OF worksheet 'Activate'.
* m_message.
* ENDIF.
REFRESH it_data.
DO i_sheets TIMES.
sheetno = sheetno + 1.
GET PROPERTY OF application 'ACTIVESHEET' = worksheet.
m_message.
CALL METHOD OF application 'WORKSHEETS' = worksheet
EXPORTING
#1 = sheetno.
IF sy-subrc 0.
IF sheetno = 1.
m_message.
ELSE.
EXIT.
ENDIF.
ENDIF.
CALL METHOD OF worksheet 'Activate'.
m_message.
GET PROPERTY OF worksheet 'NAME' = sheetname.
m_message.
" Filtro por sheetname
* CHECK sheetname = i_sheetname OR i_sheetname IS NOT SUPPLIED.
GET PROPERTY OF application 'ACTIVESHEET' = worksheet.
m_message.
** set property of application 'Visible' = 1.
** m_message.
* GET PROPERTY OF application 'ACTIVESHEET' = worksheet.
* m_message.
* mark whole spread sheet
CALL METHOD OF worksheet 'Cells' = h_cell
EXPORTING #1 = i_begin_row #2 = i_begin_col.
m_message.
CALL METHOD OF worksheet 'Cells' = h_cell1
EXPORTING #1 = i_end_row #2 = i_end_col.
m_message.
CALL METHOD OF worksheet 'RANGE' = range
EXPORTING #1 = h_cell #2 = h_cell1.
m_message.
CALL METHOD OF range 'SELECT'.
m_message.
* copy marked area (whole spread sheet) into Clippboard
CALL METHOD OF range 'COPY'.
m_message.
* read clipboard into ABAP
CALL METHOD z_cl_gui_frontend_services=>clipboard_import
IMPORTING
data = excel_tab
EXCEPTIONS
cntl_error = 1
* ERROR_NO_GUI = 2
* NOT_SUPPORTED_BY_GUI = 3
OTHERS = 4.
IF sy-subrc 0.
MESSAGE a037(alsmex).
ENDIF.
PERFORM separated_to_intern_convert TABLES excel_tab it_data
USING ld_separator
sheetno
sheetname.
* clear clipboard
REFRESH excel_tab.
CALL METHOD z_cl_gui_frontend_services=>clipboard_export
IMPORTING
data = excel_tab
CHANGING
rc = ld_rc
EXCEPTIONS
cntl_error = 1
* ERROR_NO_GUI = 2
* NOT_SUPPORTED_BY_GUI = 3
OTHERS = 4.
ENDDO. " 适应多个 Sheet
* quit Excel and free ABAP Object - unfortunately, this does not kill
* the Excel process
CALL METHOD OF application 'QUIT'.
m_message.
* >>>>> Begin of change note 575877
* to kill the Excel process it's necessary to free all used objects
FREE OBJECT h_cell. m_message.
FREE OBJECT h_cell1. m_message.
FREE OBJECT range. m_message.
FREE OBJECT worksheet. m_message.
FREE OBJECT workbook. m_message.
FREE OBJECT application. m_message.
*
