Using ole2 objects to create an excel file

RicardoRomero_1 · ‎03-29-2012

Tired of download always the same excel sheet ? Without colours, borders, validations, etc...

Let's see how to create a lovely excel sheet like this using ole2 objects: :grin:

First you need to know the different parts in MS excel. Each part will represent an ole2 object in our program:

Starting

All the examples below use the following template report. You only need to copy the code on the example and paste it in the space reserved for this purpose.

In this report you can see how to create a new document, how to save it and how to close it.

Template Report

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.

Basic movements

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

Select a Range of cells
* 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 a Column
CALL METHOD OF lo_worksheet 'Columns' = lo_column EXPORTING #1 = 1.

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

Get the selection reference
* 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.

Change the active Worksheet
CALL METHOD OF lo_application 'Worksheets' = lo_worksheet EXPORTING #1 = 2. CALL METHOD OF lo_worksheet 'Activate'.

Change the name of the Worksheet
SET PROPERTY OF lo_worksheet 'Name' = 'Hello!'.

Add a new Worksheet
CALL METHOD OF lo_worksheet 'add'.

Modifying the content

I think the best way to understand how it works is creating a macro in excel and seeing the Visual Basic code in order to "translate" it to abap.

For create a macro you need first to activate the developer tab, the following link explains how to do it:

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

Create a macro is easy, you can follow the following link:

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

I also recommend to download the VB language reference as guide.

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

Compare the VB code with the Abap code. And you will understand how it works. You don't need to transform the entire VB code in Abap in all the cases, only the parts you need.

1 - Select a cell and set a Value:

Abap
CALL METHOD OF lo_worksheet 'Cells' = lo_cell EXPORTING #1 = 1 "Row #2 = 2. "Column SET PROPERTY OF lo_cell 'Value' = 'Hello World'.

Result:

2- Change Font and Size

Abap
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.

Result:

3- Change Colour, Bold, Underline and Italics:

Abap

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.

Result:

TIP:

In this example you can see we are using the VB constant xlUnderlineStyleSingle with the value 2. To know the values of those constants in excel you can download the VBA language reference from the link http://msdn.microsoft.com/en-us/library/aa220733(v=office.11).aspx and follow the following path for see all the constants. Or also you can use this link to look up the constants values online http://msdn.microsoft.com/en-us/library/aa221100(v=office.11).aspx.

But I prefer to execute the macro recorded step by step for debug the code and you can see the value of the constants you want leaving the mouse cursor over that constant.

- 4 Add Borders

Abap

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

Result:

- 5 Change cell format

Abap

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' = '# ?/?'.

Result:

- 6 Add validation

For example allow only dates between Jan-2000 and Jan-2010 and show an error if not.

Abap

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'.

Result:

- 7 Create a drop down list with value list in other worksheet:

Here you have an example of creation in excel:

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

Abap

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.

Result:

Improving the performance

If you want to download a large amount of data it can take a lot of time. For improve the performance we are going to copy the data from abap to clipboard and paste it to excel.

Compares the execution time of these two examples:

Cell by Cell

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.

Printing cell by cell takes about 145 seconds:

Copy-Paste

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'.

Printing doing copy-paste takes less than 4 seconds !!!

Useful subrutines

I've created an include that you can include in your programs with several useful subrutines. The code of the include is at the end of this document. And here you have a test report in wich you can see how it works:

Test report: Example of use the include ZRIC_OLE2_UTILS

REPORT zric_ole2.

INCLUDE: zric_ole2_utils.

DATA: BEGIN OF lt_spfli OCCURS 0,
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 0.
ENDLOOP.

* Copy-paste the data from cell A1
PERFORM paste_clipboard USING 1 1.

* Bold the header:
PERFORM change_format USING 1 1 3 1   "Range of cells
0 space   "Font Colour
0 space   "Background Colour
12 'X'   "Size
1   'X'. "Bold

* Change the colour of the item's header.
PERFORM set_soft_colour USING 5 1 5 6 "Range of cells
c_theme_col_white 'X'      "Font Colour
0 space                    "Font TintAndShade
c_theme_col_light_blue 'X' "Background Colour
'0.49' 'X'.                "Bkg Col. TintAndShade

* Add borders
PERFORM add_border USING 5 1 25 6.

* 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 6 1 25 2 c_center.

