ABAP EXCEL上载

---

前言

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、封装上载函数

基于SAP标准函数ALSM_EXCEL_TO_INTERNAL_TABLE，重新封装。

FUNCTION Z_ALSM_EXCEL_TO_INTERNAL_TABLE .
*"----------------------------------------------------------------------
*"*"本地接口：
*"  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(I_SHEET_NAME) TYPE  CHAR50 OPTIONAL
*"  TABLES
*"      INTERN STRUCTURE  ZBCS_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'.
    "WPS无法导入问题
    IF SY-SUBRC NE 0.
      CREATE OBJECT APPLICATION 'ET.Application'.
    ENDIF.
    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.
*-----------------------------------------------------------------------*
*--CHANGED BY YANGSEN
*-----------------------------------------------------------------------*
  IF I_SHEET_NAME = SPACE."用默认模式
    GET PROPERTY OF  APPLICATION 'ACTIVESHEET' = WORKSHEET.
    M_MESSAGE.
  ELSE.
*-->可以实现读取多个sheet
    CALL METHOD OF APPLICATION 'WORKSHEETS' = WORKSHEET
    EXPORTING
      #1 = I_SHEET_NAME.

    CALL METHOD OF WORKSHEET 'Activate'.
    M_MESSAGE.
  ENDIF.

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

*--------------------------------------------------------------------*
"重复从剪切板读取数据
  DO 10 TIMES.
* 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).
    ELSE.
      IF EXCEL_TAB[] IS INITIAL.
        WAIT UP TO 1 SECONDS.
      ELSE.
        EXIT.
      ENDIF.
    ENDIF.
  ENDDO.


  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.

二、函数调用

 DATA: LT_EXCEL TYPE STANDARD TABLE OF ZBCS_ALSMEX_TABLINE, "导入文件数据内表
        LV_ROWNO TYPE I.                               "导入模板excel行号

  CALL FUNCTION 'Z_ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      FILENAME                = P_FNAME
      I_BEGIN_COL             = 1
      I_BEGIN_ROW             = 5
      I_END_COL               = 28
      I_END_ROW               = 65535
    TABLES
      INTERN                  = LT_EXCEL
    EXCEPTIONS
      INCONSISTENT_PARAMETERS = 1
      UPLOAD_OLE              = 2
      OTHERS                  = 3.
  IF SY-SUBRC <> 0. "读取EXCEL 数据失败
    MESSAGE TEXT-M09 TYPE ZIF_BC_CONS=>CNS_E.
  ELSE.
    LOOP AT LT_EXCEL INTO DATA(LS_EXCEL).
      ASSIGN COMPONENT LS_EXCEL-COL OF STRUCTURE GS_OUT TO FIELD-SYMBOL(<LFS_DATA>).
      CHECK SY-SUBRC EQ 0 .
      <LFS_DATA> = LS_EXCEL-VALUE.
      AT END OF ROW.
        APPEND GS_OUT TO GT_OUT.
        CLEAR GS_OUT.
      ENDAT.

    ENDLOOP.
  ENDIF.

三、解决问题

WPS导入问题

EXCEL 剪贴板问题

---