前言:在日常的开发批导报表中，使用的excel上载标准函数ALSM_EXCEL_TO_INTERNAL_TABLE，其中存放数据的内表结构，行列数4位长度最大只支持到9999，这样在遇到一些数据量比较大的文件就不满足需求了，所以就需要对该函数进行扩展，保证它能够满足我们的需求。

扩展步骤：

1.复制标准函数ALSM_EXCEL_TO_INTERNAL_TABLE

扩展EXCEL上载函数 - 图1

2.编辑自定义函数

扩展EXCEL上载函数 - 图2
扩展EXCEL上载函数 - 图3

  1. TYPE-POOLS: ole2.
  2. *      value of excel-cell
  3. TYPES: ty_d_itabvalue             TYPE zalsmex_tabline-value,             "注意:若表结构更改了，这个也要改为对应的类型"
  4. *      internal table containing the excel data
  5.        ty_t_itab                  TYPE zalsmex_tabline   OCCURS 0,        "注意:若表结构更改了，这个也要改为对应的类型"
  6. *      line type of sender table
  7.        BEGIN OF ty_s_senderline,
  8.          line(4096)               TYPE c,
  9.        END OF ty_s_senderline,
  10. *      sender table
  11.        ty_t_sender                TYPE ty_s_senderline  OCCURS 0.
  12. *
  13. CONSTANTS:  gc_esc              VALUE '"'.

扩展EXCEL上载函数 - 图4
扩展EXCEL上载函数 - 图5

引用:INCLUDE LALSMEXF01.

扩展EXCEL上载函数 - 图6
扩展EXCEL上载函数 - 图7
扩展EXCEL上载函数 - 图8

结构的内容如下:

扩展EXCEL上载函数 - 图9
扩展EXCEL上载函数 - 图10

3.扩展sheet

如果还想让函数根据sheet 页来读取数据，需要在自定义函数里添加参数:

扩展EXCEL上载函数 - 图11

然后在程序源代码里加入以下代码：

扩展EXCEL上载函数 - 图12

