IT干货网

ABAP使用OLE2对象创建EXCEL文件

wyy 2022年06月08日 SAP 295 0

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

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

OLE2创建的EXCEL工作表

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

EXCEL的不同部分

本文地址:IT虾米网

原文地址: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代码。

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

IT虾米网

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

IT虾米网

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

IT虾米网

对比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

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

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

IT虾米网

下拉菜单

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_char = CL_ABAP_CONV_IN_CE=>uccp( lv_num ). 
    SET PROPERTY OF lo_cell 'Value' = lv_char. 
  ENDDO. 
 
* Select the range and set a name; 
* 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 = lv_cont  "Row 
    #2 = 1. 
* Select the Range: 
  CALL METHOD OF lo_worksheet 'RANGE' = lo_range 
    EXPORTING 
    #1 = lo_cellstart 
    #2 = lo_cellend. 
CALL METHOD OF lo_range 'select'. 
* Set a name to this Range 
SET PROPERTY OF lo_range 'Name' = lv_range_name. 
 
* Return to sheet 1 
  CALL METHOD OF lo_application 'Worksheets' = lo_worksheet 
    EXPORTING #1 = 1. 
  CALL METHOD OF lo_worksheet 'Activate'. 
 
* Select the cell A1 
  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. 
CONCATENATE '=' lv_range_name INTO lv_range_name. 
CALL METHOD OF lo_validation 'Add' 
  EXPORTING 
  #1 = 3 "'xlValidateList' 
  #2 = 1 "'xlValidAlertStop' 
  #3 = 1 "'xlBetween' 
  #4 = lv_range_name.
ABAP

结果:

结果7

改善性能

如果你想要下载大量数据,会花费很多时间。为了提高性能,我们从abap复制数据到剪切板,然后把它粘贴到excel里面。

比较两种方式的运行时间(代码已折叠):

DATA: lt_ekpo TYPE ekpo OCCURS 0 WITH HEADER LINE, 
        lv_cont TYPE i, 
        lv_row  TYPE i. 
 
   FIELD-SYMBOLS: <field>  TYPE ANY. 
 
*  Select some data; 
   SELECT * INTO TABLE lt_ekpo FROM ekpo UP TO 50ROWS. 
 
*  Print the data cell by cell: 
   LOOP AT lt_ekpo. 
     lv_cont = 1. 
     lv_row = sy-tabix. 
*    Write for example 15 columns per row.     
     DO 15 TIMES. 
      CALL METHOD OF lo_worksheet 'Cells' = lo_cell 
        EXPORTING 
        #1 = lv_row 
        #2 = lv_cont. 
       ASSIGN COMPONENT lv_cont OF STRUCTURE lt_ekpoTO <field>. 
       SET PROPERTY OF lo_cell 'Value' = <field>. 
       ADD 1 TO lv_cont. 
     ENDDO. 
   ENDLOOP.
Cell by Cell

一个单元格一个单元格地写入,要花费约145秒

时间花费

TYPES: ty_data(1500) TYPE c. 
  DATA: lt_data TYPE ty_data OCCURS 0 WITH HEADER LINE. 
  DATA: lt_ekpo TYPE ekpo OCCURS 0 WITH HEADER LINE, 
        lv_cont TYPE. 
 
 
  FIELD-SYMBOLS: <field>  TYPE ANY. 
 
* Select some data; 
  SELECT * INTO TABLE lt_ekpo FROM ekpo UP TO 50 ROWS. 
 
* Prepare the data before copy to clipboard; 
 
  LOOP AT lt_ekpo. 
     lv_cont = 1. 
*    Write for example 15 columns per row. 
     DO 15 TIMES. 
       ASSIGN COMPONENT lv_cont OF STRUCTURE lt_ekpo TO <field>. 
       CONCATENATE lt_data <field> INTO lt_data SEPARATED BY cl_abap_char_utilities=>horizontal_tab. 
       ADD 1 TO lv_cont. 
     ENDDO. 
     SHIFT lt_data BY 1 PLACES LEFT. 
     APPEND lt_data. CLEAR lt_data. 
  ENDLOOP. 
 
* Copy to clipboard into ABAP 
  CALL FUNCTION 'CONTROL_FLUSH' 
    EXCEPTIONS 
      OTHERS = 3. 
  CALL FUNCTION 'CLPB_EXPORT' 
    TABLES 
      data_tab   = lt_data 
    EXCEPTIONS 
      clpb_error = 1 
      OTHERS     = 2. 
 
* Select the cell A1 
  CALL METHOD OF lo_worksheet 'Cells' = lo_cell 
    EXPORTING 
    #1 = 1  "Row 
    #2 = 1. "Column 
 
* Paste clipboard from cell A1 
  CALL METHOD OF lo_cell 'SELECT'. 
  CALL METHOD OF lo_worksheet 'PASTE'.
Copy-Paste

使用复制粘贴的方法,花费的时间少于4秒!

时间花费

有用的子程序

