ABAP中读取EXCEL中不同的SHEET数据_SAP刘梦_新浪博客

有时候因为各种原因吧，需要读取多个sheet页数据，

比如导入数据量过大时，印象里excel导入，每个sheet页能读到的最大行数是4999.

超过这个数，就得放到第二个sheet页再继续读。

如果需要读多个sheet页，可以参考下面的自建fm

FUNCTION ZALSM_EXCEL_TO_INTERNAL_TABLE .

*"----------------------------------------------------------------------

*"*"Local interface:

*"  IMPORTING

*"     VALUE(FILENAME) LIKE  RLGRAP-FILENAME

*"     VALUE(I_BEGIN_COL) TYPE  I

*"     VALUE(I_BEGIN_ROW) TYPE  I

*"     VALUE(I_END_COL) TYPE  I

*"     VALUE(I_END_ROW) TYPE  I

*"     VALUE(IV_SHEET_NAME) TYPE  ALSMEX_TABLINE-VALUE OPTIONAL

*"  TABLES

*"      INTERN STRUCTURE  ALSMEX_TABLINE

*"  EXCEPTIONS

*"      INCONSISTENT_PARAMETERS

*"      UPLOAD_OLE

*"----------------------------------------------------------------------

  DATA: excel_tab     TYPE  ty_t_sender.

  DATA: ld_separator  TYPE  c.

  DATA: application   TYPE  ole2_object,

        workbook      TYPE  ole2_object,

        range         TYPE  ole2_object,

        worksheet     TYPE  ole2_object.

  DATA: h_cell        TYPE  ole2_object,

        h_cell1       TYPE  ole2_object.

  DATA:

    ld_rc             TYPE i.

*   Rückgabewert der Methode "clipboard_export     "

* Makro für Fehlerbehandlung der Methods

  DEFINE m_message.

    case sy-subrc.

      when 0.

      when 1.

        message id sy-msgid type sy-msgty number sy-msgno

                with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

      when others. raise upload_ole.

    endcase.

  END-OF-DEFINITION.

* check parameters

  IF i_begin_row > i_end_row. RAISE inconsistent_parameters. ENDIF.

  IF i_begin_col > i_end_col. RAISE inconsistent_parameters. ENDIF.

* Get TAB-sign for separation of fields

  CLASS cl_abap_char_utilities DEFINITION LOAD.

  ld_separator = cl_abap_char_utilities=>horizontal_tab.

* open file in Excel

  IF application-header = space OR application-handle = -1.

    CREATE OBJECT application 'Excel.Application'.

    m_message.

  ENDIF.

  CALL METHOD  OF application    'Workbooks' = workbook.

  m_message.

  CALL METHOD  OF workbook 'Open'    EXPORTING #1 = filename.

  m_message.

*-----delete by jinj

**  set property of application 'Visible' = 1.

**  m_message.

*  GET PROPERTY OF  application 'ACTIVESHEET' = worksheet.

*  m_message.

*-----end delete

*--ADD JINJ-----------------------------------------------

**  set property of application 'Visible' = 1.

**  m_message.

  if IV_SHEET_NAME = SPACE."用默认模式

    get property of  APPLICATION 'ACTIVESHEET' = WORKSHEET.

    M_MESSAGE.

  else.

*-->可以实现读取多个sheet

    call method of APPLICATION 'WORKSHEETS' = WORKSHEET

      exporting

        #1 = IV_SHEET_NAME.

    call method of WORKSHEET 'Activate'.

    M_MESSAGE.

  endif.

*---END OF------------------------------------------------

* mark whole spread sheet

  CALL METHOD OF worksheet 'Cells' = h_cell

      EXPORTING #1 = i_begin_row #2 = i_begin_col.

  m_message.

  CALL METHOD OF worksheet 'Cells' = h_cell1

      EXPORTING #1 = i_end_row #2 = i_end_col.

  m_message.

  CALL METHOD  OF worksheet 'RANGE' = range

                 EXPORTING #1 = h_cell #2 = h_cell1.

  m_message.

  CALL METHOD OF range 'SELECT'.

  m_message.

* copy marked area (whole spread sheet) into Clippboard

  CALL METHOD OF range 'COPY'.

  m_message.

* read clipboard into ABAP

  CALL METHOD cl_gui_frontend_services=>clipboard_import

    IMPORTING

      data                 = excel_tab

    EXCEPTIONS

      cntl_error           = 1

*      ERROR_NO_GUI         = 2

*      NOT_SUPPORTED_BY_GUI = 3

      OTHERS               = 4

          .

  IF sy-subrc <> 0.

     MESSAGE a037(alsmex).

  ENDIF.

  PERFORM separated_to_intern_convert TABLES excel_tab intern

                                      USING  ld_separator.

* clear clipboard

  REFRESH excel_tab.

  CALL METHOD cl_gui_frontend_services=>clipboard_export

     IMPORTING

        data                 = excel_tab

     CHANGING

        rc                   = ld_rc

     EXCEPTIONS

        cntl_error           = 1

*       ERROR_NO_GUI         = 2

*       NOT_SUPPORTED_BY_GUI = 3

        OTHERS               = 4

          .

* quit Excel and free ABAP Object - unfortunately, this does not kill

* the Excel process

  CALL METHOD OF application 'QUIT'.

  m_message.

* >>>>> Begin of change note 575877

* to kill the Excel process it's necessary to free all used objects

  FREE OBJECT h_cell.       m_message.

  FREE OBJECT h_cell1.      m_message.

  FREE OBJECT range.        m_message.

  FREE OBJECT worksheet.    m_message.

  FREE OBJECT workbook.     m_message.

  FREE OBJECT application.  m_message.

* <<<<< End of change note 575877

ENDFUNCTION.

欢迎关注SAP学习记录公众号：

saplearning