ABAP使用OLE2对象创建EXCEL文件

最新推荐文章于 2024-03-14 16:56:53 发布

转载

最新推荐文章于 2024-03-14 16:56:53 发布 · 692 阅读

3 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/hhelibeb/p/5787396.html

这篇博客介绍了如何利用ABAP的OLE2对象创建具有丰富格式的EXCEL文件，包括设置单元格值、修改字体样式、添加边框、验证数据以及创建下拉菜单。通过对比不同的实现方式，展示了复制剪贴板以提升性能的方法，并提供了有用的子程序代码示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

厌倦了总是下载一模一样的EXCEL文档？没有颜色，边框，有效性验证....

让我们看看怎样用OLE2对象来创造可爱的EXCEL工作表吧！（效果如下）

OLE2创建的EXCEL工作表

首先你需要知道微软EXCEL中的不同部分的名称，每个部分在我们的程序中都代表一个OLE2对象

EXCEL的不同部分

本文地址：http://www.cnblogs.com/hhelibeb/p/5787396.html

原文地址：Using ole2 objects for create an excel file

转载请注明

2018.03.29：做了一年多ABAP开发工作之后，译者认为OLE是个很难用很烦人的东西，强烈推荐使用XLSX Workbench进行EXCEL表单/报表的开发工作：

XLSX Workbench for SAP

开始

　　所有例子都使用了下面这个模板报表。你只需要复制代码，并且粘贴到为它预留的空白位置。

　　在这个报表当中，你将会看到如何创建一个新文档，如何保存它，以及如何关闭它。

REPORT zric_ole2.
TYPE-POOLS: soi,ole2.
DATA:  lo_application   TYPE  ole2_object,
       lo_workbook      TYPE  ole2_object,
       lo_workbooks     TYPE  ole2_object,
       lo_range         TYPE  ole2_object,
       lo_worksheet     TYPE  ole2_object,
       lo_worksheets    TYPE  ole2_object,
       lo_column        TYPE  ole2_object,
       lo_row           TYPE  ole2_object,
       lo_cell          TYPE  ole2_object,
       lo_font          TYPE ole2_object.

  DATA: lo_cellstart      TYPE ole2_object,
        lo_cellend        TYPE ole2_object,
        lo_selection      TYPE ole2_object,
        lo_validation     TYPE ole2_object.

  DATA: lv_selected_folder TYPE string,
        lv_complete_path   TYPE char256,
        lv_titulo          TYPE string.

    CALL METHOD cl_gui_frontend_services=>directory_browse
      EXPORTING
        window_title    = lv_titulo
        initial_folder  = 'C:\'
      CHANGING
        selected_folder = lv_selected_folder
      EXCEPTIONS
        cntl_error      = 1
        error_no_gui    = 2
        OTHERS          = 3.
  CHECK NOT lv_selected_folder IS INITIAL.

  CREATE OBJECT lo_application 'Excel.Application'.
  CALL METHOD OF lo_application 'Workbooks' = lo_workbooks.
  CALL METHOD OF lo_workbooks 'Add' = lo_workbook.
  SET PROPERTY OF lo_application 'Visible' = 0.
  GET PROPERTY OF lo_application 'ACTIVESHEET' = lo_worksheet.


* ----------
* ---- PASTE HERE THE CODE
* ----------


CONCATENATE lv_selected_folder '\Test' INTO lv_complete_path.

  CALL METHOD OF lo_workbook 'SaveAs'
    EXPORTING
    #1 = lv_complete_path.
  IF sy-subrc EQ 0.
     MESSAGE 'File downloaded successfully' TYPE 'S'.
  ELSE.
     MESSAGE 'Error downloading the file' TYPE 'E'.
  ENDIF.

  CALL METHOD OF lo_application 'QUIT'.
  FREE OBJECT lo_worksheet.
  FREE OBJECT lo_workbook.
  FREE OBJECT lo_application.

基本动作

  CALL METHOD OF lo_worksheet 'Cells' = lo_cell
     EXPORTING
     #1 = 1  "Row
     #2 = 2. "Column

Select a cell

* 1. Select starting cell
  CALL METHOD OF lo_worksheet 'Cells' = lo_cellstart
    EXPORTING
    #1 = 1
    #2 = 1.

