ABAP使用OLE导出Excel详解编程语言

*&---------------------------------------------------------------------* 
*& Report  ZTEMP01 
*& 
*&---------------------------------------------------------------------* 
*& OLE应用示例 
*&---------------------------------------------------------------------* 
REPORT ztemp01. 
 
DATA: excel    TYPE ole2_object, 
      Workbook TYPE ole2_object, 
      sheet    TYPE ole2_object, 
      cell     TYPE ole2_object, 
      font     TYPE ole2_object, 
      column   TYPE ole2_object, 
      interior TYPE ole2_object, 
      range    TYPE ole2_object. 
DATA color     TYPE i. 
DATA color_row TYPE i. 
DATA color_col TYPE i. 
 
* 创建一个Excel对象 
CREATE OBJECT   excel   'EXCEL.APPLICATION'. 
SET PROPERTY OF excel   'Visible'   = 1.          "设置Excel可见 1；不可见 0 
SET PROPERTY OF excel   'SheetsInNewWorkbook' = 2."设置 Microsoft Excel 软件打开时，自动插入到新工作簿中的工作表数目（即初始sheet数目，默认名字依次为 Sheet1、Sheet2.....） 
CALL METHOD OF  excel   'Workbooks' = Workbook.   "在Excel对象中创建一个workbook 
CALL METHOD OF Workbook 'Add'       = sheet.      "在workbook中创建一个sheet页 
 
* 将A1~J1单元格合并为一个单元格 
CALL METHOD OF excel 'Range' = range 
  EXPORTING 
    #1 = 'A1' 
    #2 = 'J1'. 
CALL METHOD  OF range 'Select'. 
* 合并单元格 
SET PROPERTY OF range 'MergeCells' = 1. 
SET PROPERTY OF range 'HorizontalAlignment' = -4108. 
SET PROPERTY OF range 'VerticalAlignment'   = -4108. 
FREE range. 
CALL METHOD OF excel 'Range' = range 
  EXPORTING 
    #1 = 'A1' 
    #2 = 'A1'. 
SET PROPERTY OF range 'Value' =  'Excel导出'. 
FREE font. 
GET PROPERTY OF range 'Font' = font. 
SET PROPERTY OF font  'Bold' = 1. 
SET PROPERTY OF font  'Size' = 25. 
SET PROPERTY OF font 'Underline'  = 1. " 1 表示没有下划线, 2 表示有下划线. 
SET PROPERTY OF font 'ColorIndex' = 7. 
PERFORM fm_cell USING: 2 1  '列1', 
                       2 2  '列2', 
                       2 3  '列3', 
                       2 4  '列4', 
                       2 5  '列5', 
                       2 6  '列6', 
                       2 7  '列7', 
                       2 8  '列8', 
                       2 9  '列9', 
                       2 10 '列10', 
                       3 1   '●', 
                       3 2  '0001', 
                       3 3  '3000', 
                       3 4  '00001000096', 
                       3 5  'W00', 
                       3 6  '0002', 
                       3 7  'A', 
                       3 8  '32.56', 
                       3 9  'test', 
                       3 10 '文本', 
                       4 1  'OLE中的颜色编号：'. 
color = 0. 
color_row = 4. 
DO 6 TIMES. 
  color_row = color_row + 1. 
  color_col = 0. 
  DO 10 TIMES. 
    color_col = color_col + 1. 
    color = color + 1. 
    PERFORM fm_cell USING color_row color_col color. 
  ENDDO. 
ENDDO. 
 
GET PROPERTY OF excel 'ActiveSheet'    = sheet.   "激活工作簿 
GET PROPERTY OF excel 'ActiveWorkbook' = Workbook."激活工作区 
*CALL METHOD OF workbook 'saveas' 
*  EXPORTING 
*    #1 = 'C:/Users/ybin/Desktop/export.xlsx' 
*    #2 = 1. 
CALL METHOD OF workbook 'Close'.  "关闭工作区 
CALL METHOD OF excel 'Quit'.      "退出excel 
 
FREE OBJECT sheet. 
FREE OBJECT Workbook. 
FREE OBJECT excel. 
CLEAR:Workbook, sheet, excel,interior,range. 
 
*&---------------------------------------------------------------------* 
*& FORM fm_cell 
*&---------------------------------------------------------------------* 
FORM fm_cell USING row col value. 
  CALL METHOD  OF  excel 'Columns' = column. 
  SET PROPERTY OF column 'NumberFormat' = '@'."让数值按实际显示 
  CALL METHOD  OF column 'Autofit'.           "导出excel自动适应宽度 
  CALL METHOD  OF  excel 'Cells' = cell 
    EXPORTING 
      #1 = row 
      #2 = col. 
  SET PROPERTY OF cell 'Value'    = value. 
  SET PROPERTY OF cell 'HorizontalAlignment' = -4108. 
  FREE font. 
  GET PROPERTY OF cell 'Font'     = font. 
  GET PROPERTY OF cell 'Interior' = interior."单元格颜色 
  IF row >= 4. 
    SET PROPERTY OF interior 'ColorIndex' = color. 
  ENDIF. 
  IF row = 2. 
    SET PROPERTY OF font 'Bold' = 1. 
  ELSE. 
    SET PROPERTY OF font 'Bold' = 0. 
  ENDIF. 
  IF row = 2. 
    SET PROPERTY OF font 'ColorIndex' = 3. 
  ENDIF. 
 
ENDFORM.