ABAP OLE EXCEL DOWNLOAD 2

*&---------------------------------------------------------------------*
*& Report  ZDEMO15
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT zdemo15.

DATA:BEGIN OF gs_itab,
      matnr TYPE makt-matnr,
      maktx TYPE makt-maktx,
      END OF gs_itab,
      gt_itab LIKE TABLE OF gs_itab.

DATA:gv_lines TYPE i.

INCLUDE ole2incl.

DATA: gv_excel TYPE ole2_object, " Excel object
      gv_workbook  TYPE ole2_object,        " workbook
      gv_sheet TYPE ole2_object, " sheet
      gv_zl    TYPE ole2_object,        " cell
      gv_f TYPE ole2_object. " font

***dowm muban
DATA:gs_wwwdatatab  TYPE wwwdatatab,         "Contents of table WWWDATA
     gv_filename TYPE rlgrap-filename,
     gv_rc TYPE sy-subrc.

PERFORM frm_get_file_path USING gv_filename.
CHECK gv_filename IS NOT INITIAL.

SELECT SINGLE
   relid "Region in IMPORT/EXPORT Data Table
   objid      "SAP WWW Gateway Object Name
 FROM  wwwdata
 INTO CORRESPONDING FIELDS OF gs_wwwdatatab
 WHERE srtf2 = '0'
 AND relid = 'MI'
 AND objid = 'ZDEMO15'.
IF sy-subrc <> 0 .
  MESSAGE 'not exit' TYPE 'I'.
ENDIF.

CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
  EXPORTING
    key = gs_wwwdatatab
    destination = gv_filename
  IMPORTING
    rc = gv_rc.
IF gv_rc <> 0.
  MESSAGE 'Down faile' TYPE 'E'.
ENDIF.

* start Excel
CREATE OBJECT gv_excel 'EXCEL.APPLICATION'.
PERFORM err_hdl.
SET PROPERTY OF gv_excel 'Visible' = 0. "1 DISPLAY 0 NO-DISPLAY
PERFORM err_hdl.

* get list of workbooks, initially empty
CALL METHOD OF
    gv_excel
    'WORKBOOKS' = gv_workbook.
PERFORM err_hdl.

* OPEN DOWNLOAD EXCEL
CALL METHOD OF
    gv_workbook
    'OPEN'

  EXPORTING
    #1          = gv_filename.

CALL METHOD OF
    gv_excel
    'SHEETS' = gv_sheet
  EXPORTING
    #1       = 1.

CALL METHOD OF
    gv_sheet
    'SELECT'.

CALL METHOD OF gv_sheet 'ACTIVATE'.          "sheet ACTIVATE

** add a new workbook
*CALL METHOD OF
* gv_workbook
* 'Add' = gv_map.
*PERFORM err_hdl.

* output column headings to active Excel sheet
PERFORM fill_cell USING 1 1 1 '物料编码'.
PERFORM fill_cell USING 1 2 1 '物料描述'.

SELECT matnr maktx INTO TABLE gt_itab
  FROM makt UP TO 10 ROWS.

gv_lines = 1.
LOOP AT gt_itab INTO gs_itab.
  gv_lines = gv_lines + 1.
  PERFORM fill_cell USING gv_lines 1 0 gs_itab-matnr.
  PERFORM fill_cell USING gv_lines 2 0 gs_itab-maktx.
ENDLOOP.

SET PROPERTY OF gv_excel 'DisplayAlerts' = 0.

CALL METHOD OF
    gv_sheet
    'SaveAs'

  EXPORTING
    #1 = gv_filename
    #2 = 1
    #3 = '123456'."增加密码设置 FZH

CALL METHOD OF
    gv_sheet
    'close'

  EXPORTING
    #1       = 0.

CALL METHOD OF
    gv_sheet
    'quit'.

CALL METHOD OF
    gv_sheet
    'Exit'   = gv_sheet.

* disconnect from Excel
DO.
  CALL FUNCTION 'FLUSH'
    EXCEPTIONS
      OTHERS = 0.

  FREE OBJECT gv_excel.
  IF sy-subrc EQ 0.
    EXIT.
  ENDIF.
ENDDO.
PERFORM err_hdl.



*&---------------------------------------------------------------------*
*&      Form  fill_cell
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->I          text
*      -->J          text
*      -->BOLD       text
*      -->VAL        text
*----------------------------------------------------------------------*
FORM fill_cell USING i j bold val.
  CALL METHOD OF
      gv_excel
      'Cells'  = gv_zl
    EXPORTING
      #1       = i
      #2       = j.
  PERFORM err_hdl.
  SET PROPERTY OF gv_zl 'Value' = val .
  PERFORM err_hdl.
  GET PROPERTY OF gv_zl 'Font' = gv_f.
  PERFORM err_hdl.
  SET PROPERTY OF gv_f 'Bold' = bold .
  PERFORM err_hdl.
ENDFORM.                    "fill_cell

* output column headings to active Excel sheet
*&---------------------------------------------------------------------*
*& Form  err_hdl
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM err_hdl.
  IF sy-subrc <> 0.
    WRITE: / 'Fehler bei OLE-Automation:'(010), sy-subrc.
    STOP.
  ENDIF.
ENDFORM. " ERR_HDL

*&---------------------------------------------------------------------*
*&      Form  frm_get_file_path
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      <--CV_FILE    text
*----------------------------------------------------------------------*
FORM frm_get_file_path CHANGING cv_file TYPE rlgrap-filename.
  DATA:lv_rc TYPE i,
        lv_filename TYPE string,
        lv_path     TYPE string,
        lv_fullpath TYPE string.

  CALL METHOD cl_gui_frontend_services=>file_save_dialog
    EXPORTING
      window_title = '选择文件'
      file_filter  = '(*.xls)|*.xls|'
    CHANGING
      filename     = lv_filename
      path         = lv_path
      fullpath     = lv_fullpath
    EXCEPTIONS
      OTHERS       = 5.

  IF sy-subrc EQ 0.
    cv_file = lv_fullpath.
  ENDIF.
ENDFORM.                    "FRM_GET_FILE_PATH