ABAP 程序如何一次性读取 Excel 文件的不同 Sheet

从文件中读取数据是项目实现中非常常见的要求。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.
* <<<<< End of change note 575877

ENDFUNCTION.

Top 包含文件 LZFGEXCEL_INTTOP

FUNCTION-POOL zfgexcel_int.                 "MESSAGE-ID ..

* INCLUDE LZFGEXCEL_INTD...                  " Local class definition
TYPE-POOLS: ole2.

*      value of excel-cell
TYPES: ty_d_itabvalue TYPE zalsmex_tabline4-value,
*      internal table containing the excel data
       ty_t_itab      TYPE zalsmex_tabline4   OCCURS 0,

*      line type of sender table
       BEGIN OF ty_s_senderline,
         line(32768) TYPE c,
       END OF ty_s_senderline,
*      sender table
       ty_t_sender TYPE ty_s_senderline  OCCURS 0.

*
CONSTANTS:  gc_esc              VALUE '"'.

F01 包含文件 LZFGEXCEL_INTF01

LZFGEXCEL_INTF01文件如下：

*----------------------------------------------------------------------*
***INCLUDE LZFGEXCEL_INTF01.
*----------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*&      Form  SEPARATED_TO_INTERN_CONVERT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_EXCEL_TAB  text
*      -->P_INTERN  text
*      -->P_LD_SEPARATOR  text
*----------------------------------------------------------------------*
FORM separated_to_intern_convert TABLES i_tab       TYPE ty_t_sender
                                        i_intern    TYPE ty_t_itab
                                USING  i_separator TYPE c
                                       i_sheetno   TYPE i
                                       i_sheetname TYPE char50.

  DATA: l_sic_tabix LIKE sy-tabix,
        l_sic_col   TYPE kcd_ex_col.
  DATA: l_fdpos     LIKE sy-fdpos.

*  REFRESH i_intern. “ 多个 Sheet 读取到一个内表，因此注释

  LOOP AT i_tab.
    l_sic_tabix = sy-tabix.
    l_sic_col = 0.
    WHILE i_tab CA i_separator.
      l_fdpos = sy-fdpos.
      l_sic_col = l_sic_col + 1.
      PERFORM line_to_cell_separat TABLES i_intern
      USING  i_tab l_sic_tabix l_sic_col i_separator l_fdpos i_sheetno i_sheetname.
    ENDWHILE.
    IF i_tab <> space.
      CLEAR i_intern.
      i_intern-sheetno   = i_sheetno. " Added
      i_intern-sheetname = i_sheetname. " Added
      i_intern-row = l_sic_tabix.
      i_intern-col = l_sic_col + 1.
      i_intern-value = i_tab.
      APPEND i_intern.
    ENDIF.
  ENDLOOP.
ENDFORM.                    " SEPARATED_TO_INTERN_CONVERT
*---------------------------------------------------------------------*
FORM line_to_cell_separat TABLES i_intern    TYPE ty_t_itab
USING i_line
      i_row       LIKE sy-tabix
      ch_cell_col TYPE kcd_ex_col
      i_separator TYPE c
      i_fdpos     LIKE sy-fdpos
      i_sheetno   TYPE i
      i_sheetname TYPE char50.
  DATA: l_string   TYPE ty_s_senderline.
  DATA  l_sic_int  TYPE i.

  CLEAR i_intern.
  l_sic_int = i_fdpos.
  i_intern-sheetno = i_sheetno.
  i_intern-sheetname = i_sheetname.
  i_intern-row = i_row.
  l_string = i_line.
  i_intern-col = ch_cell_col.
* csv Dateien mit separator in Zelle: --> ;"abc;cd";
  IF ( i_separator = ';' OR  i_separator = ',' ) AND
  l_string(1) = gc_esc.
    PERFORM line_to_cell_esc_sep USING l_string
          l_sic_int
          i_separator
          i_intern-value.
  ELSE.
    IF l_sic_int > 0.
      i_intern-value = i_line(l_sic_int).
    ENDIF.
  ENDIF.
  IF l_sic_int > 0.
    APPEND i_intern.
  ENDIF.
  l_sic_int = l_sic_int + 1.
  i_line = i_line+l_sic_int.
ENDFORM.

*---------------------------------------------------------------------*
FORM line_to_cell_esc_sep USING i_string
      i_sic_int      TYPE i
      i_separator    TYPE c
      i_intern_value TYPE ty_d_itabvalue.
  DATA: l_int         TYPE i,
        l_cell_end(2).
  FIELD-SYMBOLS: <l_cell>.
  l_cell_end = gc_esc.
  l_cell_end+1 = i_separator .

  IF i_string CS gc_esc.
    i_string = i_string+1.
    IF i_string CS l_cell_end.
      l_int = sy-fdpos.
      ASSIGN i_string(l_int) TO <l_cell>.
      i_intern_value = <l_cell>.
      l_int = l_int + 2.
      i_sic_int = l_int.
      i_string = i_string+l_int.
    ELSEIF i_string CS gc_esc.
*     letzte Celle
      l_int = sy-fdpos.
      ASSIGN i_string(l_int) TO <l_cell>.
      i_intern_value = <l_cell>.
      l_int = l_int + 1.
      i_sic_int = l_int.
      i_string = i_string+l_int.
      l_int = strlen( i_string ).
      IF l_int > 0 . MESSAGE x001(kx) . ENDIF.
    ELSE.
      MESSAGE x001(kx) . "was ist mit csv-Format
    ENDIF.
  ENDIF.

ENDFORM.

测试

打开一个包含 7 个 Sheet 页的 Excel，然后调用 ZALSM_EXCEL_TO_INTERNAL_TABLE4 函数模块：

可以点进去这个表格查看是否和你的Excel 不同 Sheet 相一致：

然后，我们针对这些数据，就可以通过 Read IT_DATA ... WITH SHEETNAME ... 来做不同的处理，比如用于 ALV 前台显示或者保存到不同的数据库中。