SAP 标准函数上传 Excel 的程序 Demo 体验

有很多 SAP 标准函数可以将 Excel 电子表格文件上传到ABAP内部表中。之前写过自定义 ALSM_EXCEL_TO_INTERNAL_TABLE 功能模块来实现上传 Excel 文件，这种做法也是大多数开发使用的方法。

• ALSM_EXCEL_TO_INTERNAL_TABLE

• FILE_READ_AND_CONVERT_SAP_DATA

• IMPORT_FROM_SPREADSHEET

• KCD_EXCEL_OLE_TO_INT_CONVERT

• TEXT_CONVERT_XLS_TO_SAP

• UPLOAD_XLS_FILE_2_ITAB

• OBJECT ORIENTED

注意：所有这些功能都有一个共同点：它们不是为客户发布的，SAP 也不为您提供支持。

源代码

*&---------------------------------------------------------------------*
*& Report ZS_EXCEL
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zs_excel LINE-SIZE 512.

TYPES:
  tv_data(256) TYPE c,

  BEGIN OF ts_data,
    value_0001 TYPE tv_data,
    value_0002 TYPE tv_data,
    value_0003 TYPE tv_data,
    value_0004 TYPE tv_data,
    value_0005 TYPE tv_data,
    value_0006 TYPE tv_data,
    value_0007 TYPE tv_data,
    value_0008 TYPE tv_data,
    value_0009 TYPE tv_data,
    value_0010 TYPE tv_data,
    value_0011 TYPE tv_data,
    value_0012 TYPE tv_data,
    value_0013 TYPE tv_data,
    value_0014 TYPE tv_data,
    value_0015 TYPE tv_data,
    value_0016 TYPE tv_data,
    value_0017 TYPE tv_data,
    value_0018 TYPE tv_data,
    value_0019 TYPE tv_data,
    value_0020 TYPE tv_data,
  END OF ts_data,

  tt_data     TYPE TABLE OF ts_data,
  tv_index(4) TYPE n.

CONSTANTS:
  co_max_col TYPE i VALUE 20,
  co_max_row TYPE i VALUE 9999.

*&---------------------------------------------------------------------*
*&      SELECTION-SCREEN
*&---------------------------------------------------------------------*
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_fname FOR FIELD pa_fname.
PARAMETERS pa_fname TYPE localfile OBLIGATORY LOWER CASE MEMORY ID fnm.
SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN SKIP.

SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (83) tx_read0.
SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read1 FOR FIELD pa_read1.
PARAMETERS pa_read1 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read2 FOR FIELD pa_read2.
PARAMETERS pa_read2 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read3 FOR FIELD pa_read3.
PARAMETERS pa_read3 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read4 FOR FIELD pa_read4.
PARAMETERS pa_read4 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read5 FOR FIELD pa_read5.
PARAMETERS pa_read5 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read6 FOR FIELD pa_read6.
PARAMETERS pa_read6 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN SKIP.

SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read7 FOR FIELD pa_read7.
PARAMETERS pa_read7 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.


AT SELECTION-SCREEN OUTPUT.
  tx_fname = 'Path and name of Excel to open'.
  tx_read0 = 'Function:'.
  tx_read1 = 'ALSM_EXCEL_TO_INTERNAL_TABLE'.
  tx_read2 = 'FILE_READ_AND_CONVERT_SAP_DATA'.
  tx_read3 = 'IMPORT_FROM_SPREADSHEET'.
  tx_read4 = 'KCD_EXCEL_OLE_TO_INT_CONVERT'.
  tx_read5 = 'TEXT_CONVERT_XLS_TO_SAP'.
  tx_read6 = 'UPLOAD_XLS_FILE_2_ITAB'.
  tx_read7 = 'OBJECT ORIENTED'.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR pa_fname.

  DATA: lv_rc TYPE i.
  DATA: lt_file_table TYPE filetable,
        ls_file_table TYPE file_table.

  CALL METHOD cl_gui_frontend_services=>file_open_dialog
    EXPORTING
      window_title = 'Select a file'
    CHANGING
      file_table   = lt_file_table
      rc           = lv_rc.
  IF sy-subrc = 0.
    READ TABLE lt_file_table INTO ls_file_table INDEX 1.
    pa_fname = ls_file_table-filename.
  ENDIF.

