OLE操作EXCEL

最近知道了OLE这个概念,  打算学习学习


新建excel, 改变字体颜色, 填充单元格颜色; 下面两段代码效果一样

代码1

PARAMETERS: pr_xls(64) TYPE c DEFAULT 'D:\ZEROTEST.XLS'.

INCLUDE ole2incl.
DATA: excel     TYPE ole2_object,
      workbook  TYPE ole2_object,
      sheet     TYPE ole2_object,
      cell      TYPE ole2_object,
      row       TYPE ole2_object,
      font      TYPE ole2_object,
      int       TYPE ole2_object.

DATA: lv_rc TYPE c.

CREATE OBJECT excel 'EXCEL.APPLICATION'.

GET PROPERTY OF excel 'WORKBOOKS' = workbook.
CALL METHOD OF workbook 'ADD'.

CALL METHOD OF excel 'Worksheets' = sheet
  EXPORTING #1 = 1.
CALL METHOD OF sheet 'Activate'. " should be active otherwise can't be saved
SET PROPERTY OF sheet 'Name' = 'Sheet1'.

* Select cell and modify font color
CALL METHOD OF sheet 'CELLS' = cell
  EXPORTING #1 = 2 #2 = 3.
CALL METHOD OF cell 'FONT' = font.
SET PROPERTY OF font 'SIZE' = '24'.
SET PROPERTY OF cell 'VALUE' = 'zero' .
SET PROPERTY OF font 'COLORINDEX' = '4'.
FREE OBJECT CELL.

* Fill the cell color
CALL METHOD OF sheet 'CELLS' = cell
  EXPORTING #1 = 2 #2 = 4.
CALL METHOD OF cell 'INTERIOR' = int.
SET PROPERTY OF int 'ColorIndex' = '4'.


*  release and exit Excel.
CALL METHOD OF sheet 'SAVEAS'
  EXPORTING
  #1 = pr_xls.
CALL METHOD OF excel 'QUIT'.

*  Free all objects
FREE OBJECT font.
FREE OBJECT cell.
FREE OBJECT sheet.
FREE OBJECT workbook.
FREE OBJECT excel.
excel-handle = -1.
FREE OBJECT row.


代码2

INCLUDE ole2incl.

DATA: excel         TYPE ole2_object,
      workbook      TYPE ole2_object,
      sheet         TYPE ole2_object,
      cell          TYPE ole2_object,
      font          TYPE ole2_object,
      int           TYPE ole2_object.

PARAMETERS p_file TYPE string DEFAULT 'D:\ZEROTEST.XLS'.

* Run Excel
CREATE OBJECT excel 'EXCEL.APPLICATION'.

* get reference to workbook list in application
* it is empty right now
CALL METHOD OF excel 'WORKBOOKS' = workbook.
CALL METHOD OF workbook 'ADD'.
* get refer to active workbook
GET PROPERTY OF excel 'ACTIVEWORKBOOK' = workbook.

* Select cell and modify font color
CALL METHOD OF excel 'CELLS' = cell
  EXPORTING #1 = 2 #2 = 3.
CALL METHOD OF cell 'FONT' = font.
SET PROPERTY OF font 'SIZE' = '24'.
SET PROPERTY OF cell 'VALUE' = 'zero' .
SET PROPERTY OF font 'COLORINDEX' = '4'.
FREE OBJECT CELL.

* Fill the cell color
CALL METHOD OF excel 'CELLS' = cell
  EXPORTING #1 = 2 #2 = 4.
CALL METHOD OF cell 'INTERIOR' = int.
SET PROPERTY OF int 'COLORINDEX' = '4'.

* save workbook
CALL METHOD OF workbook 'SAVEAS'
  EXPORTING
  #1 = p_file.

* quit excel
CALL METHOD OF excel 'QUIT'.

*  Free all objects
FREE OBJECT font.
FREE OBJECT cell.
FREE OBJECT workbook.
FREE OBJECT excel.
excel-handle = -1.

打开excel, insert行, 填写数据

PARAMETERS: pr_xls(64) TYPE c DEFAULT 'D:\ZEROTEST.XLS'.

INCLUDE ole2incl.

TYPES: BEGIN OF ty_person,
  name(8) TYPE c,
  location(2) TYPE c,
END OF ty_person.

DATA: excel     TYPE ole2_object,
      workbook  TYPE ole2_object,
      sheet     TYPE ole2_object,
      cell      TYPE ole2_object,
      row       TYPE ole2_object.

DATA: lt_person TYPE STANDARD TABLE OF ty_person,
      lw_person TYPE ty_person.

CLEAR lw_person.
lw_person-name = 'Palm'.
lw_person-location = '31F'.
APPEND lw_person TO lt_person.

CLEAR lw_person.
lw_person-name = 'Luke'.
lw_person-location = '13F'.
APPEND lw_person TO lt_person.

CLEAR lw_person.
lw_person-name = 'Zero'.
lw_person-location = '14F'.
APPEND lw_person TO lt_person.

