简单abap OLE实例

OLE分大致3步:

1.SMW0上传模板到服务器

2.在程序中下载模板

3.下载完模板后，打开模板文件，填入数据

1.SMW0上传模板到服务器:不详细叙述

excel模板内容如下：

2.编程

*&---------------------------------------------------------------------**& Report  ZLEARNING12_OLE_FIST*&*&---------------------------------------------------------------------**&*&*&---------------------------------------------------------------------*REPORT ZLEARNING12_OLE_FIST.INCLUDE OLE2INCL.*&---------------------------------------------------------------------**&内表和工作区声明*&---------------------------------------------------------------------*DATA: it_data TYPE TABLE OF MAKT WITH HEADER LINE, "      wa_data LIKE LINE OF it_data.                   "DATA: workbook TYPE OLE2_OBJECT,        excel TYPE ole2_object,      sheet TYPE ole2_object,      cell TYPE ole2_object.DATA: fname LIKE rlgrap-filename.DATA: index TYPE i.TABLES: MAKT.*&---------------------------------------------------------------------**&选择屏幕的定义*&---------------------------------------------------------------------*SELECTION-SCREEN BEGIN OF BLOCK blk0 WITH FRAME TITLE text-001.SELECT-OPTIONS: S_MATNR FOR MAKT-MATNR. "SELECT-OPTIONS: S_SPRAS FOR MAKT-SPRAS . "SELECTION-SCREEN END OF BLOCK blk0.*&---------------------------------------------------------------------**&INITIALIZATION*&---------------------------------------------------------------------**&---------------------------------------------------------------------**&选择屏幕事件*&---------------------------------------------------------------------**&---------------------------------------------------------------------**&START-OF-SELECTION 程序开始*&---------------------------------------------------------------------*START-OF-SELECTION.  PERFORM temp_excel_get USING 'ZOLE3011' ."从服务器下载模板*   下载完模板后，打开模板文件，填入数据  PERFORM frm_excel_open.*  &---------------------------------------------------------------------**取数放到it_data  PERFORM frm_get_data.*将取出的数放入excel  LOOP AT it_data INTO wa_data.    index = sy-tabix + 2."从第三行开始写入数据*    PERFORM excel_row_insert USING sheet index 1.    PERFORM fill_range USING index 1 wa_data-MANDT.    PERFORM fill_range USING index 2 wa_data-MATNR.    PERFORM fill_range USING index 3 wa_data-SPRAS.    PERFORM fill_range USING index 4 wa_data-MAKTX.    PERFORM fill_range USING index 5 wa_data-MAKTG.  ENDLOOP.*设置EXCEL中所插入的数据行边框线格式为黑色有边框*  bod = tab.*  CONDENSE bod NO-GAPS.*  CONCATENATE 'A6:F' bod INTO bod.**  PERFORM borderrange USING excel bod.  PERFORM sub_excel_save."保存excel数据*  PERFORM frm_data_display.**&---------------------------------------------------------------------***& END-OF-SELECTION 程序结束**&---------------------------------------------------------------------***&---------------------------------------------------------------------**&子程序部分*&---------------------------------------------------------------------**下载EXCEL模板FORM*----------------------------------------------------------------------**      -->VALUE(templat)    上传的excel模板名*      <--VALUE(ls_destination)    返回excel文件模板对象*      *----------------------------------------------------------------------*FORM  temp_excel_get USING template TYPE any.  DATA:  lo_objdata LIKE wwwdatatab,         lo_mime LIKE w3mime,         lc_filename  TYPE string VALUE 'ole',"默认名         lc_fullpath  TYPE string ,  "C:\Users\yang\Desktop\文件名         lc_path      TYPE  string , "C:\Users\yang\Desktop\   不包括文件名         ls_destination LIKE rlgrap-filename,         ls_objnam TYPE string,         li_rc LIKE sy-subrc,         ls_errtxt TYPE string.  DATA:p_objid TYPE wwwdatatab-objid,       p_dest LIKE sapb-sappfad.*  p_objid = 'ZOLE3011'. "此处为EXCEL模板名称   p_objid = template.  CONCATENATE lc_filename '_' SY-DATUM '_' SY-UZEIT               INTO lc_filename.  "给模板命名  CALL METHOD cl_gui_frontend_services=>file_save_dialog "调用保存对话框    EXPORTING      default_extension    = 'XLS'      default_file_name    = lc_filename    CHANGING      filename             = lc_filename      path                 = lc_path      fullpath             = lc_fullpath    EXCEPTIONS      cntl_error           = 1      error_no_gui         = 2      not_supported_by_gui = 3      OTHERS               = 4.  IF lc_fullpath = ''.    MESSAGE  '不能打开excel' TYPE 'E'.  ENDIF.  IF sy-subrc = 0.    p_dest = lc_fullpath.*    concatenate p_objid '.XLS' into ls_objnam.    CONDENSE ls_objnam NO-GAPS.    SELECT SINGLE relid objid FROM wwwdata INTO CORRESPONDING FIELDS OF lo_objdata           WHERE srtf2 = 0 AND relid = 'MI' AND objid = p_objid.*检查表wwwdata中是否存在所指定的模板文件    IF sy-subrc NE 0 OR lo_objdata-objid EQ space."如果不存在，则给出错误提示      CONCATENATE '模板文件' ls_objnam '不存在' INTO ls_errtxt.      MESSAGE ls_errtxt TYPE 'I'.    ENDIF.    ls_destination = p_dest. "保存路径*如果存在，调用DOWNLOAD_WEB_OBJECT 函数下载模板到路径下    CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'      EXPORTING        key         = lo_objdata        destination = ls_destination      IMPORTING        rc          = li_rc.    IF li_rc NE 0.      CONCATENATE '模板文件：' ls_objnam '下载失败' INTO ls_errtxt.      MESSAGE ls_errtxt TYPE 'E'.    ENDIF.      fname = ls_destination.  "fname 全局  ENDIF.ENDFORM.                    "fm_excel*&---------------------------------------------------------------------**&      Form  FRM_EXCEL_OPEN*&---------------------------------------------------------------------**       打开excel*----------------------------------------------------------------------**  -->  p1        text*  <--  p2        text*----------------------------------------------------------------------*FORM FRM_EXCEL_OPEN .  CLEAR: workbook,excel,sheet,cell,index.  CREATE OBJECT excel 'EXCEL.APPLICATION'.  "Create EXCEL OBJECT  IF sy-subrc NE 0.    EXIT.  ENDIF.  SET PROPERTY OF excel 'Visible' = 1.  "1/0 是否立即显示EXCEL  CALL METHOD OF      excel      'Workbooks' = workbook.  CALL METHOD OF      workbook      'Open'    EXPORTING      #1       = fname."打开上面下载路径下的excel文件  CALL METHOD OF      excel 'Sheets' = sheet    EXPORTING      #1       = 1.  CALL METHOD OF      sheet 'Select'.  CALL METHOD OF sheet 'ACTIVATE'. "sheet 激活*  SET PROPERTY OF sheet 'NAME' = sheetname. "设定sheet名称  SET PROPERTY OF sheet 'NAME' = 'SHEET1'.ENDFORM.                    " FRM_EXCEL_OPEN*&---------------------------------------------------------------------**& 向excel中的指定行插入N行*&---------------------------------------------------------------------*FORM excel_row_insert USING lcobj_sheet                            lc_row                            lc_count.  DATA lc_range TYPE ole2_object.  DATA h_borders  TYPE ole2_object.  DO lc_count TIMES.    CALL METHOD OF        lcobj_sheet        'Rows'      = lc_range      EXPORTING        #1          = 6.    CALL METHOD OF lc_range 'Copy'.  "COPY第6行插入一个新行    CALL METHOD OF        lcobj_sheet        'Rows'      = lc_range      EXPORTING        #1          = lc_row.    CALL METHOD OF        lc_range        'Insert'.    CALL METHOD OF lc_range 'ClearContents'. "是否需要清空Cell  ENDDO.ENDFORM.                    "excel_row_insert*&---------------------------------------------------------------------**&      Form  fill_range*&---------------------------------------------------------------------**       text  填充EXCEL 单元格*----------------------------------------------------------------------**      -->VALUE(F_ROW)    text*      -->VALUE(F_COL)    text*      -->VALUE(F_VALUE)  text*----------------------------------------------------------------------*FORM fill_range USING value(f_row)                      value(f_col)                      value(f_value).  DATA:    row TYPE i,    col TYPE i.  row = f_row.  col = f_col.  CALL METHOD OF      excel      'CELLS' = cell    EXPORTING      #1      = row      #2      = col.  SET PROPERTY OF cell 'VALUE' = f_value.ENDFORM.                    "fill_range*&---------------------------------------------------------------------**&      Form  borderrange*&---------------------------------------------------------------------**       text：设置EXCEL中所插入的数据行边框线格式*----------------------------------------------------------------------**      -->LCOBJ_EXCEL  text*      -->RANGE        text*----------------------------------------------------------------------*FORM borderrange USING lcobj_excel                       range .  DATA: lc_cell TYPE ole2_object ,        lc_borders TYPE ole2_object .  CALL METHOD OF      lcobj_excel      'RANGE'     = lc_cell    EXPORTING      #1          = range.  DO 4 TIMES .    CALL METHOD OF        lc_cell        'BORDERS' = lc_borders      EXPORTING        #1        = sy-index.    SET PROPERTY OF lc_borders 'LineStyle' = '1'.    SET PROPERTY OF lc_borders 'WEIGHT' = 2.                "4=max    SET PROPERTY OF lc_borders 'ColorIndex' = '1'.  ENDDO.  FREE OBJECT lc_borders.  FREE OBJECT lc_cell.ENDFORM.                    "borderrange*&---------------------------------------------------------------------**&      Form  FRM_GET_DATA*&---------------------------------------------------------------------**       text*----------------------------------------------------------------------**  -->  p1        text*  <--  p2        text*----------------------------------------------------------------------*FORM FRM_GET_DATA .  CLEAR: it_data, wa_data.  SELECT *    FROM MAKT UP TO 5 ROWS    INTO CORRESPONDING FIELDS OF TABLE it_data    WHERE MAKT~MATNR IN s_matnr    AND MAKT~spras IN S_SPRAS.ENDFORM.                    " FRM_GET_DATA*&---------------------------------------------------------------------**&      Form  sub_excel_save*&---------------------------------------------------------------------**       text*----------------------------------------------------------------------*FORM sub_excel_save.  GET PROPERTY OF excel 'ActiveSheet' = sheet. "获取活动SHEET  FREE OBJECT sheet.  FREE OBJECT workbook.  GET PROPERTY OF excel 'ActiveWorkbook' = workbook.  CALL METHOD OF      workbook      'SAVE'.  SET PROPERTY OF excel 'Visible' = 1.  "是否显示EXCEL 此处显示不退出* SET PROPERTY OF excel 'Visible' = 1.*  CALL METHOD OF workbook 'CLOSE'.*  CALL METHOD OF excel 'QUIT'. 注释部分为不显示直接退出  FREE OBJECT sheet.  FREE OBJECT workbook.  FREE OBJECT excel.ENDFORM.                    "save_book

运行效果（只显示前五行数据）：