SAP ABAP ZALSM_EXCEL_TO_INTERNAL_TABLE 完整实施的源代码、附件、说明

SAP ABAP ZALSM_EXCEL_TO_INTERNAL_TABLE 完整实施的源代码、附件、说明

引言： 分享 ZALSM_EXCEL_TO_INTERNAL_TABLE 完整实施的源代码、附件，并对一些关键点作出说明。文末有关于 ABAP to Excel 的文章列表。

关键字：SAP ABAP Excel ZALSM_EXCEL OLE2

---

1. 实施效果

2. 说明

• 这份源代码缺少了Sheet Name 读取的部分
• 实现中有不完美的地方，如 ZALSMEX_TABLINE 结构组件类型未使用数据元素
• 其他不影响，可实施后直接使用

3. 源代码

FUNCTION ZALSM_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
*"  TABLES
*"      INTERN STRUCTURE  ZALSMEX_TABLINE
*"  EXCEPTIONS
*"      INCONSISTENT_PARAMETERS
*"      UPLOAD_OLE
*"----------------------------------------------------------------------
  TYPE-POOLS: OLE2.
  TYPES: TY_D_ITABVALUE TYPE ZALSMEX_TABLINE-VALUE,
         TY_T_ITAB      TYPE ZALSMEX_TABLINE   OCCURS 0,
         BEGIN OF TY_S_SENDERLINE,
           LINE(4096) TYPE C,
         END OF TY_S_SENDERLINE,
         TY_T_SENDER TYPE TY_S_SENDERLINE  OCCURS 0.
  CONSTANTS:  GC_ESC              VALUE '"'.

  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.

*&---------------------------------------------------------------------*
*&      Form  separated_to_intern_convert
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->I_TAB        text
*      -->I_INTERN     text
*      -->I_SEPARATOR  text
*----------------------------------------------------------------------*
TYPES: TY_D_ITABVALUE TYPE ZALSMEX_TABLINE-VALUE,
*      internal table containing the excel data
       TY_T_ITAB      TYPE ZALSMEX_TABLINE   OCCURS 0,

*      line type of sender table
       BEGIN OF TY_S_SENDERLINE,
         LINE(4096) TYPE C,
       END OF TY_S_SENDERLINE,
*      sender table
       TY_T_SENDER TYPE TY_S_SENDERLINE  OCCURS 0.

CONSTANTS:  GC_ESC              VALUE '"'.

*&---------------------------------------------------------------------*
*&      Form  SEPARATED_TO_INTERN_CONVERT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->I_TAB        text
*      -->I_INTERN     text
*      -->I_SEPARATOR  text
*----------------------------------------------------------------------*
FORM SEPARATED_TO_INTERN_CONVERT TABLES I_TAB       TYPE TY_T_SENDER
                                        I_INTERN    TYPE TY_T_ITAB
                                 USING  I_SEPARATOR TYPE C.
  DATA: L_SIC_TABIX LIKE SY-TABIX,
        L_SIC_COL   TYPE KCD_EX_COL.
  DATA: L_FDPOS     LIKE SY-FDPOS.
  REFRESH I_INTERN.

  LOOP AT I_TAB.
    L_SIC_TABIX = SY-TABIX.
    L_SIC_COL = 0.
    WHILE I_TAB CA I_SEPARATOR.
      L_FDPOS = SY-FDPOS.
      L_SIC_COL = L_SIC_COL + 1.
      PERFORM LINE_TO_CELL_SEPARAT TABLES I_INTERN
                                   USING  I_TAB L_SIC_TABIX L_SIC_COL
                                          I_SEPARATOR L_FDPOS.
    ENDWHILE.
    IF I_TAB <> SPACE.
      CLEAR I_INTERN.
      I_INTERN-ROW = L_SIC_TABIX.
      I_INTERN-COL = L_SIC_COL + 1.
      I_INTERN-VALUE = I_TAB.
      APPEND I_INTERN.
    ENDIF.
  ENDLOOP.
