一个elo小记录

*&---------------------------------------------------------------------*
*& Report  ZCESHI_22
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT  ZCESHI_22.
*report ZLUPING05
*       no standard page heading line-size 255.

*&---------------------------------------------------------------------*
*& Report  ZTEST_OLE_EXCEL
*&
*&---------------------------------------------------------------------*
*&  测试OLE EXCEL
*&---------------------------------------------------------------------*
*REPORT  ZTEST_OLE_EXCEL
*  MESSAGE-ID ZTEST.

*&---------------------------------------------------------------------*
*& TYPE-POOLS
*&---------------------------------------------------------------------*
TYPE-POOLS:
  OLE2.      "或者：INCLUDE OLE2INCL

*&---------------------------------------------------------------------*
*& 定义数据
*&---------------------------------------------------------------------*
DATA:
  TD_BKPF TYPE TABLE OF BKPF WITH HEADER LINE,
  TH_BKPF TYPE BKPF.

DATA:
  W_TMP_FILE(65) TYPE C.

* OLE2对象
DATA:
  W_EXCEL TYPE OLE2_OBJECT,  "EXCEL
  W_BOOKS TYPE OLE2_OBJECT,  "LIST OF WORKBOOKS
  W_BOOK  TYPE OLE2_OBJECT,  "WORKBOOK
  W_SHEET TYPE OLE2_OBJECT,  "SHEET
  W_CELL  TYPE OLE2_OBJECT.  "CELL OF SHEET

CONSTANTS:

* 前面一定要添加'MI'
  CNS_DOWN_KEY TYPE WWWDATATAB VALUE 'MIZTSTOLE_EXCEL',
  CNS_DOWN_PATH TYPE LOCALFILE VALUE 'C:\ZTST_TMP.XLT',  "下载地址
* 部分字符串
  CNS_VISIBLE TYPE CHAR32 VALUE 'VISIBLE',
  CNS_WORKBOOKS TYPE CHAR32 VALUE 'WORKBOOKS',
  CNS_OPEN TYPE CHAR32 VALUE 'OPEN',
  CNS_WORKSHEETS TYPE CHAR32 VALUE 'WORKSHEETS',
  CNS_ACTIVATE TYPE CHAR32 VALUE 'ACTIVATE',
  CNS_RANGE TYPE CHAR32 VALUE 'RANGE',
  CNS_VALUE TYPE CHAR32 VALUE 'VALUE'.


DATA: l_fname     LIKE rlgrap-filename.
*&---------------------------------------------------------------------*
*& 选择屏幕
*&---------------------------------------------------------------------*
PARAMETERS:
  P_BUKRS TYPE BKPF-BUKRS ,  "公司代码
  P_GJAHR TYPE BKPF-GJAHR .   "会计年度

*&---------------------------------------------------------------------*
*&  START-OF-SELECTION
*&---------------------------------------------------------------------*
START-OF-SELECTION.

* 取得数据
  PERFORM FRM_GET_BKPF_DATA.

*-从SAP服务器上下载模板（要先通过SMWO上传）
  PERFORM FRM_DOWN_TEMPLATE CHANGING l_fname.

*-向EXCEL中填入数据，并显示出来
  PERFORM FRM_EXPORT_DATA.

*&---------------------------------------------------------------------*
*&      Form  FRM_GET_BKPF_DATA
*&---------------------------------------------------------------------*
*       取得数据
*----------------------------------------------------------------------*
FORM FRM_GET_BKPF_DATA .

  SELECT *
    FROM BKPF
    INTO TABLE TD_BKPF
   WHERE BUKRS = P_BUKRS
     AND GJAHR = P_GJAHR.

  IF SY-SUBRC <> 0.
*    MESSAGE S001 DISPLAY LIKE 'E'.
*    LEAVE LIST-PROCESSING.
  ENDIF.
Td_BKPF-bukrs = '1000'.
Td_BKPF-belnr = '188888888'.

APPEND TD_BKPF.

ENDFORM.                    " FRM_GET_BKPF_DATA

*&---------------------------------------------------------------------*
*&      Form  FRM_DOWN_TEMPLATE
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM FRM_DOWN_TEMPLATE  CHANGING fname.
  DATA:
    LW_SUBRC TYPE SY-SUBRC.

* 显示进度
  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
    EXPORTING
      PERCENTAGE       = 0
      TEXT             = 'Download the template!'.


    DATA:  l_objdata     LIKE wwwdatatab,
         l_destination LIKE rlgrap-filename,
         l_rc          LIKE sy-subrc,
         l_errtxt      TYPE string.

  SELECT SINGLE relid objid
    FROM wwwdata
    INTO CORRESPONDING FIELDS OF l_objdata
   WHERE srtf2 = 0
     AND relid = 'MI'
     AND objid = 'ZZCFZB02'."'ZHJ88'.'ZZCFZB01' "此处为EXCEL模板名称

* 检查表wwwdata中是否存在所指定的模板文件
  IF sy-subrc NE 0 OR
     l_objdata-objid = space."如果不存在，则给出错误提示
    CONCATENATE '保函交接模板文件' '不存在' INTO l_errtxt.
    MESSAGE l_errtxt TYPE 'E'.
  ENDIF.

  l_destination = 'C:\保函交接单.XLS'.

