【ABAP】ole2多sheet导入类

方便复用，实际就是ole2方式

1、SE24创建类
2、复制代码至相应位置

*----------------------------------------------------------------------*
*       CLASS ZCL_TAB_DOC  DEFINITIO
*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
CLASS zcl_tab_doc DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC

  GLOBAL FRIENDS cl_gui_frontend_services .

*"* public components of class ZCL_TAB_DOC
*"* do not include other source files here!!!
  PUBLIC SECTION.

    TYPE-POOLS: abap.

    TYPES:
        BEGIN OF ty_sheetname_info,
          sheetname        TYPE string,
          startrow         TYPE i,
          startcol         TYPE i,
          every_copy_lines TYPE i,
          endcol           TYPE i,
        END OF ty_sheetname_info .
    TYPES:
      tt_sheetname_info TYPE STANDARD TABLE OF ty_sheetname_info .
    TYPES:
      ty_char30000 TYPE c LENGTH 30000 .
    TYPES:
      tt_char30000 TYPE TABLE OF ty_char30000 .

    DATA:
      dt_excel_context TYPE STANDARD TABLE OF ty_char30000 .
    TYPES:
      BEGIN OF ty_tabc,
        sheetname TYPE string, "sheet名称
        context   LIKE dt_excel_context, "sheet页的数据
      END OF ty_tabc .
    TYPES:
      tt_tabc TYPE STANDARD TABLE OF ty_tabc .

    "保存文件
    CLASS-METHODS file_save_dialog_default
      IMPORTING
        value(i_window_title)      TYPE string DEFAULT 'Select Download Path'
        value(i_default_extension) TYPE string DEFAULT 'xlsx'
        value(i_default_filename)  TYPE string DEFAULT 'Download'
        !i_with_encoding           TYPE abap_bool OPTIONAL
        value(i_file_filter)       TYPE string DEFAULT 'Excel(*.xlsx)|*.xlsx|Excel(*.xlsm)|*.xlsm|Excel 97-2003(*.xls)|*.xls'
        value(i_initial_directory) TYPE string OPTIONAL
        !i_prompt_on_overwrite     TYPE abap_bool DEFAULT 'X'
      CHANGING
        !c_filename                TYPE string
        !c_path                    TYPE string
        !c_fullpath                TYPE string
        !c_user_action             TYPE i OPTIONAL
        !c_file_encoding           TYPE abap_encoding OPTIONAL
      EXCEPTIONS
        cntl_error
        error_no_gui
        not_supported_by_gui
        invalid_default_file_name .
    "读取文件
    CLASS-METHODS file_open_dialog_default
      IMPORTING
        value(i_window_title)      TYPE string DEFAULT 'Select Upload File'
        value(i_default_extension) TYPE string DEFAULT '.xlsx|.xls'
        value(i_default_filename)  TYPE string OPTIONAL
        value(i_file_filter)       TYPE string DEFAULT 'Excel(*.xlsx)|*.xlsx|Excel(*.xlsm)|*.xlsm|Excel 97-2003(*.xls)|*.xls'
        value(i_with_encoding)     TYPE abap_bool OPTIONAL
        value(i_initial_directory) TYPE string OPTIONAL
        value(i_multiselection)    TYPE abap_bool DEFAULT ''
      CHANGING
        !ct_file_table             TYPE filetable
        !c_rc                      TYPE i
        !c_user_action             TYPE i OPTIONAL
        !c_file_encoding           TYPE abap_encoding OPTIONAL
      EXCEPTIONS
        file_open_dialog_failed
        cntl_error
        error_no_gui
        not_support_by_gui .
    "导入excel到内表
    CLASS-METHODS import_excel_by_ole
      IMPORTING
        value(i_filename)   TYPE string
      CHANGING
        value(ct_sheetname) TYPE tt_sheetname_info
        value(ct_tabc)      TYPE tt_tabc
      EXCEPTIONS
        file_open_error .
    "EXCEL 内容转化为 内表-(由调用程序决定)
    METHODS excel_convert_table
      IMPORTING
        value(i_separator)       TYPE abap_char1 DEFAULT cl_abap_char_utilities=>horizontal_tab
        value(is_table)          TYPE ty_tabc
        value(i_repid)           TYPE sy-repid DEFAULT sy-repid
        value(i_special_process) TYPE string OPTIONAL"special process form
      CHANGING
        !ct_table                TYPE STANDARD TABLE
      EXCEPTIONS
        context_convert_failed .