ENDFORM.                    " SEPARATED_TO_INTERN_CONVERT
*---------------------------------------------------------------------*
FORM LINE_TO_CELL_SEPARAT TABLES I_INTERN    TYPE TY_T_ITAB
                          USING  I_LINE
                                 I_ROW       LIKE SY-TABIX
                                 CH_CELL_COL TYPE KCD_EX_COL
                                 I_SEPARATOR TYPE C
                                 I_FDPOS     LIKE SY-FDPOS.
  DATA: L_STRING   TYPE TY_S_SENDERLINE.
  DATA  L_SIC_INT  TYPE I.

  CLEAR I_INTERN.
  L_SIC_INT = I_FDPOS.
  I_INTERN-ROW = I_ROW.
  L_STRING = I_LINE.
  I_INTERN-COL = CH_CELL_COL.
* csv Dateien mit separator in Zelle: --> ;"abc;cd";
  IF ( I_SEPARATOR = ';' OR  I_SEPARATOR = ',' ) AND
       L_STRING(1) = GC_ESC.
    PERFORM LINE_TO_CELL_ESC_SEP USING L_STRING
                                       L_SIC_INT
                                       I_SEPARATOR
                                       I_INTERN-VALUE.
  ELSE.
    IF L_SIC_INT > 0.
      I_INTERN-VALUE = I_LINE(L_SIC_INT).
    ENDIF.
  ENDIF.
  IF L_SIC_INT > 0.
    APPEND I_INTERN.
  ENDIF.
  L_SIC_INT = L_SIC_INT + 1.
  I_LINE = I_LINE+L_SIC_INT.
ENDFORM.                    "line_to_cell_separat

*---------------------------------------------------------------------*
FORM LINE_TO_CELL_ESC_SEP USING I_STRING
                                I_SIC_INT      TYPE I
                                I_SEPARATOR    TYPE C
                                I_INTERN_VALUE TYPE TY_D_ITABVALUE.
  DATA: L_INT         TYPE I,
        L_CELL_END(2).
  FIELD-SYMBOLS: <L_CELL>.
  L_CELL_END = GC_ESC.
  L_CELL_END+1 = I_SEPARATOR .

  IF I_STRING CS GC_ESC.
    I_STRING = I_STRING+1.
    IF I_STRING CS L_CELL_END.
      L_INT = SY-FDPOS.
      ASSIGN I_STRING(L_INT) TO <L_CELL>.
      I_INTERN_VALUE = <L_CELL>.
      L_INT = L_INT + 2.
      I_SIC_INT = L_INT.
      I_STRING = I_STRING+L_INT.
    ELSEIF I_STRING CS GC_ESC.
*     letzte Celle
      L_INT = SY-FDPOS.
      ASSIGN I_STRING(L_INT) TO <L_CELL>.
      I_INTERN_VALUE = <L_CELL>.
      L_INT = L_INT + 1.
      I_SIC_INT = L_INT.
      I_STRING = I_STRING+L_INT.
      L_INT = STRLEN( I_STRING ).
      IF L_INT > 0 . MESSAGE X001(KX) . ENDIF.
    ELSE.
      MESSAGE X001(KX) . "was ist mit csv-Format
    ENDIF.
  ENDIF.

ENDFORM.                    "line_to_cell_esc_sep

• 增强后的数据结构 ZALSMEX_TABLINE
  
• 原标准数据结构 ALSMEX_TABLINE
  

4. 完整实施附件

注意：非新手开发可以不看完整实施附件自己完成实施。

• 资源链接
  SAP ABAP ZALSM_EXCEL_TO_INTERNAL_TABLE 完整实施附件（源代码、说明）

5. 注意事项

• 增强后的数据结构 ZALSMEX_TABLINE
  • 能满足行列大小不够的问题
  • 能满足 Cell Value 长度不够的问题
• 这份源代码没有实现 Sheet Name的读取
  需要的自己加上，
  
• 如果用于生产，实施时注意各项开发规范

6. 相关文章合集

SAP ABAP 我的文章合集：ABAP and Excel

---

其他

我的微信群、QQ群和其他发布平台。

• 关于微信群、QQ群和其他发布平台

长期提供开发和运维服务。

• 关于提供开发和运维服务的详情