ABAP excel 数据上传函数改造

    TYPES:      tty_alsmex_tabline TYPE TABLE OF zsalsmex_tabline2 .    TYPES ty_d_itabvalue TYPE zsalsmex_tabline2-value .    TYPES:*      internal table containing the excel data      ty_t_itab      TYPE TABLE OF zsalsmex_tabline2 .    TYPES:*      line type of sender table      BEGIN OF ty_s_senderline,        line(4096) TYPE c,      END OF ty_s_senderline .    TYPES:*      sender table      ty_t_sender TYPE TABLE OF ty_s_senderline .

  METHOD alsm_excel_to_internal_table.
    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 OTHERS.          e_return = fill_sy_message( sy ).RETURN.*        WHEN OTHERS. "raise upload_ole.      ENDCASE.    END-OF-DEFINITION.
***  Fetch file address    IF filename IS INITIAL.      filename = get_file_address( ).    ENDIF.
* 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.
    me->separated_to_intern_convert( EXPORTING  i_separator = ld_separator                                                i_tab =  excel_tab                                     IMPORTING  i_intern =  intern ).
* 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.


  ENDMETHOD.

  METHOD separated_to_intern_convert.
    DATA: l_sic_tabix LIKE sy-tabix,          l_sic_col   TYPE kcd_ex_col.    DATA: l_fdpos     LIKE sy-fdpos.    DATA:cw_intern TYPE zsalsmex_tabline2.
    CLEAR cw_intern.
    LOOP AT i_tab INTO DATA(cw_tab).      l_sic_tabix = sy-tabix.      l_sic_col = 0.      WHILE cw_tab CA i_separator.        l_fdpos = sy-fdpos.        l_sic_col = l_sic_col + 1.
        me->line_to_cell_separat( EXPORTING i_row  = l_sic_tabix                                            ch_cell_col = l_sic_col                                            i_separator = i_separator                                            i_fdpos     = l_fdpos                                  CHANGING  c_line = cw_tab                                            e_intern = i_intern ).
      ENDWHILE.      IF cw_tab <> space.        CLEAR cw_intern.        cw_intern-row = l_sic_tabix.        cw_intern-col = l_sic_col + 1.        cw_intern-value = cw_tab.        APPEND cw_intern TO i_intern.      ENDIF.    ENDLOOP.

  ENDMETHOD.

  METHOD line_to_cell_separat.
    DATA: l_string   TYPE ty_s_senderline.    DATA  l_sic_int  TYPE i.    DATA:i_intern TYPE zsalsmex_tabline2.    DATA: l_int         TYPE i,          l_cell_end(2).    FIELD-SYMBOLS: <l_cell> TYPE any.
    CLEAR i_intern.    l_sic_int = i_fdpos.    i_intern-row = i_row.    l_string = c_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) = '"'.
      l_cell_end = '"'.      l_cell_end+1 = i_separator .
      IF l_string CS '"'.        l_string = l_string+1.        IF l_string CS l_cell_end.          l_int = sy-fdpos.          ASSIGN l_string(l_int) TO <l_cell>.          i_intern-value = <l_cell>.          l_int = l_int + 2.          l_sic_int = l_int.          l_string = l_string+l_int.        ELSEIF l_string CS '"'.*     letzte Celle          l_int = sy-fdpos.          ASSIGN l_string(l_int) TO <l_cell>.          i_intern-value = <l_cell>.          l_int = l_int + 1.          l_sic_int = l_int.          l_string = l_string+l_int.          l_int = strlen( l_string ).          IF l_int > 0 . MESSAGE x001(kx) . ENDIF.        ELSE.          MESSAGE x001(kx) . "was ist mit csv-Format        ENDIF.      ENDIF.
    ELSE.      IF l_sic_int > 0.        i_intern-value = c_line(l_sic_int).      ENDIF.      IF l_sic_int = 0.        i_intern-value = ''.      ENDIF.    ENDIF.    IF l_sic_int > 0.      APPEND i_intern TO e_intern.    ENDIF.    IF l_sic_int = 0.      APPEND i_intern TO e_intern.    ENDIF.
    l_sic_int = l_sic_int + 1.    c_line = c_line+l_sic_int.
  ENDMETHOD.

  METHOD upload_excel_data.*************************************************************************示例:***    DATA:gt_upload_data like table of zsmm056.***    zcl_devp_tool=>get_instance( )->upload_excel_data( EXPORTING im_path      = p_file***                                                                 im_begin_col = 1***                                                                 im_begin_row = 4***                                                                 im_end_col   = 12***                                                                 im_end_row   = 50000***                                                                 im_sturc     = 'ZSMM056'***                                                       IMPORTING ex_tab       = gt_upload_data ).**********************************************************************    FIELD-SYMBOLS <fs_excel_value> TYPE any.    DATA:lt_excel TYPE TABLE OF zsalsmex_tabline2,         ls_excel TYPE zsalsmex_tabline2.    DATA:lv_conversion_no_number TYPE REF TO cx_sy_conversion_no_number.    DATA:ex_tab_cl   TYPE REF TO data,         ex_tab_area TYPE REF TO data.    FIELD-SYMBOLS <fs_ex_tab> TYPE STANDARD TABLE.
    alsm_excel_to_internal_table( EXPORTING filename    = im_path                                            i_begin_col = im_begin_col                                            i_begin_row = im_begin_row                                            i_end_col   = im_end_col                                            i_end_row   = im_end_row                                  IMPORTING e_return    = e_return                                            intern      = lt_excel[]  ).
    IF e_return-type = 'E'  .      EXIT.    ELSE.
      CHECK lt_excel IS NOT INITIAL.      CREATE DATA ex_tab_cl TYPE TABLE OF (im_sturc).      ASSIGN ex_tab_cl->* TO <fs_ex_tab>.      CREATE DATA ex_tab_area TYPE (im_sturc).      ASSIGN ex_tab_area->* TO FIELD-SYMBOL(<fs_ex_tab_area>).
      LOOP AT lt_excel INTO ls_excel.
        ASSIGN COMPONENT ls_excel-col OF STRUCTURE <fs_ex_tab_area> TO <fs_excel_value>.
        TRY .            <fs_excel_value> = ls_excel-value.          CATCH cx_sy_conversion_no_number INTO lv_conversion_no_number.            e_return-type = 'E'.            e_return-message = lv_conversion_no_number->get_text( ).            RETURN.        ENDTRY.
        AT END OF row.          APPEND <fs_ex_tab_area> TO <fs_ex_tab>.          CLEAR <fs_ex_tab_area>.        ENDAT.
      ENDLOOP.
      ex_tab = <fs_ex_tab>.    ENDIF.
  ENDMETHOD.