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.

 附OLE颜色代码：

 

note:

1.与ole相关的关键字存储在表oleload中。

2.如果有些ole方法不熟悉，可以利用vba录制宏来寻找相关的关键字。

https://blog.csdn.net/qq_16116183/article/details/80531293