*&---------------------------------------------------------------------*
*&      START-OF-SELECTION
*&---------------------------------------------------------------------*
START-OF-SELECTION.

  DATA:
        lt_data  TYPE tt_data.

  IF pa_read1 = 'X'.
    WRITE: / 'Function ALSM_EXCEL_TO_INTERNAL_TABLE'.
    SKIP.
    PERFORM excel_read1 CHANGING lt_data.

  ELSEIF pa_read2 = 'X'.
    WRITE: / 'Function FILE_READ_AND_CONVERT_SAP_DATA'.
    SKIP.
    PERFORM excel_read2 CHANGING lt_data.

  ELSEIF pa_read3 = 'X'.
    WRITE: / 'Function IMPORT_FROM_SPREADSHEET'.
    SKIP.
    PERFORM excel_read3 CHANGING lt_data.

  ELSEIF pa_read4 = 'X'.
    WRITE: / 'Function KCD_EXCEL_OLE_TO_INT_CONVERT'.
    SKIP.
    PERFORM excel_read4 CHANGING lt_data.

  ELSEIF pa_read5 = 'X'.
    WRITE: / 'Function TEXT_CONVERT_XLS_TO_SAP'.
    SKIP.
    PERFORM excel_read5 CHANGING lt_data.

  ELSEIF pa_read6 = 'X'.
    WRITE: / 'Function UPLOAD_XLS_FILE_2_ITAB'.
    SKIP.
    PERFORM excel_read6 CHANGING lt_data.

  ELSEIF pa_read7 = 'X'.
    WRITE: / 'OBJECT ORIENTED'.
    SKIP.
    PERFORM excel_read7 CHANGING lt_data.
  ENDIF.

  PERFORM itab_display USING lt_data.

*&---------------------------------------------------------------------*
*&      Form  excel_read1
*&---------------------------------------------------------------------*
FORM excel_read1 CHANGING pt_data TYPE tt_data.

  DATA:
    lt_excel TYPE STANDARD TABLE OF alsmex_tabline,
    ls_excel TYPE alsmex_tabline,
    lv_data  TYPE tv_data,
    lv_error TYPE string.

  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                = pa_fname
      i_begin_col             = 1
      i_begin_row             = 1
      i_end_col               = co_max_col
      i_end_row               = co_max_row
    TABLES
      intern                  = lt_excel
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.

  IF sy-subrc <> 0.
    WRITE: / 'SY-SUBRC = ', sy-subrc.
    MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
    WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_error.
    WRITE: / lv_error.
    STOP.
  ENDIF.

  LOOP AT lt_excel INTO ls_excel.
    lv_data = ls_excel-value.
    PERFORM itab_insert_value USING ls_excel-col ls_excel-row lv_data
    CHANGING pt_data.
  ENDLOOP.
ENDFORM.                    "excel_read1

*&---------------------------------------------------------------------*
*&      Form  excel_read2
*&---------------------------------------------------------------------*
FORM excel_read2 CHANGING pt_data TYPE tt_data.

  DATA:
    lv_fname TYPE filename-fileintern,
    lv_error TYPE string.

  lv_fname = pa_fname.
  CALL FUNCTION 'FILE_READ_AND_CONVERT_SAP_DATA'
    EXPORTING
      i_filename           = lv_fname
      i_servertyp          = 'OLE2'
      i_fileformat         = 'XLS'
*     I_FIELD_SEPERATOR    =
*     I_LINE_HEADER        =
    TABLES
      i_tab_receiver       = pt_data
    EXCEPTIONS
      file_not_found       = 1
      close_failed         = 2
      authorization_failed = 3
      open_failed          = 4
      conversion_failed    = 5
      OTHERS               = 6.

  IF sy-subrc <> 0.
    WRITE: / 'SY-SUBRC = ', sy-subrc.
    MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
    WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_error.
    WRITE: / lv_error.
    STOP.
  ENDIF.
ENDFORM.                    "excel_read2