*"* protected components of class ZCL_TAB_DOC
*"* do not include other source files here!!!
protected section.
  "单元格的特殊处理
    METHODS excel_convert_special_process
      IMPORTING
        !is_componet TYPE abap_compdescr OPTIONAL
        !i_row       TYPE i
        !i_col       TYPE i
      CHANGING
        !c_cell      TYPE string
      EXCEPTIONS
        convert_error .

*"* private components of class ZCL_TAB_DOC
*"* do not include other source files here!!!
private section.
  DATA m_special_process_form TYPE string.
    DATA m_repid TYPE sy-repid VALUE sy-repid.

下面是4个方法

method FILE_SAVE_DIALOG_DEFAULT.
  CALL METHOD cl_gui_frontend_services=>file_save_dialog
      EXPORTING
        window_title              = i_window_title
        default_extension         = i_default_extension
        default_file_name         = i_default_filename
        with_encoding             = i_with_encoding
        file_filter               = i_file_filter
        initial_directory         = i_initial_directory
        prompt_on_overwrite       = i_prompt_on_overwrite
      CHANGING
        filename                  = c_filename
        path                      = c_path
        fullpath                  = c_fullpath
        user_action               = c_user_action
        file_encoding             = c_file_encoding
      EXCEPTIONS
        cntl_error                = 1
        error_no_gui              = 2
        not_supported_by_gui      = 3
        invalid_default_file_name = 4
        OTHERS                    = 5.
    IF sy-subrc <> 0.
      CASE sy-subrc.
        WHEN 1.
          RAISE cntl_error.
        WHEN 2.
          RAISE error_no_gui.
        WHEN 3.
          RAISE not_supported_by_gui.
        WHEN 4.
          RAISE invalid_default_file_name.
        WHEN OTHERS.
      ENDCASE.
    ENDIF.
endmethod.

method FILE_OPEN_DIALOG_DEFAULT.
  CALL METHOD cl_gui_frontend_services=>file_open_dialog
      EXPORTING
        window_title            = i_window_title
        default_extension       = i_default_extension
        default_filename        = i_default_filename
        file_filter             = i_file_filter
        with_encoding           = i_with_encoding
        initial_directory       = i_initial_directory
        multiselection          = i_multiselection
      CHANGING
        file_table              = ct_file_table
        rc                      = c_rc
        user_action             = c_user_action
        file_encoding           = c_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.
      CASE sy-subrc.
        WHEN 1.
          RAISE file_open_dialog_failed.
        WHEN 2.
          RAISE cntl_error.
        WHEN 3.
          RAISE error_no_gui.
        WHEN 4.
          RAISE not_support_by_gui.
        WHEN OTHERS.
      ENDCASE.
    ENDIF.
endmethod.

这个方法比较灵活，未传入sheet名也可以全部读取

METHOD import_excel_by_ole.
  DATA lw_tabc TYPE ty_tabc.
  DATA lt_tabc LIKE TABLE OF lw_tabc.

  DATA:
    lv_column_num TYPE i,
    lv_column_skp TYPE i,
    lw_tab_ref    TYPE REF TO data.

  TYPE-POOLS:
    ole2.

  DATA:
    ole_excel      TYPE ole2_object,
    ole_workbooks  TYPE ole2_object,
    ole_workbook   TYPE ole2_object,
    ole_worksheets TYPE ole2_object,
    ole_worksheet  TYPE ole2_object,
    ole_cell_begin TYPE ole2_object,
    ole_cell_end   TYPE ole2_object,
    ole_range      TYPE ole2_object.
  DATA:
    lv_subrc     TYPE sy-subrc,
    lv_begin_col TYPE i,
    lv_end_col   TYPE i,
    lv_begin_row TYPE i,
    lv_end_row   TYPE i,
    lv_add_rows  TYPE i VALUE 3000. "默认，每次COPY3000行

  DATA:
    lt_excel_tab     TYPE STANDARD TABLE OF ty_char30000,
    lw_excel_tab     TYPE ty_char30000,
    lw_excel_tab_tmp TYPE ty_char30000.

