前言：在项目中碰到过这样的需求，用户的EXCEL文件模板里面有多个SHEET页，每个SHEET页的数据是不一样的，每一份数据对应单独的需求，这样我们就要根据具体的业务需求读取相应SHEET页的数据，在我的上一篇随笔扩展EXCEL上载函数中提到过如何自定义EXCEL上载函数针对指定SHEET页，这里就演示下怎么用该函数读取SHEET页中的数据。

1.创建一份EXCEL文件(包含数据)

示例如下： SHEET1:ABC 和SHEET2:DEF

2.读取sheet页签 - 图1
2.读取sheet页签 - 图2

2. 创示例程序:

  1. *&---------------------------------------------------------------------*
  2. *& Report ZDEMO_EXCEL_UPLOAD
  3. *&---------------------------------------------------------------------*
  4. *&
  5. *&---------------------------------------------------------------------*
  6. REPORT zdemo_excel_upload.
  7. **********************************************************************
  8. * PROGRAM DETAIL                                                     *
  9. **********************************************************************
  10. * --T-CODE:ZDEMO_EXCEL_UPLOAD
  11. * --AUTHOR:GJBOY
  12. * --CREATE DATE:17.12.2021 15:28:56
  13. * --DESCRIBLE:EXCEL 文件上传sheet页测试
  14. **********************************************************************
  15. * EDIT LOG                                                           *
  16. **********************************************************************
  17. * VERSION          DATE              AUTHOR           REMARK
  18. * <DEVK90XXX>     <YYYY/MM/DD>       <...>            <REASON>
  20. **********************************************************************
  21. * DECLARATIONS                                                       *
  22. **********************************************************************
  23. *TABLES: .
  24. *CONSTANTS: .
  25. TYPES:BEGIN OF ty_data,
  26.         id       TYPE i,
  27.         name(10) TYPE c,
  28.       END OF  ty_data.
  30. DATA: gt_data  TYPE TABLE OF ty_data WITH HEADER LINE.  "SHEET1:ABC"
  31. DATA: gt_data2 TYPE TABLE OF ty_data WITH HEADER LINE.  "SHEET1:DEF"
  32. **********************************************************************
  33. * SELECTION SCREEN                                                   *
  34. **********************************************************************
  35. SELECTION-SCREEN: BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-001.
  36.   PARAMETERS: p_file LIKE rlgrap-filename OBLIGATORY.
  37. *  SELECT-OPTIONS: .
  38. SELECTION-SCREEN: END OF BLOCK b1.
  39. **********************************************************************
  40. * INITIALIZATION                                                     *
  41. **********************************************************************
  42. INITIALIZATION.
  44. **********************************************************************
  45. * AT SELECTION SCREEN                                                *
  46. **********************************************************************
  47. AT SELECTION-SCREEN.
  48. AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  49.   PERFORM frm_get_filename USING p_file.
  51. **********************************************************************
  52. * TOP OF PAGE                                                        *
  53. **********************************************************************
  54. TOP-OF-PAGE.
  55. **********************************************************************
  56. * END OF PAGE                                                        *
  57. **********************************************************************
  58. END-OF-PAGE.
  59. **********************************************************************
  60. * AT USER COMMAND                                                    *
  61. **********************************************************************
  62. AT USER-COMMAND.
  63. **********************************************************************
  64. * START OF SELECTION                                                 *
  65. **********************************************************************
  66. START-OF-SELECTION.
  67.   PERFORM frm_upoload_file TABLES gt_data USING 'ABC'.
  68.   PERFORM frm_upoload_file TABLES gt_data2 USING 'DEF'.
  70. **********************************************************************
  71. * END OF SELECTION                                                   *
  72. **********************************************************************
  73. END-OF-selection.
  74.   IF gt_data[] IS NOT INITIAL.
  75.     WRITE:/ |SHEET1:ABC数据|.
  76.     LOOP AT gt_data.
  77.       WRITE: / |{ gt_data-id } { gt_data-name }|.
  78.     ENDLOOP.
  79.   ENDIF.
  80.   IF gt_data2[] IS NOT INITIAL.
  81.     WRITE:/ |SHEET2:DEF数据|.
  82.     LOOP AT gt_data2.
  83.       WRITE: / |{ gt_data2-id } { gt_data2-name }|.
  84.     ENDLOOP.
  85.   ENDIF.
  86. **********************************************************************
  87. * FORMS DECLARATIONS                                                 *
  88. **********************************************************************
  89. FORM frm_get_filename  USING  uv_file.
  90.   "出现打开文件的对话框
  91.   CALL FUNCTION 'WS_FILENAME_GET'
  92.     EXPORTING
  93.       mask             = ',*.*,*.*.'
  94.       mode             = 'O' "S为保存，O为打开
  95.     IMPORTING
  96.       filename         = uv_file
  97.     EXCEPTIONS
  98.       inv_winsys       = 1
  99.       no_batch         = 2
  100.       selection_cancel = 3
  101.       selection_error  = 4
  102.       OTHERS           = 5.
  103. ENDFORM.                    "GET_FILENAME
  104. FORM frm_upoload_file TABLES lt_input TYPE STANDARD TABLE USING sheet_name.
  105.   DATA lt_upload  LIKE TABLE OF zalsmex_tabline WITH HEADER LINE.
  106.   DATA: ls_i TYPE i.
  107.   FIELD-SYMBOLS <f>.
  108.   CALL FUNCTION 'ZFM_EXCEL_TO_INTERNAL_TABLE'       "这里的EXCEL上传函数是已经按照上一篇随笔做了SHEET处理的"
  109.     EXPORTING
  110.       filename                = p_file
  111.       i_begin_col             = 
  112.       i_begin_row             = 2
  113.       i_end_col               = 6
  114.       i_end_row               = 50000
  115.       i_sheet_name            = sheet_name
  116.     TABLES
  117.       intern                  = lt_upload
  118.     EXCEPTIONS
  119.       inconsistent_parameters = 1
  120.       upload_ole              = 2
  121.       OTHERS                  = 3.
  122.   IF sy-subrc = 0.
  123. *--->处理读取出的数据
  124.     SORT lt_upload BY row col.
  125.     LOOP AT lt_upload.
  126.       ls_i = lt_upload-col.
  127.       ASSIGN COMPONENT ls_i OF STRUCTURE lt_input TO <f>.
  128.       <f> = lt_upload-value.
  129.       AT END OF row.
  130.         APPEND lt_input TO lt_input.
  131.         CLEAR:lt_input.
  132.       ENDAT.
  133.     ENDLOOP.
  134.   ELSE.
  135.     WRITE: / 'EXCEL UPLOAD FAILED ', p_file, sy-subrc.
  136.   ENDIF.
  137. ENDFORM.                    " UPOLOAD_FILE

3.效果图:

2.读取sheet页签 - 图3