SAP DOI&BDS

说明

 DOI是SAP与office继承提供的解决方案。

本文主要介绍OAOR上传excel至SAP端，通过DOI技术将excel模板赋值并展示出来

实现效果

维护BDS视图

事务代码：SBDSV1

OAOR模版上传

创建程序，创建屏幕

 

代码

REPORT zcwtdoitext01.

TABLES : ekko.

*-->控件及界面定义
DATA: gv_container  TYPE scrfname VALUE 'ALV_CON',  "ALV控键定义
      g_cust_con    TYPE REF TO cl_gui_custom_container,
      g_container   TYPE REF TO cl_gui_container,
      g_control     TYPE REF TO i_oi_container_control,
      g_document    TYPE REF TO i_oi_document_proxy,  "
      g_spreadsheet TYPE REF TO i_oi_spreadsheet.

*-->类型池定义
DATA: g_bds_documents      TYPE REF TO cl_bds_document_set,
      gv_classname         TYPE sbdst_classname,
      gv_classtype         TYPE sbdst_classtype,
      gv_objectkey         TYPE sbdst_object_key,
      gv_doc_components    TYPE sbdst_components,
      gv_doc_signature     TYPE sbdst_signature,
      gt_bds_uris          TYPE sbdst_uri,
      gs_bds_url           LIKE LINE OF gt_bds_uris,
      gv_template_url(256) TYPE c.    "模板url存储

*-->定义写入excel时需要的ranges和contents
DATA: gt_ranges   TYPE soi_range_list,    "确定写入的单元格
      gs_range    TYPE soi_range_item,
      gt_contents TYPE soi_generic_table, "确定写入的值
      gs_content  TYPE soi_generic_item.


*-->内表定义
DATA : BEGIN OF gw_ekko,
         ebeln TYPE ekko-ebeln,
         bsart TYPE ekko-bsart,
         aedat TYPE ekko-aedat,
         lifnr TYPE ekko-lifnr,
       END OF gw_ekko.
DATA : gt_ekko LIKE TABLE OF gw_ekko.

*-->辅助字段定义
DATA : ok_code TYPE sy-ucomm.

*-->选择屏幕定义
SELECTION-SCREEN BEGIN OF BLOCK blc_01 WITH FRAME TITLE TEXT-001.
  SELECT-OPTIONS : s_ebeln FOR ekko-ebeln.
SELECTION-SCREEN END OF BLOCK blc_01.


INITIALIZATION.
  "为参数赋值,此处对应事务码OAOR上传的文档信息
  gv_classname = 'ZCWTTEXT'.
  gv_classtype = 'OT'.
  gv_objectkey = 'TEXT'.

START-OF-SELECTION.

  PERFORM frm_get_data.

  PERFORM frm_deal_data.

  CALL SCREEN 100.

end-of-SELECTION.
*&---------------------------------------------------------------------*
*& Form frm_get_data
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_get_data .
  SELECT ebeln
         bsart
         aedat
         lifnr
    FROM ekko
    INTO TABLE gt_ekko
    WHERE ebeln IN s_ebeln.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_deal_data
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_deal_data .

ENDFORM.
*&---------------------------------------------------------------------*
*& Module STATUS_0100 OUTPUT
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
MODULE status_0100 OUTPUT.
  SET PF-STATUS '100'.
  SET TITLEBAR '100'.
  PERFORM frm_excel_output.
ENDMODULE.
*&---------------------------------------------------------------------*
*&      Module  USER_COMMAND_0100  INPUT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
MODULE user_command_0100 INPUT.

  CASE ok_code.
    WHEN '&F03' OR '&F12' OR '&F15'.
      "退出前进行对象的释放
      PERFORM release_objects.
      LEAVE TO SCREEN 0.

      WHEN 'SAVE' .

        READ TABLE gt_ekko INTO gW_ekko INDEX 1 .

  ENDCASE.

ENDMODULE.
*&---------------------------------------------------------------------*
*& Form main
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_excel_output .

  DATA: gv_available TYPE i.
  DATA: gv_row       TYPE i.  "计算内表有多少行
  DATA: gv_col       TYPE i.  "计算内表有多少列
  DATA: row_index    TYPE i.  "数据写入时的行

  DEFINE add_content.       "写入单元格操作
    gs_content-row    = &1.  "行
    gs_content-column = &2.  "列
    gs_content-value  = &3.  "值
    APPEND gs_content TO gt_contents.
    CLEAR gs_content.
  END-OF-DEFINITION.

  IF g_cust_con IS INITIAL.
    CREATE OBJECT g_cust_con
      EXPORTING
        container_name = gv_container.
  ENDIF.

*-->创建容器 create container control
  CALL METHOD c_oi_container_control_creator=>get_container_control
    IMPORTING
      control = g_control.

*-->初始化
  CALL METHOD g_control->init_control
    EXPORTING
      inplace_enabled          = 'X '
      inplace_scroll_documents = 'X'
      register_on_close_event  = 'X'
      register_on_custom_event = 'X'
      r3_application_name      = 'CWT TEXT DOI'
      parent                   = g_cust_con.

*-->url获取
  CREATE OBJECT g_bds_documents.
  CALL METHOD cl_bds_document_set=>get_info
    EXPORTING
      classname  = gv_classname  "initialization位置下定义
      classtype  = gv_classtype  "initialization位置下定义
      object_key = gv_objectkey  "initialization位置下定义
    CHANGING
      components = gv_doc_components
      signature  = gv_doc_signature.

  CALL METHOD cl_bds_document_set=>get_with_url
    EXPORTING
      classname  = gv_classname
      classtype  = gv_classtype
      object_key = gv_objectkey
    CHANGING
      uris       = gt_bds_uris
      signature  = gv_doc_signature.

  FREE g_bds_documents.


  "读取
  READ TABLE gt_bds_uris INTO gs_bds_url INDEX 1.
  gv_template_url = gs_bds_url-uri.


