感谢@汪子熙 ,@

两位大佬的文章

我这里写了一个Demo,实现了本地上传Excel并解析为内表,支持通过sheet页名称,sheet页顺序号导入单sheet页。为了方便sheet页名称的指定,这里选择屏幕不直接使用类型,而是使用了lower case的数据元素。这样默认的进入SAP后,不会变成,这样会无法完成上传的。

个人感觉这个方法应该封装成函数,这样返回的内表可以是动态的,或者修改类,返回的数据是解析到的众单元格数据(类似的返回表)。

基本解析步骤是先根据上传路径,将xlsx格式的Excel文档,上传成格式的字符串,然后使用这个接口将字符串解析成xml文档,然后指定sheet页,获取到所有单元格的值,最后解析到内表当中。

对类修改后,可以轻松实现后台解析excel的功能。

*&---------------------------------------------------------------------*
*& Report ZZXLSX
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zzxlsx.
CLASS zxlsx DEFINITION.
  PUBLIC SECTION.
    DATA error_text TYPE string.
    METHODS import_document_from_frontend
      IMPORTING  pi_filename     TYPE /iwbep/sb_odata_ty_char_400sm
                 pi_sheetid      TYPE i OPTIONAL
                 pi_sheetname    TYPE /iwbep/sb_odata_ty_char_400sm OPTIONAL
                 check_structure TYPE char1"检查内表结构和文档是否一致
                 start_row       TYPE i"正文从第几行开始
      RETURNING  VALUE(pt_tab)   TYPE zstpp_fm_003_itab
      EXCEPTIONS file_open_error.
ENDCLASS.
CLASS zxlsx IMPLEMENTATION.
  "下列代码实现了Excel 单个sheet页的导入 代码可以直接使用
  METHOD import_document_from_frontend.
*create a xlsx handler
    DATA(xlsxhandler) = cl_ehfnd_xlsx=>get_instance( )."创建了句柄
*open xlsx into xstring
    TRY.
        DATA im_file_name TYPE string.
        im_file_name = pi_filename.
        DATA(xstring_excel) = cl_openxml_helper=>load_local_file( im_file_name ). "获取excel路径 为 xstring格式
      CATCH cx_openxml_not_found INTO DATA(openxml_not_found).
        error_text = openxml_not_found->get_text( ).
    ENDTRY.
    IF error_text IS NOT INITIAL.
      MESSAGE e001(00) RAISING file_open_error WITH error_text.
    ENDIF.
*load the xlsx
    TRY.
        DATA(xlsxdocument) = xlsxhandler->load_doc( iv_file_data = xstring_excel ). "加载excel
      CATCH cx_openxml_format INTO DATA(openxml_format).
        error_text = openxml_format->get_text( ).
      CATCH cx_openxml_not_allowed INTO DATA(openxml_not_allowed).
        error_text = openxml_not_allowed->get_text( ).
      CATCH cx_dynamic_check INTO DATA(dynamic_check).
        error_text = dynamic_check->get_text( ).
    ENDTRY.
    IF error_text IS NOT INITIAL.
      MESSAGE e001(00) RAISING file_open_error WITH error_text.
    ENDIF.
*获取指定sheet名称或者id或者第一个sheet页数据
    TRY.
        IF pi_sheetid IS NOT INITIAL.
          DATA(firstsheet) = xlsxdocument->get_sheet_by_id( pi_sheetid ).
        ELSEIF pi_sheetname IS NOT INITIAL.
          DATA iv_sheet_name TYPE string.
          iv_sheet_name = pi_sheetname.
          firstsheet = xlsxdocument->get_sheet_by_name( iv_sheet_name ).
        ELSE.
          firstsheet = xlsxdocument->get_sheet_by_id( 1 ).
        ENDIF.
      CATCH cx_openxml_format  INTO openxml_format.
        error_text = openxml_format->get_text( ).
        error_text = |Error occurs when extract data from specific sheet: | && error_text.
      CATCH cx_openxml_not_found  INTO openxml_not_found.
        error_text = openxml_not_found->get_text( ).
        error_text = |Error occurs when extract data from specific sheet: | && error_text .
      CATCH cx_dynamic_check  INTO dynamic_check.
        error_text = |Error occurs when extract data from specific sheet: | && dynamic_check->get_text( )  .
    ENDTRY.
    IF error_text IS NOT INITIAL.
      MESSAGE e001(00) RAISING file_open_error WITH error_text.
    ENDIF.
    "return if no sheet in xlsx file
    CHECK NOT firstsheet IS INITIAL.
    "check file structure, first line of excel file
    DATA(columncount) = firstsheet->get_last_column_number_in_row( 1 ). "获取第1行列数 (一共几列)
    DATA column TYPE i VALUE 1.
    "data tablecomponents type cl_abap_structdescr=>component_table.
