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

开发运维 2023-09-22 贤蛋大眼萌 手机阅读

从文件中读取数据是项目实现中非常常见的要求。Excel 电子表格对文本文件更简单、更易读。标准 ABAP 功能模块ALSM_EXCEL_TO_INTERNAL_TABLE 将数据从 Excel 文件读取到类型 ALSMEX_TABLINE 的内部表中。

此功能模块仅从活动工作表中读取数据，即，如果 Excel 文件有多个工作表，则可以从任何一个工作表中读取数据。本文介绍一种方法可用于将 Excel 文件内所有工作表中的数据读入内部表。

1.自定义结构

标准 ALSMEX_TABLINE 结构只包括行号（ROW）、列（COL）和值信息（VALUE)。因此需要自定义一个结构用于存储工作表的 Sheet 编号和 Sheet Name，如下：

image.png

2.转到 SE80 新建函数组

创建一个新的函数组 FM,命名为 ZFGEXCEL_INT，创建功能模块 ZALSM_EXCEL_TO_INTERNAL_TABLE4，如下图：

image.png

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.
*