*->生成Excel object
  CREATE OBJECT ole_excel 'Excel.Application'.
  IF sy-subrc <> 0.
    MESSAGE e368(00) WITH 'file_open_failed' RAISING file_open_error.
  ENDIF.

  SET PROPERTY OF ole_excel 'Visible' = 0.

  GET PROPERTY OF ole_excel 'Workbooks' = ole_workbooks.

  CALL METHOD OF
    ole_workbooks
    'Open' = ole_workbook
    EXPORTING
    #1 = i_filename. "文件名

*->get Sheet
  DATA lv_process_text TYPE string.
  DATA l_count TYPE i.
  l_count = LINES( ct_sheetname )."Sheets 名称

  DATA ls_sheetname LIKE LINE OF ct_sheetname.
  DATA l_tabix TYPE sy-tabix.

  IF ct_sheetname[] IS NOT INITIAL.
    LOOP AT ct_sheetname INTO ls_sheetname.
      l_tabix = sy-tabix.
*      DATA(l_percentage) = l_tabix / l_count * 100.
*      ##NO_TEXT
*      lv_process_text = 'Uploading Sheet:' && ls_sheetname-sheetname.
*
*      CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
*        EXPORTING
*          percentage = l_percentage
*          text       = lv_process_text.

      IF ls_sheetname-sheetname IS NOT INITIAL.
        GET PROPERTY OF ole_workbook 'Worksheets' = ole_worksheets
          exporting
            #1 = ls_sheetname-sheetname.
        lw_tabc-sheetname = ls_sheetname-sheetname.
      ELSE.
        CALL METHOD OF ole_excel 'Worksheets' = ole_worksheets
          EXPORTING #1 = l_tabix.
      ENDIF.

      CALL METHOD OF ole_worksheets 'Activate'.

      IF lw_tabc-sheetname IS INITIAL.
        GET PROPERTY OF ole_workbook 'Worksheets' = ole_worksheets
         exporting
           #1 = l_tabix.

        GET PROPERTY OF ole_worksheets 'Name' = lw_tabc-sheetname.
      ENDIF.

      lv_begin_col = ls_sheetname-startcol."开始列
      lv_end_col = ls_sheetname-endcol."开始行
      lv_begin_row = 0.
      lv_end_row = 0.
      lv_add_rows = ls_sheetname-every_copy_lines.

      CLEAR lv_subrc.

      WHILE lv_subrc IS INITIAL.

        IF lv_begin_row IS INITIAL.
          lv_begin_row = ls_sheetname-startrow.
          lv_end_row   = lv_add_rows.
        ELSE.
          lv_begin_row = lv_begin_row + lv_add_rows.
          lv_end_row   = lv_end_row   + lv_add_rows.
        ENDIF.

        CALL METHOD OF
          ole_worksheets
          'Cells' = ole_cell_begin
          EXPORTING
          #1 = lv_begin_row
          #2 = lv_begin_col.

        CALL METHOD OF
          ole_worksheets
          'Cells' = ole_cell_end
          EXPORTING
          #1 = lv_end_row
          #2 = lv_end_col.

        CALL METHOD OF
          ole_worksheets
          'RANGE' = ole_range
          EXPORTING
          #1 = ole_cell_begin
          #2 = ole_cell_end.

        CALL METHOD OF
          ole_range
          'SELECT'.
        IF sy-subrc <> 0.
          EXIT.
        ENDIF.

        CALL METHOD OF
          ole_range
          'COPY'.

