记录ABAP开发的日常——读取EXCEL文件sheet页签的数据

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

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

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

2.创建示例程序

*&---------------------------------------------------------------------*
*& Report ZDEMO_EXCEL_UPLOAD
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zdemo_excel_upload.

**********************************************************************
* PROGRAM DETAIL                                                     *
**********************************************************************
* --T-CODE:ZDEMO_EXCEL_UPLOAD
* --AUTHOR:秋の夕
* --CREATE DATE:17.12.2021 15:28:56
* --DESCRIBLE:EXCEL 文件上传sheet页测试
**********************************************************************
* EDIT LOG                                                           *
**********************************************************************
* VERSION          DATE              AUTHOR           REMARK
* <DEVK90XXX>     <YYYY/MM/DD>       <...>            <REASON>

**********************************************************************
* DECLARATIONS                                                       *
**********************************************************************
*TABLES: .

*CONSTANTS: .

TYPES:BEGIN OF ty_data,
        id       TYPE i,
        name(10) TYPE c,
      END OF  ty_data.

DATA: gt_data  TYPE TABLE OF ty_data WITH HEADER LINE.  "SHEET1:ABC"
DATA: gt_data2 TYPE TABLE OF ty_data WITH HEADER LINE.  "SHEET1:DEF"

**********************************************************************
* SELECTION SCREEN                                                   *
**********************************************************************
SELECTION-SCREEN: BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-001.
  PARAMETERS: p_file LIKE rlgrap-filename OBLIGATORY.

*  SELECT-OPTIONS: .

SELECTION-SCREEN: END OF BLOCK b1.


**********************************************************************
* INITIALIZATION                                                     *
**********************************************************************
INITIALIZATION.


**********************************************************************
* AT SELECTION SCREEN                                                *
**********************************************************************
AT SELECTION-SCREEN.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  PERFORM frm_get_filename USING p_file.

**********************************************************************
* TOP OF PAGE                                                        *
**********************************************************************
TOP-OF-PAGE.


**********************************************************************
* END OF PAGE                                                        *
**********************************************************************
END-OF-PAGE.


**********************************************************************
* AT USER COMMAND                                                    *
**********************************************************************
AT USER-COMMAND.


**********************************************************************
* START OF SELECTION                                                 *
**********************************************************************
START-OF-SELECTION.
  PERFORM frm_upoload_file TABLES gt_data USING 'ABC'.
  PERFORM frm_upoload_file TABLES gt_data2 USING 'DEF'.

**********************************************************************
* END OF SELECTION                                                   *
**********************************************************************
END-OF-selection.
  IF gt_data[] IS NOT INITIAL.
    WRITE:/ |SHEET1:ABC数据|.
    LOOP AT gt_data.
      WRITE: / |{ gt_data-id } { gt_data-name }|.
    ENDLOOP.
  ENDIF.

  IF gt_data2[] IS NOT INITIAL.
    WRITE:/ |SHEET2:DEF数据|.
    LOOP AT gt_data2.
      WRITE: / |{ gt_data2-id } { gt_data2-name }|.
    ENDLOOP.
  ENDIF.


**********************************************************************
* FORMS DECLARATIONS                                                 *
**********************************************************************
FORM frm_get_filename  USING  uv_file.

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

ENDFORM.                    "GET_FILENAME


FORM frm_upoload_file TABLES lt_input TYPE STANDARD TABLE USING sheet_name.

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

  CALL FUNCTION 'ZFM_EXCEL_TO_INTERNAL_TABLE'       "这里的EXCEL上传函数是已经按照上一篇随笔做了SHEET处理的"
    EXPORTING
      filename                = p_file
      i_begin_col             = 1
      i_begin_row             = 2
      i_end_col               = 6
      i_end_row               = 50000
      i_sheet_name            = sheet_name
    TABLES
      intern                  = lt_upload
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.


  IF sy-subrc = 0.
*--->处理读取出的数据
    SORT lt_upload BY row col.
    LOOP AT lt_upload.
      ls_i = lt_upload-col.
      ASSIGN COMPONENT ls_i OF STRUCTURE lt_input TO <f>.
      <f> = lt_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

3.效果展示

这里面要注意的事项：如果不指定I_SHEET_NAME参数，默认读取第一个SHEET页；如果指定I_SHEET_NAME参数，参数值一定要跟EXCEL文件里的SHEET名对上，不然读取不到数据。