*&---------------------------------------------------------------------*
*&      Form  excel_read3
*&---------------------------------------------------------------------*
FORM excel_read3 CHANGING pt_data TYPE tt_data.

  DATA:
    lv_url(256) TYPE c,
    ls_range    TYPE soi_dimension_item,
    lt_range    TYPE soi_dimension_table,
    lt_excel    TYPE soi_generic_table,
    ls_excel    TYPE soi_generic_item,
    lv_data     TYPE tv_data,
    lv_col      TYPE tv_index,
    lv_row      TYPE tv_index.

  CONCATENATE 'file://' pa_fname INTO lv_url.

  ls_range-row     = 1.
  ls_range-column  = 1.
  ls_range-rows    = co_max_row.
  ls_range-columns = co_max_col.
  APPEND ls_range TO lt_range.

  CALL FUNCTION 'IMPORT_FROM_SPREADSHEET'
    EXPORTING
      item_url      = lv_url
      document_type = 'Excel.Sheet'
    TABLES
      data_table    = lt_excel
      ranges        = lt_range.

  LOOP AT lt_excel INTO ls_excel.
    lv_data = ls_excel-value.
    lv_col = ls_excel-column.
    lv_row = ls_excel-row.
    PERFORM itab_insert_value USING lv_col lv_row lv_data
    CHANGING pt_data.
  ENDLOOP.
ENDFORM.                    "excel_read3

*&---------------------------------------------------------------------*
*&      Form  excel_read4
*&---------------------------------------------------------------------*
FORM excel_read4 CHANGING pt_data TYPE tt_data.

  DATA:
    lt_excel TYPE kcde_intern,
    ls_excel TYPE kcde_intern_struc,
    lv_data  TYPE tv_data,
    lv_error TYPE string.

  CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'
    EXPORTING
      filename                = pa_fname
      i_begin_col             = 1
      i_begin_row             = 1
      i_end_col               = co_max_col
      i_end_row               = co_max_row
    TABLES
      intern                  = lt_excel
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.

  IF sy-subrc <> 0.
    WRITE: / 'SY-SUBRC = ', sy-subrc.
    MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
    WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_error.
    WRITE: / lv_error.
    STOP.
  ENDIF.

  LOOP AT lt_excel INTO ls_excel.
    lv_data = ls_excel-value.
    PERFORM itab_insert_value USING ls_excel-col ls_excel-row lv_data
    CHANGING pt_data.
  ENDLOOP.
ENDFORM.                    "excel_read4

*&---------------------------------------------------------------------*
*&      Form  excel_read5
*&---------------------------------------------------------------------*
FORM excel_read5 CHANGING pt_data TYPE tt_data.

  DATA:
    lt_raw_data(4096) TYPE c OCCURS 0,
    lt_data           TYPE STANDARD TABLE OF string,
    lv_error          TYPE string.

  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
*     I_FIELD_SEPERATOR    =
*     I_LINE_HEADER        =
      i_tab_raw_data       = lt_raw_data
      i_filename           = pa_fname
    TABLES
      i_tab_converted_data = pt_data
    EXCEPTIONS
      conversion_failed    = 1
      OTHERS               = 2.

  IF sy-subrc <> 0.
    WRITE: / 'SY-SUBRC = ', sy-subrc.
    MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
    WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_error.
    WRITE: / lv_error.
    STOP.
  ENDIF.
ENDFORM.                    "excel_read5

*&---------------------------------------------------------------------*
*&      Form  excel_read6
*&---------------------------------------------------------------------*
FORM excel_read6 CHANGING pt_data TYPE tt_data.

  DATA:
        lv_error          TYPE string.

  CALL FUNCTION 'UPLOAD_XLS_FILE_2_ITAB'
    EXPORTING
      i_filename = pa_fname
    TABLES
      e_itab     = pt_data
    EXCEPTIONS
      file_error = 1
      OTHERS     = 2.

  IF sy-subrc <> 0.
    WRITE: / 'SY-SUBRC = ', sy-subrc.
    MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
    WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_error.
    WRITE: / lv_error.
    STOP.
  ENDIF.
ENDFORM.                    "excel_read6

*&---------------------------------------------------------------------*
*&      Form  excel_read7
*&---------------------------------------------------------------------*
FORM excel_read7 CHANGING pt_data TYPE tt_data.

  DATA:
    lo_control     TYPE REF TO i_oi_container_control,
    lo_error       TYPE REF TO i_oi_error,
    lo_container   TYPE REF TO cl_gui_custom_container,
    lo_document    TYPE REF TO i_oi_document_proxy,
    lo_spreadsheet TYPE REF TO i_oi_spreadsheet,
    lv_url         TYPE char256,
    lv_has_sheet   TYPE i.