* 2. Select ending cell
  CALL METHOD OF lo_worksheet 'Cells' = lo_cellend
    EXPORTING
    #1 = 3
    #2 = 3.

* Select the Range:
  CALL METHOD OF lo_worksheet 'RANGE' = lo_range
    EXPORTING
    #1 = lo_cellstart
    #2 = lo_cellend.

Select range of cells

CALL METHOD OF lo_worksheet 'Columns' = lo_column
    EXPORTING
    #1 = 1.

Select a column

CALL METHOD OF lo_worksheet 'Rows' = lo_row
    EXPORTING
    #1 = 1.

Select a row

* Select a Row
  CALL METHOD OF lo_worksheet 'Rows' = lo_row
    EXPORTING
    #1 = 1.

* Active the selection
  CALL METHOD OF lo_row 'Select'.* Get the selection object.
  CALL METHOD OF lo_application 'selection' = lo_selection.

Get the selection reference

CALL METHOD OF lo_application 'Worksheets' = lo_worksheet
    EXPORTING #1 = 2.

  CALL METHOD OF lo_worksheet 'Activate'.

Change the active worksheet

  SET PROPERTY OF lo_worksheet 'Name' = 'Hello!'.

Change the name of worksheet

  CALL METHOD OF lo_application 'Sheets' = lo_worksheets .
  CALL METHOD OF lo_worksheets 'Add' = new_worksheet.
  CALL METHOD OF new_worksheet 'Activate'.

Add worksheet

 GET PROPERTY OF lo_worksheet  'PageSetup' = lo_pagesetup.

 SET PROPERTY OF lo_pagesetup 'ZOOM' = 70.

Zoom

（译者注：原文的Add worksheet代码有遗漏，本人进行了补正）

修改内容

我认为理解这部分的工作原理的最佳方式是在EXCEL中创建一个宏，观察它的Visual Basic代码，以此“翻译”成ABAP代码。

要创建一个宏，首先你需要激活开发者标签，下面的链接解释了如何做这件事：

http://office.microsoft.com/en-us/excel-help/show-the-developer-tab-or-run-in-developer-mode-HA010173052.aspx

创建一个宏很简单，你可以按照这个链接中的办法做:

http://office.microsoft.com/en-us/excel-help/create-or-delete-a-macro-HP010014111.aspx

我也建议你下载一份VB语言参考作为指导：

http://msdn.microsoft.com/en-us/library/aa220733(v=office.11).aspx

对比VB代码和ABAP代码，你会理解它的工作原理。你不需要在任何情形下都把VB代码完全转换为ABAP，只转换你需要的部分。

1 - 选取一个单元格，设置值:

VB选取单元格设置值

CALL METHOD OF lo_worksheet 'Cells' = lo_cell
    EXPORTING
    #1 = 1  "Row
    #2 = 2. "Column

SET PROPERTY OF lo_cell 'Value' = 'Hello World'.

结果：

结果1

2- 修改字体大小

VB修改字体大小

  CALL METHOD OF lo_worksheet 'Cells' = lo_cell
      EXPORTING
      #1 = 1  "Row
      #2 = 2. "Column

  SET PROPERTY OF lo_cell 'Value' = 'Hello World'.
  CALL METHOD OF lo_cell 'FONT' = lo_font.
  SET PROPERTY OF lo_font 'Name' = 'Arial'.
  SET PROPERTY OF lo_font 'Size' = 15.

结果：

结果2

3- 颜色，粗体，下划线，斜体:

VB颜色粗体下划线斜体

    CALL METHOD OF lo_worksheet 'Cells' = lo_cell
      EXPORTING
      #1 = 1  "Row
      #2 = 2. "Column

  SET PROPERTY OF lo_cell 'Value' = 'Hello World'.
  CALL METHOD OF lo_cell 'FONT' = lo_font.

  SET PROPERTY OF lo_font 'Color' = -16776961.
  SET PROPERTY OF lo_font 'TintAndShade' = 0.

  SET PROPERTY OF lo_font 'Bold' = 1.
  SET PROPERTY OF lo_font 'Italic' = 1.
  SET PROPERTY OF lo_font 'Underline' = 2. "xlUnderlineStyleSingle

  DATA: lo_interior TYPE ole2_object.
  CALL METHOD OF lo_cell 'Interior' = lo_interior.
  SET PROPERTY OF lo_interior 'Color' = 15773696.