* read clipboard into ABAP
        CALL METHOD cl_gui_frontend_services=>clipboard_import
          IMPORTING
            data                 = lt_excel_tab
          EXCEPTIONS
            cntl_error           = 1
            error_no_gui         = 2
            not_supported_by_gui = 3
            OTHERS               = 4.
        IF sy-subrc <> 0.
          CALL METHOD OF
            ole_excel
            'QUIT'.
          "##no_text
          MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1
        sy-msgv2 sy-msgv3 sy-msgv4.
        ENDIF.

        lv_subrc = 4.
        LOOP AT lt_excel_tab INTO lw_excel_tab.
          lw_excel_tab_tmp = lw_excel_tab.
          REPLACE ALL OCCURRENCES OF cl_abap_char_utilities=>horizontal_tab IN lw_excel_tab_tmp WITH space.
          IF NOT ( lw_excel_tab_tmp = space OR lw_excel_tab_tmp IS INITIAL ).
            APPEND lw_excel_tab TO lw_tabc-context[].
            CLEAR lv_subrc.
          ENDIF.
        ENDLOOP.
        CLEAR lt_excel_tab.

      ENDWHILE.

      DATA: lv_rc TYPE i.

*      clear clipboard
      CALL METHOD cl_gui_frontend_services=>clipboard_export
        IMPORTING
          data                 = lt_excel_tab
        CHANGING
          rc                   = lv_rc
        EXCEPTIONS
          cntl_error           = 1
          error_no_gui         = 2
          not_supported_by_gui = 3
          OTHERS               = 4.
      IF sy-subrc NE 0.
        MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1
        sy-msgv2 sy-msgv3 sy-msgv4.
      ENDIF.
      APPEND lw_tabc TO lt_tabc.
      CLEAR:lw_tabc.

      CALL METHOD OF
        ole_worksheets
        'COLSE'.

    ENDLOOP.
  ELSE.
    " 未配置sheet
    CALL METHOD OF ole_excel 'WORKSHEETS' = ole_worksheets.
    GET PROPERTY OF ole_worksheets 'COUNT' = l_count.

    l_tabix = 0.
    DO l_count TIMES.
      l_tabix = l_tabix + 1.

      CALL METHOD OF ole_excel 'Worksheets' = ole_worksheets
        EXPORTING #1 = l_tabix.

      CALL METHOD OF ole_worksheets 'Activate'.

      GET PROPERTY OF ole_workbook 'Worksheets' = ole_worksheets
          exporting
            #1 = l_tabix.

      GET PROPERTY OF ole_worksheets 'Name' = lw_tabc-sheetname.



      lv_begin_col = 1.
      lv_end_col = 999.
      lv_begin_row = 1.
      lv_end_row = 999.
      lv_add_rows = 999.
      CLEAR lv_subrc.

*        IF lv_begin_row IS INITIAL.
*          lv_begin_row = 1.
*          lv_end_row   = lv_add_rows.
*        ELSE.
*          lv_begin_row = lv_begin_row + lv_add_rows.
*          lv_end_row   = lv_end_row   + lv_add_rows.
*        ENDIF.

      CALL METHOD OF
        ole_worksheets
        'Cells' = ole_cell_begin
        EXPORTING
        #1 = lv_begin_row
        #2 = lv_begin_col.

      CALL METHOD OF
        ole_worksheets
        'Cells' = ole_cell_end
        EXPORTING
        #1 = lv_end_row
        #2 = lv_end_col.

      CALL METHOD OF
        ole_worksheets
        'RANGE' = ole_range
        EXPORTING
        #1 = ole_cell_begin
        #2 = ole_cell_end.

      CALL METHOD OF
        ole_range
        'SELECT'.
      IF sy-subrc <> 0.
        EXIT.
      ENDIF.

      CALL METHOD OF
        ole_range
        'COPY'.

* read clipboard into ABAP
      CALL METHOD cl_gui_frontend_services=>clipboard_import
        IMPORTING
          data                 = lt_excel_tab
        EXCEPTIONS
          cntl_error           = 1
          error_no_gui         = 2
          not_supported_by_gui = 3
          OTHERS               = 4.
      IF sy-subrc <> 0.
        CALL METHOD OF
          ole_excel
          'QUIT'.
        "##no_text
        MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1
      sy-msgv2 sy-msgv3 sy-msgv4.
      ENDIF.

      lv_subrc = 4.
      LOOP AT lt_excel_tab INTO lw_excel_tab.
        lw_excel_tab_tmp = lw_excel_tab.
        REPLACE ALL OCCURRENCES OF cl_abap_char_utilities=>horizontal_tab IN lw_excel_tab_tmp WITH space.
        IF NOT ( lw_excel_tab_tmp = space OR lw_excel_tab_tmp IS INITIAL ).
          APPEND lw_excel_tab TO lw_tabc-context[].
          CLEAR lv_subrc.
        ENDIF.
      ENDLOOP.
      CLEAR lt_excel_tab.

