SAP_DOi测试_操作实现与程序设计_获得EXCEL选中cell的数据

SAP中DOI获得Excel选中CELL的数据

我的实现操作步骤：

DOI导出excel操作文档

By 程旺

1技术说明

Desktop Office Integration(doi) 是用来集成支持OLE2的应用程序到SAP系统中.它提供了OO的操作方法对应用程序如EXCEL,WORLD等进行打开,修改等操作.目前在一个报表中使用EXCEL作为模板上传,然后将数据填充到EXCEL模板中的指定位置中. 本文档讲述我的第一个doi测试过程。[在此测试中，需要创建一个屏幕，再将控件导入，然后通过class的方法填充模板]

首先使用Tcode: OAOR [ Business Document Navigator]将EXCEL模板上传到系统中. 然后使用cl_bds_document_set类获取文档, 再用DOI的相关来操作EXCEL.

2技术实现

2.1 上传模板到系统中 t_code:oaor

图2.1.1设置相应导入信息[程序中需要的参数]

这里注意：

CLASS NAME: HRFPM_EXCEL_STANDARD(一般sap有的，可以自己定义 T-CODE:SBDSV1) 我自己在BP6上也定义了一个，但是，我还是用系统的。

如果选择自定义class 步骤如下：

T-CODE:SBDSV1

图2.1.2 自定义class

点击新条目

图2.1.3设置好后保存

图2.1.4选择导入[回到图2.1选择运行后进入]

图2.1.5 导入成功

双击table template如果能够出现模板文件，那么就是导入木板成功了。如下图

然后编写相应的程序程序运行后：

3程序设计

3.1.top模块

3.2 screen 模块

*&---------------------------------------------------------------------*
*&  包括                Z2014CW_TESTDOI_SCREEN
*&---------------------------------------------------------------------*
START-OF-SELECTION.
  SELECT-OPTIONS MATNR FOR MAKT-MATNR.
  CALL SCREEN 100.

3.3 form模块

*&---------------------------------------------------------------------*
*&  包括                Z2014CW_TESTDOI_FORM
*&---------------------------------------------------------------------*

*&---------------------------------------------------------------------*
*&      Form  getdata
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM GETDATA.
  SELECT *
  FROM MAKT
  INTO TABLE GT_MAKT
  WHERE MAKT~MATNR IN MATNR.
ENDFORM.                    " getdata

*———————————————————————*
*       FORM create_basic_objects 将用到的模板参数都传入
*———————————————————————*
FORM  CREATE_BASIC_OBJECTS USING P_APP_NAME
                                 P_CLASSNAME
                                 P_CLASSTYPE
                                 P_OBJ_KEY
                                 P_DOCNAME.

  DATA L_APP_NAME(200).
  DATA: BDS_INSTANCE TYPE REF TO CL_BDS_DOCUMENT_SET.
  DATA: DOC_SIGNATURE TYPE SBDST_SIGNATURE,
        WA_DOC_SIGNATURE LIKE LINE OF DOC_SIGNATURE,
        DOC_COMPONENTS TYPE SBDST_COMPONENTS,
        DOC_URIS TYPE SBDST_URI,

        WA_DOC_URIS LIKE LINE OF DOC_URIS.
  DATA: DOC_CLASSNAME TYPE SBDST_CLASSNAME VALUE 'HRFPM_EXCEL_STANDARD',
        DOC_CLASSTYPE TYPE SBDST_CLASSTYPE VALUE 'OT',
        DOC_OBJECT_KEY TYPE SBDST_OBJECT_KEY VALUE 'Z2014CW_WORK013'.

  CHECK INITIALIZED IS INITIAL.

*  处理参数
  IF P_APP_NAME IS INITIAL.
    L_APP_NAME = 'R/3 Reporter'.
  ELSE.
    L_APP_NAME = P_APP_NAME.
  ENDIF.

  "如果已经在pbo中重新设置三个参数
  IF  NOT (  P_CLASSNAME IS INITIAL OR P_CLASSTYPE IS INITIAL
         OR P_OBJ_KEY IS INITIAL ).
    "
    DOC_CLASSNAME =  P_CLASSNAME.
    DOC_CLASSTYPE =  P_CLASSTYPE.
    DOC_OBJECT_KEY = P_OBJ_KEY.

  ENDIF.

  "没有重新设定参数

*  创建容器控制器实例
  CALL METHOD C_OI_CONTAINER_CONTROL_CREATOR=>GET_CONTAINER_CONTROL
    IMPORTING
      CONTROL = CONTROL    "CONTROL TYPE REF TO I_OI_CONTAINER_CONTROL,
      ERROR   = ERROR.
  CALL METHOD ERROR->RAISE_MESSAGE
    EXPORTING
      TYPE = 'E'.

*&--------------------------------------------------------------------*
*&*  创建屏幕上的容器对象
*&--------------------------------------------------------------------*
  CREATE OBJECT CONTAINER
    EXPORTING
      CONTAINER_NAME = 'SCREEN_DOI'."创建的屏幕容器实例