我已经创建了一个包含文件,其中含有多个有用的子程序。包含文件的代码放在本文的末尾。你可以测试报表程序,观察它们是如何运行的。

  1 REPORT zric_ole2. 
  2  
  3 INCLUDE: zric_ole2_utils. 
  4  
  5 DATA: BEGIN OF lt_spfli OCCURS 0, 
  6         carrid   TYPE s_carr_id, 
  7         connid   TYPE s_conn_id, 
  8         cityfrom TYPE s_from_cit, 
  9         cityto   TYPE s_to_city, 
 10         deptime   TYPE s_dep_time, 
 11         arrtime   TYPE s_arr_time, 
 12       END OF lt_spfli. 
 13  
 14 DATA: lv_selected_folder TYPE string, 
 15       lv_complete_path   TYPE char256, 
 16       lv_title           TYPE string. 
 17  
 18 START-OF-SELECTION. 
 19  
 20   CALL METHOD cl_gui_frontend_services=>directory_browse 
 21     EXPORTING 
 22       window_title    = lv_title 
 23       initial_folder  = 'C:\' 
 24     CHANGING 
 25       selected_folder = lv_selected_folder 
 26     EXCEPTIONS 
 27       cntl_error      = 1 
 28       error_no_gui    = 2 
 29       OTHERS          = 3. 
 30   CHECK NOT lv_selected_folder IS INITIAL. 
 31  
 32 * Create the document; 
 33   PERFORM create_document. 
 34  
 35 * --------------------------------------------------------* 
 36 * Select some flights 
 37   SELECT carrid connid cityfrom cityto deptime  arrtime 
 38   INTO TABLE lt_spfli FROM spfli UP TO 20 ROWS. 
 39  
 40 * Fill a header with some data of the passenger: 
 41   gs_data = 'Passenger name'. APPEND gs_data TO gt_data. 
 42   gs_data = 'Passport'.       APPEND gs_data TO gt_data. 
 43   gs_data = 'Nacionality'.    APPEND gs_data TO gt_data. 
 44 * Add an empty line 
 45   CLEAR gs_data.  APPEND gs_data TO gt_data. 
 46  
 47 * Fill the positions: 
 48  
 49 * First a Header with the column's names 
 50   CLEAR gt_lines[]. 
 51   gs_lines-value = 'Airline Code'.      APPEND gs_lines TO gt_lines. 
 52   gs_lines-value = 'Connection Number'. APPEND gs_lines TO gt_lines. 
 53   gs_lines-value = 'Departure city'.    APPEND gs_lines TO gt_lines. 
 54   gs_lines-value = 'Arrival city'.      APPEND gs_lines TO gt_lines. 
 55   gs_lines-value = 'Departure time'.    APPEND gs_lines TO gt_lines. 
 56   gs_lines-value = 'Arrival time'.      APPEND gs_lines TO gt_lines. 
 57 * Add the header to data to be printed 
 58   PERFORM add_line2print_from_table. 
 59  
 60 * Print the rest of the data: 
 61   LOOP AT lt_spfli. 
 62     PERFORM add_line2print USING lt_spfli 0. 
 63   ENDLOOP. 
 64  
 65 * Copy-paste the data from cell A1 
 66   PERFORM paste_clipboard USING 1 1. 
 67  
 68 * Bold the header: 
 69   PERFORM change_format USING 1 1 3 1   "Range of cells 
 70                               0 space   "Font Colour 
 71                               0 space   "Background Colour 
 72                               12  'X'   "Size 
 73                               1   'X'.  "Bold 
 74  
 75 * Change the colour of the item's header. 
 76   PERFORM set_soft_colour USING 5 1 5 6 "Range of cells 
 77                                 c_theme_col_white 'X'      "Font Colour 
 78                                 0 space                    "Font TintAndShade 
 79                                 c_theme_col_light_blue 'X' "Background Colour 
 80                                 '0.49' 'X'.                "Bkg Col. TintAndShade 
 81  
 82 * Add borders 
 83   PERFORM add_border USING 5 1 25 6. 
 84  
 85 * Adjust the width of the cells to content 
 86   DATA: lo_columns TYPE ole2_object. 
 87   CALL METHOD OF go_application 'Columns' = lo_columns. 
 88   CALL METHOD OF lo_columns 'Autofit'. 
 89  
 90 * Align centered the two first columns of the item table 
 91   PERFORM align_cells USING 6 1 25 2 c_center. 
 92  
 93 * Set the width to the second column 
 94   PERFORM column_width USING 2 50. 
 95  
 96 * --------------------------------------------------------* 
 97 * Add a drop down list for select the city; 
 98  
 99 * Select cities: 
100   DATA: BEGIN OF lt_cities OCCURS 0, 
101           city TYPE s_city, 
102         END OF lt_cities, 
103         lv_lines TYPE i. 
104   SELECT city FROM sgeocity INTO TABLE lt_cities. 
105  
106 * Go to worksheet 2; 
107   CALL METHOD OF go_application 'Worksheets' = go_worksheet 
108     EXPORTING #1 = 2. 
109   CALL METHOD OF go_worksheet 'Activate'. 
110  
111 * Print the cities: 
112   CLEAR: gt_data[]. "Delete first the previous data 
113   LOOP AT lt_cities. 
114     PERFORM add_line2print USING lt_cities 0. 
115   ENDLOOP. 
116 * Copy-paste the data from cell A1 
117   PERFORM paste_clipboard USING 1 1. 
118  
119 * Set a name to this values: 
120   DESCRIBE TABLE lt_cities LINES lv_lines. 
121   PERFORM set_range_name USING 1 1 lv_lines 1 'cities'. 
122  
123 * Change the name of the worksheet: 
124   SET PROPERTY OF go_worksheet 'Name' = 'Cities'. 
125 * Lock the cells: 
126   PERFORM lock_cells USING 1 1 lv_lines 1. 
127  
128 * Return to the worksheet 1 and create the drop down list: 
129   CALL METHOD OF go_application 'Worksheets' = go_worksheet 
130     EXPORTING #1 = 1. 
131   CALL METHOD OF go_worksheet 'Activate'. 
132   PERFORM drop_down_list USING 6 3 25 3 'cities'. 
133 * Change the name of the worksheet: 
134   SET PROPERTY OF go_worksheet 'Name' = 'Flights'. 
135 * --------------------------------------------------------* 
136  
137 * If you have an internal table with a lot of fields 
138 * but you only need to print some of these fields 
139 * you can use the subrutine print_data_fieldcat: 
140  
141   DATA: lt_spfli_2 TYPE STANDARD TABLE OF spfli. 
142   SELECT * FROM SPFLI INTO TABLE lt_spfli_2. 
143  
144 * Go to worksheet 3; 
145   CALL METHOD OF go_application 'Worksheets' = go_worksheet 
146     EXPORTING #1 = 3. 
147   CALL METHOD OF go_worksheet 'Activate'. 
148  
149 * Fill the field catalog: 
150   gs_fieldcat-field = 'CARRID'. 
151   gs_fieldcat-text  = 'Airline Code'. 
152   gs_fieldcat-width = 0. 
153   APPEND gs_fieldcat TO gt_fieldcat. 
154   gs_fieldcat-field = 'COUNTRYFR'. 
155   gs_fieldcat-text  = 'Country Key'. 
156   gs_fieldcat-width = 20. 
157   APPEND gs_fieldcat TO gt_fieldcat. 
158   gs_fieldcat-field = 'CITYFROM'. 
159   gs_fieldcat-text  = 'Departure city'. 
160   gs_fieldcat-width = 25. 
161   APPEND gs_fieldcat TO gt_fieldcat. 
162   gs_fieldcat-field = 'CITYTO'. 
163   gs_fieldcat-text  = 'Arrival city'. 
164   gs_fieldcat-width = 25. 
165   APPEND gs_fieldcat TO gt_fieldcat. 
166  
167 * Print the data: 
168   PERFORM print_data_fieldcat USING lt_spfli_2 1 1 'X'. 
169   DESCRIBE TABLE gt_fieldcat LINES lv_lines. 
170 * Change the colour of the header. 
171   PERFORM set_soft_colour USING 1 1 1 lv_lines         "Range of cells 
172                                 c_theme_col_white 'X'  "Font Colour 
173                                 0 space                "Font TintAndShade 
174                                 c_theme_col_green 'X'  "Background Colour 
175                                 '0.49' 'X'.            "Bkg Col. TintAndShade 
176  
177 * Change the name of the worksheet: 
178   SET PROPERTY OF go_worksheet 'Name' = 'Data field catalog'. 
179  
180 * Return to the worksheet 1 
181   CALL METHOD OF go_application 'Worksheets' = go_worksheet 
182     EXPORTING #1 = 1. 
183   CALL METHOD OF go_worksheet 'Activate'. 
184  
185 * File name 
186   CONCATENATE lv_selected_folder '\Flights' INTO lv_complete_path. 
187  
188 * Save the document 
189   CALL METHOD OF go_workbook 'SaveAs' 
190     EXPORTING 
191     #1 = lv_complete_path. 
192   IF sy-subrc EQ 0. 
193     MESSAGE 'File downloaded successfully' TYPE 'S'. 
194   ELSE. 
195     MESSAGE 'Error downloading the file' TYPE 'E'. 
196   ENDIF. 
197  
198 * Close the document and free memory 
199   PERFORM close_document.
Test report: Example of use the include ZRIC_OLE2_UTILS