4.附加函数代码

  1. FUNCTION zfm_excel_to_internal_table .
  2. *"----------------------------------------------------------------------
  3. *"*"本地接口：
  4. *"  IMPORTING
  5. *"     VALUE(FILENAME) LIKE  RLGRAP-FILENAME
  6. *"     VALUE(I_BEGIN_COL) TYPE  I
  7. *"     VALUE(I_BEGIN_ROW) TYPE  I
  8. *"     VALUE(I_END_COL) TYPE  I
  9. *"     VALUE(I_END_ROW) TYPE  I
  10. *"     VALUE(I_SHEET_NAME) TYPE  CHAR50 OPTIONAL
  11. *"  TABLES
  12. *"      INTERN STRUCTURE  ZALSMEX_TABLINE
  13. *"  EXCEPTIONS
  14. *"      INCONSISTENT_PARAMETERS
  15. *"      UPLOAD_OLE
  16. *"----------------------------------------------------------------------
  18.   DATA: excel_tab     TYPE  ty_t_sender.
  19.   DATA: ld_separator  TYPE  c.
  20.   DATA: application TYPE  ole2_object,
  21.         workbook    TYPE  ole2_object,
  22.         range       TYPE  ole2_object,
  23.         worksheet   TYPE  ole2_object.
  24.   DATA: h_cell  TYPE  ole2_object,
  25.         h_cell1 TYPE  ole2_object.
  26.   DATA:
  27.     ld_rc             TYPE i.
  28. *   Rückgabewert der Methode "clipboard_export     "
  29. * Makro für Fehlerbehandlung der Methods
  30.   DEFINE m_message.
  31.     CASE sy-subrc.
  32.       WHEN 0.
  33.       WHEN 1.
  34.         MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
  35.                 WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  36.       WHEN OTHERS. RAISE upload_ole.
  37.     ENDCASE.
  38.   END-OF-DEFINITION.
  40. * check parameters
  41.   IF i_begin_row > i_end_row. RAISE inconsistent_parameters. ENDIF.
  42.   IF i_begin_col > i_end_col. RAISE inconsistent_parameters. ENDIF.
  43. * Get TAB-sign for separation of fields
  44.   CLASS cl_abap_char_utilities DEFINITION LOAD.
  45.   ld_separator = cl_abap_char_utilities=>horizontal_tab.
  46. * open file in Excel
  47.   IF application-header = space OR application-handle = -1.
  48.     CREATE OBJECT application 'Excel.Application'.
  49.     m_message.
  50.   ENDIF.
  51.   CALL METHOD  OF application    'Workbooks' = workbook.
  52.   m_message.
  53.   CALL METHOD  OF workbook 'Open'    EXPORTING #1 = filename.
  54.   m_message.
  55. *  set property of application 'Visible' = 1.
  56. *  m_message.
  57. *<START>------「Add By ZYH Time: 17.12.2021 15:07:51」-------<START>*
  58.   IF i_sheet_name = space.
  59.     GET PROPERTY OF  application 'ACTIVESHEET' = worksheet.
  60.     m_message.
  61.   ELSE.
  62.     CALL METHOD OF application 'WORKSHEETS' = worksheet
  63.       EXPORTING #1 = i_sheet_name.
  64.     CALL METHOD OF worksheet 'Activate'.
  65.     m_message.
  66.   ENDIF.
  67. *<END>--------「Add By ZYH Time: 17.12.2021 15:07:51」---------<END>*
  68. * mark whole spread sheet
  69.   CALL METHOD OF worksheet 'Cells' = h_cell
  70.       EXPORTING #1 = i_begin_row #2 = i_begin_col.
  71.   m_message.
  72.   CALL METHOD OF worksheet 'Cells' = h_cell1
  73.       EXPORTING #1 = i_end_row #2 = i_end_col.
  74.   m_message.
  75.   CALL METHOD  OF worksheet 'RANGE' = range
  76.                  EXPORTING #1 = h_cell #2 = h_cell1.
  77.   m_message.
  78.   CALL METHOD OF range 'SELECT'.
  79.   m_message.
  80. * copy marked area (whole spread sheet) into Clippboard
  81.   CALL METHOD OF range 'COPY'.
  82.   m_message.
  84. * read clipboard into ABAP
  85.   CALL METHOD cl_gui_frontend_services=>clipboard_import
  86.     IMPORTING
  87.       data       = excel_tab
  88.     EXCEPTIONS
  89.       cntl_error = 1
  90. *     ERROR_NO_GUI         = 2
  91. *     NOT_SUPPORTED_BY_GUI = 3
  92.       OTHERS     = 4.
  93.   IF sy-subrc <> 0.
  94.     MESSAGE a037(alsmex).
  95.   ENDIF.
  96.   PERFORM separated_to_intern_convert TABLES excel_tab intern
  97.                                       USING  ld_separator.
  99. * clear clipboard
  100.   REFRESH excel_tab.
  101.   CALL METHOD cl_gui_frontend_services=>clipboard_export
  102.     IMPORTING
  103.       data       = excel_tab
  104.     CHANGING
  105.       rc         = ld_rc
  106.     EXCEPTIONS
  107.       cntl_error = 1
  108. *     ERROR_NO_GUI         = 2
  109. *     NOT_SUPPORTED_BY_GUI = 3
  110.       OTHERS     = 4.
  111. * quit Excel and free ABAP Object - unfortunately, this does not kill
  112. * the Excel process
  113.   CALL METHOD OF application 'QUIT'.
  114.   m_message.
  115. * >>>>> Begin of change note 575877
  116. * to kill the Excel process it's necessary to free all used objects
  117.   FREE OBJECT h_cell.       m_message.
  118.   FREE OBJECT h_cell1.      m_message.
  119.   FREE OBJECT range.        m_message.
  120.   FREE OBJECT worksheet.    m_message.
  121.   FREE OBJECT workbook.     m_message.
  122.   FREE OBJECT application.  m_message.
  123. * <<<<< End of change note 575877
  124. ENDFUNCTION.