ABAP--一個讀取EXCEL單元格的內容超過256個字元的程式碼樣例

weixin_30924079發表於2020-04-04

我們在sap上限初期經常會將EXCEL資料倒入到SAP,sap也為此封裝了許多的函式,但是這些函式都有單元文字長度限制。具體介紹如下:

1、  'TEXT_CONVERT_XLS_TO_SAP'單元格文字不能超過256個字元
   CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
      i_line_header              = 'X'
      i_tab_raw_data             = lt_raw
      i_filename                 = p_file
    TABLES
      i_tab_converted_data       = it_data
    EXCEPTIONS
      conversion_failed          = 1
      OTHERS                     = 2
  .

2、'ALSM_EXCEL_TO_INTERNAL_TABLE'單元格文字不能超過50個字元
data: gt_upload type alsmex_tabline occurs 0 with header line.
      call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
        exporting
          filename    = p_file
          i_begin_col = '1'
          i_begin_row = '1'
          i_end_col   = '9'
          i_end_row   = '50000'
        tables
          intern      = gt_upload.

3、自程式設計序倒入EXCEL,單元格文字長度可以自定義,程式碼如下(實現的功能和'ALSM_EXCEL_TO_INTERNAL_TABLE類似):
REPORT RSDEMO01 NO STANDARD PAGE HEADING.
* this report demonstrates how to send some ABAP data to an
* EXCEL sheet using OLE automation.
TYPE-POOLS: ole2.
**TYPE UPLOAD TABLE
TYPES: BEGIN OF ZLSMEX_TABLINE,
          row TYPE KCD_EX_ROW_N,
          COL TYPE KCD_EX_COL_N,
          VALUE(500),
       END OF ZLSMEX_TABLINE.
DATA gt_upload  type TABLE OF ZLSMEX_TABLINE WITH HEADER LINE.

*      value of excel-cell
TYPES: ty_d_itabvalue             TYPE ZLSMEX_TABLINE-value,
*      internal table containing the excel data
       ty_t_itab                  TYPE ZLSMEX_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 '"'.
* handles for OLE objects
DATA: H_EXCEL       TYPE OLE2_OBJECT,        " Excel object
      workbooks     TYPE OLE2_OBJECT,         " list of workbooks
      workbook      TYPE OLE2_OBJECT,          " workbook
      worksheet     TYPE  ole2_object,
      H_CELL        TYPE OLE2_OBJECT,           " cell
      H_F           TYPE OLE2_OBJECT.            " font

DATA  H TYPE I.
*----------------------------------------------------------------------*
* Type Declaration
*----------------------------------------------------------------------*
TYPE-POOLS: slis, truxs.


PARAMETERS: p_file LIKE rlgrap-filename OBLIGATORY.
  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.
*&---------------------------------------------------------------------*
*&      Form  SEPARATED_TO_INTERN_CONVERT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        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
*&---------------------------------------------------------------------*
*&      Form  read_excel
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM zalsm_excel_to_internal_table
    TABLES
      INTERN  LIKE  gt_upload[]
    USING
       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.

  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

* 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
ENDForm.                    "zalsm_excel_to_internal_table

*&---------------------------------------------------------------------*
*&   Event START-OF-SELECTION
*&---------------------------------------------------------------------*
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
    EXPORTING
      mask      = ',Microsoft Excel Files,*.xls'
      static    = 'X'
    CHANGING
      file_name = p_file.
*&---------------------------------------------------------------------*
*&   Event START-OF-SELECTION
*&---------------------------------------------------------------------*
START-OF-SELECTION.
PERFORM ZALSM_EXCEL_TO_INTERNAL_TABLE
            TABLES
               gt_upload
            USING
                p_file
                1
                1
                9
                5000.
LOOP AT GT_UPLOAD.
  WRITE GT_UPLOAD-VALUE.
ENDLOOP.

轉載於:https://www.cnblogs.com/xiaomaohai/archive/2007/07/26/6157264.html

相關文章