本文分享笔者在从事 SAP 项目开发工作中，涉及到的通过编程方式操作 Excel 的业务场景，希望起到抛砖引玉的作用。

使用 Excel 导入数据到 SAP Cloud for Customer 系统

假设要上传Account的数据到系统：
点download metadata:

自动download一个zip下来：

在folder Templates里维护要上传的Account data:

把维护好的excel重新打成zip, upload:

稍后在monitor里会观察到成功上传的task:

看到import成功的消息：

UI上能搜索出这条于 2017-11-14 日创建的Account：

如何通过 Excel import 的方式导入测试数据到 SAP Commerce Cloud 服务器

登录 SAP Commerce Cloud Administration Cockpit，从下拉菜单里选择 Product Cockpit:

点击这个箭头，打开 product 视图：

点击这个 excel import 按钮：

选择好 excel 和 media.zip 之后，点击 import：

点击这个按钮查看 import 进度：

进到 Backoffice 页面，根据 excel 里的 product id 查看一个 product，确保导入成功：

使用 SAP ABAP 封装的 Office Integration class 访问本地 Excel 文件

先看这个 report 执行的效果：打开本地 1.xlsx excel 文件：

这个 Excel 内容如下：

使用 get_ranges_data 方法，成功读取 Excel 的数据如下：

本例完整代码如下：

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 文件内容的详细技术实现。