ABAP TEXT_CONVERT_XLS_TO_SAP ALSM_EXCEL_TO_INTERNAL_TABLE讀取EXCEL CSV XML TEXT等檔案內容到SAP內表

讀取外部檔案內容到SAP

1.獲取檔案路徑

請查看另一篇博客：ABAP 獲取檔案路徑的方法

2.讀入檔案

方法一：函式TEXT_CONVERT_XLS_TO_SAP

TEXT_CONVERT_XLS_TO_SAP函式屬于函陣列TRUX，
其他類似的函式如下

示例：

TYPE-POOLS: TRUXS.

PARAMETERS : P_FILE TYPE string.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.

  DATA: lv_rc TYPE i.
  DATA: lt_file_table TYPE filetable.

  CALL METHOD cl_gui_frontend_services=>file_open_dialog
    EXPORTING
      window_title = '打開檔案'
    CHANGING
      file_table   = lt_file_table
      rc           = lv_rc.
  IF sy-subrc = 0.
    READ TABLE lt_file_table INTO DATA(wa_file_table) INDEX 1.
    P_FILE = wa_file_table-filename.
  ENDIF.

START-OF-SELECTION.
  DATA: lt_raw TYPE truxs_t_text_data.

  TYPES: BEGIN OF lty_upload,
        col1 TYPE C LENGTH 20,
        col2 TYPE C LENGTH 20,
        END OF lty_upload.

  DATA: lt_upload TYPE TABLE OF lty_upload.
  DATA: wa_upload TYPE lty_upload.
  DATA: lv_file TYPE RLGRAP-FILENAME.

  lv_file = p_file.    "I_FILENAME 接受的型別不為string，所以要把P_file賦值到lv_file  若想直接傳值,定義螢屏欄位使用PARAMETERS : P_FILE TYPE RLGRAP-FILENAME


  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
*     I_FIELD_SEPERATOR          =
      I_LINE_HEADER              =  'X'
      I_TAB_RAW_DATA             =  lt_raw
      I_FILENAME                 =  lv_file
    TABLES
      I_TAB_CONVERTED_DATA       =  lt_upload
*   EXCEPTIONS
*     CONVERSION_FAILED          = 1
*     OTHERS                     = 2
            .
  IF SY-SUBRC <> 0.
* Implement suitable error handling here
  ENDIF.

  LOOP AT lt_upload INTO wa_upload.
    WRITE:/ wa_upload-col1,wa_upload-col2.
  ENDLOOP.

EXCEL內容：

運行結果：

方法二：OLE函式ALSM_EXCEL_TO_INTERNAL_TABLE

注意AT NEW ROW AT END OF ROW的用法，在判斷ROW值時，其他值會填充成*；一般復制一個作業區或者使用FIELD-SYMBOLS

TYPE-POOLS: TRUXS.

PARAMETERS : P_FILE TYPE RLGRAP-FILENAME.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.

  DATA: lv_rc TYPE i.
  DATA: lt_file_table TYPE filetable.

  CALL METHOD cl_gui_frontend_services=>file_open_dialog
    EXPORTING
      window_title = '打開檔案'
    CHANGING
      file_table   = lt_file_table
      rc           = lv_rc.
  IF sy-subrc = 0.
    READ TABLE lt_file_table INTO DATA(wa_file_table) INDEX 1.
    P_FILE = wa_file_table-filename.
  ENDIF.

START-OF-SELECTION.
  TYPES: BEGIN OF lty_upload,
        col1 TYPE C LENGTH 20,
        col2 TYPE C LENGTH 20,
        col3 TYPE C LENGTH 20,
        col4 TYPE C LENGTH 20,
        END OF lty_upload.

  DATA: lt_upload TYPE TABLE OF lty_upload.
  DATA: wa_upload TYPE lty_upload.

  DATA: lt_file TYPE TABLE OF alsmex_tabline.

  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                = p_file
      i_begin_col             = 1
      i_begin_row             = 2
      i_end_col               = 9
      i_end_row               = 99999
    TABLES
      intern                  = lt_file
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.
  IF sy-subrc = 0.
    PERFORM frm_move_data.
  ELSE.
    MESSAGE '讀取資料失敗，請檢查模板！' TYPE 'E'.
  ENDIF.

  LOOP AT lt_upload INTO wa_upload.
    WRITE:/ wa_upload-col1,wa_upload-col2,wa_upload-col3,wa_upload-col4.
  ENDLOOP.

FORM FRM_MOVE_DATA.
  DATA: lv_num TYPE I,
        lv_filedname TYPE C LENGTH 20.
  FIELD-SYMBOLS <FS_field> TYPE ANY.
  DATA: wa_cfile TYPE alsmex_tabline.      "AT NEW row AT NEW row 會將除ROW的值全部轉換成****，所以新建一個作業區保存

  CLEAR: wa_upload,lt_upload,lv_num.
  LOOP AT lt_file INTO DATA(wa_file).
    lv_num = lv_num + 1.
    MOVE-CORRESPONDING wa_file to wa_cfile.

    AT NEW row.                             "ROW值開始
      CLEAR:lv_num.                         "新ROW開始時，清空計數
      lv_num = lv_num + 1.                  "清空了lv_num,重新賦值1
      lv_filedname = |wa_upload-col{ lv_num }|.
      ASSIGN (lv_filedname) TO <FS_field>.
      <FS_field> = wa_cfile-value.
    ENDAT.

      lv_filedname = |wa_upload-col{ lv_num }|.
      ASSIGN (lv_filedname) TO <FS_field>.
      <FS_field> = wa_cfile-value.

    AT END OF row.                          "ROW結束
      lv_filedname = |wa_upload-col{ lv_num }|.
      ASSIGN (lv_filedname) TO <FS_field>.
      <FS_field> = wa_cfile-value.
      APPEND wa_upload TO lt_upload.
    ENDAT.
  ENDLOOP.
ENDFORM.

EXCEL內容：

運行結果：