*-->打开上传的excel文档
  "
  CALL METHOD g_control->get_document_proxy
    EXPORTING
      document_type      = 'Excel.Sheet'
      no_flush           = 'X'
      register_container = 'X'
    IMPORTING
      document_proxy     = g_document.

  CALL METHOD g_document->open_document
    EXPORTING
      open_inplace = 'X'
      document_url = gv_template_url.

  CALL METHOD g_document->has_spreadsheet_interface
    EXPORTING
      no_flush     = 'X'
    IMPORTING
      is_available = gv_available.

  CALL METHOD g_document->get_spreadsheet_interface
    EXPORTING
      no_flush        = 'X'
    IMPORTING
      sheet_interface = g_spreadsheet.

  "选择展示的sheet页
  CALL METHOD g_spreadsheet->select_sheet
    EXPORTING
      name     = 'Sheet1'
      no_flush = 'X'.

*-->往excel中写入数据
  "检查内表是否有数据
  CHECK gt_ekko IS NOT INITIAL.

  "获取内表的行数和列数
  PERFORM frm_get_line_col USING gv_row gv_col.

  CALL METHOD g_spreadsheet->insert_range_dim
    EXPORTING
      name     = 'text'
      no_flush = 'X'
      top      = 3        "从第几行开始
      left     = 1        "从第几列考试
      rows     = gv_row   "写几行
      columns  = gv_col.  "写几列

  "根据行列填充ranges
  CLEAR :gs_range,gt_ranges.
  gs_range-name = 'text'.
  gs_range-rows = gv_row.
  gs_range-columns = gv_col.
  gs_range-code = 4.
  APPEND gs_range TO gt_ranges.

  "批量的把数据写入
  CLEAR row_index .
  row_index = '1'.

  "把值精确到每一行每一列的单元格
  LOOP AT gt_ekko INTO gw_ekko.
    CLEAR gs_content.
    add_content row_index 1 gw_ekko-ebeln.
    add_content row_index 2 gw_ekko-bsart.
    add_content row_index 3 gw_ekko-aedat.
    add_content row_index 4 gw_ekko-lifnr.
    row_index = row_index + 1.
  ENDLOOP.

  CALL METHOD g_spreadsheet->set_ranges_data
    EXPORTING
      ranges   = gt_ranges
      contents = gt_contents
      no_flush = 'X'.

  "把数据精确写入到某一行某一列

  PERFORM frm_write_single USING '1' '7' sy-uname.  "写入用户名
  PERFORM frm_write_single USING '2' '7' sy-datum.  "写入时间

ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_get_line_col
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*&      --> P_
*&      --> GV_LINE
*&      --> GV_COL
*&---------------------------------------------------------------------*
FORM frm_get_line_col  USING p_row p_col.
  CLEAR : p_row,p_col.
  FIELD-SYMBOLS <fs>.
  "获取行数
  p_row = lines( gt_ekko ).
  "获取列数
  DO.
    ASSIGN COMPONENT sy-index OF STRUCTURE gw_ekko TO <fs>.
    IF sy-subrc IS INITIAL.
      p_col = p_col + 1.
    ELSE.
      EXIT.
    ENDIF.
  ENDDO.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form release_objects
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM release_objects .

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

  IF NOT g_control IS INITIAL.
    CALL METHOD g_control->destroy_control.
    FREE g_control.
  ENDIF.

  IF g_container IS NOT INITIAL.
    CALL METHOD g_container->free.
  ENDIF.


ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_write_single
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*&      --> P_
*&      --> P_
*&      --> SY_UNAME
*&---------------------------------------------------------------------*
FORM frm_write_single  USING  p_row p_col p_value.
  DATA : lt_ranges   TYPE soi_range_list,
         ls_ranges   TYPE soi_range_item,
         lt_contents TYPE soi_generic_table,
         ls_contents TYPE soi_generic_item.

  CLEAR :lt_ranges,ls_ranges,lt_contents,ls_contents.
  "ranges中只存储一行一列
  ls_ranges-name = 'text' .
  ls_ranges-columns = 1.
  ls_ranges-rows = 1.
  ls_ranges-code = 4.
  APPEND ls_ranges TO lt_ranges.
  "写入数据也只存一个值
  ls_contents-column = 1.
  ls_contents-row = 1.
  ls_contents-value = p_value .
  APPEND ls_contents TO lt_contents.

  "每次只写一行一列
  CALL METHOD g_spreadsheet->insert_range_dim
    EXPORTING
      name     = 'text'
      no_flush = 'X'
      top      = p_row
      left     = p_col
      rows     = 1
      columns  = 1.

  CALL METHOD g_spreadsheet->set_ranges_data
    EXPORTING
      ranges   = lt_ranges
      contents = lt_contents
      no_flush = 'X'.
ENDFORM.

INCLUDE zcwtdoitext_create_basic_obo01.

*----------------------------------------------------------------------*
***INCLUDE ZJHTRAIN009_CREATE_BASIC_OBO01.
*----------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*& Module CREATE_BASIC_OBJECTS OUTPUT
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
MODULE create_basic_objects OUTPUT.
 SET PF-STATUS '100'.
 SET TITLEBAR '100'.
ENDMODULE.