ABAP to Upload Excel document into internal table on SAP system

朴灿烈づ我的快乐病毒、 2022-09-30 07:07 10阅读 0赞

转自： [http://www.sapdev.co.uk/file/file\_upexcelalt1.htm][http_www.sapdev.co.uk_file_file_upexcelalt1.htm]

ABAP code for uploading an Excel document into an internal table using function module KCD\_EXCEL\_OLE\_TO\_INT\_CONVERT. The code is based on uploading a [simple Excel spreadsheet][]. This code also works with the new MS excel format .xlsx. Be carefull though for some reason SAP have decided to use a table structure which only allows a length of 32 characters per spreadsheet cell.

*..............................................................
    *: Description                                                :
    *: -----------                                                :
    *: This is a simple example program to get data from an excel :
    *: file and store it in an internal table.                    :
    *:                                                            :
    *: Author : www.sapdev.co.uk                                  :
    *:                                                            :
    *: SAP Version : 4.7                                          :
    *:............................................................:
    REPORT zupload_excel_to_itab NO STANDARD PAGE HEADING.
    
    TYPES:   BEGIN OF t_datatab ,
             col1(25)  TYPE c,
             col2(30)  TYPE c,
             col3(30)  TYPE c,
             col4(30)  TYPE c,
             col5(30)  TYPE c,
             col6(30)  TYPE c,
             col7(30) TYPE c,
             col8(30)  TYPE c,
             col9(30)  TYPE c,
             col10(30)  TYPE c,
             col11(30)    TYPE c,
           END OF t_datatab.
    DATA: it_datatab TYPE STANDARD TABLE OF t_datatab INITIAL SIZE 0,
          wa_datatab TYPE t_datatab.
    
    DATA : gd_scol   TYPE i VALUE '1',
           gd_srow   TYPE i VALUE '1',
           gd_ecol   TYPE i VALUE '256',
           gd_erow   TYPE i VALUE '65536'.
    
    DATA: it_tab TYPE filetable,
          gd_subrc TYPE i.
    
    *Selection screen definition
    SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
    PARAMETERS:  p_file LIKE rlgrap-filename
                   DEFAULT 'c:/test.xls' OBLIGATORY.   " File Name
    SELECTION-SCREEN END OF BLOCK b1.
    
    
    ***********************************************************************
    * AT SELECTION-SCREEN
    AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
      REFRESH: it_tab.
      CALL METHOD cl_gui_frontend_services=>file_open_dialog
        EXPORTING
          window_title     = 'Select File'
          default_filename = '*.xls'
          multiselection   = ' '
        CHANGING
          file_table       = it_tab
          rc               = gd_subrc.
    
      LOOP AT it_tab INTO p_file.
    *    so_fpath-sign = 'I'.
    *    so_fpath-option = 'EQ'.
    *    append so_fpath.
      ENDLOOP.
    
    
    ***********************************************************************
    * START-OF-SELECTION.
    START-OF-SELECTION.
      PERFORM upload_excel_file TABLES   it_datatab
                                 USING   p_file
                                         gd_scol
                                         gd_srow
                                         gd_ecol
                                         gd_erow.
    
    
    ***********************************************************************
    * END-OF-SELECTION.
    END-OF-SELECTION.
      LOOP AT it_datatab INTO wa_datatab.
        WRITE:/ wa_datatab-col1,
                wa_datatab-col2,
                wa_datatab-col3,
                wa_datatab-col4,
                wa_datatab-col5,
                wa_datatab-col6,
                wa_datatab-col7,
                wa_datatab-col8,
                wa_datatab-col9,
                wa_datatab-col10,
                wa_datatab-col11.
      ENDLOOP.
    
    
    *&--------------------------------------------------------------------*
    *&      Form  UPLOAD_EXCEL_FILE
    *&--------------------------------------------------------------------*
    *       upload excel spreadsheet into internal table
    *---------------------------------------------------------------------*
    *      -->P_TABLE    Table to return excel data into
    *      -->P_FILE     file name and path
    *      -->P_SCOL     start column
    *      -->P_SROW     start row
    *      -->P_ECOL     end column
    *      -->P_EROW     end row
    *---------------------------------------------------------------------*
    FORM upload_excel_file TABLES   p_table
                           USING    p_file
                                    p_scol
                                    p_srow
                                    p_ecol
                                    p_erow.
    
      DATA : lt_intern TYPE  kcde_cells OCCURS 0 WITH HEADER LINE.
    * Has the following format:
    *             Row number   | Colum Number   |   Value
    *             ---------------------------------------
    *      i.e.     1                 1             Name1
    *               2                 1             Joe
    
      DATA : ld_index TYPE i.
      FIELD-SYMBOLS : <fs>.
    
    * Note: Alternative function module - 'ALSM_EXCEL_TO_INTERNAL_TABLE'
      CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'
        EXPORTING
          filename                = p_file
          i_begin_col             = p_scol
          i_begin_row             = p_srow
          i_end_col               = p_ecol
          i_end_row               = p_erow
        TABLES
          intern                  = lt_intern
        EXCEPTIONS
          inconsistent_parameters = 1
          upload_ole              = 2
          OTHERS                  = 3.
      IF sy-subrc <> 0.
        FORMAT COLOR COL_BACKGROUND INTENSIFIED.
        WRITE:/ 'Error Uploading file'.
        EXIT.
      ENDIF.
    
      IF lt_intern[] IS INITIAL.
        FORMAT COLOR COL_BACKGROUND INTENSIFIED.
        WRITE:/ 'No Data Uploaded'.
        EXIT.
      ELSE.
        SORT lt_intern BY row col.
        LOOP AT lt_intern.
          MOVE lt_intern-col TO ld_index.
          ASSIGN COMPONENT ld_index OF STRUCTURE p_table TO <fs>.
          MOVE lt_intern-value TO <fs>.
          AT END OF row.
            APPEND p_table.
            CLEAR p_table.
          ENDAT.
        ENDLOOP.
      ENDIF.
    ENDFORM.                    "UPLOAD_EXCEL_FILE

[http_www.sapdev.co.uk_file_file_upexcelalt1.htm]: http://www.sapdev.co.uk/file/file_upexcelalt1.htm
[simple Excel spreadsheet]: http://writeblog.csdn.net/file_xls.htm