*  Create an Excel object and start Excel.
CREATE OBJECT excel 'EXCEL.APPLICATION'.
IF sy-subrc <> 0.
  MESSAGE 'The excel object can''t be created' TYPE 'I'.
ENDIF.

*  Create an Excel workbook Object.
CALL METHOD OF excel 'WORKBOOKS' = workbook .

*  Transfer the header line to Excel.
CALL METHOD OF workbook 'OPEN' EXPORTING #1 = pr_xls.
CALL METHOD OF excel 'RANGE' = cell EXPORTING #1 = 'A1'.
SET PROPERTY OF cell 'VALUE' = 'Name' .
CALL METHOD OF excel 'RANGE' = cell EXPORTING #1 = 'B1'.
SET PROPERTY OF cell 'VALUE' = 'Location' .

LOOP AT lt_person INTO lw_person.
  CALL METHOD OF excel 'ROWS' = row EXPORTING #1 = '2' .
  CALL METHOD OF row 'INSERT'.
  CALL METHOD OF excel 'RANGE' = cell EXPORTING #1 = 'A2' .
  SET PROPERTY OF cell 'VALUE' = lw_person-name.
  CALL METHOD OF excel 'RANGE' = cell EXPORTING #1 = 'B2' .
  SET PROPERTY OF cell 'VALUE' = lw_person-location.
ENDLOOP.

*  release and exit Excel.
CALL METHOD OF excel 'SAVE'.
CALL METHOD OF excel 'QUIT'.

*  Free all objects
FREE OBJECT cell.
FREE OBJECT workbook.
FREE OBJECT excel.
excel-handle = -1.
FREE OBJECT row.


当我写完这段代码后, 发现弹出一个框a file named 'resume.xlw' already exists. 对Workbook的save会同时保存工作区, 默认保存在My document里, 第二次运行这个程序,对话框就会弹出来, 而对于Active workbook的save就不会保存工作区.


这个是excel的工作区(workspace), 它的作用是保存Excel编辑的布局,比如我打开了两个Excel文件,  zero1.xls和zero2.xls, 我可以把布局保存成下图这样. 下次我们打开文件'resume.xlw', 就会自动打开这两个文件并且恢复这个布局.



下面是解决方法, 改了两行代码

PARAMETERS: pr_xls(64) TYPE c DEFAULT 'D:\ZEROTEST.XLS'.

INCLUDE ole2incl.

TYPES: BEGIN OF ty_person,
  name(8) TYPE c,
  location(2) TYPE c,
END OF ty_person.

DATA: excel     TYPE ole2_object,
      workbook  TYPE ole2_object,
      sheet     TYPE ole2_object,
      cell      TYPE ole2_object,
      row       TYPE ole2_object.

DATA: lt_person TYPE STANDARD TABLE OF ty_person,
      lw_person TYPE ty_person.

CLEAR lw_person.
lw_person-name = 'Palm'.
lw_person-location = '31F'.
APPEND lw_person TO lt_person.

CLEAR lw_person.
lw_person-name = 'Luke'.
lw_person-location = '13F'.
APPEND lw_person TO lt_person.

CLEAR lw_person.
lw_person-name = 'Zero'.
lw_person-location = '14F'.
APPEND lw_person TO lt_person.

*  Create an Excel object and start Excel.
CREATE OBJECT excel 'EXCEL.APPLICATION'.
IF sy-subrc <> 0.
  MESSAGE 'The excel object can''t be created' TYPE 'I'.
ENDIF.

*  Create an Excel workbook Object.
CALL METHOD OF excel 'WORKBOOKS' = workbook .

*  Transfer the header line to Excel.
CALL METHOD OF workbook 'OPEN' EXPORTING #1 = pr_xls.
CALL METHOD OF excel 'RANGE' = cell EXPORTING #1 = 'A1'.
SET PROPERTY OF cell 'VALUE' = 'Name' .
CALL METHOD OF excel 'RANGE' = cell EXPORTING #1 = 'B1'.
SET PROPERTY OF cell 'VALUE' = 'Location' .

LOOP AT lt_person INTO lw_person.
  CALL METHOD OF excel 'ROWS' = row EXPORTING #1 = '2' .
  CALL METHOD OF row 'INSERT'.
  CALL METHOD OF excel 'RANGE' = cell EXPORTING #1 = 'A2' .
  SET PROPERTY OF cell 'VALUE' = lw_person-name.
  CALL METHOD OF excel 'RANGE' = cell EXPORTING #1 = 'B2' .
  SET PROPERTY OF cell 'VALUE' = lw_person-location.
ENDLOOP.

GET PROPERTY OF excel 'ACTIVEWORKBOOK' = workbook.

*  release and exit Excel.
CALL METHOD OF workbook 'SAVE'.
CALL METHOD OF excel 'QUIT'.

*  Free all objects
FREE OBJECT cell.
FREE OBJECT workbook.
FREE OBJECT excel.
excel-handle = -1.
FREE OBJECT row.
相关文章
相关标签/搜索