厌倦了总是下载一模一样的EXCEL文档？没有颜色，边框，有效性验证....
让我们看看怎样用OLE2对象来创造可爱的EXCEL工作表吧！（效果如下）
首先你需要知道微软EXCEL中的不同部分的名称，每个部分在我们的程序中都代表一个OLE2对象
本文地址：http://www.cnblogs.com/hhelibeb/p/5787396.html
原文地址：Using ole2 objects for create an excel file
转载请注明
2018.03.29：做了一年多ABAP开发工作之后，译者认为OLE是个很难用很烦人的东西，强烈推荐使用XLSX Workbench进行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 =
error_no_gui =
OTHERS = .
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' = .
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
# = lv_complete_path.
IF sy-subrc EQ .
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
# = "Row
# = . "Column
Select a cell
* 1. Select starting cell
CALL METHOD OF lo_worksheet 'Cells' = lo_cellstart
EXPORTING
# =
# = . * 2. Select ending cell
CALL METHOD OF lo_worksheet 'Cells' = lo_cellend
EXPORTING
# =
# = . * Select the Range:
CALL METHOD OF lo_worksheet 'RANGE' = lo_range
EXPORTING
# = lo_cellstart
# = lo_cellend.
Select range of cells
CALL METHOD OF lo_worksheet 'Columns' = lo_column
EXPORTING
# = .
Select a column
CALL METHOD OF lo_worksheet 'Rows' = lo_row
EXPORTING
# = .
Select a row
* Select a Row
CALL METHOD OF lo_worksheet 'Rows' = lo_row
EXPORTING
# = . * 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 # = . 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' = .
Zoom
（译者注：原文的Add worksheet代码有遗漏，本人进行了补正）
修改内容
我认为理解这部分的工作原理的最佳方式是在EXCEL中创建一个宏，观察它的Visual Basic代码，以此“翻译”成ABAP代码。
要创建一个宏，首先你需要激活开发者标签，下面的链接解释了如何做这件事：
创建一个宏很简单，你可以按照这个链接中的办法做:
http://office.microsoft.com/en-us/excel-help/create-or-delete-a-macro-HP010014111.aspx
我也建议你下载一份VB语言参考作为指导：
http://msdn.microsoft.com/en-us/library/aa220733(v=office.11).aspx
对比VB代码和ABAP代码，你会理解它的工作原理。你不需要在任何情形下都把VB代码完全转换为ABAP，只转换你需要的部分。
1 - 选取一个单元格，设置值:
CALL METHOD OF lo_worksheet 'Cells' = lo_cell
EXPORTING
# = "Row
# = . "Column SET PROPERTY OF lo_cell 'Value' = 'Hello World'.
结果：
2- 修改字体大小
CALL METHOD OF lo_worksheet 'Cells' = lo_cell
EXPORTING
# = "Row
# = . "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' = .
结果：
3- 颜色，粗体，下划线，斜体:
CALL METHOD OF lo_worksheet 'Cells' = lo_cell
EXPORTING
# = "Row
# = . "Column SET PROPERTY OF lo_cell 'Value' = 'Hello World'.
CALL METHOD OF lo_cell 'FONT' = lo_font. SET PROPERTY OF lo_font 'Color' = -.
SET PROPERTY OF lo_font 'TintAndShade' = . SET PROPERTY OF lo_font 'Bold' = .
SET PROPERTY OF lo_font 'Italic' = .
SET PROPERTY OF lo_font 'Underline' = . "xlUnderlineStyleSingle DATA: lo_interior TYPE ole2_object.
CALL METHOD OF lo_cell 'Interior' = lo_interior.
SET PROPERTY OF lo_interior 'Color' = .
结果：
4- 添加边框
DATA: lo_borders TYPE ole2_object. CALL METHOD OF lo_cell 'Borders' = lo_borders EXPORTING # = ''. "xlEdgeLeft
SET PROPERTY OF lo_borders 'LineStyle' = ''. "xlContinuous CALL METHOD OF lo_cell 'Borders' = lo_borders EXPORTING # = ''. "xlEdgeTop
SET PROPERTY OF lo_borders 'LineStyle' = ''. "xlContinuous CALL METHOD OF lo_cell 'Borders' = lo_borders EXPORTING # = ''. "xlEdgeBottom
SET PROPERTY OF lo_borders 'LineStyle' = ''. "xlContinuous CALL METHOD OF lo_cell 'Borders' = lo_borders EXPORTING # = ''. "xlEdgeRight
SET PROPERTY OF lo_borders 'LineStyle' = ''. "xlContinuous * Increase the weight of the border if you want, in this case only for EdgeRight:
SET PROPERTY OF lo_borders 'WEIGHT' = . "xlThick
结果：
5 - 修改单元格格式
CALL METHOD OF lo_worksheet 'Cells' = lo_cell
EXPORTING
# = "Row
# = . "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
# = "Row
# = . "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
# = "Row
# = . "Column
SET PROPERTY OF lo_cell 'NumberFormat' = '0.00'.
SET PROPERTY OF lo_cell 'Value' = '1/2'.
SET PROPERTY OF lo_cell 'NumberFormat' = '# ?/?'.
结果：
6 - 添加有效性验证
比如说，只允许2000.01和2010.01之间的日期，如果在这个范围之外，提示错误。
CALL METHOD OF lo_worksheet 'Cells' = lo_cell
EXPORTING
# = "Row
# = . "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
# = "Type = xlValidateDate
# = "AlertStype = xlValidAlertStop
# = "Operator = xlBetween
# = '1/1/2000' "Formula1
# = '1/1/2010'."Formula2 SET PROPERTY OF lo_validation 'ErrorMessage' = 'Enter a valid date'.
结果：
7 - 创建一个包含其它工作簿中的值的下拉菜单:
这里有一个在EXCEL里面创建的例子:
http://office.microsoft.com/en-us/excel-help/create-or-remove-a-drop-down-list-HP005202215.aspx
DATA: lv_range_name TYPE char24 VALUE 'Values'. * Go to sheet 2
CALL METHOD OF lo_application 'Worksheets' = lo_worksheet
EXPORTING # = .
CALL METHOD OF lo_worksheet 'Activate'. * Fill the cells with the values;
DATA: lv_row TYPE i,
lv_cont() TYPE n VALUE '',
lv_num(),
lv_char.
DO TIMES.
ADD TO: lv_cont, lv_row.
CALL METHOD OF lo_worksheet 'Cells' = lo_cell
EXPORTING
# = lv_row "Row
# = . "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
# =
# = .
* 2. Select ending cell
CALL METHOD OF lo_worksheet 'Cells' = lo_cellend
EXPORTING
# = lv_cont "Row
# = .
* Select the Range:
CALL METHOD OF lo_worksheet 'RANGE' = lo_range
EXPORTING
# = lo_cellstart
# = 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 # = .
CALL METHOD OF lo_worksheet 'Activate'. * Select the cell A1
CALL METHOD OF lo_worksheet 'Cells' = lo_cell
EXPORTING
# = "Row
# = . "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
# = "'xlValidateList'
# = "'xlValidAlertStop'
# = "'xlBetween'
# = lv_range_name.
ABAP
结果：
改善性能
如果你想要下载大量数据，会花费很多时间。为了提高性能，我们从abap复制数据到剪切板，然后把它粘贴到excel里面。
比较两种方式的运行时间（代码已折叠）：
DATA: lt_ekpo TYPE ekpo OCCURS 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 = .
lv_row = sy-tabix.
* Write for example 15 columns per row.
DO TIMES.
CALL METHOD OF lo_worksheet 'Cells' = lo_cell
EXPORTING
# = lv_row
# = lv_cont.
ASSIGN COMPONENT lv_cont OF STRUCTURE lt_ekpoTO <field>.
SET PROPERTY OF lo_cell 'Value' = <field>.
ADD TO lv_cont.
ENDDO.
ENDLOOP.
Cell by Cell
一个单元格一个单元格地写入，要花费约145秒
TYPES: ty_data() TYPE c.
DATA: lt_data TYPE ty_data OCCURS WITH HEADER LINE.
DATA: lt_ekpo TYPE ekpo OCCURS WITH HEADER LINE,
lv_cont TYPE. FIELD-SYMBOLS: <field> TYPE ANY. * Select some data;
SELECT * INTO TABLE lt_ekpo FROM ekpo UP TO ROWS. * Prepare the data before copy to clipboard; LOOP AT lt_ekpo.
lv_cont = .
* Write for example 15 columns per row.
DO 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 TO lv_cont.
ENDDO.
SHIFT lt_data BY PLACES LEFT.
APPEND lt_data. CLEAR lt_data.
ENDLOOP. * Copy to clipboard into ABAP
CALL FUNCTION 'CONTROL_FLUSH'
EXCEPTIONS
OTHERS = .
CALL FUNCTION 'CLPB_EXPORT'
TABLES
data_tab = lt_data
EXCEPTIONS
clpb_error =
OTHERS = . * Select the cell A1
CALL METHOD OF lo_worksheet 'Cells' = lo_cell
EXPORTING
# = "Row
# = . "Column * Paste clipboard from cell A1
CALL METHOD OF lo_cell 'SELECT'.
CALL METHOD OF lo_worksheet 'PASTE'.
Copy-Paste
使用复制粘贴的方法，花费的时间少于4秒！
有用的子程序
我已经创建了一个包含文件，其中含有多个有用的子程序。包含文件的代码放在本文的末尾。你可以测试报表程序，观察它们是如何运行的。
REPORT zric_ole2. INCLUDE: zric_ole2_utils. DATA: BEGIN OF lt_spfli OCCURS ,
carrid TYPE s_carr_id,
connid TYPE s_conn_id,
cityfrom TYPE s_from_cit,
cityto TYPE s_to_city,
deptime TYPE s_dep_time,
arrtime TYPE s_arr_time,
END OF lt_spfli. DATA: lv_selected_folder TYPE string,
lv_complete_path TYPE char256,
lv_title TYPE string. START-OF-SELECTION. CALL METHOD cl_gui_frontend_services=>directory_browse
EXPORTING
window_title = lv_title
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 the document;
PERFORM create_document. * --------------------------------------------------------*
* Select some flights
SELECT carrid connid cityfrom cityto deptime arrtime
INTO TABLE lt_spfli FROM spfli UP TO 20 ROWS. * Fill a header with some data of the passenger:
gs_data = 'Passenger name'. APPEND gs_data TO gt_data.
gs_data = 'Passport'. APPEND gs_data TO gt_data.
gs_data = 'Nacionality'. APPEND gs_data TO gt_data.
* Add an empty line
CLEAR gs_data. APPEND gs_data TO gt_data. * Fill the positions: * First a Header with the column's names
CLEAR gt_lines[].
gs_lines-value = 'Airline Code'. APPEND gs_lines TO gt_lines.
gs_lines-value = 'Connection Number'. APPEND gs_lines TO gt_lines.
gs_lines-value = 'Departure city'. APPEND gs_lines TO gt_lines.
gs_lines-value = 'Arrival city'. APPEND gs_lines TO gt_lines.
gs_lines-value = 'Departure time'. APPEND gs_lines TO gt_lines.
gs_lines-value = 'Arrival time'. APPEND gs_lines TO gt_lines.
* Add the header to data to be printed
PERFORM add_line2print_from_table. * Print the rest of the data:
LOOP AT lt_spfli.
PERFORM add_line2print USING lt_spfli .
ENDLOOP. * Copy-paste the data from cell A1
PERFORM paste_clipboard USING . * Bold the header:
PERFORM change_format USING "Range of cells
space "Font Colour
space "Background Colour
'X' "Size
'X'. "Bold * Change the colour of the item's header.
PERFORM set_soft_colour USING "Range of cells
c_theme_col_white 'X' "Font Colour
space "Font TintAndShade
c_theme_col_light_blue 'X' "Background Colour
'0.49' 'X'. "Bkg Col. TintAndShade * Add borders
PERFORM add_border USING . * Adjust the width of the cells to content
DATA: lo_columns TYPE ole2_object.
CALL METHOD OF go_application 'Columns' = lo_columns.
CALL METHOD OF lo_columns 'Autofit'. * Align centered the two first columns of the item table
PERFORM align_cells USING c_center. * Set the width to the second column
PERFORM column_width USING . * --------------------------------------------------------*
* Add a drop down list for select the city; * Select cities:
DATA: BEGIN OF lt_cities OCCURS ,
city TYPE s_city,
END OF lt_cities,
lv_lines TYPE i.
SELECT city FROM sgeocity INTO TABLE lt_cities. * Go to worksheet 2;
CALL METHOD OF go_application 'Worksheets' = go_worksheet
EXPORTING # = .
CALL METHOD OF go_worksheet 'Activate'. * Print the cities:
CLEAR: gt_data[]. "Delete first the previous data
LOOP AT lt_cities.
PERFORM add_line2print USING lt_cities .
ENDLOOP.
* Copy-paste the data from cell A1
PERFORM paste_clipboard USING . * Set a name to this values:
DESCRIBE TABLE lt_cities LINES lv_lines.
PERFORM set_range_name USING lv_lines 'cities'. * Change the name of the worksheet:
SET PROPERTY OF go_worksheet 'Name' = 'Cities'.
* Lock the cells:
PERFORM lock_cells USING lv_lines . * Return to the worksheet 1 and create the drop down list:
CALL METHOD OF go_application 'Worksheets' = go_worksheet
EXPORTING # = .
CALL METHOD OF go_worksheet 'Activate'.
PERFORM drop_down_list USING 'cities'.
* Change the name of the worksheet:
SET PROPERTY OF go_worksheet 'Name' = 'Flights'.
* --------------------------------------------------------* * If you have an internal table with a lot of fields
* but you only need to print some of these fields
* you can use the subrutine print_data_fieldcat: DATA: lt_spfli_2 TYPE STANDARD TABLE OF spfli.
SELECT * FROM SPFLI INTO TABLE lt_spfli_2. * Go to worksheet 3;
CALL METHOD OF go_application 'Worksheets' = go_worksheet
EXPORTING # = .
CALL METHOD OF go_worksheet 'Activate'. * Fill the field catalog:
gs_fieldcat-field = 'CARRID'.
gs_fieldcat-text = 'Airline Code'.
gs_fieldcat-width = .
APPEND gs_fieldcat TO gt_fieldcat.
gs_fieldcat-field = 'COUNTRYFR'.
gs_fieldcat-text = 'Country Key'.
gs_fieldcat-width = .
APPEND gs_fieldcat TO gt_fieldcat.
gs_fieldcat-field = 'CITYFROM'.
gs_fieldcat-text = 'Departure city'.
gs_fieldcat-width = .
APPEND gs_fieldcat TO gt_fieldcat.
gs_fieldcat-field = 'CITYTO'.
gs_fieldcat-text = 'Arrival city'.
gs_fieldcat-width = .
APPEND gs_fieldcat TO gt_fieldcat. * Print the data:
PERFORM print_data_fieldcat USING lt_spfli_2 'X'.
DESCRIBE TABLE gt_fieldcat LINES lv_lines.
* Change the colour of the header.
PERFORM set_soft_colour USING lv_lines "Range of cells
c_theme_col_white 'X' "Font Colour
space "Font TintAndShade
c_theme_col_green 'X' "Background Colour
'0.49' 'X'. "Bkg Col. TintAndShade * Change the name of the worksheet:
SET PROPERTY OF go_worksheet 'Name' = 'Data field catalog'. * Return to the worksheet 1
CALL METHOD OF go_application 'Worksheets' = go_worksheet
EXPORTING # = .
CALL METHOD OF go_worksheet 'Activate'. * File name
CONCATENATE lv_selected_folder '\Flights' INTO lv_complete_path. * Save the document
CALL METHOD OF go_workbook 'SaveAs'
EXPORTING
# = lv_complete_path.
IF sy-subrc EQ .
MESSAGE 'File downloaded successfully' TYPE 'S'.
ELSE.
MESSAGE 'Error downloading the file' TYPE 'E'.
ENDIF. * Close the document and free memory
PERFORM close_document.
Test report: Example of use the include ZRIC_OLE2_UTILS
运行后，你可以下载到这样的一个excel：
*&---------------------------------------------------------------------*
*& Include ZRIC_OLE2_UTILS
*&---------------------------------------------------------------------*
*& Author: Ricardo Romero. Feb. 2012.
*& http://scn.sap.com/people/ricardo.romeromata
*&---------------------------------------------------------------------*
*&
*& Versions Management.
*&
*& Versión No. | Author | Descrìption
*& 1.0 Ricardo Romero Initial version.
*& 2.x
*& 3.x
*&---------------------------------------------------------------------* TYPE-POOLS: soi,ole2. DATA: go_application TYPE ole2_object,
go_workbook TYPE ole2_object,
go_workbooks TYPE ole2_object,
go_worksheet TYPE ole2_object. DATA: gv_lines TYPE i. "Lines printed by the moment * Data to be printed.
* You must to concatenate the fields of the line you want to print
* separated by cl_abap_char_utilities=>horizontal_tab.
* Use the subrutine add_line2print for fill the tabla.
TYPES: ty_data() TYPE c.
DATA: gt_data TYPE TABLE OF ty_data,
gs_data LIKE LINE OF gt_data. * Data to be printed.
* Fill the table with the text you want to print in a line.
* Use the subrutine add_line2print_from_table to pass the
* table.
TYPES: BEGIN OF ty_line,
value TYPE char255,
END OF ty_line.
DATA: gt_lines TYPE TABLE OF ty_line,
gs_lines LIKE LINE OF gt_lines. * Fields to be printed
* Use the subrutine print_data_fieldcat.
TYPES: BEGIN OF ty_fieldcat,
field LIKE dd03d-fieldname, "Field name in your internal table
text LIKE dd03p-ddtext, "Description of the column
width TYPE i, "Width of the column
END OF ty_fieldcat.
DATA: gt_fieldcat TYPE TABLE OF ty_fieldcat,
gs_fieldcat LIKE LINE OF gt_fieldcat. * Some colours you can use:
CONSTANTS:
c_col_black TYPE i VALUE ,
c_col_white TYPE i VALUE ,
c_col_red TYPE i VALUE ,
c_col_light_green TYPE i VALUE ,
c_col_dark_blue TYPE i VALUE ,
c_col_yellow TYPE i VALUE ,
c_col_pink TYPE i VALUE ,
c_col_light_blue TYPE i VALUE ,
c_col_brown TYPE i VALUE . * Theme Colours:
* Use the subrutine set_soft_colour.
CONSTANTS:
c_theme_col_white TYPE i VALUE ,
c_theme_col_black TYPE i VALUE ,
c_theme_col_yellow TYPE i VALUE ,
c_theme_col_dark_blue TYPE i VALUE ,
c_theme_col_light_blue TYPE i VALUE ,
c_theme_col_red TYPE i VALUE ,
c_theme_col_green TYPE i VALUE ,
c_theme_col_violet TYPE i VALUE ,
c_theme_col_pal_blue TYPE i VALUE ,
c_theme_col_orange TYPE i VALUE . * Align:
CONSTANTS:
c_center TYPE i VALUE -,
c_left TYPE i VALUE -,
c_right TYPE i VALUE -. *&---------------------------------------------------------------------*
*& Form CREATE_DOCUMENT
*&---------------------------------------------------------------------*
* Instanciate the application, workbook and the first worksheet.
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM create_document. CREATE OBJECT go_application 'Excel.Application'.
CALL METHOD OF go_application 'Workbooks' = go_workbooks.
CALL METHOD OF go_workbooks 'Add' = go_workbook.
SET PROPERTY OF go_application 'Visible' = .
GET PROPERTY OF go_application 'ACTIVESHEET' = go_worksheet. ENDFORM. " CREATE_DOCUMENT *&---------------------------------------------------------------------*
*& Form CLOSE_DOCUMENT
*&---------------------------------------------------------------------*
* Close the document and free memory objects.
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM close_document. CALL METHOD OF go_application 'QUIT'.
FREE OBJECT go_worksheet.
FREE OBJECT go_workbook.
FREE OBJECT go_workbooks.
FREE OBJECT go_application. ENDFORM. " CLOSE_DOCUMENT *&---------------------------------------------------------------------*
*& Form PRINT_LINE
*&---------------------------------------------------------------------*
* Print line cell by cell with colurs, etc.
*----------------------------------------------------------------------*
* --> p_data Data to print
* --> p_row Number of the Row in excel to print
* --> p_num_cols Number of fields to be printed, if 0 all the fields
* will be printed
* --> p_colour Colour of the font
* --> p_colourx Set to X if want to change the Colour
* --> p_bkg_col Background colour of the cell
* --> p_bkg_colx Set to X if want to change the Background colour
* --> p_size Size of the font
* --> p_sizex Set to X if want to change the Size
* --> p_bold Bold
* --> p_boldx Set to X if want to change to Bold
*----------------------------------------------------------------------*
FORM print_line
USING
p_data TYPE any
p_row TYPE i
p_num_cols TYPE i
p_colour TYPE i
p_colourx TYPE char1
p_bkg_col TYPE i
p_bkg_colx TYPE char1
p_size TYPE i
p_sizex TYPE char1
p_bold TYPE i
p_boldx TYPE char1. DATA: lo_font TYPE ole2_object,
lo_cell TYPE ole2_object,
lo_interior TYPE ole2_object,
lv_cont TYPE i. FIELD-SYMBOLS: <field> TYPE ANY. DO.
ADD TO lv_cont.
ASSIGN COMPONENT lv_cont OF STRUCTURE p_data TO <field>.
IF sy-subrc NE . EXIT. ENDIF. * Select the cell;
CALL METHOD OF go_worksheet 'Cells' = lo_cell
EXPORTING
# = p_row
# = lv_cont.
* Assign the value;
SET PROPERTY OF lo_cell 'Value' = <field>.
* Format:
CALL METHOD OF lo_cell 'FONT' = lo_font.
* Colour:
IF p_colourx EQ 'X'.
SET PROPERTY OF lo_font 'ColorIndex' = p_colour.
ENDIF.
* Background colour;
IF p_bkg_colx EQ 'X'.
CALL METHOD OF lo_cell 'Interior' = lo_interior.
SET PROPERTY OF lo_interior 'ColorIndex' = p_bkg_col.
ENDIF.
* Size
IF p_sizex EQ 'X'.
SET PROPERTY OF lo_font 'SIZE' = p_size.
ENDIF.
* Bold
IF p_boldx EQ 'X'.
SET PROPERTY OF lo_font 'BOLD' = p_bold.
ENDIF. * Exit the loop?
IF lv_cont EQ p_num_cols. EXIT. ENDIF.
ENDDO. ENDFORM. "print_line
*&---------------------------------------------------------------------*
*& Form add_line2print
*&---------------------------------------------------------------------*
*& Add line to be printed in subrutine PASTE_CLIPBOARD
*&---------------------------------------------------------------------*
* --> p_data Data to print
* --> p_num_cols Number of fields to be printed, if 0 all the field
* will be printed
*&---------------------------------------------------------------------*
FORM add_line2print
USING
p_data TYPE any
p_num_cols TYPE i. FIELD-SYMBOLS: <field> TYPE ANY.
DATA: lv_cont TYPE i,
lv_char TYPE char128. DATA: lo_abap_typedescr TYPE REF TO cl_abap_typedescr. CLEAR gs_data.
DO.
ADD TO lv_cont.
ASSIGN COMPONENT lv_cont OF STRUCTURE p_data TO <field>.
IF sy-subrc NE . EXIT. ENDIF. * Convert data depend on the kind type.
CALL METHOD cl_abap_typedescr=>describe_by_data
EXPORTING
p_data = <field>
RECEIVING
p_descr_ref = lo_abap_typedescr.
CASE lo_abap_typedescr->type_kind.
* Char
WHEN lo_abap_typedescr->typekind_char.
CONCATENATE gs_data <field> INTO gs_data
SEPARATED BY cl_abap_char_utilities=>horizontal_tab.
* Date
WHEN lo_abap_typedescr->typekind_date.
WRITE <field> TO lv_char DD/MM/YYYY.
CONCATENATE gs_data lv_char INTO gs_data
SEPARATED BY cl_abap_char_utilities=>horizontal_tab.
* Time
WHEN lo_abap_typedescr->typekind_time.
CONCATENATE <field>() <field>+() <field>+() INTO lv_char SEPARATED BY ':'.
CONCATENATE gs_data lv_char INTO gs_data
SEPARATED BY cl_abap_char_utilities=>horizontal_tab.
* Others
WHEN OTHERS.
WRITE <field> TO lv_char.
CONCATENATE gs_data lv_char INTO gs_data
SEPARATED BY cl_abap_char_utilities=>horizontal_tab.
ENDCASE. * Exit the loop?
IF lv_cont EQ p_num_cols. EXIT. ENDIF.
ENDDO. * Quit the first horizontal_tab:
SHIFT gs_data BY PLACES LEFT. APPEND gs_data TO gt_data. CLEAR gs_data. ENDFORM. "add_line2print
*&---------------------------------------------------------------------*
*& Form add_line2print_from_table
*&---------------------------------------------------------------------*
*& Add line to be printed in subrutine PASTE_CLIPBOARD from a table.
*&---------------------------------------------------------------------*
FORM add_line2print_from_table. CLEAR gs_data.
LOOP AT gt_lines INTO gs_lines.
CONCATENATE gs_data gs_lines-value INTO gs_data
SEPARATED BY cl_abap_char_utilities=>horizontal_tab.
ENDLOOP. * Quit the first horizontal_tab:
SHIFT gs_data BY PLACES LEFT. APPEND gs_data TO gt_data. CLEAR gs_data. ENDFORM. "add_line2print_from_table
*&---------------------------------------------------------------------*
*& Form PASTE_CLIPBOARD
*&---------------------------------------------------------------------*
*& Paste Clipboard from the cell passed by parameter
*&---------------------------------------------------------------------*
* --> p_row
* --> p_col
*&---------------------------------------------------------------------*
FORM paste_clipboard USING p_row TYPE i
p_col TYPE i. DATA: lo_cell TYPE ole2_object. * Copy to clipboard into ABAP
CALL FUNCTION 'CONTROL_FLUSH'
EXCEPTIONS
OTHERS = .
CALL FUNCTION 'CLPB_EXPORT'
TABLES
data_tab = gt_data
EXCEPTIONS
clpb_error =
OTHERS = . * Select the cell A1
CALL METHOD OF go_worksheet 'Cells' = lo_cell
EXPORTING
# = p_row
# = p_col. * Paste clipboard from cell A1
CALL METHOD OF lo_cell 'SELECT'.
CALL METHOD OF go_worksheet 'PASTE'. ENDFORM. " PASTE_CLIPBOARD
*&---------------------------------------------------------------------*
*& Form change_format
*&---------------------------------------------------------------------*
*& Change cell format
*&---------------------------------------------------------------------*
* --> p_rowini p_colini Initial Range Cell
* --> p_rowend p_colend End Range Cell
* --> p_colour Colour of the font
* --> p_colourx Set to X if want to change the Colour
* --> p_bkg_col Background colour of the cell
* --> p_bkg_colx Set to X if want to change the Background colour
* --> p_size Size of the font
* --> p_sizex Set to X if want to change the Size
* --> p_bold Bold
* --> p_boldx Set to X if want to change to Bold
*&---------------------------------------------------------------------*
FORM change_format USING p_rowini p_colini
p_rowend p_colend
p_colour TYPE i
p_colourx TYPE char1
p_bkg_col TYPE i
p_bkg_colx TYPE char1
p_size TYPE i
p_sizex TYPE char1
p_bold TYPE i
p_boldx TYPE char1. DATA: lo_cellstart TYPE ole2_object,
lo_cellend TYPE ole2_object,
lo_selection TYPE ole2_object,
lo_range TYPE ole2_object,
lo_font TYPE ole2_object,
lo_interior TYPE ole2_object. * Select the Range of Cells:
CALL METHOD OF go_worksheet 'Cells' = lo_cellstart
EXPORTING
# = p_rowini
# = p_colini.
CALL METHOD OF go_worksheet 'Cells' = lo_cellend
EXPORTING
# = p_rowend
# = p_colend.
CALL METHOD OF go_worksheet 'Range' = lo_range
EXPORTING
# = lo_cellstart
# = lo_cellend. * Format:
CALL METHOD OF lo_range 'FONT' = lo_font.
* Colour:
IF p_colourx EQ 'X'.
SET PROPERTY OF lo_font 'ColorIndex' = p_colour.
ENDIF.
* Background colour;
IF p_bkg_colx EQ 'X'.
CALL METHOD OF lo_range 'Interior' = lo_interior.
SET PROPERTY OF lo_interior 'ColorIndex' = p_bkg_col.
ENDIF.
* Size
IF p_sizex EQ 'X'.
SET PROPERTY OF lo_font 'SIZE' = p_size.
ENDIF.
* Bold
IF p_boldx EQ 'X'.
SET PROPERTY OF lo_font 'BOLD' = p_bold.
ENDIF. ENDFORM. "change_format
*&---------------------------------------------------------------------*
*& Form set_soft_colour
*&---------------------------------------------------------------------*
*& Set a theme colour.
*& For colour and bkgcolour use the theme colour constants.
*& Shade and bkg_shade values : from -1 to 1.
*&---------------------------------------------------------------------*
* --> p_rowini p_colini Initial Range Cell
* --> p_rowend p_colend End Range Cell
* --> p_colour Colour of the font
* --> p_colourx Set to X if want to change the Colour
* --> p_shade Tint and Shade
* --> p_shadex Set to X if want to change the shade
* --> p_bkg_col Background colour of the cell
* --> p_bkg_colx Set to X if want to change the Background colour
* --> p_bkg_shade Tint and Shade
* --> p_bkg_shadex Set to X if want to change the shade
*&---------------------------------------------------------------------*
FORM set_soft_colour USING p_rowini p_colini
p_rowend p_colend
p_colour TYPE i
p_colourx TYPE char1
p_shade TYPE float
p_shadex TYPE char1
p_bkg_col TYPE i
p_bkg_colx TYPE char1
p_bkg_shade TYPE float
p_bkg_shadex TYPE char1. DATA: lo_cellstart TYPE ole2_object,
lo_cellend TYPE ole2_object,
lo_selection TYPE ole2_object,
lo_range TYPE ole2_object,
lo_font TYPE ole2_object,
lo_interior TYPE ole2_object. * Select the Range of Cells:
CALL METHOD OF go_worksheet 'Cells' = lo_cellstart
EXPORTING
# = p_rowini
# = p_colini.
CALL METHOD OF go_worksheet 'Cells' = lo_cellend
EXPORTING
# = p_rowend
# = p_colend.
CALL METHOD OF go_worksheet 'Range' = lo_range
EXPORTING
# = lo_cellstart
# = lo_cellend. * Format:
CALL METHOD OF lo_range 'FONT' = lo_font. * Colour:
IF p_colourx EQ 'X'.
SET PROPERTY OF lo_font 'ThemeColor' = p_colour.
IF p_shadex EQ 'X'.
SET PROPERTY OF lo_font 'TintAndShade' = p_shade.
ENDIF.
ENDIF. * BackGround Colour:
IF p_bkg_colx EQ 'X'.
CALL METHOD OF lo_range 'Interior' = lo_interior.
SET PROPERTY OF lo_interior 'ThemeColor' = p_bkg_col.
IF p_bkg_shadex EQ 'X'.
SET PROPERTY OF lo_interior 'TintAndShade' = p_bkg_shade.
ENDIF.
ENDIF. ENDFORM. "set_soft_colour
*&---------------------------------------------------------------------*
*& Form Column_width
*&---------------------------------------------------------------------*
* Adjust column width
*----------------------------------------------------------------------*
* --> p_column Column numbe
* --> p_width Width
*----------------------------------------------------------------------*
FORM column_width USING p_column TYPE i
p_width TYPE i. DATA: lo_cellstart TYPE ole2_object,
lo_cellend TYPE ole2_object,
lo_selection TYPE ole2_object,
lo_column TYPE ole2_object. * Select the Column
CALL METHOD OF go_worksheet 'Columns' = lo_column
EXPORTING
# = p_column. CALL METHOD OF lo_column 'select'.
CALL METHOD OF go_application 'selection' = lo_selection. SET PROPERTY OF lo_column 'ColumnWidth' = p_width. ENDFORM. "Column_width
*&---------------------------------------------------------------------*
*& Form WrapText
*&---------------------------------------------------------------------*
* Wrap Text
*----------------------------------------------------------------------*
* --> p_rowini p_colini Initial Range Cell
* --> p_rowend p_colend End Range Cell
*----------------------------------------------------------------------*
FORM wrap_text USING p_rowini
p_colini
p_rowend
p_colend. DATA: lo_cellstart TYPE ole2_object,
lo_cellend TYPE ole2_object,
lo_selection TYPE ole2_object,
lo_range TYPE ole2_object. * Select the Range of Cells:
CALL METHOD OF go_worksheet 'Cells' = lo_cellstart
EXPORTING
# = p_rowini
# = p_colini.
CALL METHOD OF go_worksheet 'Cells' = lo_cellend
EXPORTING
# = p_rowend
# = p_colend.
CALL METHOD OF go_worksheet 'Range' = lo_range
EXPORTING
# = lo_cellstart
# = lo_cellend. SET PROPERTY OF lo_range 'WrapText' = . ENDFORM. "WrapText
*&---------------------------------------------------------------------*
*& Form Merge Cells
*&---------------------------------------------------------------------*
* Merge Cells
*----------------------------------------------------------------------*
* --> p_rowini p_colini Initial Range Cell
* --> p_rowend p_colend End Range Cell
*----------------------------------------------------------------------*
FORM merge_cells USING p_rowini
p_colini
p_rowend
p_colend. DATA: lo_cellstart TYPE ole2_object,
lo_cellend TYPE ole2_object,
lo_selection TYPE ole2_object,
lo_range TYPE ole2_object. * Select the Range of Cells:
CALL METHOD OF go_worksheet 'Cells' = lo_cellstart
EXPORTING
# = p_rowini
# = p_colini.
CALL METHOD OF go_worksheet 'Cells' = lo_cellend
EXPORTING
# = p_rowend
# = p_colend.
CALL METHOD OF go_worksheet 'Range' = lo_range
EXPORTING
# = lo_cellstart
# = lo_cellend. CALL METHOD OF lo_range 'Select' .
CALL METHOD OF lo_range 'Merge' . ENDFORM. "merge_cells
*&---------------------------------------------------------------------*
*& Form align Cells
*&---------------------------------------------------------------------*
* Align Cells
*----------------------------------------------------------------------*
* --> p_rowini p_colini Initial Range Cell
* --> p_rowend p_colend End Range Cell
* --> p_align Align: c_center, c_left, c_right.
*----------------------------------------------------------------------*
FORM align_cells USING p_rowini p_colini
p_rowend p_colend
p_align. DATA: lo_cellstart TYPE ole2_object,
lo_cellend TYPE ole2_object,
lo_selection TYPE ole2_object,
lo_range TYPE ole2_object. * Select the Range of Cells:
CALL METHOD OF go_worksheet 'Cells' = lo_cellstart
EXPORTING
# = p_rowini
# = p_colini.
CALL METHOD OF go_worksheet 'Cells' = lo_cellend
EXPORTING
# = p_rowend
# = p_colend.
CALL METHOD OF go_worksheet 'Range' = lo_range
EXPORTING
# = lo_cellstart
# = lo_cellend. CALL METHOD OF lo_range 'select'.
SET PROPERTY OF lo_range 'HorizontalAlignment' = p_align. ENDFORM. "align_cells
*&---------------------------------------------------------------------*
*& Form Lock cells
*&---------------------------------------------------------------------*
* Lock Cells
*----------------------------------------------------------------------*
* --> p_rowini p_colini Initial Range Cell
* --> p_rowend p_colend End Range Cell
*----------------------------------------------------------------------*
FORM lock_cells USING p_rowini p_colini
p_rowend p_colend. DATA: lo_cellstart TYPE ole2_object,
lo_cellend TYPE ole2_object,
lo_selection TYPE ole2_object,
lo_range TYPE ole2_object. * Select the Range of Cells:
CALL METHOD OF go_worksheet 'Cells' = lo_cellstart
EXPORTING
# = p_rowini
# = p_colini.
CALL METHOD OF go_worksheet 'Cells' = lo_cellend
EXPORTING
# = p_rowend
# = p_colend.
CALL METHOD OF go_worksheet 'Range' = lo_range
EXPORTING
# = lo_cellstart
# = lo_cellend. CALL METHOD OF lo_range 'select'.
CALL METHOD OF go_application 'Selection' = lo_selection.
SET PROPERTY OF lo_selection 'Locked' = . CALL METHOD OF go_worksheet 'Protect'
EXPORTING
# =
# = . ENDFORM. "Lock_cells
*&---------------------------------------------------------------------*
*& Form Add Border
*&---------------------------------------------------------------------*
* Add Border
*----------------------------------------------------------------------*
* --> p_rowini p_colini Initial Range Cell
* --> p_rowend p_colend End Range Cell
*----------------------------------------------------------------------*
FORM add_border USING p_rowini p_colini
p_rowend p_colend. DATA: lo_cellstart TYPE ole2_object,
lo_cellend TYPE ole2_object,
lo_selection TYPE ole2_object,
lo_range TYPE ole2_object,
lo_borders TYPE ole2_object. * Select the Range of Cells:
CALL METHOD OF go_worksheet 'Cells' = lo_cellstart
EXPORTING
# = p_rowini
# = p_colini.
CALL METHOD OF go_worksheet 'Cells' = lo_cellend
EXPORTING
# = p_rowend
# = p_colend.
CALL METHOD OF go_worksheet 'Range' = lo_range
EXPORTING
# = lo_cellstart
# = lo_cellend. CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING # = ''. "xlEdgeLeft
SET PROPERTY OF lo_borders 'LineStyle' = ''. "xlContinuous CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING # = ''. "xlEdgeTop
SET PROPERTY OF lo_borders 'LineStyle' = ''. "xlContinuous CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING # = ''. "xlEdgeBottom
SET PROPERTY OF lo_borders 'LineStyle' = ''. "xlContinuous CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING # = ''. "xlEdgeRight
SET PROPERTY OF lo_borders 'LineStyle' = ''. "xlContinuous CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING # = ''. "xlInsideVertical
SET PROPERTY OF lo_borders 'LineStyle' = ''. "xlContinuous CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING # = ''. "xlInsideHorizontal
SET PROPERTY OF lo_borders 'LineStyle' = ''. "xlContinuous ENDFORM. "Add Border
*&---------------------------------------------------------------------*
*& Form set_range_name
*&---------------------------------------------------------------------*
* set_range_name
*----------------------------------------------------------------------*
* --> p_rowini p_colini Initial Range Cell
* --> p_rowend p_colend End Range Cell
* --> p_name name of the range
*----------------------------------------------------------------------*
FORM set_range_name USING p_rowini p_colini
p_rowend p_colend
p_name. DATA: lo_cellstart TYPE ole2_object,
lo_cellend TYPE ole2_object,
lo_selection TYPE ole2_object,
lo_range TYPE ole2_object. * Select the Range of Cells:
CALL METHOD OF go_worksheet 'Cells' = lo_cellstart
EXPORTING
# = p_rowini
# = p_colini.
CALL METHOD OF go_worksheet 'Cells' = lo_cellend
EXPORTING
# = p_rowend
# = p_colend.
CALL METHOD OF go_worksheet 'Range' = lo_range
EXPORTING
# = lo_cellstart
# = lo_cellend. * Set a name to this Range
SET PROPERTY OF lo_range 'Name' = p_name. ENDFORM. "set_range_name
*&---------------------------------------------------------------------*
*& Form drop_down_list
*&---------------------------------------------------------------------*
* drop_down_list
*----------------------------------------------------------------------*
* --> p_rowini p_colini Initial Range Cell
* --> p_rowend p_colend End Range Cell
* --> p_name name of the value list
*----------------------------------------------------------------------*
FORM drop_down_list USING p_rowini p_colini
p_rowend p_colend
p_name. DATA: lo_cellstart TYPE ole2_object,
lo_cellend TYPE ole2_object,
lo_selection TYPE ole2_object,
lo_range TYPE ole2_object,
lo_validation TYPE ole2_object. DATA: lv_range_name TYPE char24. * Select the Range of Cells:
CALL METHOD OF go_worksheet 'Cells' = lo_cellstart
EXPORTING
# = p_rowini
# = p_colini.
CALL METHOD OF go_worksheet 'Cells' = lo_cellend
EXPORTING
# = p_rowend
# = p_colend.
CALL METHOD OF go_worksheet 'Range' = lo_range
EXPORTING
# = lo_cellstart
# = lo_cellend. CALL METHOD OF lo_range 'select'.
CALL METHOD OF go_application 'selection' = lo_selection.
CALL METHOD OF lo_selection 'Validation' = lo_validation.
CONCATENATE '=' p_name INTO lv_range_name.
CALL METHOD OF lo_validation 'Add'
EXPORTING
# = "'xlValidateList'
# = "'xlValidAlertStop'
# = "'xlBetween'
# = lv_range_name. ENDFORM. "drop_down_list
*&---------------------------------------------------------------------*
*& Form print_data_fieldcat
*&---------------------------------------------------------------------*
*& Add data to be printed in subrutine PASTE_CLIPBOARD
*& Only the fields in table gt_fieldcat will be included.
*&---------------------------------------------------------------------*
* --> p_data Data to print
* --> p_row p_col Cell from the data will be printed
* --> p_header Print the header
*&---------------------------------------------------------------------*
FORM print_data_fieldcat USING p_data TYPE STANDARD TABLE
p_row TYPE i
p_col TYPE i
p_header. FIELD-SYMBOLS: <field> TYPE ANY,
<ls_data> TYPE ANY.
DATA: lv_char TYPE char128,
lv_cont TYPE i,
lo_column TYPE ole2_object,
lo_selection TYPE ole2_object. DATA: lo_abap_typedescr TYPE REF TO cl_abap_typedescr. CLEAR: gs_data, gt_data[]. * Print the header:
IF p_header EQ 'X'.
CLEAR gt_lines[].
LOOP AT gt_fieldcat INTO gs_fieldcat.
gs_lines-value = gs_fieldcat-text. APPEND gs_lines TO gt_lines.
ENDLOOP.
PERFORM add_line2print_from_table.
ENDIF. * Print the data:
LOOP AT p_data ASSIGNING <ls_data>.
LOOP AT gt_fieldcat INTO gs_fieldcat.
ASSIGN COMPONENT gs_fieldcat-field OF STRUCTURE <ls_data> TO <field>.
IF sy-subrc EQ .
* Convert data depend on the kind type.
CALL METHOD cl_abap_typedescr=>describe_by_data
EXPORTING
p_data = <field>
RECEIVING
p_descr_ref = lo_abap_typedescr.
CASE lo_abap_typedescr->type_kind.
* Char
WHEN lo_abap_typedescr->typekind_char.
CONCATENATE gs_data <field> INTO gs_data
SEPARATED BY cl_abap_char_utilities=>horizontal_tab.
* Date
WHEN lo_abap_typedescr->typekind_date.
WRITE <field> TO lv_char DD/MM/YYYY.
CONCATENATE gs_data lv_char INTO gs_data
SEPARATED BY cl_abap_char_utilities=>horizontal_tab.
* Time
WHEN lo_abap_typedescr->typekind_time.
CONCATENATE <field>() <field>+() <field>+() INTO lv_char SEPARATED BY ':'.
CONCATENATE gs_data lv_char INTO gs_data
SEPARATED BY cl_abap_char_utilities=>horizontal_tab.
* Others
WHEN OTHERS.
WRITE <field> TO lv_char.
CONCATENATE gs_data lv_char INTO gs_data
SEPARATED BY cl_abap_char_utilities=>horizontal_tab.
ENDCASE.
ENDIF.
ENDLOOP.
* Quit the first horizontal_tab:
SHIFT gs_data BY PLACES LEFT. APPEND gs_data TO gt_data. CLEAR gs_data.
ENDLOOP. * Print the data:
PERFORM paste_clipboard USING p_row p_col. DATA: lo_columns TYPE ole2_object.
CALL METHOD OF go_application 'Columns' = lo_columns.
CALL METHOD OF lo_columns 'Autofit'. * Set the columns width
CLEAR lv_cont.
LOOP AT gt_fieldcat INTO gs_fieldcat.
ADD TO lv_cont.
IF gs_fieldcat-width NE .
CALL METHOD OF go_worksheet 'Columns' = lo_column
EXPORTING
# = lv_cont. CALL METHOD OF lo_column 'select'.
CALL METHOD OF go_application 'selection' = lo_selection.
SET PROPERTY OF lo_column 'ColumnWidth' = gs_fieldcat-width.
ENDIF.
ENDLOOP. ENDFORM. "print_data_fieldcat
Code of include ZRIC_OLE2_UTILS
相关内容:
http://help.sap.com/printdocu/core/print46c/en/data/pdf/bcfesde6/bcfesde6.pdf
https://msdn.microsoft.com/en-us/library/office/ff840772.aspx
译者注：如果读者不知道某些属性的枚举值应如何设定，可以参考相关内容中的微软文档中的内容。