运行后,你可以下载到这样的一个excel:

download result

  1 *&---------------------------------------------------------------------* 
  2 *&  Include           ZRIC_OLE2_UTILS 
  3 *&---------------------------------------------------------------------* 
  4 *& Author: Ricardo Romero.          Feb. 2012. 
  5 *& http://scn.sap.com/people/ricardo.romeromata 
  6 *&---------------------------------------------------------------------* 
  7 *& 
  8 *& Versions Management. 
  9 *& 
 10 *& Versión No.    |         Author        |     Descrìption 
 11 *&     1.0            Ricardo Romero         Initial version. 
 12 *&     2.x 
 13 *&     3.x 
 14 *&---------------------------------------------------------------------* 
 15  
 16 TYPE-POOLS: soi,ole2. 
 17  
 18 DATA:  go_application   TYPE  ole2_object, 
 19        go_workbook      TYPE  ole2_object, 
 20        go_workbooks     TYPE  ole2_object, 
 21        go_worksheet     TYPE  ole2_object. 
 22  
 23 DATA: gv_lines          TYPE i. "Lines printed by the moment 
 24  
 25 * Data to be printed. 
 26 * You must to concatenate the fields of the line you want to print 
 27 * separated by cl_abap_char_utilities=>horizontal_tab. 
 28 * Use the subrutine add_line2print for fill the tabla. 
 29 TYPES: ty_data(1500) TYPE c. 
 30 DATA: gt_data TYPE TABLE OF ty_data, 
 31       gs_data LIKE LINE OF gt_data. 
 32  
 33 * Data to be printed. 
 34 * Fill the table with the text you want to print in a line. 
 35 * Use the subrutine add_line2print_from_table to pass the 
 36 * table. 
 37 TYPES: BEGIN OF ty_line, 
 38          value TYPE char255, 
 39        END OF ty_line. 
 40 DATA: gt_lines TYPE TABLE OF ty_line, 
 41       gs_lines LIKE LINE OF gt_lines. 
 42  
 43 * Fields to be printed 
 44 * Use the subrutine print_data_fieldcat. 
 45 TYPES: BEGIN OF ty_fieldcat, 
 46         field LIKE dd03d-fieldname,  "Field name in your internal table 
 47         text  LIKE dd03p-ddtext,     "Description of the column 
 48         width TYPE i,                "Width of the column 
 49        END OF  ty_fieldcat. 
 50 DATA: gt_fieldcat TYPE TABLE OF ty_fieldcat, 
 51       gs_fieldcat LIKE LINE OF gt_fieldcat. 
 52  
 53 * Some colours you can use: 
 54 CONSTANTS: 
 55            c_col_black       TYPE i VALUE 0, 
 56            c_col_white       TYPE i VALUE 2, 
 57            c_col_red         TYPE i VALUE 3, 
 58            c_col_light_green TYPE i VALUE 4, 
 59            c_col_dark_blue   TYPE i VALUE 5, 
 60            c_col_yellow      TYPE i VALUE 6, 
 61            c_col_pink        TYPE i VALUE 7, 
 62            c_col_light_blue  TYPE i VALUE 8, 
 63            c_col_brown       TYPE i VALUE 9. 
 64  
 65 * Theme Colours: 
 66 * Use the subrutine set_soft_colour. 
 67 CONSTANTS: 
 68            c_theme_col_white      TYPE i VALUE 1, 
 69            c_theme_col_black      TYPE i VALUE 2, 
 70            c_theme_col_yellow     TYPE i VALUE 3, 
 71            c_theme_col_dark_blue  TYPE i VALUE 4, 
 72            c_theme_col_light_blue TYPE i VALUE 5, 
 73            c_theme_col_red        TYPE i VALUE 6, 
 74            c_theme_col_green      TYPE i VALUE 7, 
 75            c_theme_col_violet     TYPE i VALUE 8, 
 76            c_theme_col_pal_blue   TYPE i VALUE 9, 
 77            c_theme_col_orange     TYPE i VALUE 10. 
 78  
 79 * Align: 
 80 CONSTANTS: 
 81           c_center TYPE i VALUE -4108, 
 82           c_left   TYPE i VALUE -4131, 
 83           c_right  TYPE i VALUE -4152. 
 84  
 85 *&---------------------------------------------------------------------* 
 86 *&      Form  CREATE_DOCUMENT 
 87 *&---------------------------------------------------------------------* 
 88 *  Instanciate the application, workbook and the first worksheet. 
 89 *----------------------------------------------------------------------* 
 90 *  -->  p1        text 
 91 *  <--  p2        text 
 92 *----------------------------------------------------------------------* 
 93 FORM create_document. 
 94  
 95   CREATE OBJECT go_application 'Excel.Application'. 
 96   CALL METHOD OF go_application 'Workbooks' = go_workbooks. 
 97   CALL METHOD OF go_workbooks 'Add' = go_workbook. 
 98   SET PROPERTY OF go_application 'Visible' = 0. 
 99   GET PROPERTY OF go_application 'ACTIVESHEET' = go_worksheet. 
