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