结果：

结果3

4- 添加边框

VB添加边框

  DATA: lo_borders TYPE ole2_object. 

  CALL METHOD OF lo_cell 'Borders' = lo_borders EXPORTING #1 = '7'. "xlEdgeLeft
  SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous

  CALL METHOD OF lo_cell 'Borders' = lo_borders EXPORTING #1 = '8'. "xlEdgeTop
  SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous

  CALL METHOD OF lo_cell 'Borders' = lo_borders EXPORTING #1 = '9'. "xlEdgeBottom
  SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous

  CALL METHOD OF lo_cell 'Borders' = lo_borders EXPORTING #1 = '10'. "xlEdgeRight
  SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous

* Increase the weight of the border if you want, in this case only for EdgeRight:
  SET PROPERTY OF lo_borders 'WEIGHT' = 4. "xlThick

结果：

结果4

5 - 修改单元格格式

VB修改单元格格式

CALL METHOD OF lo_worksheet 'Cells' = lo_cell
      EXPORTING
      #1 = 1  "Row
      #2 = 1. "Column
  SET PROPERTY OF lo_cell 'Value' = '1.23'.
  SET PROPERTY OF lo_cell 'NumberFormat' = '0.00'.

  CALL METHOD OF lo_worksheet 'Cells' = lo_cell
      EXPORTING
      #1 = 3  "Row
      #2 = 1. "Column
  SET PROPERTY OF lo_cell 'Value' = '02/01/2012'.
  SET PROPERTY OF lo_cell 'NumberFormat' = 'm/d/yyyy'.

  CALL METHOD OF lo_worksheet 'Cells' = lo_cell
      EXPORTING
      #1 = 5  "Row
      #2 = 1. "Column
  SET PROPERTY OF lo_cell 'NumberFormat' = '0.00'.
  SET PROPERTY OF lo_cell 'Value' = '1/2'.
  SET PROPERTY OF lo_cell 'NumberFormat' = '# ?/?'.

结果：

结果5

6 - 添加有效性验证

比如说，只允许2000.01和2010.01之间的日期，如果在这个范围之外，提示错误。

VB Add validation

  CALL METHOD OF lo_worksheet 'Cells' = lo_cell
      EXPORTING
      #1 = 1  "Row
      #2 = 1. "Column

  CALL METHOD OF lo_cell 'select'.
  CALL METHOD OF lo_application 'selection' = lo_selection.
  CALL METHOD OF lo_selection 'Validation' = lo_validation.

  CALL METHOD OF lo_validation 'Add'
    EXPORTING
    #1 = 4 "Type       = xlValidateDate
    #2 = 1 "AlertStype = xlValidAlertStop
    #3 = 1 "Operator   = xlBetween
    #4 = '1/1/2000' "Formula1
    #5 = '1/1/2010'."Formula2

  SET PROPERTY OF lo_validation 'ErrorMessage' = 'Enter a valid date'.

结果：

结果6

7 - 创建一个包含其它工作簿中的值的下拉菜单:

这里有一个在EXCEL里面创建的例子:

http://office.microsoft.com/en-us/excel-help/create-or-remove-a-drop-down-list-HP005202215.aspx

下拉菜单

DATA: lv_range_name TYPE char24 VALUE 'Values'.

* Go to sheet 2
  CALL METHOD OF lo_application 'Worksheets' = lo_worksheet
    EXPORTING #1 = 2.
  CALL METHOD OF lo_worksheet 'Activate'.

* Fill the cells with the values;
  DATA: lv_row TYPE i,
        lv_cont(4) TYPE n VALUE '0040',
        lv_num(4),
        lv_char.
  DO 7 TIMES.
    ADD 1 TO: lv_cont, lv_row.
    CALL METHOD OF lo_worksheet 'Cells' = lo_cell
        EXPORTING
        #1 = lv_row  "Row
        #2 = 1.       "Column
*   Convert num to ascii
    lv_num = lv_cont.
    lv_ch