* Set the width to the second column
PERFORM column_width USING 2 50.

* --------------------------------------------------------*
* Add a drop down list for select the city;

* Select cities:
DATA: BEGIN OF lt_cities OCCURS 0,
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 #1 = 2.
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 0.
ENDLOOP.
* Copy-paste the data from cell A1
PERFORM paste_clipboard USING 1 1.

* Set a name to this values:
DESCRIBE TABLE lt_cities LINES lv_lines.
PERFORM set_range_name USING 1 1 lv_lines 1 'cities'.

* Change the name of the worksheet:
SET PROPERTY OF go_worksheet 'Name' = 'Cities'.
* Lock the cells:
PERFORM lock_cells USING 1 1 lv_lines 1.

* Return to the worksheet 1 and create the drop down list:
CALL METHOD OF go_application 'Worksheets' = go_worksheet
EXPORTING #1 = 1.
CALL METHOD OF go_worksheet 'Activate'.
PERFORM drop_down_list USING 6 3 25 3 '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 #1 = 3.
CALL METHOD OF go_worksheet 'Activate'.

* Fill the field catalog:
gs_fieldcat-field = 'CARRID'.
gs_fieldcat-text = 'Airline Code'.
gs_fieldcat-width = 0.
APPEND gs_fieldcat TO gt_fieldcat.
gs_fieldcat-field = 'COUNTRYFR'.
gs_fieldcat-text = 'Country Key'.
gs_fieldcat-width = 20.
APPEND gs_fieldcat TO gt_fieldcat.
gs_fieldcat-field = 'CITYFROM'.
gs_fieldcat-text = 'Departure city'.
gs_fieldcat-width = 25.
APPEND gs_fieldcat TO gt_fieldcat.
gs_fieldcat-field = 'CITYTO'.
gs_fieldcat-text = 'Arrival city'.
gs_fieldcat-width = 25.
APPEND gs_fieldcat TO gt_fieldcat.

* Print the data:
PERFORM print_data_fieldcat USING lt_spfli_2 1 1 'X'.
DESCRIBE TABLE gt_fieldcat LINES lv_lines.
* Change the colour of the header.
PERFORM set_soft_colour USING 1 1 1 lv_lines         "Range of cells
c_theme_col_white 'X' "Font Colour
0 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 #1 = 1.
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
#1 = lv_complete_path.
IF sy-subrc EQ 0.
MESSAGE 'File downloaded successfully' TYPE 'S'.
ELSE.
MESSAGE 'Error downloading the file' TYPE 'E'.
ENDIF.

* Close the document and free memory
PERFORM close_document.

After the execution you can download an excel sheet like this:

Code of include ZRIC_OLE2_UTILS

*&---------------------------------------------------------------------*
*& Include           ZRIC_OLE2_UTILS
*&---------------------------------------------------------------------*
*& Author: Ricardo Romero.          Feb. 2012.
*& 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(1500) 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 0,
c_col_white       TYPE i VALUE 2,
c_col_red         TYPE i VALUE 3,
c_col_light_green TYPE i VALUE 4,
c_col_dark_blue   TYPE i VALUE 5,
c_col_yellow      TYPE i VALUE 6,
c_col_pink        TYPE i VALUE 7,
c_col_light_blue TYPE i VALUE 8,
c_col_brown       TYPE i VALUE 9.

* Theme Colours:
* Use the subrutine set_soft_colour.
CONSTANTS:
c_theme_col_white      TYPE i VALUE 1,
c_theme_col_black      TYPE i VALUE 2,
c_theme_col_yellow     TYPE i VALUE 3,
c_theme_col_dark_blue TYPE i VALUE 4,
c_theme_col_light_blue TYPE i VALUE 5,
c_theme_col_red        TYPE i VALUE 6,
c_theme_col_green      TYPE i VALUE 7,
c_theme_col_violet     TYPE i VALUE 8,
c_theme_col_pal_blue   TYPE i VALUE 9,
c_theme_col_orange     TYPE i VALUE 10.

* Align:
CONSTANTS:
c_center TYPE i VALUE -4108,
c_left   TYPE i VALUE -4131,
c_right TYPE i VALUE -4152.

*&---------------------------------------------------------------------*
*&      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' = 0.
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 1 TO lv_cont.
ASSIGN COMPONENT lv_cont OF STRUCTURE p_data TO <field>.
IF sy-subrc NE 0. EXIT. ENDIF.