** 下载sap服务器上的RFC模板（html）
*  CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
*    EXPORTING
*      KEY               = CNS_DOWN_KEY
*      DESTINATION       = CNS_DOWN_PATH
*    IMPORTING
*      RC                = LW_SUBRC
*    CHANGING
*      TEMP              = W_TMP_FILE.


  DATA: l_fname     LIKE rlgrap-filename,
        l_filename  TYPE string,
        l_icount    TYPE i,
        l_irow      TYPE i.
  CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
    EXPORTING
      key         = l_objdata
      destination = l_destination
*      KEY               = CNS_DOWN_KEY
*      DESTINATION       = CNS_DOWN_PATH
    IMPORTING
      rc          = l_rc.
  IF l_rc NE 0.
    CONCATENATE '保函交接单模板文件：' l_destination '下载失败' INTO l_errtxt.
    MESSAGE l_errtxt TYPE 'E'.
  ENDIF.

  IF LW_SUBRC <> 0.
*    MESSAGE 'DOWNLOAD TEMPLATE FALL!' TYPE 'S' DISPLAY LIKE 'E'.
    LEAVE LIST-PROCESSING.
  ENDIF.
 fname = l_destination.
ENDFORM.                    " FRM_DOWN_TEMPLATE

*&---------------------------------------------------------------------*
*&      Form  FRM_EXPORT_DATA
*&---------------------------------------------------------------------*
*       向EXCEL中填入数据，并显示出来
*----------------------------------------------------------------------*
FORM FRM_EXPORT_DATA .
  DATA:
    LW_H TYPE CHAR2,
    LW_S TYPE CHAR2.

* 创建EXCEL
  CREATE OBJECT W_EXCEL 'EXCEL.APPLICATION'.
  SET PROPERTY OF W_EXCEL CNS_VISIBLE = 0.
* 设置可见
  SET PROPERTY OF W_EXCEL CNS_VISIBLE = 1 NO FLUSH.
* 创建EXCEL的WORKSBOOKS
  CALL METHOD OF W_EXCEL CNS_WORKBOOKS = W_BOOKS.
* 创建BOOK FOR WORKSBOOKS
  CALL METHOD OF W_BOOKS CNS_OPEN = W_BOOK
    EXPORTING
      #1 = l_fname."CNS_DOWN_PATH.

  CALL METHOD OF W_BOOK CNS_WORKSHEETS = W_SHEET
    EXPORTING
      #1 = 'Sheet1'.

  CALL METHOD OF W_SHEET CNS_ACTIVATE.

* 输出到EXCEL
  LW_S = '6'.
  LOOP AT TD_BKPF. "INTO TH_BKPF.

    LW_H = 'B'.
    PERFORM FRM_FILL_CELL USING LW_H LW_S Td_BKPF-BUKRS.

    LW_H = 'C'.
    PERFORM FRM_FILL_CELL USING LW_H LW_S Td_BKPF-BELNR.

*    LW_H = 'C'.
*    PERFORM FRM_FILL_CELL USING LW_H LW_S TH_BKPF-GJAHR.
*
*    LW_H = 'D'.
*    PERFORM FRM_FILL_CELL USING LW_H LW_S TH_BKPF-BLART.
*
*    LW_H = 'E'.
*    PERFORM FRM_FILL_CELL USING LW_H LW_S TH_BKPF-BLDAT.
*
*    LW_H = 'F'.
*    PERFORM FRM_FILL_CELL USING LW_H LW_S TH_BKPF-BUDAT.
*
*    LW_H = 'G'.
*    PERFORM FRM_FILL_CELL USING LW_H LW_S TH_BKPF-MONAT.
*
*    LW_H = 'H'.
*    PERFORM FRM_FILL_CELL USING LW_H LW_S TH_BKPF-CPUDT.
*
*    LW_H = 'I'.
*    PERFORM FRM_FILL_CELL USING LW_H LW_S TH_BKPF-XBLNR.
*
*    LW_H = 'J'.
*    PERFORM FRM_FILL_CELL USING LW_H LW_S TH_BKPF-BKTXT.

    LW_S = LW_S + 1.

  ENDLOOP.

* 设置可见
  SET PROPERTY OF W_EXCEL CNS_VISIBLE = 1 NO FLUSH.

  FREE OBJECT:
    W_EXCEL,
    W_BOOKS,
    W_BOOK,
    W_SHEET,
    W_CELL.

ENDFORM.                    " FRM_EXPORT_DATA

*&---------------------------------------------------------------------*
*&      Form  FRM_FILL_CELL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->i_H  text
*      -->i_S  text
*      -->i_value  text
*----------------------------------------------------------------------*
FORM FRM_FILL_CELL  USING  VALUE(I_H)
                           VALUE(I_S)
                           VALUE(I_VALUE).
  DATA:
    LW_CELL TYPE CHAR4.

* 填充单元格（定位）
  CONCATENATE I_H I_S INTO LW_CELL.
  CALL METHOD OF W_SHEET CNS_RANGE = W_CELL
    EXPORTING
      #1 = LW_CELL.
* 插入值
  SET PROPERTY OF W_CELL CNS_VALUE = I_VALUE.

ENDFORM.                    " FRM_FILL_CELL