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.
*-->可以实现读取多个sheetCALL METHOD OF application 'WORKSHEETS' = worksheetEXPORTING#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 MethodsDEFINE m_message.case sy-subrc.when 0.when 1.message id sy-msgid type sy-msgty number sy-msgnowith sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.when others. raise upload_ole.endcase.END-OF-DEFINITION.* check parametersIF 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 fieldsCLASS cl_abap_char_utilities DEFINITION LOAD.ld_separator = cl_abap_char_utilities=>horizontal_tab.* open file in ExcelIF 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-Reagon2008IF sheet_name = space."用默认模式GET PROPERTY OF  application 'ACTIVESHEET' = worksheet.m_message.ELSE.
*-->可以实现读取多个sheetCALL METHOD OF application 'WORKSHEETS' = worksheetEXPORTING#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 sheetCALL METHOD OF worksheet 'Cells' = h_cellEXPORTING #1 = i_begin_row #2 = i_begin_col.m_message.CALL METHOD OF worksheet 'Cells' = h_cell1EXPORTING #1 = i_end_row #2 = i_end_col.m_message.CALL METHOD  OF worksheet 'RANGE' = rangeEXPORTING #1 = h_cell #2 = h_cell1.m_message.CALL METHOD OF range 'SELECT'.m_message.* copy marked area (whole spread sheet) into ClippboardCALL METHOD OF range 'COPY'.m_message.* read clipboard into ABAPCALL METHOD cl_gui_frontend_services=>clipboard_importIMPORTINGdata                 = excel_tabEXCEPTIONScntl_error           = 1
*      ERROR_NO_GUI         = 2
*      NOT_SUPPORTED_BY_GUI = 3OTHERS               = 4.IF sy-subrc <> 0.MESSAGE a037(alsmex).ENDIF.PERFORM separated_to_intern_convert TABLES excel_tab internUSING  ld_separator.* clear clipboardREFRESH excel_tab.CALL METHOD cl_gui_frontend_services=>clipboard_exportIMPORTINGdata                 = excel_tabCHANGINGrc                   = ld_rcEXCEPTIONScntl_error           = 1
*       ERROR_NO_GUI         = 2
*       NOT_SUPPORTED_BY_GUI = 3OTHERS               = 4.* quit Excel and free ABAP Object - unfortunately, this does not kill
* the Excel processCALL METHOD OF application 'QUIT'.m_message.* >>>>> Begin of change note 575877
* to kill the Excel process it's necessary to free all used objectsFREE 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

五、程序调用代码如下：

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'EXPORTINGmask             = ',*.*,*.*.'mode             = 'O' "S为保存，O为打开IMPORTINGfilename         = p_p_fileEXCEPTIONSinv_winsys       = 1no_batch         = 2selection_cancel = 3selection_error  = 4OTHERS           = 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'EXPORTINGfilename                = p_filei_begin_col             = '1'i_begin_row             = '2'i_end_col               = '14'i_end_row               = '50000'sheet_name              = sheet_name "指定SHEET名TABLESintern                  = gt_uploadEXCEPTIONSinconsistent_parameters = 1upload_ole              = 2OTHERS                  = 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