*   Select the cell;
CALL METHOD OF go_worksheet 'Cells' = lo_cell
EXPORTING
#1 = p_row
#2 = 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 1 TO lv_cont.
ASSIGN COMPONENT lv_cont OF STRUCTURE p_data TO <field>.
IF sy-subrc NE 0. 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>(2) <field>+2(2) <field>+4(2) 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 1 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 1 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 = 3.
CALL FUNCTION 'CLPB_EXPORT'
TABLES
data_tab   = gt_data
EXCEPTIONS
clpb_error = 1
OTHERS     = 2.

* Select the cell A1
CALL METHOD OF go_worksheet 'Cells' = lo_cell
EXPORTING
#1 = p_row
#2 = 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
#1 = p_rowini
#2 = p_colini.
CALL METHOD OF go_worksheet 'Cells' = lo_cellend
EXPORTING
#1 = p_rowend
#2 = p_colend.
CALL METHOD OF go_worksheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = 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
#1 = p_rowini
#2 = p_colini.
CALL METHOD OF go_worksheet 'Cells' = lo_cellend
EXPORTING
#1 = p_rowend
#2 = p_colend.
CALL METHOD OF go_worksheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = 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
#1 = 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
#1 = p_rowini
#2 = p_colini.
CALL METHOD OF go_worksheet 'Cells' = lo_cellend
EXPORTING
#1 = p_rowend
#2 = p_colend.
CALL METHOD OF go_worksheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.

SET PROPERTY OF lo_range 'WrapText' = 1.

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
#1 = p_rowini
#2 = p_colini.
CALL METHOD OF go_worksheet 'Cells' = lo_cellend
EXPORTING
#1 = p_rowend
#2 = p_colend.
CALL METHOD OF go_worksheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = 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
#1 = p_rowini
#2 = p_colini.
CALL METHOD OF go_worksheet 'Cells' = lo_cellend
EXPORTING
#1 = p_rowend
#2 = p_colend.
CALL METHOD OF go_worksheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = 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
#1 = p_rowini
#2 = p_colini.
CALL METHOD OF go_worksheet 'Cells' = lo_cellend
EXPORTING
#1 = p_rowend
#2 = p_colend.
CALL METHOD OF go_worksheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.

CALL METHOD OF lo_range 'select'.
CALL METHOD OF go_application 'Selection' = lo_selection.
SET PROPERTY OF lo_selection 'Locked' = 1.

CALL METHOD OF go_worksheet 'Protect'
EXPORTING
#01 = 0
#02 = 0.

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
#1 = p_rowini
#2 = p_colini.
CALL METHOD OF go_worksheet 'Cells' = lo_cellend
EXPORTING
#1 = p_rowend
#2 = p_colend.
CALL METHOD OF go_worksheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = lo_cellend.

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

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

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

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

CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '11'. "xlInsideVertical
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous

CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '12'. "xlInsideHorizontal
SET PROPERTY OF lo_borders 'LineStyle' = '1'. "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
#1 = p_rowini
#2 = p_colini.
CALL METHOD OF go_worksheet 'Cells' = lo_cellend
EXPORTING
#1 = p_rowend
#2 = p_colend.
CALL METHOD OF go_worksheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = 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
#1 = p_rowini
#2 = p_colini.
CALL METHOD OF go_worksheet 'Cells' = lo_cellend
EXPORTING
#1 = p_rowend
#2 = p_colend.
CALL METHOD OF go_worksheet 'Range' = lo_range
EXPORTING
#1 = lo_cellstart
#2 = 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
#1 = 3 "'xlValidateList'
#2 = 1 "'xlValidAlertStop'
#3 = 1 "'xlBetween'
#4 = 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 0.
*         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>(2) <field>+2(2) <field>+4(2) 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 1 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 1 TO lv_cont.
IF gs_fieldcat-width NE 0.
CALL METHOD OF go_worksheet 'Columns' = lo_column
EXPORTING
#1 = 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

Using ole2 objects to create an excel file

Get Started with the ABAP Development Tools for SAP NetWeaver

Become an ABAP in Eclipse Feature Explorer and earn the Explorer Badge

Six kinds of debugging tips to find the source code where the message is raised