Skip to Content
Author's profile photo Ricardo Romero Mata
Ricardo Romero Mata
March 29, 2012 27 minute read

Using ole2 objects to create an excel file

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

1.JPG

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

2.JPG

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

3.JPG

 

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-HA010173052.aspx

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:

ejercicio 1.jpg

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:

4.JPG

2- Change Font and Size

ejercicio 2.jpg

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:

5.JPG

3- Change Colour, Bold, Underline and Italics:

ejercicio 3.jpg

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:

6.JPG

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.

7.JPG

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.

8.JPG

 

– 4 Add Borders

ejercicio 4.jpg

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:

9.JPG

– 5 Change cell format

ejercicio 5.jpg

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:

10.JPG

– 6 Add validation

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

ejercicio 6.jpg

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:

11.JPG

– 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

ejercicio 7.jpg

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:

12.JPG

 

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:

13.JPG

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 !!!

14.JPG

 

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:

2.JPG

Code of include ZRIC_OLE2_UTILS

*&———————————————————————*
*&  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(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

Related content:

http://help.sap.com/printdocu/core/print46c/en/data/pdf/bcfesde6/bcfesde6.pdf

http://wiki.sdn.sap.com/wiki/display/Snippets/ABAP+OLE+-+Download+tables+to+multiple+worksheets+in+Excel

Assigned Tags

      32 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Madhu Vadlamani
      Madhu Vadlamani

      Hi Ricardo,

      Good work.

      Regards,

      Madhu. 🙂

      Author's profile photo Ivan Femia
      Ivan Femia

      Hi Ricardo,

      try to consider abap2xlsx project, there are a series of blogs that explain the features and a collection of demo reports.

      Have a look to the blog abap2xlsx - Generate your professional Excel spreadsheet from ABAP

      OLE2 technology depends on front-end, does not allow batch generation and, even more, you cannot read back the file.

      Regards,

      Ivan

      Author's profile photo Ricardo Romero Mata
      Ricardo Romero Mata
      Blog Post Author

      Hi Ivan,

      thanks for the info !

      Sounds really interesting.  When I have some free time I'll try to learn how it works.

      Author's profile photo Former Member
      Former Member

      Hi Ricardo, great work.

      I'm having issues to convert this VBA code to ABAP, I can't convert the instruction of     With Selection.Interior.Gradient.ColorStops.Add(0) to ABAP. Do you know how to do this?

      I'm trying to mix in cells several colours, for example yellow and red in this code:

      Sub Macro1()

      '

      ' Macro1 Macro

      '

      '

          With Selection.Interior

              .Pattern = xlPatternLinearGradient

              .Gradient.Degree = 0

              .Gradient.ColorStops.Clear

          End With

          With Selection.Interior.Gradient.ColorStops.Add(0)

              .Color = 65535

              .TintAndShade = 0

          End With

          With Selection.Interior.Gradient.ColorStops.Add(1)

              .Color = 255

              .TintAndShade = 0

          End With

      End Sub

      Let me know, thanks. 🙂

      Author's profile photo Ricardo Romero Mata
      Ricardo Romero Mata
      Blog Post Author

      Hi Mario,

      Try with this code:

      DATA: lo_interior   TYPE ole2_object,
             lo_gradient   TYPE ole2_object,
             lo_colorstops TYPE ole2_object,
             lo_add        TYPE ole2_object.

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

      SET PROPERTY OF lo_cell 'Value' = 'Hello World'.

      * Active the selection
      CALL METHOD OF lo_cell 'Select'.

      * Get the selection object.
      CALL METHOD OF lo_application 'selection' = lo_selection.
      CALL METHOD OF lo_selection 'Interior' = lo_interior.
      SET PROPERTY OF lo_interior 'Pattern' = 4000. "xlPatternLinearGradient

      CALL METHOD OF lo_interior 'Gradient' = lo_gradient.
      SET PROPERTY OF lo_gradient 'Degree' = 0.

      CALL METHOD OF lo_gradient 'ColorStops' = lo_colorstops.
      CALL METHOD OF lo_colorstops 'Clear'.

      * Color 1
      CALL METHOD OF lo_colorstops 'Add' = lo_add
         EXPORTING
         #1 = 0.
      SET PROPERTY OF lo_add 'Color' = 65535.
      SET PROPERTY OF lo_add 'TintAndShade' = 0.

      * Color 2
      CALL METHOD OF lo_colorstops 'Add' = lo_add
         EXPORTING
         #1 = 1.
      SET PROPERTY OF lo_add 'Color' = 255.
      SET PROPERTY OF lo_add 'TintAndShade' = 0.

      Regards

      Author's profile photo Former Member
      Former Member

      Hi Ricardo,

      I am impressed, thank you for your work.

      Do you know the way how to save the EXCEL to PDF ('SaveAs' PDF).

      In advance, Thank you.

      Best Regards,

      Serge

      Author's profile photo Former Member
      Former Member

      Hi Ricardo,

      I could solve it with the following statement:

      CALL METHOD OF lo_worksheets 'ExportAsFixedFormat'

           EXPORTING

           #1 = '0'

           #2 = lv_complete_path.

      Thanks and best regards,

      Serge

      Author's profile photo Former Member
      Former Member

      Hi serge Thuet,

      Above method worked and its great.

      But can you please help to convert Entire workbook into single pdf because here i have around 15 sheets in workbook and they all needed to be in one pdf file.

      Thanks in advance.

      Regards

      Pavan

      Author's profile photo Former Member
      Former Member

      Hi Pavan,

      I will send you the solution begining of next week.

      Thank you for your understanding.

      Best Regards,

      Serge

      Author's profile photo Former Member
      Former Member

      Hi serge Thuet,

          

        I will be waiting for your reply......i am currently developing for the same requirement i asked you. I am unable to crack VBA to abap for this particular one. Try to reply as soon as possible.

      Thank you in advance and wish you a very wonderful new year ahead.

      Regards,

      Pavan

      Author's profile photo Former Member
      Former Member

      Hi Ricardo,

      Nice Document 🙂 .

      Regard's

      Smruti

      Author's profile photo Murthy Sannidhi
      Murthy Sannidhi

      Hi Ricardo,

      love you man , really good work

      it will slove half of my problems

      Regards,

      Murthy Sannidhi

      Author's profile photo Former Member
      Former Member

      Nicely Documented... Good To Learn... Thanks for sharing

      Author's profile photo Manfred Fettinger
      Manfred Fettinger

      Wonderful! Thank you

      Author's profile photo Ravichandran Nithyapalani
      Ravichandran Nithyapalani

      Nicely documented, Can any one help me by providing the code to embed a image file(Insert Object) to EXCEL. I did it with insert picture with the pc file.. but the image cannot be carried with the file when sent as email.

      Thanks in advance.

      Ravi

      Author's profile photo Ricardo Romero Mata
      Ricardo Romero Mata
      Blog Post Author

      Hi Ravichandran,

      Try this code to insert the picture in your file:

      DATA:

          lv_image_path TYPE  dxlpath,

          lo_pictures   TYPE ole2_object,

          lo_shapes     TYPE ole2_object.

      * Choose Image;

      CALL FUNCTION 'F4_DXFILENAME_TOPRECURSION'

         EXPORTING

           i_location_flag = 'P'

           i_path          = lv_image_path

           filemask        = '*.*'

           fileoperation   = 'W'

         IMPORTING

           o_path          = lv_image_path

         EXCEPTIONS

           rfc_error       = 1

           error_with_gui  = 2

           OTHERS          = 3.

      CHECK sy-subrc EQ 0.

      * Insert the image;

      *  VB Code: ActiveSheet.Pictures.Insert("C:\Users\Ricardo\Desktop\test.JPG").Select

      GET PROPERTY OF lo_worksheet 'Pictures' = lo_pictures.

      CALL METHOD OF lo_pictures 'Insert'

         EXPORTING

           #1          = lv_image_path.

      * Select the image;

      *  VB Code: ActiveSheet.Shapes.Range(Array("1 Picture")).Select

      CALL METHOD OF lo_worksheet 'Shapes' = lo_shapes

         EXPORTING

           #1          = '1 Picture'.

      * The image is inserted at the top left corner in the document;

      * Move to the right

      *   VB Code: Selection.ShapeRange.IncrementLeft 150

      CALL METHOD OF lo_shapes 'IncrementLeft'

         EXPORTING

           #1          = 150.

      * Move down;

      *   VB Code: Selection.ShapeRange.IncrementTop 100

      CALL METHOD OF lo_shapes 'IncrementTop'

         EXPORTING

           #1          = 100.

      * Scale the image to 50%

      *  VB Code: Selection.ShapeRange.ScaleHeight 0.5, msoFalse, msoScaleFromTopLeft

      CALL METHOD OF lo_shapes 'ScaleHeight'

         EXPORTING

           #1          = '0,5'

           #2          = 0

           #3          = 0.

      Regards,
      Ricardo.

      Author's profile photo phanindra ghanta
      phanindra ghanta

      Hello Ricardo,

      Thanks a Lot, Great document.

      I have a requirement for creation of excel with 100 sheets in it each sheet has employee data.

      everything works fine but sometimes i an not getting 100 sheets.

      report is behaving weird sometimes i get 100 sheets some times bellow 10 don't know why excel is not getting created with number of sheets provided.

      i using below code to create sheets.

      CREATE OBJECT gv_excel 'EXCEL.APPLICATION'.

         SET PROPERTY OF gv_excel  'VISIBLE' = 0.

         CALL METHOD OF

             gv_excel

             'WORKBOOKS' = gv_workbook.

         CALL METHOD OF

             gv_workbook

             'ADD'.

      *Create 100 sheets

         SET PROPERTY OF gv_excel 'SheetsInNewWorkbook' = lv_sheets.


      Request you help on this.


      Thanks in advance for your help.

      Regards,

      Phanindra

      Author's profile photo Ricardo Romero Mata
      Ricardo Romero Mata
      Blog Post Author

      Hi, Phanindra


      I don't know what can be the problem, I've never used this method.... mmm

      You can always use something like this:

      GET PROPERTY OF gv_excel 'ACTIVESHEET' = lo_worksheet.

      DO 100 TIMES.

          CALL METHOD OF lo_worksheet 'add'.

      ENDDO.


      It's less elegant, but it works fine 😉

      Author's profile photo phanindra ghanta
      phanindra ghanta

      thanks for the response...:)

      can we insert a macro in excel which can auto calculate hours enterd and display in the total column.

      thanks,
      Phanindra

      Author's profile photo Raja Dhandapani
      Raja Dhandapani

      Hi Ricardo,

      Thanks for sharing your knowledge!

      Can you please confirm if it is possible to add the image stored in SE78 or Application Server to our excel?

      If yes, please let me know.

      Cheers,

      Raj

      Author's profile photo Former Member
      Former Member

      Hi Ricardo,

      Very useful.Well illustration.

      Thanks for sharing.

      Regards,

      Sucheta

      Author's profile photo Former Member
      Former Member

      That’s really helpful who wants to learn OLE.

      By the way, could anybody help me to read the value of radio button from Excel to abap using OLE.

      Regards,

      Sakthi Sri.

      Author's profile photo Ramesh Mahankali
      Ramesh Mahankali

      Hi Ricardo,

      I have a scenario where, I need to populate data into pre formatted excel template in background mode. Do you know if OLE2 supports excell sheet generation in background mode?

      Also the excel template has marco's build into it and has an file extension of .XLSM.

      Thanks in Advance,

      Ramesh

      Author's profile photo Sandra Rossi
      Sandra Rossi

      OLE requires a connection from ABAP server to a server with OLE application installed (note that all Microsoft Office applications (Excel, Word...) are OLE compatible). In dialog, all OLE applications from the frontend computer can be accessed. In background, the frontend computers cannot be accessed, or it's difficult/rather unusual to do it. So we always prefer using abap2xlsx to generate directly the Excel file, and it's much faster too.

      Author's profile photo Former Member
      Former Member

      Hi Ricardo thanks for sharing your knowledge, in question to the subject I would like to see if they could help me to convert a code from a Macro to Abap.

      I’m new to this is the basics but I’m having a few things.

      Here is the code I am converting:

      finrgo = Range(“B65536”).End(xlUp).Row
      Range(“B9”).Select / call method of lo_worksheet ‘Cells’ = w_cell exporting #1 = 9 #2 = 2 
      While ActiveCell.Row <= finrgo
      If ActiveCell.Value = “” Then / SET PROPERTY OF w_cell ‘Value’ = ”.
      ActiveCell.EntireRow.Delete
      finrgo = finrgo – 1
      Else
      ActiveCell.Offset(1, 0).Select
      End If
      Wend

      THANK YOU!!

      Author's profile photo Mauro Calenda
      Mauro Calenda

      Excellent work, Ricardo!

      It helped me a lot to export easlily several flows of data in Excel.

      I'm struggling (so far with no result) trying to convert in ABAP the macro below.

      The purpose is to put a Red/Yellow/Green trafficlight according with the values (8,9,10) stored in the cells of the range A1:A3000

       

      Range("A1:A3000").Select
      Selection.FormatConditions.AddIconSetCondition
      Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With Selection.FormatConditions(1)
      .ReverseOrder = False
      .ShowIconOnly = False
      .IconSet = ActiveWorkbook.IconSets(8)
      End With
      With Selection.FormatConditions(1).IconCriteria(2)
      .Type = xlConditionValueNumber
      .Value = 9
      .Operator = 7
      End With
      With Selection.FormatConditions(1).IconCriteria(3)
      .Type = xlConditionValueNumber
      .Value = 10
      .Operator = 7
      End With

       

      Any help would be appreciated

       

      Thanks in advance.

       

      M.

      Author's profile photo Ricardo Romero Mata
      Ricardo Romero Mata
      Blog Post Author

      Hello, Mauro.

      Try with this code, use the template report in this blog.

      I’m geting this result:

      * ——————————————————–*

      gs_data = ‘8’.   APPEND gs_data TO gt_data.
      gs_data = ‘9’.   APPEND gs_data TO gt_data.
      gs_data = ’10’.  APPEND gs_data TO gt_data.

      PERFORM paste_clipboard USING 1 1.

      *——————————————————————–*
      DATA: lo_cellstart  TYPE ole2_object,
      lo_cellend    TYPE ole2_object,
      lo_selection  TYPE ole2_object,
      lo_range      TYPE ole2_object,
      lo_format     TYPE ole2_object,
      lo_icon       TYPE ole2_object,
      lo_count      TYPE ole2_object.

      CALL METHOD OF go_worksheet ‘Cells’ = lo_cellstart
      EXPORTING
      #1 = 1
      #2 = 1.
      CALL METHOD OF go_worksheet ‘Cells’ = lo_cellend
      EXPORTING
      #1 = 3000
      #2 = 1.
      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 ‘FormatConditions’ = lo_format.
      CALL METHOD OF lo_format ‘AddIconSetCondition’.

      CALL METHOD OF lo_format ‘Count’ = lo_count.

      CALL METHOD OF lo_selection ‘FormatConditions’ = lo_format
      EXPORTING #1 = lo_count.
      CALL METHOD OF  lo_format ‘SetFirstPriority’.

      CALL METHOD OF lo_selection ‘FormatConditions’ = lo_format
      EXPORTING #1 = ‘1’.

      SET PROPERTY OF lo_format ‘ReverseOrder’ = ‘False’.
      SET PROPERTY OF lo_format ‘ShowIconOnly’ = ‘False’.
      SET PROPERTY OF lo_format ‘IconSet’ = ‘ActiveWorkbook.IconSets(8)’.

      CALL METHOD OF lo_format ‘IconCriteria’ = lo_icon EXPORTING #1 = ‘2’.
      SET PROPERTY OF lo_icon ‘Type’ = ‘0’. “xlConditionValueNumber
      SET PROPERTY OF lo_icon ‘Value’ = ‘9’.
      SET PROPERTY OF lo_icon ‘Operator’ = ‘7’.

      CALL METHOD OF lo_format ‘IconCriteria’ = lo_icon EXPORTING #1 = ‘3’.
      SET PROPERTY OF lo_icon ‘Type’ = ‘0’. “xlConditionValueNumber
      SET PROPERTY OF lo_icon ‘Value’ = ’10’.
      SET PROPERTY OF lo_icon ‘Operator’ = ‘7’.

       

       

      Author's profile photo Mauro Calenda
      Mauro Calenda

      Hi Ricardo!

      Sorry for my late reply.

      Thanks mate, it helped me a lot!

      Author's profile photo Shreepati Shenoy
      Shreepati Shenoy

      Hi Ricardo,

      Very nicely explained.

      I tried this and it is working perfectly fine but it is creating some formatting issues with a field which has value like - ''08-2019" and its a character field in my internal table but at the time of saving in excel it is showing the output as - " Aug 19".

      I don't want excel to convert its formatting.

      Can you please help me regarding the same.

      Thanks in advance.

      Author's profile photo Raul Galavis
      Raul Galavis

      Hello Ricardo!

       

      Hope you're doing fine, I did implement your code (with the include) and is working, I mean file is being downloaded, however there's no format... is actually like your first image. Do you know what could happen?

      Author's profile photo Eugenio Rouhani
      Eugenio Rouhani

      Hello Ricardo!

       

      What A Tutorial!!

       

      Very Simple to read and easy to implement but with milions possibilities of creation.

       

      Thank You.

       

      Eugenio.

      Author's profile photo Priti Singh
      Priti Singh

      Hello Ricardo,

      My requirement is for adding a dropdown to one of the columns. I have tried to execute your code but I am getting it into any format. its plain excel getting downloaded and there is no dropdown for Departure city.

      Regards,

      Priti