Standard service - a fixed amount of work that is performed at a fixed price.  Post Service

  

Friday, 03 May 2019 18:15

A to Z of OLE Excel in ABAP 7.4

Written by  https://sapyard.com/a-to-z-of-ole-excel-in-abap-7-4/
Rate this item
(0 votes)

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.

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>***Includeobjects
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>***Popoluatetables
SELECTebeln,
bukrs,
bstyp,
bsart
INTOTABLE@DATA(gt_ekko)
FROMekko
UP TO5ROWS
WHEREebelnLIKE'45%'.
IFgt_ekko[]ISNOTINITIAL.
SELECTebeln,
ebelp,
matnr,
bukrs
INTOTABLE@DATA(gt_ekpo)
FROMekpo
FORALLENTRIESIN@gt_ekko
WHEREebeln=@gt_ekko-ebeln.
IFgt_ekpo[]ISNOTINITIAL.
SELECTebeln,
ebelp,
gjahr,
belnr
INTOTABLE@DATA(gt_ekbe)
FROMekbe
FORALLENTRIESIN@gt_ekpo
WHEREebeln=@gt_ekpo-ebeln
ANDebelp=@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)TYPEc,
ty_dataTYPETABLEOFdata1.
*** Variables
DATA:gt_1TYPEty_dataWITHHEADERLINE,
gt_2TYPEty_dataWITHHEADERLINE,
gt_3TYPEty_dataWITHHEADERLINE,
deli(1)TYPEc.
"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>**Headerforfirstsheet
gt_1=|EBELN{deli}BUKRS{deli}BSTYP{deli}BSART|.
APPENDgt_1.
CLEARgt_1.
**Data for first sheet
LOOP ATgt_ekkoINTODATA(wa_ekko).
gt_1=|{wa_ekko-ebeln}{deli}{wa_ekko-bukrs}{deli}{wa_ekko-bstyp}{deli}{wa_ekko-bsart}|.
APPENDgt_1.
CLEARgt_1.
ENDLOOP.
**Header for second sheet
gt_2=|EBELN{deli}EBELP{deli}MATNR{deli}BUKRS|.
APPENDgt_2.
CLEARgt_2.
**Data for second sheet
LOOP ATgt_ekpoINTODATA(wa_ekpo).
gt_2=|{wa_ekpo-ebeln}{deli}{wa_ekpo-ebelp}{deli}{wa_ekpo-matnr}{deli}{wa_ekpo-bukrs}|.
APPENDgt_2.
CLEARgt_2.
ENDLOOP.
**Header for third sheet
gt_3=|EBELN{deli}EBELP{deli}GJAHR{deli}BELNR|.
APPENDgt_3.
CLEARgt_3.
**Data for third sheet
LOOP ATgt_ekbeINTODATA(wa_ekbe).
gt_3=|{wa_ekbe-ebeln}{deli}{wa_ekbe-ebelp}{deli}{wa_ekbe-gjahr}{deli}{wa_ekbe-belnr}|.
APPENDgt_3.
CLEARgt_3.
ENDLOOP.</code>

Step 5 – Time for OLE Application

Create an OLE object as shown below.

1
2
3
<code>*startExcel
CREATE OBJECTh_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>*---getlistofworkbooks,initiallyempty
CALL METHODOFh_excel'Workbooks'=h_sheets.
SET PROPERTYOFh_excel'Visible'=1.If1itopensexcelapplicationinfrontendandif0thenexcelwillbecreatedinbackendmode.
CALL METHODOFh_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>*OledataDeclarations
DATA:h_excelTYPEole2_object," Excel object
h_sheetsTYPEole2_object," list of workbooks
h_sheetTYPEole2_object," workbook
h_cellTYPEole2_object," cell
worksheetTYPEole2_object,"Worksheet
e_colorTYPEole2_object,"Color
rangeTYPEole2_object,"Range
bordersTYPEole2_object,"Borders
h_sheet1TYPEole2_object,"First sheet
h_sheet2TYPEole2_object,"Second Sheet
h_sheet3TYPEole2_object,"Third Sheet
gs_fontTYPEole2_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 PROPERTYOFh_excel'ACTIVESHEET'=worksheet.
SET PROPERTYOFworksheet'Name'=’EKKO’.“Sheetname</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>**Copydatainclipboard
CALL METHODcl_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>**choosefirstcell.
CALL METHODOFh_excel'Cells'=w_cell1
EXPORTING
#1=1"Row
#2=1."Column
**choose second cell.
CALL METHODOFh_excel'Cells'=w_cell2
EXPORTING
#1=1"Row
#2=1."Column
**Make range from selected cell
CALL METHODOFh_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>**Changewidthofcolumn.
SET PROPERTYOFw_cell1'Columnwidth'=12.</code>

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