* 1. control get
  CALL METHOD c_oi_container_control_creator=>get_container_control
    IMPORTING
      control = lo_control
      error   = lo_error.

  IF lo_error->has_failed = 'X'.
    CALL METHOD lo_error->raise_message( type = 'E' ).
  ENDIF.

* 2. container create
  CREATE OBJECT lo_container
    EXPORTING
*     parent                      =
      container_name              = 'CONTAINER'
*     style                       =
*     lifetime                    = lifetime_default
*     repid                       =
*     dynnr                       =
*     no_autodef_progid_dynnr     =
    EXCEPTIONS
      cntl_error                  = 1
      cntl_system_error           = 2
      create_error                = 3
      lifetime_error              = 4
      lifetime_dynpro_dynpro_link = 5
      OTHERS                      = 6.

  ASSERT sy-subrc = 0.

* 3. control init with container
  CALL METHOD lo_control->init_control
    EXPORTING
*     dynpro_nr                = SY-DYNNR
*     gui_container            = ' '
      inplace_enabled          = 'X'
*     inplace_mode             = 0
*     inplace_resize_documents = ' '
      inplace_scroll_documents = 'X'
*     inplace_show_toolbars    = 'X'
*     no_flush                 = ' '
*     parent_id                = cl_gui_cfw=>dynpro_0
      r3_application_name      = 'Excel'
*     register_on_close_event  = ' '
*     register_on_custom_event = ' '
*     rep_id                   = SY-REPID
*     shell_style              = 1384185856
      parent                   = lo_container
*     name                     =
*     autoalign                = 'x'
    IMPORTING
      error                    = lo_error
    EXCEPTIONS
      javabeannotsupported     = 1
      OTHERS                   = 2.

  ASSERT sy-subrc = 0.

  IF lo_error->has_failed = 'X'.
    CALL METHOD lo_error->raise_message( type = 'E' ).
  ENDIF.

* 4. control get proxy
  CALL METHOD lo_control->get_document_proxy
    EXPORTING
*     document_format    = 'NATIVE'
      document_type  = soi_doctype_excel_sheet " 'Excel.Sheet'
*     no_flush       = ' '
*     register_container = ' '
    IMPORTING
      document_proxy = lo_document
      error          = lo_error.

  IF lo_error->has_failed = 'X'.
    CALL METHOD lo_error->raise_message( type = 'E' ).
  ENDIF.

* 5. document open
  CONCATENATE 'FILE://' pa_fname INTO lv_url.

  CALL METHOD lo_document->open_document
    EXPORTING
*     document_title   = ' '
      document_url  = lv_url
*     no_flush      = ' '
      open_inplace  = 'X'
      open_readonly = 'X'
*     protect_document = ' '
*     onsave_macro  = ' '
*     startup_macro = ''
*     user_info     =
    IMPORTING
      error         = lo_error.

  IF lo_error->has_failed = 'X'.
    CALL METHOD lo_error->raise_message( type = 'E' ).
  ENDIF.

* 6. spreadsheet check exists
  CALL METHOD lo_document->has_spreadsheet_interface
*   EXPORTING
*     no_flush     = ' '
    IMPORTING
      error        = lo_error
      is_available = lv_has_sheet.

  IF lo_error->has_failed = 'X'.
    CALL METHOD lo_error->raise_message( type = 'E' ).
  ENDIF.

  IF NOT lv_has_sheet IS INITIAL.

*   7. spreadsheet get
    CALL METHOD lo_document->get_spreadsheet_interface
      EXPORTING
        no_flush        = ' '
      IMPORTING
        error           = lo_error
        sheet_interface = lo_spreadsheet.

    IF lo_error->has_failed = 'X'.
      CALL METHOD lo_error->raise_message( type = 'E' ).
    ENDIF.

