频道栏目
首页 > 程序开发 > 综合编程 > 其他综合 > 正文
ABAP使用OLE2对象创建EXCEL文件
2016-08-22 09:38:05         来源:氢氦  
收藏   我要投稿

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

简单的EXCEL

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

OLE2创建的EXCEL工作表

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

EXCEL的不同部分

 

开始

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

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

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_worksheet 'add'.
Add a new worksheet

 

修改内容

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

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

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

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

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

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

https://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里面创建的例子:

https://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_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:   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 .
       SET PROPERTY OF lo_cell 'Value' = .
       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:   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 .
       CONCATENATE lt_data  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 *& https://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:  TYPE ANY.
159 
160   DO.
161     ADD 1 TO lv_cont.
162     ASSIGN COMPONENT lv_cont OF STRUCTURE p_data TO .
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' = .
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:  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 .
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      = 
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  INTO gs_data
233           SEPARATED BY cl_abap_char_utilities=>horizontal_tab.
234 *     Date
235       WHEN lo_abap_typedescr->typekind_date.
236         WRITE  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 (2) +2(2) +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  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:    TYPE ANY,
778                   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 .
799     LOOP AT gt_fieldcat INTO gs_fieldcat.
800       ASSIGN COMPONENT gs_fieldcat-field OF STRUCTURE  TO .
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      = 
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  INTO gs_data
812               SEPARATED BY cl_abap_char_utilities=>horizontal_tab.
813 *           Date
814           WHEN lo_abap_typedescr->typekind_date.
815             WRITE  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 (2) +2(2) +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  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
点击复制链接 与好友分享!回本站首页
相关TAG标签 对象 文件
上一篇:[深入学习Web安全](5)详解MySQL注射
下一篇:简单封装分页功能pageView.js
相关文章
图文推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站