A to Z of OLE Excel in ABAP 7.4

SAP users, both business and end users always need to download the output of a report to spreadsheet and do their analytics. The standard excel output from a report is very simple process but it is old fashioned and the spreadsheet looks quite boring. There is no default formatting and the users have to do all the hard work of changing the fonts, coloring the texts, marking the borders etc.

Updated 16th Aug 2019 – If you are working in non-ABAP 7.4 (below), there is complete reference program for you too. Go to the end of this article. Thank you Legxis (LeonievonK) for the share.

I acknowledge, whatever I mentioned above can be achieved in many ways programmatically. We can do it in the old traditional ABAP way but providing multiple tabs in the spreadsheet and formatting is quite tricky with non OLE method.

OLE = Object Linking and Embedding

The high level agenda of this article is to be the G.O.A.T. (please google it if you do not know the full form) of OLE Excel Guide. We want this one article to be the light house of all ABAP developers who needs to work with OLE Excel in their projects. Too much of chest thumping before the start. Right?

We would use the new ABAP 7.4+ syntaxes to created Excel with OLE. We would format the spreadsheet. Headers in bold, font color would be different at different area, background would be blue or any color of your choice. We would mark the borders better and also create multiple Tabs in the excel spreadsheet.

Also Read: Create your first OData Service

Let’s start our dive into the OLE approach of creating Excel.

Step 1 – Include OLE2INCL in the program

The OLE automation programs “OLE2INCL” include needs to be specified. It is kind of a library of OLE syntax.

1
2
3
 
<code>*** Include objects
INCLUDE: ole2incl.</code>

Step 2 – Populate the internal tables with data

For our demo, we are fetching data from some standard SAP tables so that everyone can use our code snippet.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
 
<code>*** Popoluate tables
SELECT ebeln,
       bukrs,
       bstyp,
       bsart
  INTO TABLE @DATA(gt_ekko)
  FROM ekko
  UP TO 5 ROWS
  WHERE ebeln LIKE '45%'.
 
IF gt_ekko[] IS NOT INITIAL.
 
  SELECT ebeln,
         ebelp,
         matnr,
         bukrs
    INTO TABLE @DATA(gt_ekpo)
    FROM ekpo
    FOR ALL ENTRIES IN @gt_ekko
    WHERE ebeln = @gt_ekko-ebeln.
 
  IF gt_ekpo[] IS NOT INITIAL.
    SELECT ebeln,
           ebelp,
           gjahr,
           belnr
      INTO TABLE @DATA(gt_ekbe)
      FROM ekbe
      FOR ALL ENTRIES IN @gt_ekpo
      WHERE ebeln = @gt_ekpo-ebeln
      AND   ebelp = @gt_ekpo-ebelp.
  ENDIF.
ENDIF.</code>

We have 3 internal tables viz GT_EKKO, GT_EKPO and GT_EKBE ready.

Step 3 – Put the internal table in a string table separated by delimiter

Our goal is to put each internal table data in different tabs of the excel. So we would save the internal table data in a long character variable where the data would be separated by delimiter ‘|’. The long variable would be used later to create a file at run-time and save in the tabs of the spreadsheet.

For our explanation, we have created a Table Type with 1500 long characters and used it for storing data separated by ‘|’. I have defined the delimiter variable as ‘deli’ which contains ‘|’ value ( cl_abap_char_utilities=>horizontal_tab ).

Did you think deli was for food?

1
2
3
4
5
6
7
8
9
10
11
12
 
<code>TYPES: data1(1500) TYPE c,
       ty_data     TYPE TABLE OF data1.
 
*** Variables
DATA: gt_1              TYPE ty_data WITH HEADER LINE,
      gt_2              TYPE ty_data WITH HEADER LINE,
      gt_3              TYPE ty_data WITH HEADER LINE,
      deli(1)           TYPE c.
 
"Delimeter
deli = cl_abap_char_utilities=>horizontal_tab.</code>

Step 4 – Put internal table data to respective data/file types

Loop through the internal tables and concatenate the data to the respective string variables (gt_1, gt_2, gt_3). Please note, I have passed the headers as well, which will act like column names in excel sheets.

Check the new syntax for concatenation. If you are new to ABAP 7.4 syntax, please take the Free End to End Video Course on ABAP 7.4 Syntax and New Features.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 
<code>**Header for first sheet
gt_1 = |EBELN{ deli }BUKRS{ deli }BSTYP{ deli }BSART|.
APPEND gt_1.
CLEAR gt_1.
 
**Data for first sheet
LOOP AT gt_ekko INTO DATA(wa_ekko).
  gt_1 = | { wa_ekko-ebeln } { deli } { wa_ekko-bukrs } { deli } { wa_ekko-bstyp } { deli } { wa_ekko-bsart } |.
  APPEND gt_1.
  CLEAR gt_1.
ENDLOOP.
 
**Header for second sheet
gt_2 = |EBELN{ deli }EBELP{ deli }MATNR{ deli }BUKRS|.
APPEND gt_2.
CLEAR gt_2.
 