*   8. data read
    DATA:
      lv_row_start TYPE i VALUE 1,   " first row
      lv_row_block TYPE i VALUE 100, " number of rows read in one block (range)
      lv_row_empty TYPE i VALUE 0,   " count of empty rows at the end of block
      lt_value     TYPE soi_generic_table,
      ls_value     TYPE soi_generic_item,
      lt_rangesdef TYPE soi_dimension_table,
      ls_rangesdef TYPE soi_dimension_item,
      lt_ranges    TYPE soi_range_list,
      ls_data      TYPE ts_data,
      lv_col_index TYPE tv_index,
      lv_fieldname TYPE string.

    FIELD-SYMBOLS:
    <fs_value> TYPE tv_data.

    WHILE lv_row_empty < 5. " max empty rows

*     range create
      CLEAR ls_rangesdef.
      CLEAR lt_rangesdef.
      ls_rangesdef-row     = lv_row_start.
      ls_rangesdef-column  = 1.
      ls_rangesdef-rows    = lv_row_block.
      ls_rangesdef-columns = co_max_col.
      INSERT ls_rangesdef INTO TABLE lt_rangesdef.

*     data read from range
      CALL METHOD lo_spreadsheet->get_ranges_data
        EXPORTING
*         no_flush  = ' '
*         all       = ' '
*         updating  = -1
          rangesdef = lt_rangesdef
        IMPORTING
          contents  = lt_value
          error     = lo_error
        CHANGING
          ranges    = lt_ranges.

      IF lo_error->has_failed = 'X'.
        CALL METHOD lo_error->raise_message( type = 'E' ).
      ENDIF.

*     data takeover
      LOOP AT lt_value INTO ls_value.

        AT NEW row.
          CLEAR ls_data.
        ENDAT.

        lv_col_index = ls_value-column.
        CONCATENATE 'VALUE_' lv_col_index INTO lv_fieldname.
        ASSIGN COMPONENT lv_fieldname OF STRUCTURE ls_data TO <fs_value>.
        ASSERT sy-subrc = 0.
        <fs_value> = ls_value-value.

        AT END OF row.
          APPEND ls_data TO pt_data.

          IF ls_data IS INITIAL.
            ADD 1 TO lv_row_empty.
          ELSE.
            CLEAR lv_row_empty.
          ENDIF.
        ENDAT.
      ENDLOOP.

      ADD lv_row_block TO lv_row_start.
    ENDWHILE.

*   data delete empty rows at the end
    DATA:
          lv_index TYPE sy-tabix.

    lv_index = lines( pt_data ).

    WHILE lv_index > 0.
      READ TABLE pt_data INTO ls_data INDEX lv_index.
      IF ls_data IS INITIAL.
        DELETE pt_data INDEX lv_index.
      ELSE.
        EXIT.
      ENDIF.
      SUBTRACT 1 FROM lv_index.
    ENDWHILE.
  ELSE.
    MESSAGE e323(bf00) WITH pa_fname RAISING file_error.
  ENDIF.

* 9. final cleaning
  IF NOT lo_spreadsheet IS INITIAL.
    FREE lo_spreadsheet.
  ENDIF.

  IF NOT lo_document IS INITIAL.
    CALL METHOD lo_document->close_document.
    CALL METHOD lo_document->release_document.
    FREE lo_document.
  ENDIF.

  IF NOT lo_control IS INITIAL.
    CALL METHOD lo_control->destroy_control.
    FREE lo_control.
  ENDIF.
ENDFORM.                    "excel_read7

*&---------------------------------------------------------------------*
*&      Form  itab_insert_value
*&---------------------------------------------------------------------*
FORM itab_insert_value USING    pi_col   TYPE tv_index
      pi_row   TYPE tv_index
      pi_value TYPE tv_data
CHANGING pt_data  TYPE tt_data.

  DATA:
        lv_fieldname TYPE string.

  FIELD-SYMBOLS:
    <fs_data>  TYPE ts_data,
    <fs_value> TYPE tv_data.

  CHECK pi_value <> ''.

  WHILE pi_row > lines( pt_data ).
    INSERT INITIAL LINE INTO TABLE pt_data.
  ENDWHILE.

  READ TABLE pt_data ASSIGNING <fs_data> INDEX pi_row.
  ASSERT sy-subrc = 0.
  CONCATENATE 'VALUE_' pi_col INTO lv_fieldname.
  ASSIGN COMPONENT lv_fieldname OF STRUCTURE <fs_data> TO <fs_value>.
  ASSERT sy-subrc = 0.
  <fs_value> = pi_value.
