OLE使用

ABAP操作EXCEL有多重方法，今天记录一下OLE，具体步骤如下：

1. 首先要上载EXCEL模板事物代码：SMW0，具体步骤参考本博客 http://www.cnblogs.com/caizjian/p/8806419.html

2.下载EXCEL模板：

*===指定文件存放本地路径
  DATA: lv_folder TYPE string.
  DATA: lv_title  TYPE string VALUE '指定文件存放本地目录'.
  CALL METHOD cl_gui_frontend_services=>directory_browse
    EXPORTING
      window_title         =  lv_title  "弹出窗标题
      "initial_folder       =
    CHANGING
      selected_folder      = lv_folder  "得到文件夹
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4.
  IF sy-subrc <> 0.
   MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
              WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.

 DATA:
   ls_wwwdata_item LIKE wwwdatatab,      "
   lv_file         TYPE rlgrap-filename  VALUE 'IT需求单', "
   lv_objid        TYPE wwwdatatab-objid VALUE 'ZABAP002_IT.XLSX'.

*===指定本地文件存放路径
CONCATENATE lv_folder '/' lv_file '_' gs_it-action_no '.XLSX' INTO lv_file.

*===下载文档
DATA: ls_wwwdata TYPE wwwdata.

  SELECT SINGLE *
  INTO  ls_wwwdata
  FROM wwwdata
   WHERE objid = lv_objid
    AND srtf2 = ( SELECT MAX( srtf2 ) FROM wwwdata WHERE objid = lv_objid ).

MOVE-CORRESPONDING ls_wwwdata TO ls_wwwdata_item.

CALL FUNCTION 'DOWNLOAD_WEB_OBJECT' "
EXPORTING
  key         = ls_wwwdata_item
  destination = lv_file.

OLE步骤：

*===定义OLE变量
DATA:excel    TYPE ole2_object,
     workbook TYPE ole2_object,
     sheet    TYPE ole2_object,
     cell     TYPE ole2_object.


*===正在处理提示
  PERFORM frm_process_prompt USING lv_file. "lv_file是文件路径

*===打开EXCEL
  PERFORM frm_excel_open     USING lv_file.

*===EXCEL赋值
  PERFORM frm_excel_value    USING lt_lines. "LT_LINES 是传输的数据table
*===释放对象
  PERFORM frm_excel_free.

下面展示各FORM：

frm_process_prompt

FORM frm_process_prompt USING p_file TYPE rlgrap-filename.
  DATA: l_dis(150) TYPE c.
  CONCATENATE '正在处理文件:' p_file INTO l_dis.
  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
  EXPORTING
    text = l_dis.
ENDFORM.

　打开excel： frm_excel_open

FORM frm_excel_open  USING  p_file TYPE rlgrap-filename.
  CREATE OBJECT excel 'EXCEL.APPLICATION'.
  IF sy-subrc <> 0.
    MESSAGE 'Excel开打失败!.' TYPE 'S'.
    STOP.
  ELSE.
    SET PROPERTY OF excel 'VISIBLE' = 1.
    CALL METHOD OF excel 'WORKBOOKS' = workbook. "新产生一个Excel
    CALL METHOD OF workbook 'OPEN'
    EXPORTING
      #1 = p_file.    "要打开的Excel的路径
    GET PROPERTY OF excel 'ACTIVECELL' = sheet.
  ENDIF.
  CALL METHOD OF excel 'WORKSHEETS' = sheet
  EXPORTING
    #1 = 'SHEET1'.
  CALL METHOD OF sheet 'ACTIVATE'.
ENDFORM.

给excel赋值：frm_excel_value

FORM frm_excel_value USING lt_lines TYPE STANDARD TABLE.

 DATA  ls_lines  TYPE tline.
 DATA  lv_value  TYPE char10.
 DATA  lv_deatil TYPE string.
*打开第一个Sheet
  CALL METHOD OF excel 'WORKSHEETS' = sheet
  EXPORTING
    #1 = 1.
  CALL METHOD OF sheet 'ACTIVATE'.


  PERFORM frm_cell_set USING 11 2 gs_it-action_no.   "需求编号

  PERFORM frm_cell_set USING 11 4 gs_it-rq_name.     "修改顾问

  PERFORM frm_cell_set USING 11 7 gs_it-ymodule.     "所属模块

  CLEAR lv_value.
  lv_value = gs_it-zdays.
  PERFORM frm_cell_set USING 12 2 lv_value.          "工时/人天

  PERFORM frm_cell_set USING 12 4 gs_it-priority.    "优先级

  CLEAR lv_value.
  WRITE gs_it-podate TO lv_value.
  PERFORM frm_cell_set USING 12 7 lv_value.          "计划完成日期


  LOOP AT lt_lines INTO ls_lines.
    CONCATENATE lv_deatil ls_lines-tdline INTO lv_deatil.
  ENDLOOP.

  PERFORM frm_cell_set USING 17 1 lv_deatil.         "需求说明

*=====保存
  GET PROPERTY OF excel 'ACTIVESHEET' = sheet.

  GET PROPERTY OF excel 'ACTIVEWORKBOOK' = workbook.

  CALL METHOD OF workbook 'SAVE'.
ENDFORM.                    " FRM_EXCEL_VALUE

释放对象：frm_excel_free

FORM frm_excel_free .
  FREE: excel,workbook,sheet,cell.
  CLEAR gs_it.
ENDFORM.                    " FRM_EXCEL_FREE