*  初始化容器控制器
  CALL METHOD CONTROL->INIT_CONTROL
    EXPORTING
      R3_APPLICATION_NAME      = L_APP_NAME  "文件名传入
      INPLACE_ENABLED          = INPLACE     "X
      INPLACE_SCROLL_DOCUMENTS = 'X'
      PARENT                   = CONTAINER   "容器实例
      REGISTER_ON_CLOSE_EVENT  = 'X'
      REGISTER_ON_CUSTOM_EVENT = 'X'
      NO_FLUSH                 = 'X'
    IMPORTING
      ERROR                    = ERRORS.  "返回错误消息【type I_OI_ERROR】

  APPEND ERRORS.

  CLEAR ITEM_URL.

  WA_DOC_SIGNATURE-PROP_NAME = 'DESCRIPTION'.
  DOCUMENT_TYPE = EXCEL.
  WA_DOC_SIGNATURE-PROP_VALUE = P_DOCNAME.

  APPEND WA_DOC_SIGNATURE TO DOC_SIGNATURE.

*  BDS对象实例化
  CREATE OBJECT BDS_INSTANCE.

*  读取BDS内容（模板） ”将三个参数传入--》HRFPM_EXCEL_STANDARD 、  OT  、 Z2014CW_WORK013

  CALL METHOD BDS_INSTANCE->GET_INFO
    EXPORTING
      CLASSNAME  = DOC_CLASSNAME
      CLASSTYPE  = DOC_CLASSTYPE
      OBJECT_KEY = DOC_OBJECT_KEY
    CHANGING
      COMPONENTS = DOC_COMPONENTS
      SIGNATURE  = DOC_SIGNATURE.

*   读取BDS的URL
  CALL METHOD BDS_INSTANCE->GET_WITH_URL
    EXPORTING
      CLASSNAME  = DOC_CLASSNAME
      CLASSTYPE  = DOC_CLASSTYPE
      OBJECT_KEY = DOC_OBJECT_KEY
    CHANGING
      URIS       = DOC_URIS
      SIGNATURE  = DOC_SIGNATURE.

  FREE BDS_INSTANCE.
  READ TABLE DOC_URIS INTO WA_DOC_URIS INDEX 1.
  ITEM_URL = WA_DOC_URIS-URI.

* ask the SAP DOI container for a i_oi_document_proxy for Excel
*  容器控制器获得一个EXCEL文档代理
  CALL METHOD CONTROL->GET_DOCUMENT_PROXY
    EXPORTING
      DOCUMENT_TYPE  = 'Excel.Sheet'
      NO_FLUSH       = 'X'
    IMPORTING
      DOCUMENT_PROXY = DOCUMENT
      ERROR          = ERRORS.
  APPEND ERRORS.

* open a document saved in business document service.
*  容器控制器中打开指定BDS返回的文档
  CALL METHOD DOCUMENT->OPEN_DOCUMENT
    EXPORTING
      OPEN_INPLACE = INPLACE
      DOCUMENT_URL = ITEM_URL.

  DATA: HAS TYPE I.
  CALL METHOD DOCUMENT->HAS_SPREADSHEET_INTERFACE
    EXPORTING
      NO_FLUSH     = ''
    IMPORTING
      IS_AVAILABLE = HAS
      ERROR        = ERRORS.
  APPEND ERRORS.
  CALL METHOD DOCUMENT->GET_SPREADSHEET_INTERFACE
    EXPORTING
      NO_FLUSH        = ' '
    IMPORTING
      SHEET_INTERFACE = SPREADSHEET
      ERROR           = ERRORS.
  APPEND ERRORS.

* Activate  sheet
  CALL METHOD SPREADSHEET->SELECT_SHEET
    EXPORTING
      NAME     = ACTIVE_SHEET
      NO_FLUSH = ''
    IMPORTING
      ERROR    = ERRORS.
  APPEND ERRORS.

*错误处理
  LOOP AT ERRORS.
    CALL METHOD ERRORS->RAISE_MESSAGE
      EXPORTING
        TYPE = 'S'.
  ENDLOOP.
  FREE ERRORS.

  INITIALIZED = 'X'.
ENDFORM.              "' CREATE_BASIC_OBJECTS

*———————————————————————*
*       FORM output_to_excel         excel导出                                 *
*———————————————————————*
FORM OUTPUT_TO_EXCEL.

  PERFORM GETDATA.
  "---------------------9月1日 0:07我注释的
  "测试的时候，可以把哪一列要显示什么参数设置