**Data for second sheet
LOOP AT gt_ekpo INTO DATA(wa_ekpo).
  gt_2 = | { wa_ekpo-ebeln } { deli } { wa_ekpo-ebelp } { deli } { wa_ekpo-matnr } { deli } { wa_ekpo-bukrs } |.
  APPEND gt_2.
  CLEAR gt_2.
ENDLOOP.
 
**Header for third sheet
gt_3 = |EBELN{ deli }EBELP{ deli }GJAHR{ deli }BELNR|.
APPEND gt_3.
CLEAR gt_3.
 
**Data for third sheet
LOOP AT gt_ekbe INTO DATA(wa_ekbe).
  gt_3 = | { wa_ekbe-ebeln } { deli } { wa_ekbe-ebelp } { deli } { wa_ekbe-gjahr } { deli } { wa_ekbe-belnr } |.
  APPEND gt_3.
  CLEAR gt_3.
ENDLOOP.</code>

Step 5 – Time for OLE Application

Create an OLE object as shown below.

1
2
3
 
<code>* start Excel
  CREATE OBJECT h_excel 'EXCEL.APPLICATION'.</code>

Step 6 – Create Workbook and Worksheets

If you are struggling (I hope not) with the concepts of workbook and worksheets then this snapshot should help.

We are going to use all these functionalities in our OLE generation report. Excited??

Let us create Workbook and Worksheets.

1
2
3
4
5
6
7
 
<code>*--- get list of workbooks, initially empty
CALL METHOD OF h_excel 'Workbooks' = h_sheets.
 
SET PROPERTY OF h_excel 'Visible' = 1. “If ‘1’ – it opens excel application in frontend and if ‘0’ then excel will be created in backend mode.
 
CALL METHOD OF h_sheets 'Add' = h_sheet.</code>

All the objects of excel application must be declare with type ‘ole2_object’.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
<code>* Ole data Declarations
DATA: h_excel   TYPE ole2_object, " Excel object
      h_sheets  TYPE ole2_object, " list of workbooks
      h_sheet   TYPE ole2_object, " workbook
      h_cell    TYPE ole2_object, " cell
      worksheet TYPE ole2_object, "Worksheet
      e_color   TYPE ole2_object, "Color
      range     TYPE ole2_object, "Range
      borders   TYPE ole2_object, "Borders
      h_sheet1  TYPE ole2_object, "First sheet
      h_sheet2  TYPE ole2_object, "Second Sheet
      h_sheet3  TYPE ole2_object, "Third Sheet
      gs_font   TYPE ole2_object. "Font</code>

Also Check: An ABAPer’s First SAPUI5 App in SAP WebIDE

Step 7 – Activate the current worksheet and name it

1
2
3
4
 
<code>GET PROPERTY OF h_excel 'ACTIVESHEET' = worksheet.
 
SET PROPERTY OF worksheet 'Name' = ’EKKO’. “Sheet name</code>

Step 8 – Pass the data from string internal table to Excel file

There are two ways to pass the data in excel:
i) one-by-one
ii) copy-paste method

Here, we am going to copy whole data from internal table and paste it in the excel. This approach saves time and increases the performance of code. See, we revealed a way to optimize the code.

1
2
3
4
5
6
7
8
9
10
11
12
 
<code>**Copy data in clipboard
  CALL METHOD cl_gui_frontend_services=>clipboard_export
    IMPORTING
      data                 = gt1[]
    CHANGING
      rc                   = l_rc
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4.</code>

The above snippet is self explanatory. It copies the data of internal table into clipboard.

Ctrl C is always followed by Ctrl V.

Now paste the copied data from clipboard to spreadsheet.

For pasting the copied data in excel sheet, we need to select the cells and need to make the range, in which the data will be pasted.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
<code>**choose first cell.
  CALL METHOD OF h_excel 'Cells' = w_cell1
    EXPORTING
    #1 = 1 "Row
    #2 = 1. "Column
 
**choose second cell.
  CALL METHOD OF h_excel 'Cells' = w_cell2
    EXPORTING
    #1 = 1 "Row
    #2 = 1. "Column
 
**Make range from selected cell
  CALL METHOD OF h_excel 'Range' = range
    EXPORTING
    #1 = w_cell1
    #2 = w_cell2.</code>

In our program, we have EBELN as our first field in every table. After copying that data into excel sheet, we see EBELN in below format because of space constraints (less width of cell).

Change the width of particular column with property ‘Columnwidth’.

1
2
3
 
<code>**Change width of column.
  SET PROPERTY OF w_cell1 'Columnwidth' = 12.</code>

Now we need to select the range and paste it in excel worksheet.

1
2
3
4
 
<code>  CALL METHOD OF range 'Select'.
** Paste data from clipboard to worksheet.
  CALL METHOD OF worksheet 'Paste'.</code>

Step 9 – Formatting of the Excel Spreadsheet in SAP ABAP

