自建的excel上载（BW无对应函数）_SAP刘梦_新浪博客

因为最近在bw开发，bw没有'ALSM_EXCEL_TO_INTERNAL_TABLE'

所以考出来一份。

本来在bw查到一个 UPLOAD_XLS_FILE_2_ITAB 。但是没法用。

因为标准的上载函数，内表结构 value也是 char50.所以这里定义的char50.

但是，如果excel单元格数据确实比较长呢？

建议定义成char255. 防止单元格数据过长而被截取丢失。

把fm放到这里，以便copy使用

其中使用了自建结构：

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

*"  TABLES

*"      INTERN STRUCTURE  ZALSMEX_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.

*  set property of application 'Visible' = 1.

*  m_message.

  GET PROPERTY OF  application 'ACTIVESHEET' = worksheet.

  m_message.

* 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.

最后帮朋友推广个网站，美食推荐的，他一个人每天回家加班写的，一期写完了，二期三期待上呢

一个人写的，不容易

http://www.joycate.com/