MM 思爱 普(SAP)！

*&---------------------------------------------------------------------

*& Report  Zexcel_TST1
*
*&                看看abap基础    下载                                                 *
*&---------------------------------------------------------------------*
*&     create at 12/15 17:20
*   用excel输出报表
*&      12/17 18:40调试通过
*  已经下传数据成功 可以使用macro 问题在MAcro编写
*&---------------------------------------------------------------------*

REPORT  zexcel_tst1           .

*INCLUDE OLE2INCL.  "作用相同
TYPE-POOLS ole2.   "作用相同
***使用excel

TYPES:BEGIN OF zygstr,
      mandt   LIKE zygempl-mandt,
      zygname LIKE zygempl-zygname,
      zygage  LIKE zygempl-zygage,
      zygyear LIKE zygempl-zygyear,
      END   OF zygstr.

************************************************************************
***定义data 内表以及工作区(表头)
DATA:
      myexcel      TYPE ole2_object,  "excel
      workbooks1   TYPE ole2_object,  "workbook
      sheets1      TYPE ole2_object,  "sheet
      cora         TYPE ole2_object,  "cell 1217gai
      coln         TYPE ole2_object,  "col 1217gai
      Range1       TYPE ole2_object.  "ranges 1221addz
*  excel对象数据定义
DATA zygtabletop TYPE zygstr.
DATA zygtable    TYPE TABLE OF zygstr.
*数据定义
DATA  rowno TYPE i VALUE 1.
DATA  colno TYPE i VALUE 1.
FIELD-SYMBOLS .
*excel定位的行 列 值
DATA: BEGIN OF it-title OCCURS 0,
      ztitle(12) TYPE c,
     END OF it-title .
* 报表头行标题
***********************************************************************
SELECTION-SCREEN BEGIN OF SCREEN 104 AS WINDOW TITLE text-fil.
PARAMETERS:
        filename(30) TYPE c.
SELECTION-SCREEN END OF SCREEN 104 .
***********************************************************************
*数据赋值
APPEND '客户端' TO it-title .
APPEND '姓名'   TO it-title .
APPEND '年龄'   TO it-title .
APPEND '日期'   TO it-title .

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

*   perform fexcel."新建excel输出
*   PERFORM fexcel2."使用已有的excel输出
*  PERFORM fdownfile .
  PERFORM fexcel3. "使用run marco
***********************************************************************
***使用excel form
*&---------------------------------------------------------------------*
*&      Form  fexcel
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM fexcel .
***本段用于打开excel新建sheet
  PERFORM fcreate .
***逐字段写入excel
  PERFORM fwrite .
  PERFORM frelease.
ENDFORM.                    " fexcel
*&---------------------------------------------------------------------*
*&      Form  fexcel2
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
**----------------------------------------------------------------------
**
FORM fexcel2 .
***open excel
  CALL SELECTION-SCREEN 104 STARTING AT 20 5 .
  PERFORM fopen USING filename.
***write excel field by field
  PERFORM fwrite .
  PERFORM frelease.
ENDFORM.                                                    " fexcel2

*&---------------------------------------------------------------------*
*&      Form  fexcel3
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM fexcel3 .
  CALL SELECTION-SCREEN 104 STARTING AT 20 5 .
  PERFORM fopen USING filename.
  PERFORM fsql .
***以下3句测试临时使用
*loop at zygtable into zygtabletop.
*write :/ zygtabletop-zygage .
*endloop.
 perform fdownfile .
  CALL METHOD OF myexcel 'run '
    EXPORTING
      #1 = 'Contract_Load'.
  PERFORM frelease.
ENDFORM.                                                    " fexcel3

*&---------------------------------------------------------------------*
*&      Form  fcreate
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM fcreate .
  CREATE OBJECT myexcel 'Excel.Application'.       "1创建Excel对象
  SET PROPERTY OF myexcel 'Visible' = 1.                    "2 可见
  CALL METHOD OF myexcel 'WORKBOOKS' = workbooks1. "3 excel create wbook
  CALL METHOD OF workbooks1 'Add'.                 "3 wbook add sheet
  CALL METHOD OF myexcel 'Worksheets' = sheets1    "4excel use sheet
                        EXPORTING  #1 = 2 .        "sheet1注意只能<4
  CALL METHOD OF sheets1 'Activate'.               "激活
ENDFORM.                    " fcreate
*
*&---------------------------------------------------------------------*
*&      Form  fopen
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM fopen USING filename.
***本段用于打开已有excel
  CREATE OBJECT myexcel 'Excel.Application'.       "1创建Excel对象
  CALL METHOD OF myexcel 'WORKBOOKS' = workbooks1. "3 excel createwbook
*  filename = 'c:11.xls'. "1220xiugai zhushidiao
  CALL METHOD OF workbooks1 'Open'
    EXPORTING
      #1 = filename.
  SET PROPERTY OF myexcel 'Visible' = 1.                    "2 可见

**  call method of workbooks1 'Add'.                 "3 wbook add sheet
  CALL METHOD OF myexcel 'Worksheets' = sheets1    "4excel use sheet
                        EXPORTING  #1 = 1 .        "sheet1注意只能<4
  CALL METHOD OF sheets1 'Activate'.               "激活
ENDFORM.                    " fopen

************************************************************************
***formsql
FORM fsql.
  SELECT  mandt zygname zygage zygyear FROM zygempl
      INTO TABLE zygtable .
ENDFORM.                    "fsql