The above steps ensure, we have the data in our excel. Now we have the interesting job to do i.e. Formatting.

9.1 Create Borders

Whatever data we are going to paste in excel should contains borders. For achieving this, Excel application has a property as ‘borders’.

1
2
3
4
 
<code>CALL METHOD OF range 'Borders' = borders NO FLUSH
     EXPORTING #1  = 7. "7 for left side
SET PROPERTY OF borders 'LineStyle'= 1. "type of line.</code>

Above, 7 is indicating border for left side. Same way we have,
8 for right side, 9 for top side, etc.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
 
<code>**Logic to assign borders to fetched data in worksheet.
  DATA(i) = 0.
  LOOP AT it_sheet INTO DATA(ls_sheet).
    i = i + 1.
    DATA(first) = |A{ i }|. "Column from where you want to start providing borders.
    DATA(second) = |D{ i }|. "Column up to which you want to provide the borders.
 
**Make range of selected columns.
    CALL METHOD OF h_excel 'Range' = range
      EXPORTING
      #1 = first
      #2 = second.
 
**Logic to assign border on left side.
    CALL METHOD OF range 'Borders' = borders    NO FLUSH
     EXPORTING #1  = 7. "7 for left side
    SET PROPERTY OF borders 'LineStyle'= 1. "type of line.
 
**Logic to assign border on right side.
    CALL METHOD OF range 'Borders' = borders    NO FLUSH
       EXPORTING #1  = 8.
    SET PROPERTY OF borders 'LineStyle'= 1.
 
**Logic to assign border on top side.
    CALL METHOD OF range 'Borders' = borders    NO FLUSH
       EXPORTING #1  = 9.
    SET PROPERTY OF borders 'LineStyle'= 1.
 
**Logic to assign border on bottom side.
    CALL METHOD OF range 'Borders' = borders    NO FLUSH
       EXPORTING #1  = 10.
    SET PROPERTY OF borders 'LineStyle'= 1.
 
**Logic to assign border on vertical side.
    CALL METHOD OF range 'Borders' = borders    NO FLUSH
       EXPORTING #1  = 11.
    SET PROPERTY OF borders 'LineStyle'= 1.
 
**Logic to assign border on horizontal side.
    CALL METHOD OF range 'Borders' = borders    NO FLUSH
       EXPORTING #1  = 12.
    SET PROPERTY OF borders 'LineStyle'= 1.
 
  ENDLOOP.</code>

Also Check : How to export a formatted Excel file with colors, borders, filters in Web Dynpro.

9.2 Change the Font and Color of Header cells

For our case, we have only 4 headers in every worksheet. Therefore we have hard coded for 4 headers. You should make it dynamic.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
<code>** Logic to change font and color of header data.
  CLEAR i.
  DO 4 TIMES.
    i = i + 1.
    CALL METHOD OF h_excel 'CELLS'= h_cell  NO FLUSH
       EXPORTING #1 = 1
                 #2 = i.
 
    GET PROPERTY OF h_cell 'FONT' = gs_font NO FLUSH.
 
    SET PROPERTY OF gs_font 'BOLD' = 1 NO FLUSH.
 
    SET PROPERTY OF gs_font 'SIZE' = 12.
 
    CALL METHOD OF h_cell 'INTERIOR' = range.
 
    SET PROPERTY OF range 'ColorIndex' = 5.
 
    SET PROPERTY OF range 'Pattern' = 1.
  ENDDO.</code>

Above code is pretty simple. But you may want to understand the property names. For different colors, we have different numbers in OLE as shown below:

Choose your favourite color and you are done.

Check the output of the three Tabs.

Isn’t this cool? You can play with the excel application and it’s properties to explore more. Next time create your own excel output, format it with the eyes of a designer and amaze your client with the beautiful presentation. After all, SAP is not that boring as others complain.

The complete end to end code snippet is below at the end of the article. You may copy and past it in your ABAP editor. It should work without any hiccups.

This is the first time I am writing any blog in any space. Hopefully with each new articles, I would learn the tricks of writing better tutorials and learning series. SAPYard inspired me to be a technical author and I thoroughly enjoyed the process, right from conceptualizing the topic, working on the actual program to present and actually implementing my writing skills. If you want to join the Awesome SAPYard Author’s team, feel free to register or you may submit your articles directly to mail@sapyard.com or mailsapyard@gmail.com with all details (text, images and code) and SAPYard team would publish it in your name.

Source:

https://sapyard.com/a-to-z-of-ole-excel-in-abap-7-4/

Esta info la añado, pues es la parte donde podemos combinar con ABAP objetos OLE

Ejemplo

El ejemplo siguiente es para la función NumberFormat .

Visual Basic para Aplicaciones


Range("A1:A5").Value = 12345
Range("A1:A5").NumberFormat = "0.00"
Range("A1:A5").NumberFormat = "General"

Source

https://docs.microsoft.com/es-es/office/vba/api/excel.cellformat.numberformat

ABAP y SAP

Buscar este blog