*      clear clipboard
      CALL METHOD cl_gui_frontend_services=>clipboard_export
        IMPORTING
          data                 = lt_excel_tab
        CHANGING
          rc                   = lv_rc
        EXCEPTIONS
          cntl_error           = 1
          error_no_gui         = 2
          not_supported_by_gui = 3
          OTHERS               = 4.
      IF sy-subrc NE 0.
        MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1
        sy-msgv2 sy-msgv3 sy-msgv4.
      ENDIF.
      APPEND lw_tabc TO lt_tabc.
      CLEAR:lw_tabc.

      CALL METHOD OF
        ole_worksheets
        'COLSE'.

    ENDDO.

  ENDIF.

  ct_tabc = lt_tabc.


  CALL METHOD OF
    ole_excel
    'QUIT'.
  FREE OBJECT:
    ole_excel      ,
    ole_workbooks  ,
    ole_workbook   ,
    ole_worksheets ,
    ole_worksheet  ,
    ole_cell_begin ,
    ole_cell_end   ,
    ole_range      .
ENDMETHOD.

METHOD excel_convert_table.
  DATA lv_row TYPE i.
  DATA lv_col TYPE i.
  DATA lv_tabix TYPE i.
  DATA: lt_cell TYPE TABLE OF string,
        ls_cell TYPE string.
  DATA:
    lo_cx_root TYPE REF TO cx_root.
  DATA
    lr_descr TYPE REF TO cl_abap_structdescr.

  DATA lr_line TYPE REF TO data.
  FIELD-SYMBOLS: <value> TYPE ANY,
                 <line>  TYPE ANY.
  CREATE DATA lr_line LIKE LINE OF ct_table.
  ASSIGN lr_line->* TO <line>.

  lr_descr ?= cl_abap_typedescr=>describe_by_data( <line> ).

  "m_repid m_special_process
  m_repid = i_repid.
  m_special_process_form = i_special_process.
  DATA ls_context TYPE ty_char30000.
  DATA ls_component TYPE abap_compdescr.
  data lv_text TYPE char100.
  LOOP AT is_table-context INTO ls_context.
    lv_row = sy-tabix.
    SPLIT ls_context AT cl_abap_char_utilities=>horizontal_tab INTO TABLE lt_cell.
    LOOP AT lt_cell INTO ls_cell.
      lv_tabix = sy-tabix."DISPOSAL CURRENT ROW
      lv_col = lv_tabix.
      READ TABLE lr_descr->components INTO ls_component INDEX lv_tabix.
      IF sy-subrc EQ 0.

        CALL METHOD me->excel_convert_special_process
          EXPORTING
            is_componet = ls_component "字段信息
            i_row       = lv_row "行
            i_col       = lv_col "列
          CHANGING
            c_cell      = ls_cell.

        ASSIGN COMPONENT lv_col OF STRUCTURE <line> TO <value>.
        IF sy-subrc EQ 0.
          TRY.
              <value> = ls_cell.
            CATCH cx_root INTO lo_cx_root.
              "In sheet &1,the data format of Row &2 Field &3 is not right
              lv_text = 'error'."'in SHEET' && is_table-sheetname && ',the data format of row' && lv_row && 'field ' && ls_component-name && 'is not right'.
              "data(lv_text) = |in SHEET{ is_table-sheetname },the data format of row { lv_row } field { ls_component-name } is not right|.
              MESSAGE e368(00) WITH lv_text '' RAISING context_convert_failed.
          ENDTRY.
        ENDIF.
      ENDIF.
    ENDLOOP.

    APPEND <line> TO ct_table.
    CLEAR <line>.
  ENDLOOP.
ENDMETHOD.

METHOD excel_convert_special_process.
  CHECK m_special_process_form IS NOT INITIAL.
  PERFORM (m_special_process_form) IN PROGRAM (m_repid) IF FOUND
  USING is_componet i_row i_col CHANGING c_cell.
ENDMETHOD.