***********************************************************************
*&---------------------------------------------------------------------*
*&      Form  fwrite
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM fwrite .

  CALL METHOD OF sheets1 'Rows' = cora    "def row
                     EXPORTING   #1 = rowno.
  LOOP AT it-title.
    CALL METHOD OF cora 'Columns' =  coln   "def col
                     EXPORTING   #1 = colno.
    SET  PROPERTY  OF coln  'Value ' =  it-title-ztitle.
    colno = colno + 1.
    IF colno = 5 .
*      CALL METHOD OF cora 'Columns' =  coln   "def col 1221add
*                     EXPORTING   #1 = colno.
*      SET  PROPERTY  OF coln  'Value ' = 'sy-tabix '."1221add
      colno = 1.
      EXIT .
    ENDIF.
  ENDLOOP.

  PERFORM fsql.        "form可以嵌套调用      "col 1217add
  rowno = rowno + 1 .
***第一种属性:rows columns
*  LOOP AT zygtable INTO zygtabletop.
*    CALL METHOD OF sheets1 'Rows' = cora    "def row
*                   EXPORTING   #1 = rowno . "注意先引用+1在使用
*    CALL METHOD OF cora 'Columns' =  coln   "def col
*                   EXPORTING   #1 = colno.
*    SET  PROPERTY  OF coln  'Value ' =  zygtabletop-mandt .
*    colno = colno + 1 .
*
*    CALL METHOD OF cora 'Columns' =  coln   "def col
*                    EXPORTING   #1 = colno.
*    SET  PROPERTY  OF coln  'Value ' =  zygtabletop-zygname .
*    colno = colno + 1 .
*
*    CALL METHOD OF cora 'Columns' =  coln   "def col
*               EXPORTING   #1 = colno.
*    SET  PROPERTY  OF coln  'Value ' =  zygtabletop-zygage .
*    colno = colno + 1 .
*
*    CALL METHOD OF cora 'Columns' =  coln   "def col
*               EXPORTING   #1 = colno.
*    SET  PROPERTY  OF coln  'Value ' =  zygtabletop-zygyear .
*
**    colno = colno + 1 . "1221add
**    CALL METHOD OF cora 'Columns' =  coln   "def col
**               EXPORTING   #1 = colno.
**    SET  PROPERTY  OF coln  'Value ' =  sy-tabix." 1221add
*
*    rowno = rowno + 1 .
*    colno = 1 ."列号复原。
*  ENDLOOP .                         "col 1217add

***第二种属性cell #1行 #2 列 "col 1217rw no use
*  perform lsql.
*  loop at zygtable into zygtabletop.
*   assign  zygtabletop-MANDT to .
*   call method of sheets1 'Cells' = Cora
*    EXPORTING
*      #1 = rowno.
*      rowno = rowno + 1 .
*  SET  PROPERTY  OF Cora  'Value ' =  .
*  endloop . "col 1217rw no use
** " 1221 gai
loop at  zygtable into zygtabletop .
  call method of sheets1 'Cells' = Cora
    EXPORTING
      #1 = rowno  #2 = colno.
    SET  PROPERTY  OF Cora  'Value ' = zygtabletop-mandt .
  colno = colno + 1 .
    call method of sheets1 'Cells' = Cora
    EXPORTING
      #1 = rowno  #2 = colno.
    SET  PROPERTY  OF Cora  'Value ' = zygtabletop-zygname .
  colno = colno + 1 .
    call method of sheets1 'Cells' = Cora
    EXPORTING
      #1 = rowno  #2 = colno.
    SET  PROPERTY  OF Cora  'Value ' = zygtabletop-zygage .
   colno = colno + 1 .
    call method of sheets1 'Cells' = Cora
    EXPORTING
      #1 = rowno  #2 = colno.
    SET  PROPERTY  OF Cora  'Value ' = zygtabletop-zygyear .
   rowno = rowno + 1.
   colno = 1.
   endloop.
*   SET  PROPERTY  OF Cora  'ColorIndex' = '6' ."属性??
**以下是用rang填充 没有成功
*CALL METHOD OF sheets1 'Range' = Range1.
*Range1 = 'A1:D1' .
*CALL METHOD OF Range1 'Activate'.
*SET  PROPERTY  OF Range1 'FormulaR1C1' = '99test' .
ENDFORM.                    " fwrite
*&---------------------------------------------------------------------*
*&      Form  fRelease
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM frelease .
  CALL METHOD OF myexcel 'Save' .
*  CALL METHOD OF myexcel 'close' . "this is no use
*以下2句add on 1220 调出保存对话框
*call method of MYEXCEL 'Save' EXPORTING   #1 = 'c:13.xls' .
**filename = 'c:13.xls'.
**call method of MYEXCEL 'close'   EXPORTING   #1 = filename.
** 上一语句无效。
  FREE   OBJECT sheets1.
  FREE   OBJECT workbooks1.
*  FREE    MYEXCEL.    "以下两句等效
  FREE   OBJECT myexcel.
ENDFORM.                    " fRelease
*&---------------------------------------------------------------------*
*&      Form  fdownfile
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
form fdownfile .
**修改中
* CALL FUNCTION 'WS_DOWNLOAD'
**** "DOWNLOAD 也有此功能 区分
*    EXPORTING
*      filename        = 'C:bdcfile.dat'
**      FILETYPE        = 'dat'
*    TABLES
*      data_tab        = zygtable  "输出data
*    EXCEPTIONS
*      file_open_error = 2 "文件打开错误
*      OTHERS          = 1.
***1221区别：download有对话框提示，功能较好
   CALL FUNCTION 'DOWNLOAD'
    EXPORTING
      filename        = 'C:bdcfile.dat'
      FILETYPE        = 'dat'
    TABLES
      data_tab        = zygtable .  "输出data

endform.                    " fdownfile

************************************************************************