本文分享筆者在從事 SAP 專案開發工作中，涉及到的透過程式設計方式操作 Excel 的業務場景，希望起到拋磚引玉的作用。

# 使用 Excel 匯入資料到 SAP Cloud for Customer 系統

假設要上傳Account的資料到系統：
點download metadata:

![](https://img-blog.csdnimg.cn/img_convert/6d8ca1d161c837dd4f2495801b4cc681.png)

自動download一個zip下來：

![](https://img-blog.csdnimg.cn/img_convert/0cc70ac6647749a614df0503f0c512ff.png)

在folder Templates裡維護要上傳的Account data:

![](https://img-blog.csdnimg.cn/img_convert/7a61f9aadce03837e8bff6767ac6e317.png)

把維護好的excel重新打成zip, upload:

![](https://img-blog.csdnimg.cn/img_convert/e2611e7e3e3f0e330b7dbb3135cf67f7.png)

稍後在monitor裡會觀察到成功上傳的task:

![](https://img-blog.csdnimg.cn/img_convert/4d719f1fa620fcdfbf3a38e1bc41f5b2.png)

看到import成功的訊息：

![](https://img-blog.csdnimg.cn/img_convert/ede4d483548ef78e268f2812dd415b21.png)

UI上能搜尋出這條於 2017-11-14 日建立的Account：

![](https://img-blog.csdnimg.cn/img_convert/cec6f7e010f20824cfd0a7d33ef2552e.png)

# 如何透過 Excel import 的方式匯入測試資料到 SAP Commerce Cloud 伺服器

登入 SAP Commerce Cloud Administration Cockpit，從下拉選單裡選擇 Product Cockpit:

![](https://img-blog.csdnimg.cn/img_convert/527804d39579b112673907c69c960016.png)

點選這個箭頭，開啟 product 檢視：

![](https://img-blog.csdnimg.cn/img_convert/1dfd040372b6ff8dbcb63ff090b44e65.png)

點選這個 excel import 按鈕：

![](https://img-blog.csdnimg.cn/img_convert/144503c33bbe1b225f71feb0e0c696ec.png)

選擇好 excel 和 media.zip 之後，點選 import：

![](https://img-blog.csdnimg.cn/img_convert/ed38c80340b596a4e95493d33b4b5fc7.png)

點選這個按鈕檢視 import 進度：

![](https://img-blog.csdnimg.cn/img_convert/c01e62a3187098c2fd7bd9013760c7fa.png)
![](https://img-blog.csdnimg.cn/img_convert/7e95045a0fa01e4c7811133c5ec38b36.png)

進到 Backoffice 頁面，根據 excel 裡的 product id 檢視一個 product，確保匯入成功：

![](https://img-blog.csdnimg.cn/img_convert/b699ac3686442e3d125b28abad01a583.png)

# 使用 SAP ABAP 封裝的 Office Integration class 訪問本地 Excel 檔案

先看這個 report 執行的效果：開啟本地 1.xlsx excel 檔案：

![](https://img-blog.csdnimg.cn/img_convert/e8f81ad43f6d546a76c4adbf3bfdb4d7.png)

這個 Excel 內容如下：

![](https://img-blog.csdnimg.cn/img_convert/99ea13388d616afc3ba4afee5a28b28c.png)

使用 get_ranges_data 方法，成功讀取 Excel 的資料如下：

![](https://img-blog.csdnimg.cn/img_convert/df365b5a8f5a37961e2b298c042b5e25.png)
![](https://img-blog.csdnimg.cn/img_convert/fe2f0dc39f834b93be2adcf92bb9793b.png)

本例完整程式碼如下：

```abap
REPORT  Z_IMPORT.

DATA:
oref_container   TYPE REF TO cl_gui_custom_container,
iref_control     TYPE REF TO i_oi_container_control,
iref_document    TYPE REF TO i_oi_document_proxy,
iref_spreadsheet TYPE REF TO i_oi_spreadsheet,
iref_error       TYPE REF TO i_oi_error.

DATA:
v_document_url TYPE c LENGTH 256,
i_sheets TYPE soi_sheets_table,
wa_sheets TYPE soi_sheets,
i_data        TYPE soi_generic_table,
wa_data       TYPE soi_generic_item,
i_ranges      TYPE soi_range_list,
i_total       TYPE i,
p_cols        TYPE i value 2.

PARAMETERS:
p_file  TYPE  localfile OBLIGATORY,
p_rows  TYPE i DEFAULT 100 OBLIGATORY,
p_mode TYPE c AS CHECKBOX.

INITIALIZATION.

CALL METHOD c_oi_container_control_creator=>get_container_control
     IMPORTING
       control = iref_control
       error   = iref_error.

IF iref_error->has_failed = 'X'.
    CALL METHOD iref_error->raise_message
      EXPORTING
        type = 'E'.
  ENDIF.

CREATE OBJECT oref_container
    EXPORTING
*      parent                      =
       container_name              = 'CONTsadasda'
*      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.
  IF sy-subrc <> 0.
    MESSAGE e001(00) WITH 'Error while creating container'.
  ENDIF.

CALL METHOD iref_control->init_control
    EXPORTING
*      dynpro_nr                = SY-DYNNR
*      gui_container            = ' '
       inplace_enabled          = 'X'
*      inplace_mode             = 0
*      inplace_resize_documents = ' '
*      inplace_scroll_documents = ' '
*      inplace_show_toolbars    = 'X'
*      no_flush                 = ' '
*      parent_id                = cl_gui_cfw=>dynpro_0
       r3_application_name      = 'EXCEL CONTAINER'
*      register_on_close_event  = ' '
*      register_on_custom_event = ' '
*      rep_id                   = SY-REPID
*      shell_style              = 1384185856
       parent                   = oref_container
*      name                     =
*      autoalign                = 'x'
    IMPORTING
       error                    = iref_error
*      retcode                  =
    EXCEPTIONS
       javabeannotsupported     = 1
       OTHERS                   = 2
          .
  IF iref_error->has_failed = 'X'.
    CALL METHOD iref_error->raise_message
      EXPORTING
        type = 'E'.
  ENDIF.

CALL METHOD iref_control->get_document_proxy
    EXPORTING
*     document_format    = 'NATIVE'
      document_type      = soi_doctype_excel_sheet
*     no_flush           = ' '
*     register_container = ' '
     IMPORTING
       document_proxy     = iref_document
       error              = iref_error
*    retcode            =
      .
  IF iref_error->has_failed = 'X'.
    CALL METHOD iref_error->raise_message
      EXPORTING
        type = 'E'.
  ENDIF.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

* To provide F4 help for the file
  PERFORM sub_file_f4.

START-OF-SELECTION.

CONCATENATE 'FILE://' p_file INTO v_document_url.

CALL METHOD iref_document->open_document
    EXPORTING
      document_title   = 'Excel'
      document_url     = v_document_url
*     no_flush         = ' '
      open_inplace     = 'X'
*     open_readonly    = ' '
*     protect_document = ' '
*     onsave_macro     = ' '
*     startup_macro    = ''
*     user_info        =
    IMPORTING
      error            = iref_error
*     retcode          =
      .
  IF iref_error->has_failed = 'X'.
    CALL METHOD iref_error->raise_message
      EXPORTING
        type = 'I'.
    LEAVE LIST-PROCESSING.
  ENDIF.

CALL METHOD iref_document->get_spreadsheet_interface
     EXPORTING
       no_flush        = ' '
    IMPORTING
      error           = iref_error
      sheet_interface = iref_spreadsheet
*     retcode         =
      .

IF iref_error->has_failed = 'X'.
    CALL METHOD iref_error->raise_message
      EXPORTING
        type = 'I'.
    LEAVE LIST-PROCESSING.
  ENDIF.

CALL METHOD iref_spreadsheet->get_sheets
      EXPORTING
       no_flush = ' '
*      updating = -1
     IMPORTING
       sheets   = i_sheets
       error    = iref_error
*      retcode  =
      .
  IF iref_error->has_failed = 'X'.
    CALL METHOD iref_error->raise_message
      EXPORTING
        type = 'I'.
    LEAVE LIST-PROCESSING.
  ENDIF.
  LOOP AT i_sheets INTO wa_sheets.

CALL METHOD iref_spreadsheet->select_sheet
       EXPORTING
          name     = wa_sheets-sheet_name
*         no_flush = ' '
      IMPORTING
          error    = iref_error
*         retcode  =
            .
    IF iref_error->has_failed = 'X'.
      EXIT.
*      call method iref_error->raise_message
*        exporting
*          type = 'E'.
    ENDIF.
    CALL METHOD iref_spreadsheet->set_selection
      EXPORTING
        top     = 1
        left    = 1
        rows    = p_rows
        columns = p_cols.

CALL METHOD iref_spreadsheet->insert_range
      EXPORTING
        name     = 'Test'
        rows     = p_rows
        columns  = p_cols
        no_flush = ''
      IMPORTING
        error    = iref_error.
    IF iref_error->has_failed = 'X'.
      EXIT.
*      call method iref_error->raise_message
*        exporting
*          type = 'E'.
    ENDIF.

REFRESH i_data.

CALL METHOD iref_spreadsheet->get_ranges_data
       EXPORTING
*        no_flush  = ' '
         all       = 'X'
*        updating  = -1
*        rangesdef =
       IMPORTING
         contents  = i_data
         error     = iref_error
*        retcode   =
       CHANGING
         ranges    = i_ranges
             .
    DELETE i_data WHERE value IS INITIAL OR value = space.
    ULINE.
    WRITE:/1 wa_sheets-sheet_name COLOR 3.
    ULINE.

*    LOOP AT i_data INTO wa_data.
*      WRITE:(50) wa_data-value.
*      AT END OF row.
*        NEW-LINE.
*      ENDAT.
*    ENDLOOP.

ENDLOOP.

CALL METHOD iref_document->close_document
*  EXPORTING
*    do_save     = ' '
*    no_flush    = ' '
     IMPORTING
       error       = iref_error
*    has_changed =
*    retcode     =
      .
  IF iref_error->has_failed = 'X'.
    CALL METHOD iref_error->raise_message
      EXPORTING
        type = 'I'.
    LEAVE LIST-PROCESSING.
  ENDIF.
  CALL METHOD iref_document->release_document
*  EXPORTING
*    no_flush = ' '
     IMPORTING
       error    = iref_error
*    retcode  =
      .
  IF iref_error->has_failed = 'X'.
    CALL METHOD iref_error->raise_message
      EXPORTING
        type = 'I'.
    LEAVE LIST-PROCESSING.
  ENDIF.

IF p_mode = 'X'.
*    DATA: ls_data TYPE ZREPORT,
*          lt_data TYPE STANDARD TABLE OF ZREPORT,
*          chaR_index TYPE string,
*          msg TYPE string.
*    LOOP AT i_data INTO wa_data.
*
*      IF sy-tabix MOD 2 = 1.
*         char_index = sy-tabix.
*         ls_data-report_name = wa_data-value.
*         CONCATENATE 'Index' char_index ls_data-report_name INTO msg SEPARATED BY SPACE.
*         CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
*           EXPORTING
*             TEXT = msg.
*      ELSE.
*         ls_data-description = wa_data-value.
*         APPEND ls_data TO lt_data.
*         CLEAR: wa_data,ls_data.
*      ENDIF.
*    ENDLOOP.
*    DELETE FROM zreport.
*    INSERT zreport FROM TABLE lt_data.
*    COMMIT WORK AND WAIT.
*    IF sy-subrc = 0.
*      i_total = lines( lt_data ).
*      WRITE:/ 'User Assign Row Number:' , p_rows.
*      WRITE:/ 'Actually Imported Row Numer:' , i_total.
*    ENDIF.

ENDIF.
*&---------------------------------------------------------------------*
*&      Form  SUB_FILE_F4
*&---------------------------------------------------------------------*
*       F4 help for file path
*----------------------------------------------------------------------*
FORM sub_file_f4 .
  DATA:
  l_desktop       TYPE string,
  l_i_files       TYPE filetable,
  l_wa_files      TYPE file_table,
  l_rcode         TYPE int4.

* Finding desktop
  CALL METHOD cl_gui_frontend_services=>get_desktop_directory
    CHANGING
      desktop_directory    = l_desktop
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4.
  IF sy-subrc <> 0.
    MESSAGE e001(00) WITH
        'Desktop not found'.
  ENDIF.

* Update View
  CALL METHOD cl_gui_cfw=>update_view
    EXCEPTIONS
      cntl_system_error = 1
      cntl_error        = 2
      OTHERS            = 3.

CALL METHOD cl_gui_frontend_services=>file_open_dialog
     EXPORTING
       window_title            = 'Select Excel file'
       default_extension       = '.xls'
*      default_filename        =
       file_filter             = '.xls'
*      with_encoding           =
       initial_directory       = l_desktop
*      multiselection          =
    CHANGING
      file_table              = l_i_files
      rc                      = l_rcode
*     user_action             =
*     file_encoding           =
    EXCEPTIONS
      file_open_dialog_failed = 1
      cntl_error              = 2
      error_no_gui            = 3
      not_supported_by_gui    = 4
      OTHERS                  = 5
          .
  IF sy-subrc <> 0.
    MESSAGE e001(00) WITH 'Error while opening file'.
  ENDIF.

READ TABLE l_i_files INDEX 1 INTO l_wa_files.
  IF sy-subrc = 0.
    p_file = l_wa_files-filename.
  ELSE.
    MESSAGE e001(00) WITH 'Error while opening file'.
  ENDIF.

ENDFORM.                    " SUB_FILE_F4
```

# 總結

本文透過三個實際的業務場景，分別介紹瞭如何透過 Excel 作為載體，匯入資料到 SAP Cloud for Customer 和 SAP Commerce Cloud 系統，以及透過 ABAP 程式語言解析 Excel 檔案內容的詳細技術實現。

SAP 實施專案中涉及到程式設計方式操作 Excel 的幾種場景介紹

相關文章