1
2
3
4
<code>CALL METHODOFrange'Select'.
** Paste data from clipboard to worksheet.
CALL METHODOFworksheet'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 METHODOFrange'Borders'=bordersNOFLUSH
EXPORTING#1=7."7 for left side
SET PROPERTYOFborders'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>**Logictoassignborderstofetcheddatainworksheet.
DATA(i)=0.
LOOP ATit_sheetINTODATA(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 METHODOFh_excel'Range'=range
EXPORTING
#1=first
#2=second.
**Logic to assign border on left side.
CALL METHODOFrange'Borders'=bordersNOFLUSH
EXPORTING#1=7."7 for left side
SET PROPERTYOFborders'LineStyle'=1."type of line.
**Logic to assign border on right side.
CALL METHODOFrange'Borders'=bordersNOFLUSH
EXPORTING#1=8.
SET PROPERTYOFborders'LineStyle'=1.
**Logic to assign border on top side.
CALL METHODOFrange'Borders'=bordersNOFLUSH
EXPORTING#1=9.
SET PROPERTYOFborders'LineStyle'=1.
**Logic to assign border on bottom side.
CALL METHODOFrange'Borders'=bordersNOFLUSH
EXPORTING#1=10.
SET PROPERTYOFborders'LineStyle'=1.
**Logic to assign border on vertical side.
CALL METHODOFrange'Borders'=bordersNOFLUSH
EXPORTING#1=11.
SET PROPERTYOFborders'LineStyle'=1.
**Logic to assign border on horizontal side.
CALL METHODOFrange'Borders'=bordersNOFLUSH
EXPORTING#1=12.
SET PROPERTYOFborders'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>**Logictochangefontandcolorofheaderdata.
CLEARi.
DO4TIMES.
i=i+1.
CALL METHODOFh_excel'CELLS'=h_cellNOFLUSH
EXPORTING#1=1
#2=i.
GET PROPERTYOFh_cell'FONT'=gs_fontNOFLUSH.
SET PROPERTYOFgs_font'BOLD'=1NOFLUSH.
SET PROPERTYOFgs_font'SIZE'=12.
CALL METHODOFh_cell'INTERIOR'=range.
SET PROPERTYOFrange'ColorIndex'=5.
SET PROPERTYOFrange'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 toregister or you may submit your articles directly to This email address is being protected from spambots. You need JavaScript enabled to view it. or This email address is being protected from spambots. You need JavaScript enabled to view it. with all details (text, images and code) and SAPYard team would publish it in your name.

Please leave your feedback for my first article.

Please SUBSCRIBE to SAPYard’s Youtube Channel for Free End to End SAP Video Tutorial and Training.

If you want to have real time discussions and resolutions, do join our SAP Technical Telegram Group where we have more than 4675+ active SAP consultants from 6 Continents.

Please Note: You need to install Telegram App on your mobile first and then you can join the group using the above link.

Step by Step Tutorials on OOPs ABAP

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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
<code>**&amp;---------------------------------------------------------------------*
**&amp; Report ZEXCEL_TAB
**&amp;---------------------------------------------------------------------*
**&amp;
**&amp;---------------------------------------------------------------------*
REPORTzexcel_tab.
*&amp;---------------------------------------------------------------------*
*&amp; Include Z_EXCEL_DOWNLOAD_TOP Report Z_EXCEL_DOWNLOAD
*&amp;---------------------------------------------------------------------*
*** Include objects
INCLUDE:ole2incl.
TYPES:data1(1500)TYPEc,
ty_dataTYPETABLEOFdata1.
*** Variable Declarations
DATA:w_cell1TYPEole2_object,
w_cell2TYPEole2_object.
* Ole data Declarations
DATA:h_excelTYPEole2_object," Excel object
h_sheetsTYPEole2_object," list of workbooks
h_sheetTYPEole2_object," workbook
h_cellTYPEole2_object," cell
worksheetTYPEole2_object,"Worksheet
e_colorTYPEole2_object,"Color
rangeTYPEole2_object,"Range
bordersTYPEole2_object,"Borders
h_sheet1TYPEole2_object,"First sheet
h_sheet2TYPEole2_object,"Second Sheet
h_sheet3TYPEole2_object,"Third Sheet
gs_fontTYPEole2_object."Font
*** Variables
DATA:gt_1TYPEty_dataWITHHEADERLINE,
gt_2TYPEty_dataWITHHEADERLINE,
gt_3TYPEty_dataWITHHEADERLINE,
deli(1)TYPEc,
gv_sheet_name(20)TYPEc.
DATAl_rcTYPEi.
"Delimeter
deli=cl_abap_char_utilities=>horizontal_tab.
*** Popoluate tables
SELECTebeln,
bukrs,
bstyp,
bsart
INTOTABLE@DATA(gt_ekko)
FROMekko
UP TO5ROWS
WHEREebelnLIKE'45%'.
IFgt_ekko[]ISNOTINITIAL.
SELECTebeln,
ebelp,
matnr,
bukrs
INTOTABLE@DATA(gt_ekpo)
FROMekpo
FORALLENTRIESIN@gt_ekko
WHEREebeln=@gt_ekko-ebeln.
IFgt_ekpo[]ISNOTINITIAL.
SELECTebeln,
ebelp,
gjahr,
belnr
INTOTABLE@DATA(gt_ekbe)
FROMekbe
FORALLENTRIESIN@gt_ekpo
WHEREebeln=@gt_ekpo-ebeln
ANDebelp=@gt_ekpo-ebelp.
ENDIF.
ENDIF.
**Header for first sheet
gt_1=|EBELN{deli}BUKRS{deli}BSTYP{deli}BSART|.
APPENDgt_1.
CLEARgt_1.
**Data for first sheet
LOOP ATgt_ekkoINTODATA(wa_ekko).
gt_1=|{wa_ekko-ebeln}{deli}{wa_ekko-bukrs}{deli}{wa_ekko-bstyp}{deli}{wa_ekko-bsart}|.
APPENDgt_1.
CLEARgt_1.
ENDLOOP.
**Header for second sheet
gt_2=|EBELN{deli}EBELP{deli}MATNR{deli}BUKRS|.
APPENDgt_2.
CLEARgt_2.
**Data for second sheet
LOOP ATgt_ekpoINTODATA(wa_ekpo).
gt_2=|{wa_ekpo-ebeln}{deli}{wa_ekpo-ebelp}{deli}{wa_ekpo-matnr}{deli}{wa_ekpo-bukrs}|.
APPENDgt_2.
CLEARgt_2.
ENDLOOP.
**Header for third sheet
gt_3=|EBELN{deli}EBELP{deli}GJAHR{deli}BELNR|.
APPENDgt_3.
CLEARgt_3.
**Data for third sheet
LOOP ATgt_ekbeINTODATA(wa_ekbe).
gt_3=|{wa_ekbe-ebeln}{deli}{wa_ekbe-ebelp}{deli}{wa_ekbe-gjahr}{deli}{wa_ekbe-belnr}|.
APPENDgt_3.
CLEARgt_3.
ENDLOOP.
* start Excel
IFh_excel-header=spaceORh_excel-handle=-1.
CREATE OBJECTh_excel'EXCEL.APPLICATION'.
ENDIF.
*--- get list of workbooks, initially empty
CALL METHODOFh_excel'Workbooks'=h_sheets.
SET PROPERTYOFh_excel'Visible'=1.
CALL METHODOFh_sheets'Add'=h_sheet.
PERFORMcreate_sheetTABLESgt_1
USING'EKKO'h_sheet1.
PERFORMcreate_sheetTABLESgt_2
USING'EKPO'h_sheet2.
PERFORMcreate_sheetTABLESgt_3
USING'EKBE'h_sheet3.
* Free Excel objects
FREEOBJECT:h_cell,
h_sheets,
h_sheet,
h_excel.
*&amp;---------------------------------------------------------------------*
*&amp; Form CREATE_SHEET
*&amp;---------------------------------------------------------------------*
FORMcreate_sheetTABLESit_sheetTYPEty_data
USINGiv_name
iv_sheetTYPEole2_object.
DATAl_rcTYPEi.
gv_sheet_name=iv_name.
IFgv_sheet_nameNE'EKKO'.
GET PROPERTYOFh_excel'Sheets'=iv_sheet.
CALL METHODOFiv_sheet'Add'=h_sheet.
SET PROPERTYOFh_sheet'Name'=gv_sheet_name.
GET PROPERTYOFh_excel'ACTIVESHEET'=worksheet.
ELSE.
GET PROPERTYOFh_excel'ACTIVESHEET'=worksheet.
SET PROPERTYOFworksheet'Name'=gv_sheet_name.
ENDIF.
**Copy data in clipboard
CALL METHODcl_gui_frontend_services=>clipboard_export
IMPORTING
data=it_sheet[]
CHANGING
rc=l_rc
EXCEPTIONS
cntl_error=1
error_no_gui=2
not_supported_by_gui=3
OTHERS=4.
**choose first cell.
CALL METHODOFh_excel'Cells'=w_cell1
EXPORTING
#1=1"Row
#2=1."Column
**choose second cell.
CALL METHODOFh_excel'Cells'=w_cell2
EXPORTING
#1=1"Row
#2=1."Column
**Change width of column.
SET PROPERTYOFw_cell1'Columnwidth'=12.
**Make range from selected cell
CALL METHODOFh_excel'Range'=range
EXPORTING
#1=w_cell1
#2=w_cell2.
CALL METHODOFrange'Select'.
** Paste data from clipboard to worksheet.
CALL METHODOFworksheet'Paste'.
**Logic to assign borders to fetched data in worksheet.
DATA(i)=0.
LOOP ATit_sheetINTODATA(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 METHODOFh_excel'Range'=range
EXPORTING
#1=first
#2=second.
**Logic to assign border on left side.
CALL METHODOFrange'Borders'=bordersNOFLUSH
EXPORTING#1=7."7 for left side
SET PROPERTYOFborders'LineStyle'=1."type of line.
**Logic to assign border on right side.
CALL METHODOFrange'Borders'=bordersNOFLUSH
EXPORTING#1=8.
SET PROPERTYOFborders'LineStyle'=1.
**Logic to assign border on top side.
CALL METHODOFrange'Borders'=bordersNOFLUSH
EXPORTING#1=9.
SET PROPERTYOFborders'LineStyle'=1.
**Logic to assign border on bottom side.
CALL METHODOFrange'Borders'=bordersNOFLUSH
EXPORTING#1=10.
SET PROPERTYOFborders'LineStyle'=1.
**Logic to assign border on vertical side.
CALL METHODOFrange'Borders'=bordersNOFLUSH
EXPORTING#1=11.
SET PROPERTYOFborders'LineStyle'=1.
**Logic to assign border on horizontal side.
CALL METHODOFrange'Borders'=bordersNOFLUSH
EXPORTING#1=12.
SET PROPERTYOFborders'LineStyle'=1.
ENDLOOP.
** Logic to change font and color of header data.
CLEARi.
DO4TIMES.
i=i+1.
CALL METHODOFh_excel'CELLS'=h_cellNOFLUSH
EXPORTING#1=1
#2=i.
GET PROPERTYOFh_cell'FONT'=gs_fontNOFLUSH.
SET PROPERTYOFgs_font'BOLD'=1NOFLUSH.
SET PROPERTYOFgs_font'SIZE'=12.
CALL METHODOFh_cell'INTERIOR'=range.
SET PROPERTYOFrange'ColorIndex'=5.
SET PROPERTYOFrange'Pattern'=1.
ENDDO.
ENDFORM." CREATE_SHEET</code>
Read 29 times

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.