*DATA: TMPDATE(10) TYPE C.
*COLUMN_COUNT = 2."第二列
*
*  LINE_COUNT = 2."第二行
*  PERFORM FILL_CELL USING LINE_COUNT
*                          COLUMN_COUNT
*                          'TEST1'.
*  LINE_COUNT = LINE_COUNT + 1."第三行
*  PERFORM FILL_CELL USING LINE_COUNT
*                          COLUMN_COUNT
*                          'TEST2'.
*  COLUMN_COUNT = COLUMN_COUNT + 1."第三列
*  PERFORM FILL_CELL USING LINE_COUNT
*                          COLUMN_COUNT
*                          'ljc'.
  "--------------------------------
*    call METHOD spreadsheet->PROTECT【这里需要注释】
*    EXPORTING
*      protect = 'X'.
  "=============================
  DATA: TMPDATE(10) TYPE C.
  " COLUMN_COUNT = 1."第1列
  LINE_COUNT = 1."第二行开始注意行列

  "DATA NUM TYPE I VALUE 1.
  LOOP AT GT_MAKT."这里我用的是带表头的内表 type structure makt
    LINE_COUNT = LINE_COUNT + SY-TABIX.
    PERFORM FILL_CELL USING LINE_COUNT 1 GT_MAKT-MATNR.
    PERFORM FILL_CELL USING LINE_COUNT 2 GT_MAKT-SPRAS.
    PERFORM FILL_CELL USING LINE_COUNT 3 GT_MAKT-MAKTX.
    PERFORM FILL_CELL USING LINE_COUNT 4 GT_MAKT-MAKTG.
    LINE_COUNT = 1.
  ENDLOOP.

ENDFORM.                    "output_to_excel

*———————————————————————*
*       FORM fill_cell
*   txt  对excel行列数据在form OUTPUT_TO_EXCEL进行设置后  在这里填充                                            *
*———————————————————————*
FORM FILL_CELL  USING I J VAL.

  DATA: COLUMNS_NUMBER TYPE I,
        ROWS_NUMBER    TYPE I.

  COLUMNS_NUMBER = 1.
  ROWS_NUMBER = 1.

  CALL METHOD SPREADSHEET->INSERT_RANGE_DIM
    EXPORTING
      NAME     = 'cell'
      NO_FLUSH = 'X'
      TOP      = I
      LEFT     = J
      ROWS     = ROWS_NUMBER
      COLUMNS  = COLUMNS_NUMBER
    IMPORTING
      ERROR    = ERRORS.
  APPEND ERRORS.

  REFRESH: RANGES, EXCEL_INPUT.
  RANGEITEM-NAME = 'cell'.
  RANGEITEM-COLUMNS = 1.
  RANGEITEM-ROWS = 1.
  APPEND RANGEITEM TO RANGES.

  EXCEL_INPUT_WA-COLUMN = 1.
  EXCEL_INPUT_WA-ROW = 1.
  EXCEL_INPUT_WA-VALUE = VAL.
  APPEND EXCEL_INPUT_WA TO EXCEL_INPUT.

* 设置数据
  CALL METHOD SPREADSHEET->SET_RANGES_DATA
    EXPORTING
      RANGES   = RANGES
      CONTENTS = EXCEL_INPUT
      NO_FLUSH = 'X'
    IMPORTING
      ERROR    = ERRORS.
  APPEND ERRORS.

  CALL METHOD SPREADSHEET->FIT_WIDEST
    EXPORTING
      NAME     = SPACE
      NO_FLUSH = 'X'.

  REFRESH: RANGES, EXCEL_INPUT.

ENDFORM.                    "fill_cell

3.4 PBO模块

*&-------------------------------------------------------------------
*& 包括 Z2014CW_TESTDOI_PBO
*&-------------------------------------------------------------------
*&——————————————————————-
*& Module OUTPUT_TO_EXCEL OUTPUT
*&——————————————————————-

MODULE STATUS_0100 OUTPUT.
  SET PF-STATUS 'CW_GUI_DOI'.
*  创建EXCEL模板
  PERFORM CREATE_BASIC_OBJECTS USING ''  '' '' '' DOCUMENT_NAME.
*  输出EXCEL
  PERFORM OUTPUT_TO_EXCEL.
ENDMODULE.                 "' OUTPUT_TO_EXCEL  OUTPUT

3.5 PAI模块

*&-------------------------------------------------------------------
*&  包括                Z2014CW_TESTDOI_PAI
*&-------------------------------------------------------------------
*&———————————————————————*
*&      Module  EXIT  INPUT
*&———————————————————————*
MODULE USER_COMMAND_0100 INPUT.
  *释放对象

  IF NOT DOCUMENT IS INITIAL.
    CALL METHOD DOCUMENT->CLOSE_DOCUMENT.
    FREE DOCUMENT.  "type I_OI_DOCUMENT_PROXY
  ENDIF.

  IF NOT CONTROL IS INITIAL.
    CALL METHOD CONTROL->DESTROY_CONTROL.
    FREE CONTROL.  "type I_OI_CONTAINER_CONTROL
  ENDIF.
  SET SCREEN 0 .
ENDMODULE.                 " EXIT  INPUT