*get the components of structure
    DATA lw_tab_ref    TYPE REF TO data.
    CREATE DATA lw_tab_ref LIKE LINE OF pt_tab.
    DATA tablestructure TYPE REF TO cl_abap_structdescr.
    tablestructure ?= cl_abap_typedescr=>describe_by_data_ref( lw_tab_ref ). "获取内表的 components
    DATA(tablecomponents) = tablestructure->get_components( ).
    "get the content of excel.
    TYPES: BEGIN OF columninfo,
             column     TYPE i,
             columnname TYPE string,
           END OF columninfo.
    TYPES columnsinfo TYPE STANDARD TABLE OF columninfo WITH EMPTY KEY.
    DATA columnfromfile TYPE columnsinfo.
    IF check_structure = abap_on.
*    get the title row  compare with tab structure if need
      DO columncount TIMES.
        DATA(cellvalue) = firstsheet->get_cell_content(
                                  EXPORTING
                                    iv_row     = 1
                                    iv_column  = column ).
        APPEND INITIAL LINE TO columnfromfile ASSIGNING FIELD-SYMBOL().
        -column = column.
        -columnname = cellvalue.
        IF line_exists( tablecomponents[ name = cellvalue ] ).
          DELETE tablecomponents WHERE name = cellvalue.
        ELSE.
          error_text = error_text && |,{ cellvalue }|.
        ENDIF.
        column = column + 1.
      ENDDO.
      IF error_text IS NOT INITIAL.
        MESSAGE e001(00) RAISING file_open_error WITH error_text.
      ENDIF.
    ENDIF.
*    get the title row  compare with tab structure if need
    "last row
    DATA(rowcount) = firstsheet->get_last_row_number( ).
    DATA(currentrow) = start_row.
*get data of excel
    CASE check_structure.
      WHEN abap_on.
        "按照字段对应获取数据
        WHILE currentrow <= rowcount.
          APPEND INITIAL LINE TO pt_tab ASSIGNING FIELD-SYMBOL().
          LOOP AT columnfromfile REFERENCE INTO DATA(currentcolumn).
            cellvalue = firstsheet->get_cell_content( EXPORTING iv_row = currentrow
                                     iv_column  = currentcolumn->*-column ).
            ASSIGN COMPONENT currentcolumn->*-columnname OF STRUCTURE  TO FIELD-SYMBOL().
             = cellvalue.
          ENDLOOP.
          currentrow = currentrow + 1.
        ENDWHILE.
      WHEN OTHERS.
        "按照顺序获取数据
        CLEAR column.
        WHILE currentrow <= rowcount.
          APPEND INITIAL LINE TO pt_tab ASSIGNING .
          DO columncount TIMES.
            column = column + 1.
            cellvalue = firstsheet->get_cell_content(
            EXPORTING iv_row = currentrow
                      iv_column  = column ).
            ASSIGN COMPONENT column OF STRUCTURE  TO .
             = cellvalue.
          ENDDO.
          CLEAR column.
          currentrow = currentrow + 1.
        ENDWHILE.
    ENDCASE.
  ENDMETHOD.
ENDCLASS.
PARAMETERS: file  TYPE /iwbep/sb_odata_ty_char_400sm,
            sheet TYPE /iwbep/sb_odata_ty_char_400sm NO-DISPLAY,
            id    TYPE i NO-DISPLAY,
            check TYPE char1 AS CHECKBOX,
            row   TYPE i.
START-OF-SELECTION.
*  sheet = 'Sheet1'.
  DATA lr TYPE REF TO zxlsx.
  DATA itab TYPE zstpp_fm_003_itab.
  CREATE OBJECT lr.
  itab = lr->import_document_from_frontend(
               EXPORTING pi_filename     = file
                         pi_sheetname    = sheet
                         pi_sheetid      = id
                         check_structure = check
                         start_row       = row ).

excel文档实例: