SAP ABAP中使用函数ALSM_EXCEL_TO_INTERNAL_TABLE读取EXCEL中不同的SHEET数据-Toy模板网

这篇具有很好参考价值的文章主要介绍了SAP ABAP中使用函数ALSM_EXCEL_TO_INTERNAL_TABLE读取EXCEL中不同的SHEET数据。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

SAP提供了标准的读取EXCEL的函数（ALSM_EXCEL_TO_INTERNAL_TABLE），但是此标准函数无法满足对同一EXCEL 进行不同SHEET的数据读取，一下方法就是教你如何通过修改程序来实现ALSM_EXCEL_TO_INTERNAL_TABLE读取多个SHEET；

一、拷贝ALSM_EXCEL_TO_INTERNAL_TABLE函数，拷贝时函数组选择自定义函数组

二、将ALSM_EXCEL_TO_INTERNAL_TABLE函数中的LALSMEXTOP中的数据拷贝都你自定义函数的TOP中；

三、将函数ALSM_EXCEL_TO_INTERNAL_TABLE中的

INCLUDE LALSMEXUXX.INCLUDE LALSMEXF01.引用到你自定义的函数中；

四、将ALSM_EXCEL_TO_INTERNAL_TABLE中的代码修改如下：

将代码：

  GET PROPERTY OF  application 'ACTIVESHEET' = worksheet.
  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.

结果代码如下：

FUNCTION alsm_excel_to_internal_table .
*"----------------------------------------------------------------------
*"*"Lokale Schnittstelle:
*"  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
*"  TABLES
*"      INTERN STRUCTURE  ALSMEX_TABLINE
*"  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.
  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.

**START BEGIN OF Change By--激活excel表sheet页--02.08.2023 16:57:55-Reagon2008
  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.
**End OF Change By--激活excel表sheet页--02.08.2023 16:57:55-Reagon2008

* 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 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 intern
                                      USING  ld_separator.

* clear clipboard
  REFRESH excel_tab.
  CALL METHOD 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
          .

* 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

五、程序调用代码如下：文章来源地址https://www.toymoban.com/news/detail-629910.html

TABLES: zzpmttzsml,"通知书抬头表
        zzpmttzssb,"通知书设备表
        zzpmttzsxm,"通知书项目表
        zzpmttemplate."模板
DATA: lt_zzpmttzsml    LIKE TABLE OF zzpmttzsml WITH HEADER LINE, "通知书抬头表
      lt_zzpmttzssb    LIKE TABLE OF zzpmttzssb WITH HEADER LINE, "通知书设备表
      lt_zzpmttzsxm    LIKE TABLE OF zzpmttzsxm WITH HEADER LINE, "通知书项目表
      lt_zzpmttemplate LIKE TABLE OF zzpmttemplate WITH HEADER LINE. "模板

SELECTION-SCREEN BEGIN OF BLOCK block1 WITH FRAME TITLE text-001.
PARAMETERS: p_file LIKE rlgrap-filename OBLIGATORY.
PARAMETERS: p_box TYPE c AS CHECKBOX DEFAULT 'X'."P_BOX为X则是创建，为空则是修改
SELECTION-SCREEN END OF BLOCK block1.

*&-------------------------------------------------------------------*

* AT SELECTION-SCREEN

*&-------------------------------------------------------------------*
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

*  选择文件
  PERFORM get_filename USING p_file.


START-OF-SELECTION.
*---加载文件

*--取得通知书抬头表数据
  PERFORM upoload_file TABLES lt_zzpmttzsml USING 'ZZPMTTZSML'.

*--取得通知书设备表
  PERFORM upoload_file TABLES lt_zzpmttzssb USING 'ZZPMTTZSSB'.

*--取得通知书项目表
  PERFORM upoload_file TABLES lt_zzpmttzsxm USING 'ZZPMTTZSXM'.

*--取得模板
  PERFORM upoload_file TABLES lt_zzpmttemplate USING 'ZZPMTTEMPLATE'.

*--->导入数据
  PERFORM frm_save.

*&---------------------------------------------------------------------*
*&      Form  get_filename
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  选择文件
*----------------------------------------------------------------------*

FORM get_filename  USING    p_p_file.

  "出现打开文件的对话框
  CALL FUNCTION 'WS_FILENAME_GET'
    EXPORTING
      mask             = ',*.*,*.*.'
      mode             = 'O' "S为保存，O为打开
    IMPORTING
      filename         = p_p_file
    EXCEPTIONS
      inv_winsys       = 1
      no_batch         = 2
      selection_cancel = 3
      selection_error  = 4
      OTHERS           = 5.

ENDFORM.                    "GET_FILENAME
*&---------------------------------------------------------------------*
*&      Form  UPOLOAD_FILE
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  加载文件
*----------------------------------------------------------------------*

FORM upoload_file TABLES lt_input TYPE STANDARD TABLE USING sheet_name.

  DATA gt_upload  LIKE TABLE OF alsmex_tabline WITH HEADER LINE.
  DATA: ls_i TYPE i.
  FIELD-SYMBOLS <f>.

  CALL FUNCTION 'ZALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                = p_file
      i_begin_col             = '1'
      i_begin_row             = '2'
      i_end_col               = '14'
      i_end_row               = '50000'
      sheet_name              = sheet_name "指定SHEET名
    TABLES
      intern                  = gt_upload
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.

  IF sy-subrc = 0.
*--->处理读取出的数据
    SORT gt_upload BY row col.
    LOOP AT gt_upload.
      ls_i = gt_upload-col.
      ASSIGN COMPONENT ls_i OF STRUCTURE lt_input TO <f>.
      <f> = gt_upload-value.
      AT END OF row.
        APPEND lt_input TO lt_input.
        CLEAR:lt_input.
      ENDAT.
    ENDLOOP.
  ELSE.
    WRITE: / 'EXCEL UPLOAD FAILED ', p_file, sy-subrc.
  ENDIF.
ENDFORM.                    " UPOLOAD_FILE

*&---------------------------------------------------------------------*
*&      Form  FRM_SAVE
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  导入数据
*----------------------------------------------------------------------*

FORM frm_save .


ENDFORM.                    " FRM_SAVE

到了这里，关于SAP ABAP中使用函数ALSM_EXCEL_TO_INTERNAL_TABLE读取EXCEL中不同的SHEET数据的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！