100  
101 ENDFORM.                    " CREATE_DOCUMENT 
102  
103 *&---------------------------------------------------------------------* 
104 *&      Form  CLOSE_DOCUMENT 
105 *&---------------------------------------------------------------------* 
106 *   Close the document and free memory objects. 
107 *----------------------------------------------------------------------* 
108 *  -->  p1        text 
109 *  <--  p2        text 
110 *----------------------------------------------------------------------* 
111 FORM close_document. 
112  
113   CALL METHOD OF go_application 'QUIT'. 
114   FREE OBJECT go_worksheet. 
115   FREE OBJECT go_workbook. 
116   FREE OBJECT go_workbooks. 
117   FREE OBJECT go_application. 
118  
119 ENDFORM.                    " CLOSE_DOCUMENT 
120  
121 *&---------------------------------------------------------------------* 
122 *&      Form  PRINT_LINE 
123 *&---------------------------------------------------------------------* 
124 *  Print line cell by cell with colurs, etc. 
125 *----------------------------------------------------------------------* 
126 *  -->  p_data       Data to print 
127 *  -->  p_row        Number of the Row in excel to print 
128 *  -->  p_num_cols   Number of fields to be printed, if 0 all the fields 
129 *                    will be printed 
130 *  -->  p_colour     Colour of the font 
131 *  -->  p_colourx    Set to X if want to change the Colour 
132 *  -->  p_bkg_col    Background colour of the cell 
133 *  -->  p_bkg_colx   Set to X if want to change the Background colour 
134 *  -->  p_size       Size of the font 
135 *  -->  p_sizex      Set to X if want to change the Size 
136 *  -->  p_bold       Bold 
137 *  -->  p_boldx      Set to X if want to change to Bold 
138 *----------------------------------------------------------------------* 
139 FORM print_line 
140   USING 
141     p_data       TYPE any 
142     p_row        TYPE i 
143     p_num_cols   TYPE i 
144     p_colour     TYPE i 
145     p_colourx    TYPE char1 
146     p_bkg_col    TYPE i 
147     p_bkg_colx   TYPE char1 
148     p_size       TYPE i 
149     p_sizex      TYPE char1 
150     p_bold       TYPE i 
151     p_boldx      TYPE char1. 
152  
153   DATA: lo_font TYPE ole2_object, 
154         lo_cell TYPE ole2_object, 
155         lo_interior TYPE ole2_object, 
156         lv_cont TYPE i. 
157  
158   FIELD-SYMBOLS: <field> TYPE ANY. 
159  
160   DO. 
161     ADD 1 TO lv_cont. 
162     ASSIGN COMPONENT lv_cont OF STRUCTURE p_data TO <field>. 
163     IF sy-subrc NE 0. EXIT. ENDIF. 
164  
165 *   Select the cell; 
166     CALL METHOD OF go_worksheet 'Cells' = lo_cell 
167       EXPORTING 
168       #1 = p_row 
169       #2 = lv_cont. 
170 *   Assign the value; 
171     SET PROPERTY OF lo_cell 'Value' = <field>. 
172 *   Format: 
173     CALL METHOD OF lo_cell 'FONT' = lo_font. 
174 *   Colour: 
175     IF p_colourx EQ 'X'. 
176       SET PROPERTY OF lo_font 'ColorIndex' = p_colour. 
177     ENDIF. 
178 *   Background colour; 
179     IF p_bkg_colx EQ 'X'. 
180       CALL METHOD OF lo_cell 'Interior' = lo_interior. 
181       SET PROPERTY OF lo_interior 'ColorIndex' = p_bkg_col. 
182     ENDIF. 
183 *   Size 
184     IF p_sizex EQ 'X'. 
185       SET PROPERTY OF lo_font 'SIZE' = p_size. 
186     ENDIF. 
187 *   Bold 
188     IF p_boldx EQ 'X'. 
189       SET PROPERTY OF lo_font 'BOLD' = p_bold. 
190     ENDIF. 
191  
192 *   Exit the loop? 
193     IF lv_cont EQ p_num_cols. EXIT. ENDIF. 
194   ENDDO. 
195  
196 ENDFORM.                    "print_line 
197 *&---------------------------------------------------------------------* 
198 *&      Form  add_line2print 
199 *&---------------------------------------------------------------------* 
200 *& Add line to be printed in subrutine PASTE_CLIPBOARD 
201 *&---------------------------------------------------------------------* 
202 *  -->  p_data       Data to print 
203 *  -->  p_num_cols   Number of fields to be printed, if 0 all the field 
204 *                    will be printed 
205 *&---------------------------------------------------------------------* 
206 FORM add_line2print 
207     USING 
208     p_data       TYPE any 
209     p_num_cols   TYPE i. 
210  
211   FIELD-SYMBOLS: <field> TYPE ANY. 
212   DATA: lv_cont TYPE i, 
213         lv_char TYPE char128. 
214  
215   DATA: lo_abap_typedescr TYPE REF TO cl_abap_typedescr. 
216  
217   CLEAR gs_data. 
218   DO. 
219     ADD 1 TO lv_cont. 
220     ASSIGN COMPONENT lv_cont OF STRUCTURE p_data TO <field>. 
221     IF sy-subrc NE 0. EXIT. ENDIF. 
222  
223 *   Convert data depend on the kind type. 
224     CALL METHOD cl_abap_typedescr=>describe_by_data 
225       EXPORTING 
226         p_data      = <field> 
227       RECEIVING 
228         p_descr_ref = lo_abap_typedescr. 
229     CASE lo_abap_typedescr->type_kind. 
230 *     Char 
231       WHEN lo_abap_typedescr->typekind_char. 
232         CONCATENATE gs_data <field> INTO gs_data 
233           SEPARATED BY cl_abap_char_utilities=>horizontal_tab. 
234 *     Date 
235       WHEN lo_abap_typedescr->typekind_date. 
236         WRITE <field> TO lv_char DD/MM/YYYY. 
237         CONCATENATE gs_data lv_char INTO gs_data 
238           SEPARATED BY cl_abap_char_utilities=>horizontal_tab. 
239 *     Time 
240       WHEN lo_abap_typedescr->typekind_time. 
241         CONCATENATE <field>(2) <field>+2(2) <field>+4(2) INTO lv_char SEPARATED BY ':'. 
242         CONCATENATE gs_data lv_char INTO gs_data 
243           SEPARATED BY cl_abap_char_utilities=>horizontal_tab. 
244 *    Others 
245       WHEN OTHERS. 
246         WRITE <field> TO lv_char. 
247         CONCATENATE gs_data lv_char INTO gs_data 
248           SEPARATED BY cl_abap_char_utilities=>horizontal_tab. 
249     ENDCASE. 
250  
251 *   Exit the loop? 
252     IF lv_cont EQ p_num_cols. EXIT. ENDIF. 
253   ENDDO. 
254  
255 * Quit the first horizontal_tab: 
256   SHIFT gs_data BY 1 PLACES LEFT. 
257  
258   APPEND gs_data TO gt_data. CLEAR gs_data. 
259  
260 ENDFORM.                    "add_line2print 
261 *&---------------------------------------------------------------------* 
262 *&      Form  add_line2print_from_table 
263 *&---------------------------------------------------------------------* 
264 *& Add line to be printed in subrutine PASTE_CLIPBOARD from a table. 
265 *&---------------------------------------------------------------------* 
266 FORM add_line2print_from_table. 
267  
268   CLEAR gs_data. 
269   LOOP AT gt_lines INTO gs_lines. 
270     CONCATENATE gs_data gs_lines-value INTO gs_data 
271       SEPARATED BY cl_abap_char_utilities=>horizontal_tab. 
272   ENDLOOP. 
273  
274 * Quit the first horizontal_tab: 
275   SHIFT gs_data BY 1 PLACES LEFT. 
276  
277   APPEND gs_data TO gt_data. CLEAR gs_data. 
278  
279 ENDFORM.                    "add_line2print_from_table 
280 *&---------------------------------------------------------------------* 
281 *&      Form  PASTE_CLIPBOARD 
282 *&---------------------------------------------------------------------* 
283 *& Paste Clipboard from the cell passed by parameter 
284 *&---------------------------------------------------------------------* 
285 *  -->  p_row 
286 *  -->  p_col 
287 *&---------------------------------------------------------------------* 
288 FORM paste_clipboard USING p_row TYPE i 
289                            p_col TYPE i. 
290  
291   DATA: lo_cell TYPE ole2_object. 
292  
293 * Copy to clipboard into ABAP 
294   CALL FUNCTION 'CONTROL_FLUSH' 
295     EXCEPTIONS 
296       OTHERS = 3. 
297   CALL FUNCTION 'CLPB_EXPORT' 
298     TABLES 
299       data_tab   = gt_data 
300     EXCEPTIONS 
301       clpb_error = 1 
302       OTHERS     = 2. 
303  
304 * Select the cell A1 
305   CALL METHOD OF go_worksheet 'Cells' = lo_cell 
306     EXPORTING 
307     #1 = p_row 
308     #2 = p_col. 
309  
310 * Paste clipboard from cell A1 
311   CALL METHOD OF lo_cell 'SELECT'. 
312   CALL METHOD OF go_worksheet 'PASTE'. 
313  
314 ENDFORM.  " PASTE_CLIPBOARD 
315 *&---------------------------------------------------------------------* 
316 *&      Form  change_format 
317 *&---------------------------------------------------------------------* 
318 *& Change cell format 
319 *&---------------------------------------------------------------------* 
320 *  -->  p_rowini  p_colini Initial Range Cell 
321 *  -->  p_rowend  p_colend End Range Cell 
322 *  -->  p_colour     Colour of the font 
323 *  -->  p_colourx    Set to X if want to change the Colour 
324 *  -->  p_bkg_col    Background colour of the cell 
325 *  -->  p_bkg_colx   Set to X if want to change the Background colour 
326 *  -->  p_size       Size of the font 
327 *  -->  p_sizex      Set to X if want to change the Size 
328 *  -->  p_bold       Bold 
329 *  -->  p_boldx      Set to X if want to change to Bold 
330 *&---------------------------------------------------------------------* 
331 FORM change_format  USING     p_rowini  p_colini 
332                               p_rowend  p_colend 
333                               p_colour     TYPE i 
334                               p_colourx    TYPE char1 
335                               p_bkg_col    TYPE i 
336                               p_bkg_colx   TYPE char1 
337                               p_size       TYPE i 
338                               p_sizex      TYPE char1 
339                               p_bold       TYPE i 
340                               p_boldx      TYPE char1. 
341  
342   DATA: lo_cellstart  TYPE ole2_object, 
343         lo_cellend    TYPE ole2_object, 
344         lo_selection  TYPE ole2_object, 
345         lo_range      TYPE ole2_object, 
346         lo_font       TYPE ole2_object, 
347         lo_interior   TYPE ole2_object. 
348  
349 * Select the Range of Cells: 
350   CALL METHOD OF go_worksheet 'Cells' = lo_cellstart 
351     EXPORTING 
352     #1 = p_rowini 
353     #2 = p_colini. 
354   CALL METHOD OF go_worksheet 'Cells' = lo_cellend 
355     EXPORTING 
356     #1 = p_rowend 
357     #2 = p_colend. 
358   CALL METHOD OF go_worksheet 'Range' = lo_range 
359     EXPORTING 
360     #1 = lo_cellstart 
361     #2 = lo_cellend. 
362  
363 *   Format: 
364   CALL METHOD OF lo_range 'FONT' = lo_font. 
365 *   Colour: 
366   IF p_colourx EQ 'X'. 
367     SET PROPERTY OF lo_font 'ColorIndex' = p_colour. 
368   ENDIF. 
369 *   Background colour; 
370   IF p_bkg_colx EQ 'X'. 
371     CALL METHOD OF lo_range 'Interior' = lo_interior. 
372     SET PROPERTY OF lo_interior 'ColorIndex' = p_bkg_col. 
373   ENDIF. 
374 *   Size 
375   IF p_sizex EQ 'X'. 
376     SET PROPERTY OF lo_font 'SIZE' = p_size. 
377   ENDIF. 
378 *   Bold 
379   IF p_boldx EQ 'X'. 
380     SET PROPERTY OF lo_font 'BOLD' = p_bold. 
381   ENDIF. 
382  
383 ENDFORM.                  "change_format 
384 *&---------------------------------------------------------------------* 
385 *&      Form  set_soft_colour 
386 *&---------------------------------------------------------------------* 
387 *& Set a theme colour. 
388 *& For colour and bkgcolour use the theme colour constants. 
389 *& Shade and bkg_shade values : from -1 to 1. 
390 *&---------------------------------------------------------------------* 
391 *  -->  p_rowini  p_colini Initial Range Cell 
392 *  -->  p_rowend  p_colend End Range Cell 
393 *  -->  p_colour     Colour of the font 
394 *  -->  p_colourx    Set to X if want to change the Colour 
395 *  -->  p_shade      Tint and Shade 
396 *  -->  p_shadex     Set to X if want to change the shade 
397 *  -->  p_bkg_col    Background colour of the cell 
398 *  -->  p_bkg_colx   Set to X if want to change the Background colour 
399 *  -->  p_bkg_shade  Tint and Shade 
400 *  -->  p_bkg_shadex Set to X if want to change the shade 
401 *&---------------------------------------------------------------------* 
402 FORM set_soft_colour  USING  p_rowini  p_colini 
403                              p_rowend  p_colend 
404                              p_colour   TYPE i 
405                              p_colourx  TYPE char1 
406                              p_shade    TYPE float 
407                              p_shadex   TYPE char1 
408                              p_bkg_col  TYPE i 
409                              p_bkg_colx TYPE char1 
410                              p_bkg_shade TYPE float 
411                              p_bkg_shadex TYPE char1. 
412  
413   DATA: lo_cellstart  TYPE ole2_object, 
414         lo_cellend    TYPE ole2_object, 
415         lo_selection  TYPE ole2_object, 
416         lo_range      TYPE ole2_object, 
417         lo_font       TYPE ole2_object, 
418         lo_interior   TYPE ole2_object. 
419  
420 * Select the Range of Cells: 
421   CALL METHOD OF go_worksheet 'Cells' = lo_cellstart 
422     EXPORTING 
423     #1 = p_rowini 
424     #2 = p_colini. 
425   CALL METHOD OF go_worksheet 'Cells' = lo_cellend 
426     EXPORTING 
427     #1 = p_rowend 
428     #2 = p_colend. 
429   CALL METHOD OF go_worksheet 'Range' = lo_range 
430     EXPORTING 
431     #1 = lo_cellstart 
432     #2 = lo_cellend. 
433  
434 *   Format: 
435   CALL METHOD OF lo_range 'FONT' = lo_font. 
436  
437 *   Colour: 
438   IF p_colourx EQ 'X'. 
439     SET PROPERTY OF lo_font 'ThemeColor' = p_colour. 
440     IF  p_shadex EQ 'X'. 
441       SET PROPERTY OF lo_font 'TintAndShade' = p_shade. 
442     ENDIF. 
443   ENDIF. 
444  
445 * BackGround Colour: 
446   IF p_bkg_colx EQ 'X'. 
447     CALL METHOD OF lo_range 'Interior' = lo_interior. 
448     SET PROPERTY OF lo_interior 'ThemeColor' = p_bkg_col. 
449     IF p_bkg_shadex EQ 'X'. 
450       SET PROPERTY OF lo_interior 'TintAndShade' = p_bkg_shade. 
451     ENDIF. 
452   ENDIF. 
453  
454 ENDFORM.       "set_soft_colour 
455 *&---------------------------------------------------------------------* 
456 *&      Form  Column_width 
457 *&---------------------------------------------------------------------* 
458 *    Adjust column width 
459 *----------------------------------------------------------------------* 
460 *  -->  p_column Column numbe 
461 *  -->  p_width  Width 
462 *----------------------------------------------------------------------* 
463 FORM column_width  USING p_column TYPE i 
464                          p_width   TYPE i. 
465  
466   DATA: lo_cellstart  TYPE ole2_object, 
467         lo_cellend    TYPE ole2_object, 
468         lo_selection  TYPE ole2_object, 
469         lo_column      TYPE ole2_object. 
470  
471 * Select the Column 
472   CALL METHOD OF go_worksheet 'Columns' = lo_column 
473     EXPORTING 
474     #1 = p_column. 
475  
476   CALL METHOD OF lo_column 'select'. 
477   CALL METHOD OF go_application 'selection' = lo_selection. 
478  
479   SET PROPERTY OF lo_column 'ColumnWidth' = p_width. 
480  
481 ENDFORM.                    "Column_width 
482 *&---------------------------------------------------------------------* 
483 *&      Form  WrapText 
484 *&---------------------------------------------------------------------* 
485 *  Wrap Text 
486 *----------------------------------------------------------------------* 
487 *  -->  p_rowini  p_colini Initial Range Cell 
488 *  -->  p_rowend  p_colend End Range Cell 
489 *----------------------------------------------------------------------* 
490 FORM wrap_text  USING p_rowini 
491                       p_colini 
492                       p_rowend 
493                       p_colend. 
494  
495   DATA: lo_cellstart  TYPE ole2_object, 
496         lo_cellend    TYPE ole2_object, 
497         lo_selection  TYPE ole2_object, 
498         lo_range      TYPE ole2_object. 
499  
500 * Select the Range of Cells: 
501   CALL METHOD OF go_worksheet 'Cells' = lo_cellstart 
502     EXPORTING 
503     #1 = p_rowini 
504     #2 = p_colini. 
505   CALL METHOD OF go_worksheet 'Cells' = lo_cellend 
506     EXPORTING 
507     #1 = p_rowend 
508     #2 = p_colend. 
509   CALL METHOD OF go_worksheet 'Range' = lo_range 
510     EXPORTING 
511     #1 = lo_cellstart 
512     #2 = lo_cellend. 
513  
514   SET PROPERTY OF lo_range 'WrapText' = 1. 
515  
516 ENDFORM.                    "WrapText 
517 *&---------------------------------------------------------------------* 
518 *&      Form  Merge Cells 
519 *&---------------------------------------------------------------------* 
520 *  Merge Cells 
521 *----------------------------------------------------------------------* 
522 *  -->  p_rowini  p_colini Initial Range Cell 
523 *  -->  p_rowend  p_colend End Range Cell 
524 *----------------------------------------------------------------------* 
525 FORM merge_cells  USING p_rowini 
526                             p_colini 
527                             p_rowend 
528                             p_colend. 
529  
530   DATA: lo_cellstart  TYPE ole2_object, 
531         lo_cellend    TYPE ole2_object, 
532         lo_selection  TYPE ole2_object, 
533         lo_range      TYPE ole2_object. 
534  
535 * Select the Range of Cells: 
536   CALL METHOD OF go_worksheet 'Cells' = lo_cellstart 
537     EXPORTING 
538     #1 = p_rowini 
539     #2 = p_colini. 
540   CALL METHOD OF go_worksheet 'Cells' = lo_cellend 
541     EXPORTING 
542     #1 = p_rowend 
543     #2 = p_colend. 
544   CALL METHOD OF go_worksheet 'Range' = lo_range 
545     EXPORTING 
546     #1 = lo_cellstart 
547     #2 = lo_cellend. 
548  
549   CALL METHOD OF lo_range 'Select' . 
550   CALL METHOD OF lo_range 'Merge' . 
551  
552 ENDFORM.   "merge_cells 
553 *&---------------------------------------------------------------------* 
554 *&      Form  align Cells 
555 *&---------------------------------------------------------------------* 
556 *  Align Cells 
557 *----------------------------------------------------------------------* 
558 *  -->  p_rowini  p_colini Initial Range Cell 
559 *  -->  p_rowend  p_colend End Range Cell 
560 *  -->  p_align   Align: c_center, c_left, c_right. 
561 *----------------------------------------------------------------------* 
562 FORM align_cells  USING p_rowini p_colini 
563                         p_rowend p_colend 
564                         p_align. 
565  
566   DATA: lo_cellstart  TYPE ole2_object, 
567         lo_cellend    TYPE ole2_object, 
568         lo_selection  TYPE ole2_object, 
569         lo_range      TYPE ole2_object. 
570  
571 * Select the Range of Cells: 
572   CALL METHOD OF go_worksheet 'Cells' = lo_cellstart 
573     EXPORTING 
574     #1 = p_rowini 
575     #2 = p_colini. 
576   CALL METHOD OF go_worksheet 'Cells' = lo_cellend 
577     EXPORTING 
578     #1 = p_rowend 
579     #2 = p_colend. 
580   CALL METHOD OF go_worksheet 'Range' = lo_range 
581     EXPORTING 
582     #1 = lo_cellstart 
583     #2 = lo_cellend. 
584  
585   CALL METHOD OF lo_range 'select'. 
586   SET PROPERTY OF lo_range 'HorizontalAlignment' = p_align. 
587  
588 ENDFORM.   "align_cells 
589 *&---------------------------------------------------------------------* 
590 *&      Form  Lock cells 
591 *&---------------------------------------------------------------------* 
592 *  Lock Cells 
593 *----------------------------------------------------------------------* 
594 *  -->  p_rowini  p_colini Initial Range Cell 
595 *  -->  p_rowend  p_colend End Range Cell 
596 *----------------------------------------------------------------------* 
597 FORM lock_cells  USING p_rowini p_colini 
598                         p_rowend p_colend. 
599  
600   DATA: lo_cellstart  TYPE ole2_object, 
601         lo_cellend    TYPE ole2_object, 
602         lo_selection  TYPE ole2_object, 
603         lo_range      TYPE ole2_object. 
604  
605 * Select the Range of Cells: 
606   CALL METHOD OF go_worksheet 'Cells' = lo_cellstart 
607     EXPORTING 
608     #1 = p_rowini 
609     #2 = p_colini. 
610   CALL METHOD OF go_worksheet 'Cells' = lo_cellend 
611     EXPORTING 
612     #1 = p_rowend 
613     #2 = p_colend. 
614   CALL METHOD OF go_worksheet 'Range' = lo_range 
615     EXPORTING 
616     #1 = lo_cellstart 
617     #2 = lo_cellend. 
618  
619   CALL METHOD OF lo_range 'select'. 
620   CALL METHOD OF go_application 'Selection' = lo_selection. 
621   SET PROPERTY OF lo_selection 'Locked' = 1. 
622  
623   CALL METHOD OF go_worksheet 'Protect' 
624     EXPORTING 
625     #01 = 0 
626     #02 = 0. 
627  
628 ENDFORM.   "Lock_cells 
629 *&---------------------------------------------------------------------* 
630 *&      Form  Add Border 
631 *&---------------------------------------------------------------------* 
632 *  Add Border 
633 *----------------------------------------------------------------------* 
634 *  -->  p_rowini  p_colini Initial Range Cell 
635 *  -->  p_rowend  p_colend End Range Cell 
636 *----------------------------------------------------------------------* 
637 FORM add_border  USING p_rowini p_colini 
638                        p_rowend p_colend. 
639  
640   DATA: lo_cellstart  TYPE ole2_object, 
641         lo_cellend    TYPE ole2_object, 
642         lo_selection  TYPE ole2_object, 
643         lo_range      TYPE ole2_object, 
644         lo_borders TYPE ole2_object. 
645  
646 * Select the Range of Cells: 
647   CALL METHOD OF go_worksheet 'Cells' = lo_cellstart 
648     EXPORTING 
649     #1 = p_rowini 
650     #2 = p_colini. 
651   CALL METHOD OF go_worksheet 'Cells' = lo_cellend 
652     EXPORTING 
653     #1 = p_rowend 
654     #2 = p_colend. 
655   CALL METHOD OF go_worksheet 'Range' = lo_range 
656     EXPORTING 
657     #1 = lo_cellstart 
658     #2 = lo_cellend. 
659  
660   CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '7'. "xlEdgeLeft 
661   SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous 
662  
663   CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '8'. "xlEdgeTop 
664   SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous 
665  
666   CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '9'. "xlEdgeBottom 
667   SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous 
668  
669   CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '10'. "xlEdgeRight 
670   SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous 
671  
672   CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '11'. "xlInsideVertical 
673   SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous 
674  
675   CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '12'. "xlInsideHorizontal 
676   SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous 
677  
678 ENDFORM.   "Add Border 
679 *&---------------------------------------------------------------------* 
680 *&      Form  set_range_name 
681 *&---------------------------------------------------------------------* 
682 *  set_range_name 
683 *----------------------------------------------------------------------* 
684 *  -->  p_rowini  p_colini Initial Range Cell 
685 *  -->  p_rowend  p_colend End Range Cell 
686 *  -->  p_name    name of the range 
687 *----------------------------------------------------------------------* 
688 FORM set_range_name  USING p_rowini p_colini 
689                            p_rowend p_colend 
690                            p_name. 
691  
692   DATA: lo_cellstart  TYPE ole2_object, 
693         lo_cellend    TYPE ole2_object, 
694         lo_selection  TYPE ole2_object, 
695         lo_range      TYPE ole2_object. 
696  
697 * Select the Range of Cells: 
698   CALL METHOD OF go_worksheet 'Cells' = lo_cellstart 
699     EXPORTING 
700     #1 = p_rowini 
701     #2 = p_colini. 
702   CALL METHOD OF go_worksheet 'Cells' = lo_cellend 
703     EXPORTING 
704     #1 = p_rowend 
705     #2 = p_colend. 
706   CALL METHOD OF go_worksheet 'Range' = lo_range 
707     EXPORTING 
708     #1 = lo_cellstart 
709     #2 = lo_cellend. 
710  
711 * Set a name to this Range 
712   SET PROPERTY OF lo_range 'Name' = p_name. 
713  
714 ENDFORM.   "set_range_name 
715 *&---------------------------------------------------------------------* 
716 *&      Form  drop_down_list 
717 *&---------------------------------------------------------------------* 
718 *  drop_down_list 
719 *----------------------------------------------------------------------* 
720 *  -->  p_rowini  p_colini Initial Range Cell 
721 *  -->  p_rowend  p_colend End Range Cell 
722 *  -->  p_name    name of the value list 
723 *----------------------------------------------------------------------* 
724 FORM drop_down_list USING p_rowini p_colini 
725                            p_rowend p_colend 
726                            p_name. 
727  
728   DATA: lo_cellstart  TYPE ole2_object, 
729         lo_cellend    TYPE ole2_object, 
730         lo_selection  TYPE ole2_object, 
731         lo_range      TYPE ole2_object, 
732         lo_validation TYPE ole2_object. 
733  
734   DATA: lv_range_name TYPE char24. 
735  
736 * Select the Range of Cells: 
737   CALL METHOD OF go_worksheet 'Cells' = lo_cellstart 
738     EXPORTING 
739     #1 = p_rowini 
740     #2 = p_colini. 
741   CALL METHOD OF go_worksheet 'Cells' = lo_cellend 
742     EXPORTING 
743     #1 = p_rowend 
744     #2 = p_colend. 
745   CALL METHOD OF go_worksheet 'Range' = lo_range 
746     EXPORTING 
747     #1 = lo_cellstart 
748     #2 = lo_cellend. 
749  
750   CALL METHOD OF lo_range 'select'. 
751   CALL METHOD OF go_application 'selection' = lo_selection. 
752   CALL METHOD OF lo_selection 'Validation' = lo_validation. 
753   CONCATENATE '=' p_name INTO lv_range_name. 
754   CALL METHOD OF lo_validation 'Add' 
755     EXPORTING 
756     #1 = 3 "'xlValidateList' 
757     #2 = 1 "'xlValidAlertStop' 
758     #3 = 1 "'xlBetween' 
759     #4 = lv_range_name. 
760  
761 ENDFORM.   "drop_down_list 
762 *&---------------------------------------------------------------------* 
763 *&      Form  print_data_fieldcat 
764 *&---------------------------------------------------------------------* 
765 *& Add data to be printed in subrutine PASTE_CLIPBOARD 
766 *& Only the fields in table gt_fieldcat will be included. 
767 *&---------------------------------------------------------------------* 
768 *  -->  p_data       Data to print 
769 *  -->  p_row p_col  Cell from the data will be printed 
770 *  -->  p_header     Print the header 
771 *&---------------------------------------------------------------------* 
772 FORM print_data_fieldcat USING p_data TYPE STANDARD TABLE 
773                                p_row TYPE i 
774                                p_col TYPE i 
775                                p_header. 
776  
777   FIELD-SYMBOLS: <field>   TYPE ANY, 
778                  <ls_data> TYPE ANY. 
779   DATA: lv_char      TYPE char128, 
780         lv_cont      TYPE i, 
781         lo_column    TYPE ole2_object, 
782         lo_selection TYPE ole2_object. 
783  
784   DATA: lo_abap_typedescr TYPE REF TO cl_abap_typedescr. 
785  
786   CLEAR: gs_data, gt_data[]. 
787  
788 * Print the header: 
789   IF p_header EQ 'X'. 
790     CLEAR gt_lines[]. 
791     LOOP AT gt_fieldcat INTO gs_fieldcat. 
792      gs_lines-value = gs_fieldcat-text. APPEND gs_lines TO gt_lines. 
793     ENDLOOP. 
794     PERFORM add_line2print_from_table. 
795   ENDIF. 
796  
797 * Print the data: 
798   LOOP AT p_data ASSIGNING <ls_data>. 
799     LOOP AT gt_fieldcat INTO gs_fieldcat. 
800       ASSIGN COMPONENT gs_fieldcat-field OF STRUCTURE <ls_data> TO <field>. 
801       IF sy-subrc EQ 0. 
802 *         Convert data depend on the kind type. 
803         CALL METHOD cl_abap_typedescr=>describe_by_data 
804           EXPORTING 
805             p_data      = <field> 
806           RECEIVING 
807             p_descr_ref = lo_abap_typedescr. 
808         CASE lo_abap_typedescr->type_kind. 
809 *           Char 
810           WHEN lo_abap_typedescr->typekind_char. 
811             CONCATENATE gs_data <field> INTO gs_data 
812               SEPARATED BY cl_abap_char_utilities=>horizontal_tab. 
813 *           Date 
814           WHEN lo_abap_typedescr->typekind_date. 
815             WRITE <field> TO lv_char DD/MM/YYYY. 
816             CONCATENATE gs_data lv_char INTO gs_data 
817               SEPARATED BY cl_abap_char_utilities=>horizontal_tab. 
818 *           Time 
819           WHEN lo_abap_typedescr->typekind_time. 
820             CONCATENATE <field>(2) <field>+2(2) <field>+4(2) INTO lv_char SEPARATED BY ':'. 
821             CONCATENATE gs_data lv_char INTO gs_data 
822               SEPARATED BY cl_abap_char_utilities=>horizontal_tab. 
823 *          Others 
824           WHEN OTHERS. 
825             WRITE <field> TO lv_char. 
826             CONCATENATE gs_data lv_char INTO gs_data 
827               SEPARATED BY cl_abap_char_utilities=>horizontal_tab. 
828         ENDCASE. 
829       ENDIF. 
830     ENDLOOP. 
831 *   Quit the first horizontal_tab: 
832     SHIFT gs_data BY 1 PLACES LEFT. 
833  
834     APPEND gs_data TO gt_data. CLEAR gs_data. 
835   ENDLOOP. 
836  
837 * Print the data: 
838   PERFORM paste_clipboard USING p_row p_col. 
839  
840 DATA: lo_columns TYPE ole2_object. 
841    CALL METHOD OF go_application 'Columns' = lo_columns. 
842    CALL METHOD OF lo_columns 'Autofit'. 
843  
844  
845 * Set the columns width 
846   CLEAR lv_cont. 
847   LOOP AT gt_fieldcat INTO gs_fieldcat. 
848     ADD 1 TO lv_cont. 
849     IF gs_fieldcat-width NE 0. 
850       CALL METHOD OF go_worksheet 'Columns' = lo_column 
851         EXPORTING 
852         #1 = lv_cont. 
853  
854       CALL METHOD OF lo_column 'select'. 
855       CALL METHOD OF go_application 'selection' = lo_selection. 
856       SET PROPERTY OF lo_column 'ColumnWidth' = gs_fieldcat-width. 
857     ENDIF. 
858   ENDLOOP. 
859  
860 ENDFORM.                    "print_data_fieldcat
Code of include ZRIC_OLE2_UTILS

相关内容:

IT虾米网

IT虾米网

IT虾米网

 

 译者注:如果读者不知道某些属性的枚举值应如何设定,可以参考相关内容中的微软文档中的内容。


评论关闭
IT干货网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

翻译:常见问题——ABAP Development Tools for Eclipse