Demo program for TOP-OF-PAGE for ALV displayed through CL_GUI_ALV_GRID

This program demonstrates how to create TOP OF PAGE for an ALV grid displayed through CL_GUI_ALV_GRID. One interesting fact which may be considered here is that the splitter TOP-OF-PAGE is not called while printing or background spool and the TOP-OF-PAGE printed in the spool cannot be displayed in the foreground.

The TOP-OF-PAGE displayed in foreground (this TOP-OF-PAGE won't be printed):



Choose the list output:



The list output(this top-of-page will be printed):



The code:

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
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
*&---------------------------------------------------------------------*
*& Report Z_SOURAV_ALV_TOP_OF_PAGE
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT z_sourav_alv_top_of_page.

*----------------------------------------------------------------------*
* CLASS cl_main DEFINITION
*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
CLASS cl_main DEFINITION.
PUBLIC SECTION.
METHODS:
constructor,
get_data,
populate_fc_layout,
top_of_page FOR EVENT top_of_page OF cl_gui_alv_grid
IMPORTING
e_dyndoc_id
table_index,
print_top_of_page FOR EVENT print_top_of_page OF cl_gui_alv_grid
IMPORTING table_index,
display.


PROTECTED SECTION.

PRIVATE SECTION.
TYPE-POOLS:sdydo.
DATA:i_sbook TYPE STANDARD TABLE OF sbook INITIAL SIZE 0.
DATA:oref_splitter TYPE REF TO cl_gui_splitter_container,
oref_container TYPE REF TO cl_gui_custom_container,
oref_alv TYPE REF TO cl_gui_alv_grid,
container1 TYPE REF TO cl_gui_container,
container2 TYPE REF TO cl_gui_container,
oref_doc TYPE REF TO cl_dd_document,
oref_html TYPE REF TO cl_gui_html_viewer,
wa_layout TYPE lvc_s_layo,
i_fieldcatalog TYPE lvc_t_fcat.

ENDCLASS. "cl_main DEFINITION
*----------------------------------------------------------------------*
* CLASS cl_main IMPLEMENTATION
*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
CLASS cl_main IMPLEMENTATION.
METHOD constructor.

CREATE OBJECT oref_container
EXPORTING
container_name = 'CONTAINER'
repid = 'Z_SOURAV_ALV_TOP_OF_PAGE'
dynnr = '0100'
EXCEPTIONS
cntl_error = 1
cntl_system_error = 2
create_error = 3
lifetime_error = 4
lifetime_dynpro_dynpro_link = 5
OTHERS = 6.
IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.


CREATE OBJECT oref_splitter
EXPORTING
parent = oref_container
rows = 2
columns = 1
EXCEPTIONS
cntl_error = 1
cntl_system_error = 2
OTHERS = 3.
IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.

CALL METHOD oref_splitter->get_container
EXPORTING
row = 1
column = 1
RECEIVING
container = container1.

CALL METHOD oref_splitter->set_row_height
EXPORTING
id = 1
height = 20
EXCEPTIONS
cntl_error = 1
cntl_system_error = 2
OTHERS = 3.
IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.


CALL METHOD oref_splitter->get_container
EXPORTING
row = 2
column = 1
RECEIVING
container = container2.


CREATE OBJECT oref_doc
EXPORTING
style = 'ALV_GRID'.


CREATE OBJECT oref_alv
EXPORTING
i_parent = container2
EXCEPTIONS
error_cntl_create = 1
error_cntl_init = 2
error_cntl_link = 3
error_dp_create = 4
OTHERS = 5.
IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.


ENDMETHOD. "constructor
METHOD get_data.
SELECT * FROM sbook INTO TABLE i_sbook UP TO 500 ROWS.
IF sy-subrc <> 0.

ENDIF.

ENDMETHOD. "get_data
METHOD populate_fc_layout.
CALL FUNCTION 'LVC_FIELDCATALOG_MERGE'
EXPORTING
i_structure_name = 'SBOOK'
i_client_never_display = 'X'
i_internal_tabname = 'I_SBOOK'
CHANGING
ct_fieldcat = i_fieldcatalog
EXCEPTIONS
inconsistent_interface = 1
program_error = 2
OTHERS = 3.
IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.

wa_layout-cwidth_opt = 'X'.

ENDMETHOD. "populate_fc_layout
METHOD top_of_page.

DATA: text TYPE sdydo_text_element,
background_id TYPE sdydo_key VALUE 'ALV_BACKGROUND'.

text = 'Top of Page'.

CALL METHOD oref_doc->add_text
EXPORTING
text = text
sap_color = cl_dd_document=>list_positive
sap_fontsize = cl_dd_document=>large.

oref_doc->new_line( ).
text = 'Line1'.

CALL METHOD oref_doc->add_text
EXPORTING
text = text.

CREATE OBJECT oref_html
EXPORTING
parent = container1
EXCEPTIONS
cntl_error = 1
cntl_install_error = 2
dp_install_error = 3
dp_error = 4
OTHERS = 5.
IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.

oref_doc->html_control = oref_html.

CALL FUNCTION 'REUSE_ALV_GRID_COMMENTARY_SET'
EXPORTING
document = oref_doc
bottom = space.

CALL METHOD oref_doc->merge_document.

CALL METHOD oref_doc->set_document_background
EXPORTING
picture_id = background_id.

CALL METHOD oref_doc->display_document
EXPORTING
reuse_control = 'X'
parent = container1
EXCEPTIONS
html_display_error = 1
OTHERS = 2.
IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
ENDMETHOD. "create_top_of_page
METHOD display.

SET HANDLER me->top_of_page FOR oref_alv.
SET HANDLER me->print_top_of_page FOR oref_alv.

CALL METHOD oref_alv->set_table_for_first_display
EXPORTING
is_layout = wa_layout
CHANGING
it_outtab = i_sbook
it_fieldcatalog = i_fieldcatalog
EXCEPTIONS
invalid_parameter_combination = 1
program_error = 2
too_many_lines = 3
OTHERS = 4.
IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.

CALL METHOD oref_doc->initialize_document
.


CALL METHOD oref_alv->list_processing_events
EXPORTING
i_event_name = 'TOP_OF_PAGE'
i_dyndoc_id = oref_doc.


CALL SCREEN 0100.
ENDMETHOD. "display
METHOD print_top_of_page.
WRITE:/5 'Print top of page' COLOR 5 INTENSIFIED OFF.
WRITE:/5 'Line1'.
ENDMETHOD. "print_top_of_page
ENDCLASS. "cl_main IMPLEMENTATION

DATA: oref_main TYPE REF TO cl_main.
DATA:ok_code TYPE syucomm.

START-OF-SELECTION.

CREATE OBJECT oref_main.

oref_main->get_data( ).
oref_main->populate_fc_layout( ).

END-OF-SELECTION.

oref_main->display( ).
*&---------------------------------------------------------------------*
*& Module STATUS_0100 OUTPUT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
MODULE status_0100 OUTPUT.
SET PF-STATUS '0100'.
* SET TITLEBAR 'xxx'.
ENDMODULE. " STATUS_0100 OUTPUT
*&---------------------------------------------------------------------*
*& Module USER_COMMAND_0100 INPUT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
MODULE user_command_0100 INPUT.
CASE ok_code.
WHEN 'BACK'.
SET SCREEN 00.
LEAVE SCREEN.
ENDCASE.
ENDMODULE. " USER_COMMAND_0100 INPUT

Upload data from multiple worksheets of an excel file into SAP

This program demonstrates hoe to read data from multiple worksheets of an excel file.
I developed this code using SAP class:
CL_GUI_CUSTOM_CONTAINER
and intefaces:
I_OI_CONTAINER_CONTROL,
I_OI_DOCUMENT_PROXY AND
I_OI_SPREADSHEET.

The test file:



The selection screen:



The output:



The Code:

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
*&---------------------------------------------------------------------*
*& Report ZTEST_SOURAV_EXCEL
*&
*&---------------------------------------------------------------------*
*& Sourav Bhaduri 02-Dec-2008
*&---------------------------------------------------------------------*

REPORT ztest_sourav_excel NO STANDARD PAGE HEADING.

DATA:
oref_container TYPE REF TO cl_gui_custom_container,
iref_control TYPE REF TO i_oi_container_control,
iref_document TYPE REF TO i_oi_document_proxy,
iref_spreadsheet TYPE REF TO i_oi_spreadsheet,
iref_error TYPE REF TO i_oi_error.

DATA:
v_document_url TYPE c LENGTH 256,
i_sheets TYPE soi_sheets_table,
wa_sheets TYPE soi_sheets,
i_data TYPE soi_generic_table,
wa_data TYPE soi_generic_item,
i_ranges TYPE soi_range_list.

PARAMETERS:
p_file TYPE localfile OBLIGATORY,
p_rows TYPE i DEFAULT 100 OBLIGATORY, "Rows (Maximum 65536)
p_cols TYPE i DEFAULT 10 OBLIGATORY. "Columns (Maximum 256)

INITIALIZATION.

CALL METHOD c_oi_container_control_creator=>get_container_control
IMPORTING
control = iref_control
error = iref_error.
IF iref_error->has_failed = 'X'.
CALL METHOD iref_error->raise_message
EXPORTING
type = 'E'.
ENDIF.


CREATE OBJECT oref_container
EXPORTING
container_name = 'CONT'
EXCEPTIONS
cntl_error = 1
cntl_system_error = 2
create_error = 3
lifetime_error = 4
lifetime_dynpro_dynpro_link = 5
OTHERS = 6.
IF sy-subrc <> 0.
MESSAGE e001(00) WITH 'Error while creating container'.
ENDIF.

CALL METHOD iref_control->init_control
EXPORTING
inplace_enabled = 'X'
r3_application_name = 'EXCEL CONTAINER'
parent = oref_container
IMPORTING
error = iref_error
EXCEPTIONS
javabeannotsupported = 1
OTHERS = 2.
IF iref_error->has_failed = 'X'.
CALL METHOD iref_error->raise_message
EXPORTING
type = 'E'.
ENDIF.

CALL METHOD iref_control->get_document_proxy
EXPORTING
document_type = soi_doctype_excel_sheet
IMPORTING
document_proxy = iref_document
error = iref_error.
IF iref_error->has_failed = 'X'.
CALL METHOD iref_error->raise_message
EXPORTING
type = 'E'.
ENDIF.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

* To provide F4 help for the file
PERFORM sub_file_f4.

START-OF-SELECTION.

CONCATENATE 'FILE://' p_file INTO v_document_url.

CALL METHOD iref_document->open_document
EXPORTING
document_title = 'Excel'
document_url = v_document_url
open_inplace = 'X'
IMPORTING
error = iref_error.
IF iref_error->has_failed = 'X'.
CALL METHOD iref_error->raise_message
EXPORTING
type = 'I'.
LEAVE LIST-PROCESSING.
ENDIF.

CALL METHOD iref_document->get_spreadsheet_interface
EXPORTING
no_flush = ' '
IMPORTING
error = iref_error
sheet_interface = iref_spreadsheet.

IF iref_error->has_failed = 'X'.
CALL METHOD iref_error->raise_message
EXPORTING
type = 'I'.
LEAVE LIST-PROCESSING.
ENDIF.

CALL METHOD iref_spreadsheet->get_sheets
EXPORTING
no_flush = ' '
IMPORTING
sheets = i_sheets
error = iref_error.
IF iref_error->has_failed = 'X'.
CALL METHOD iref_error->raise_message
EXPORTING
type = 'I'.
LEAVE LIST-PROCESSING.
ENDIF.
LOOP AT i_sheets INTO wa_sheets.
CALL METHOD iref_spreadsheet->select_sheet
EXPORTING
name = wa_sheets-sheet_name
IMPORTING
error = iref_error.
IF iref_error->has_failed = 'X'.
EXIT.
ENDIF.
CALL METHOD iref_spreadsheet->set_selection
EXPORTING
top = 1
left = 1
rows = p_rows
columns = p_cols.

CALL METHOD iref_spreadsheet->insert_range
EXPORTING
name = 'Test'
rows = p_rows
columns = p_cols
no_flush = ''
IMPORTING
error = iref_error.
IF iref_error->has_failed = 'X'.
EXIT.
ENDIF.

REFRESH i_data.

CALL METHOD iref_spreadsheet->get_ranges_data
EXPORTING
all = 'X'
IMPORTING
contents = i_data
error = iref_error
CHANGING
ranges = i_ranges.
DELETE i_data WHERE value IS INITIAL OR value = space.
ULINE.
WRITE:/1 wa_sheets-sheet_name COLOR 3.
ULINE.

LOOP AT i_data INTO wa_data.
WRITE:(50) wa_data-value.
AT END OF row.
NEW-LINE.
ENDAT.
ENDLOOP.
ENDLOOP.

CALL METHOD iref_document->close_document
IMPORTING
error = iref_error.
IF iref_error->has_failed = 'X'.
CALL METHOD iref_error->raise_message
EXPORTING
type = 'I'.
LEAVE LIST-PROCESSING.
ENDIF.

CALL METHOD iref_document->release_document
IMPORTING
error = iref_error.
IF iref_error->has_failed = 'X'.
CALL METHOD iref_error->raise_message
EXPORTING
type = 'I'.
LEAVE LIST-PROCESSING.
ENDIF.

*&---------------------------------------------------------------------*
*& Form SUB_FILE_F4
*&---------------------------------------------------------------------*
* F4 help for file path
*----------------------------------------------------------------------*
FORM sub_file_f4 .
DATA:
l_desktop TYPE string,
l_i_files TYPE filetable,
l_wa_files TYPE file_table,
l_rcode TYPE int4.

* Finding desktop
CALL METHOD cl_gui_frontend_services=>get_desktop_directory
CHANGING
desktop_directory = l_desktop
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.
IF sy-subrc <> 0.
MESSAGE e001(00) WITH
'Desktop not found'.
ENDIF.

* Update View
CALL METHOD cl_gui_cfw=>update_view
EXCEPTIONS
cntl_system_error = 1
cntl_error = 2
OTHERS = 3.

CALL METHOD cl_gui_frontend_services=>file_open_dialog
EXPORTING
window_title = 'Select Excel file'
default_extension = '.xls'
file_filter = '.xls'
initial_directory = l_desktop
CHANGING
file_table = l_i_files
rc = l_rcode
EXCEPTIONS
file_open_dialog_failed = 1
cntl_error = 2
error_no_gui = 3
not_supported_by_gui = 4
OTHERS = 5.
IF sy-subrc <> 0.
MESSAGE e001(00) WITH 'Error while opening file'.
ENDIF.

READ TABLE l_i_files INDEX 1 INTO l_wa_files.
IF sy-subrc = 0.
p_file = l_wa_files-filename.
ELSE.
MESSAGE e001(00) WITH 'Error while opening file'.
ENDIF.

ENDFORM. " SUB_FILE_F4