ENDFORM.                    "itab_insert_value

*&---------------------------------------------------------------------*
*&      Form  itab_display
*&---------------------------------------------------------------------*
FORM itab_display USING pt_data  TYPE tt_data.

  DATA:
    lv_count TYPE i,
    ls_data  TYPE ts_data,
    lv_value TYPE tv_data.

  lv_count = lines( pt_data ).

  WRITE: / 'Number of lines', lv_count.
  SKIP.

  LOOP AT pt_data INTO ls_data.

    WRITE / ''. " sy-tabix.
    DO co_max_col TIMES VARYING lv_value FROM ls_data-value_0001
    NEXT ls_data-value_0002.
      WRITE: lv_value(10).
    ENDDO.
  ENDLOOP.
ENDFORM.                    "itab_display

运行程序，测试不同方法的结果：

读取结果：

类 cl_fdt_xl_spreadsheet

除此之外，还可以使用类 cl_fdt_xl_spreadsheet

*&---------------------------------------------------------------------*
*& Report zexcelupload
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zexcelupload.

FIELD-SYMBOLS : <gt_data>       TYPE STANDARD TABLE .

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME .
PARAMETERS : p_file TYPE ibipparms-path OBLIGATORY,
             p_ncol TYPE i OBLIGATORY DEFAULT 10.
SELECTION-SCREEN END OF BLOCK b1 .

*--------------------------------------------------------------------*
* at selection screen
*--------------------------------------------------------------------*
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

  DATA: lv_rc TYPE i.
  DATA: lt_file_table TYPE filetable,
        ls_file_table TYPE file_table.

  CALL METHOD cl_gui_frontend_services=>file_open_dialog
    EXPORTING
      window_title = 'Select a file'
    CHANGING
      file_table   = lt_file_table
      rc           = lv_rc.
  IF sy-subrc = 0.
    READ TABLE lt_file_table INTO ls_file_table INDEX 1.
    p_file = ls_file_table-filename.
  ENDIF.

START-OF-SELECTION .

  PERFORM read_file .
  PERFORM process_file.

*---------------------------------------------------------------------*
* Form READ_FILE
*---------------------------------------------------------------------*
FORM read_file .

  DATA : lv_filename      TYPE string,
         lt_records       TYPE solix_tab,
         lv_headerxstring TYPE xstring,
         lv_filelength    TYPE i.

  lv_filename = p_file.

  CALL FUNCTION 'GUI_UPLOAD'
    EXPORTING
      filename                = lv_filename
      filetype                = 'BIN'
    IMPORTING
      filelength              = lv_filelength
      header                  = lv_headerxstring
    TABLES
      data_tab                = lt_records
    EXCEPTIONS
      file_open_error         = 1
      file_read_error         = 2
      no_batch                = 3
      gui_refuse_filetransfer = 4
      invalid_type            = 5
      no_authority            = 6
      unknown_error           = 7
      bad_data_format         = 8
      header_not_allowed      = 9
      separator_not_allowed   = 10
      header_too_long         = 11
      unknown_dp_error        = 12
      access_denied           = 13
      dp_out_of_memory        = 14
      disk_full               = 15
      dp_timeout              = 16
      OTHERS                  = 17.

  "convert binary data to xstring
  "if you are using cl_fdt_xl_spreadsheet in odata then skips this step
  "as excel file will already be in xstring
  CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'
    EXPORTING
      input_length = lv_filelength
    IMPORTING
      buffer       = lv_headerxstring
    TABLES
      binary_tab   = lt_records
    EXCEPTIONS
      failed       = 1
      OTHERS       = 2.

  IF sy-subrc <> 0.
    "Implement suitable error handling here
  ENDIF.

  DATA : lo_excel_ref TYPE REF TO cl_fdt_xl_spreadsheet .

  TRY .
      lo_excel_ref = NEW cl_fdt_xl_spreadsheet(
                              document_name = lv_filename
                              xdocument     = lv_headerxstring ) .
    CATCH cx_fdt_excel_core.
      "Implement suitable error handling here
  ENDTRY .

  "Get List of Worksheets
  lo_excel_ref->if_fdt_doc_spreadsheet~get_worksheet_names(
    IMPORTING
      worksheet_names = DATA(lt_worksheets) ).

  IF NOT lt_worksheets IS INITIAL.
    READ TABLE lt_worksheets INTO DATA(lv_woksheetname) INDEX 1.

    DATA(lo_data_ref) = lo_excel_ref->if_fdt_doc_spreadsheet~get_itab_from_worksheet(
                                             lv_woksheetname ).
    "now you have excel work sheet data in dyanmic internal table
    ASSIGN lo_data_ref->* TO <gt_data>.
  ENDIF.

