ABAP ALSM_EXCEL_TO_INTERNAL_TABLE函数重新设置和SMW0上载文件下载函数

ABAP ALSM_EXCEL_TO_INTERNAL_TABLE函数重新设置和SMW0上载文件下载函数

一.重新封装原因
1、 每个CELL只能导入前50个字符；
2、 如果超过9999行，行号会初始化为从零开始。

二.修改函数中的表 对应结构ZALSMEX_TABLINE

ZALSMEX_TABLINE 详细结构：

函数导入：

函数导出：为空

三.源代码

*"----------------------------------------------------------------------"
*"*"本地接口：
*"  IMPORTING
*"     REFERENCE(FILENAME) TYPE  RLGRAP-FILENAME
*"     REFERENCE(I_BEGIN_COL) TYPE  I
*"     REFERENCE(I_BEGIN_ROW) TYPE  I
*"     REFERENCE(I_END_COL) TYPE  I
*"     REFERENCE(I_END_ROW) TYPE  I
*"  TABLES
*"      INTERN STRUCTURE  ZALSMEX_TABLINE
*"  EXCEPTIONS
*"      INCONSISTENT_PARAMETERS
*"      UPLOAD_OLE
*"----------------------------------------------------------------------
TYPE-POOLS: ole2.

*      value of excel-cell
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 '"'.

FUNCTION ZALSM_EXCEL_TO_INTERNAL_TABLE .
*"--------------------------------------------------------------------"
*"*"局部接口：
*"  IMPORTING
*"     REFERENCE(FILENAME) TYPE  RLGRAP-FILENAME
*"     REFERENCE(I_BEGIN_COL) TYPE  I
*"     REFERENCE(I_BEGIN_ROW) TYPE  I
*"     REFERENCE(I_END_COL) TYPE  I
*"     REFERENCE(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.


*&---------------------------------------------------------------------*
*&      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
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->I_INTERN     text
*      -->I_LINE       text
*      -->I_ROW        text
*      -->CH_CELL_COL  text
*      -->I_SEPARATOR  text
*      -->I_FDPOS      text
*----------------------------------------------------------------------*
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

点击激活，便可以使用了。

SMW0上载文件下载函数：

1.导入：

2.导出：

3.源代码

FUNCTION ZFUN_XLSDOWNLOAD .
*"--------------------------------------------------------------------
*"*"局部接口：
*"  IMPORTING
*"     VALUE(I_OBJID) TYPE  W3OBJID
*"     VALUE(I_NAME) TYPE  STRING DEFAULT '模板'
*"  EXPORTING
*"     VALUE(E_FULLPATH) TYPE  STRING
*"--------------------------------------------------------------------"

*  DATA: query_str TYPE w3query OCCURS 0 WITH HEADER LINE.
  DATA : lv_objdata  LIKE wwwdatatab.
  DATA : thtml TYPE w3html OCCURS 0 WITH HEADER LINE,
         tmime TYPE w3mime OCCURS 0 WITH HEADER LINE.
  DATA : user_action TYPE i.
  DATA : return_code    LIKE  w3param-ret_code,
         content_type   LIKE  w3param-cont_type,
         content_length LIKE  w3param-cont_len.
  DATA : lv_filename TYPE string,
         lv_file     TYPE rlgrap-filename,
         lv_path     TYPE string,
         lv_fullpath TYPE string.
  DATA : l_rc  TYPE sy-subrc,
         l_msg TYPE text100. "消息"


  CALL METHOD cl_gui_frontend_services=>file_save_dialog
    EXPORTING
      default_file_name         = i_name
      default_extension         = 'xlsx'
*     default_extension         = 'xls'
    CHANGING
      filename                  = lv_filename
      path                      = lv_path
      fullpath                  = lv_fullpath
      user_action               = user_action
    EXCEPTIONS
      cntl_error                = 1
      error_no_gui              = 2
      not_supported_by_gui      = 3
      invalid_default_file_name = 4
      OTHERS                    = 5.
  IF sy-subrc = 0.
    e_fullpath = lv_fullpath.
*   Implement suitable error handling here
  ELSE.

  ENDIF.
  IF user_action EQ cl_gui_frontend_services=>action_cancel.
    MESSAGE '用户取消操作！' TYPE 'S' DISPLAY LIKE 'W'. "用户取消操作
    EXIT.
  ENDIF.

  SELECT relid objid
      FROM wwwdata
      INTO  CORRESPONDING FIELDS OF lv_objdata
      UP TO 1 ROWS
      WHERE srtf2 = 0 AND relid = 'MI'
        AND objid = i_objid.
  ENDSELECT.

  IF lv_objdata IS INITIAL.
    CLEAR : l_msg.
    l_msg = '模板' && i_objid && '不存在!'.
    MESSAGE l_msg TYPE 'S' DISPLAY LIKE 'E'.
    EXIT.
  ENDIF.

  IF lv_fullpath IS NOT INITIAL.
    DATA : lr_destina TYPE rlgrap-filename.
    MOVE lv_fullpath TO lr_destina.
    CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
      EXPORTING
        key         = lv_objdata
        destination = lr_destina
      IMPORTING
        rc          = l_rc
*     CHANGING
*       TEMP        =
      .
    IF l_rc = 0.
      MESSAGE '下载成功!' TYPE 'S'.
    ELSE.
      MESSAGE '下载失败!' TYPE 'S' DISPLAY LIKE 'E'.
      EXIT.
    ENDIF.
  ENDIF.


ENDFUNCTION.

参考链接：
EXCEL上传函数ALSM_EXCEL_TO_INTERNAL_TABLE的重新封装-一毛