SAP ABAP通过ABAP2XLSX上载读取excel数据到SAP

---

前言

在使用FM : ZALSM_EXCEL_TO_INTERNAL_TABLE时速度很慢，改用开源项目ABAP2XLSX去读取excel数据

---

一、代码

  DATA:lt_filetab  TYPE filetable,
       ls_filetab  TYPE file_table,
       lv_rc       TYPE i,
       lv_title    TYPE string,
*       lv_filename TYPE rlgrap-filename.
       lv_filename TYPE string.

  DATA:lo_reader    TYPE REF TO zif_excel_reader,
       lo_excel     TYPE REF TO zcl_excel,
       lo_worksheet TYPE REF TO zcl_excel_worksheet,
       lt_cell      TYPE zexcel_t_cell_data.

  lv_title = '选择文件'.
  CALL METHOD cl_gui_frontend_services=>file_open_dialog
    EXPORTING
      window_title            = lv_title    " Title Of File Open Dialog
*     default_extension       =     " Default Extension
*     default_filename        =     " Default File Name
      file_filter             = 'All Files (*.*)|*.xlsx|*.xls' " File Extension Filter String
*     with_encoding           =     " File Encoding
      initial_directory       = 'C:\'    " Initial Directory
      multiselection          = abap_false    " Multiple selections poss.  设置单选
    CHANGING
      file_table              = lt_filetab    " Table Holding Selected Files
      rc                      = lv_rc    " Return Code, Number of Files or -1 If Error Occurred
*     user_action             =     " User Action (See Class Constants ACTION_OK, ACTION_CANCEL)
*     file_encoding           =
    EXCEPTIONS
      file_open_dialog_failed = 1
      cntl_error              = 2
      error_no_gui            = 3
      not_supported_by_gui    = 4
      OTHERS                  = 5.
  IF sy-subrc <> 0.
*   MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
*              WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.

  LOOP AT lt_filetab INTO ls_filetab.
  

*    使用ABAP2XLSX读取excel
    CREATE OBJECT lo_reader TYPE zcl_excel_reader_2007.

*lo_excel = lo_readeer->load( i_excel2007 = lv_mime_file ).
    lv_filename = ls_filetab-filename.
    TRY.
        lo_excel = lo_reader->load_file(
        i_filename             = lv_filename"'C:\Users\CMCC-LAP\Documents\SAP\Download\EXCEL\公司间用平仓项目模板.XLSX'
*               i_use_alternate_zip    = SPACE
*               i_from_applserver      = SY-BATCH
*               iv_zcl_excel_classname =
        ).
      CATCH zcx_excel.  "
    ENDTRY.

    CHECK lo_excel IS BOUND.

*    获取数据
    CALL METHOD lo_excel->set_active_sheet_index_by_name
      EXPORTING
        i_worksheet_name = 'TESTSHEET'.    " Worksheets name   你要读取的sheet名

    lo_worksheet = lo_excel->get_active_worksheet( ).

    IF lo_worksheet IS NOT INITIAL.

      lt_cell = lo_worksheet->sheet_content.
      PERFORM frm_get_data_from_excel USING lt_cell p_zbukrs CHANGING gt_data.

    ENDIF.

  ENDLOOP.

FORM frm_get_data_from_excel  USING    it_cell TYPE zexcel_t_cell_data
                                       iv_bukrs TYPE bukrs
                              CHANGING ct_203i LIKE gt_data. "存放数据的内表

  DATA:
    ls_203i    LIKE gs_203_i,
    lv_cellrow TYPE zexcel_cell_row,
*        lv_filename  TYPE rlgrap-filename,
    lt_tab_hq  TYPE TABLE OF alsmex_tabline,
    lv_data_f  TYPE zexcel_number_format,
    lv_date    TYPE sy-datum,
    lv_je      TYPE bapicurr-bapicurr,
    lv_wears   TYPE waers.

  DELETE it_cell WHERE cell_row = 1.

  SELECT SINGLE waers INTO lv_wears
    FROM t001
    WHERE bukrs EQ iv_bukrs.

  LOOP AT it_cell INTO DATA(ls_cell).   "it_cell本身就是排序表  不需要再排序

    CASE ls_cell-cell_column.
      WHEN 1.
        ls_203i-waers = ls_cell-cell_value.
      WHEN 2.
        lv_je = ls_cell-cell_value.
        lv_je = abs( lv_je ).
        CALL FUNCTION 'BAPI_CURRENCY_CONV_TO_INTERNAL'   "金额转换为内部格式
          EXPORTING
            currency             = ls_203i-waers
            amount_external      = lv_je
            max_number_of_digits = 18
          IMPORTING
            amount_internal      = ls_203i-wrbtr
*           RETURN               =
          .
      WHEN 3.
        lv_je = ls_cell-cell_value.
        lv_je = abs( lv_je ).
        CALL FUNCTION 'BAPI_CURRENCY_CONV_TO_INTERNAL'   "金额转换为内部格式
          EXPORTING
            currency             = lv_wears
            amount_external      = lv_je
            max_number_of_digits = 18
          IMPORTING
            amount_internal      = ls_203i-dmbtr
*           RETURN               =
          .
      WHEN 4.
        ls_203i-zuonr = ls_cell-cell_value.
      WHEN 5.
        ls_203i-sgtxt = ls_cell-cell_value.
      WHEN 6.
        ls_203i-xblnr = ls_cell-cell_value.
      WHEN 7.
*        lv_data_f = lo_worksheet->get_default_excel_date_format( ).
        CALL METHOD zcl_excel_common=>excel_string_to_date   "针对日期的处理
          EXPORTING
            ip_value = ls_cell-cell_value
          RECEIVING
            ep_value = ls_203i-budat.
*      CATCH zcx_excel.    "
      WHEN OTHERS.
    ENDCASE.

    AT END OF cell_row.
      APPEND ls_203i TO ct_203i.
      CLEAR:ls_203i.
    ENDAT.

  ENDLOOP.

ENDFORM.

二、效果

截图

会先出现文件选择（设置了单选），确定后就会通过代码处理把数据放入内表GT_DATA

注意日期的处理：

读取到并不是excel中展示的日期格式，和ZALSM_EXCEL_TO_INTERNAL_TABLE有些不一样

需要通过方法转换一下就OK

关于日期格式读取不对的解释

---

总结

以上