ENDFORM.

*---------------------------------------------------------------------*
* Form PROCESS_FILE
*---------------------------------------------------------------------*
FORM process_file .

  DATA : lv_numberofcolumns   TYPE i,
         lv_date_string       TYPE string,
         lv_target_date_field TYPE datum.


  FIELD-SYMBOLS : <ls_data>  TYPE any,
                  <lv_field> TYPE any.

  "you could find out number of columns dynamically from table <gt_data>
  lv_numberofcolumns = p_ncol .

  LOOP AT <gt_data> ASSIGNING <ls_data> FROM 2 .

    "processing columns
    DO lv_numberofcolumns TIMES.
      ASSIGN COMPONENT sy-index OF STRUCTURE <ls_data> TO <lv_field> .
      IF sy-subrc = 0 .
        CASE sy-index .
*          when 1 .
*          when 2 .
          WHEN 10 .
            lv_date_string = <lv_field> .
            PERFORM date_convert USING lv_date_string CHANGING lv_target_date_field .
            WRITE lv_target_date_field .
          WHEN OTHERS.
            WRITE : <lv_field> .
        ENDCASE .
      ENDIF.
    ENDDO .
    NEW-LINE .
  ENDLOOP .
ENDFORM.

*---------------------------------------------------------------------*
* Form DATE_CONVERT
*---------------------------------------------------------------------*
FORM date_convert USING iv_date_string TYPE string CHANGING cv_date TYPE datum .

  DATA: lv_convert_date(10) TYPE c.

  lv_convert_date = iv_date_string .

  "date format YYYY/MM/DD
  FIND REGEX '^\d{4}[/|-]\d{1,2}[/|-]\d{1,2}$' IN lv_convert_date.
  IF sy-subrc = 0.
    CALL FUNCTION '/SAPDMC/LSM_DATE_CONVERT'
      EXPORTING
        date_in             = lv_convert_date
        date_format_in      = 'DYMD'
        to_output_format    = ' '
        to_internal_format  = 'X'
      IMPORTING
        date_out            = lv_convert_date
      EXCEPTIONS
        illegal_date        = 1
        illegal_date_format = 2
        no_user_date_format = 3
        OTHERS              = 4.
  ELSE.

    " date format DD/MM/YYYY
    FIND REGEX '^\d{1,2}[/|-]\d{1,2}[/|-]\d{4}$' IN lv_convert_date.
    IF sy-subrc = 0.
      CALL FUNCTION '/SAPDMC/LSM_DATE_CONVERT'
        EXPORTING
          date_in             = lv_convert_date
          date_format_in      = 'DDMY'
          to_output_format    = ' '
          to_internal_format  = 'X'
        IMPORTING
          date_out            = lv_convert_date
        EXCEPTIONS
          illegal_date        = 1
          illegal_date_format = 2
          no_user_date_format = 3
          OTHERS              = 4.
    ENDIF.

  ENDIF.

  IF sy-subrc = 0.
    cv_date = lv_convert_date .
  ENDIF.

ENDFORM .

这是一种有效的方法，可以让我们读取 Excel 文件。但是，这是有风险的。SAP 建议仅在 BRF+ 框架内使用此类，而不要在外部使用。这个方法容易导致意想不到的错误：例如上传空白数据、上传数据出现乱码、加粗文本被忽略等。FDT（BRFplus）Excel 对象/方法是 BRF+ 内部特定的，不能用于通用 Excel 导出或导入或任何其他目的。对象/方法在 BRF+ 的决策表中使用，该表期望 Excel 采用特定格式。

有关警告，请参阅下面的注释。

• Note 2468709 – Usage of standard class CL_FDT_XL_SPREADSHEET