Showing posts with label Utility Tips. Show all posts
Showing posts with label Utility Tips. Show all posts

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
4 comments:

How to display a picture in a SAP screen

Step 1)Check what extensions are allowed in table "MIMITYPES".



Step 2)Modify the file extension of the picture from ".jpg" etc. to ".html"(any of the allowed MIME types from the previous step).





Step 3)Upload a picture through transaction SMW0 by creating a new "Z" object (you can save it in a transport if you want):







Step 4) Create a program as shown below with a Screen 0100, PF-STATUS "S0100" and Custom Container "CONT" . This program can be used to display any of the pictures uploaded through SMW0.

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
*&---------------------------------------------------------------------*
*& Report  ZTEST_PICTURE
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
 
REPORT  ztest_picture.
TYPE-POOLS: cndp.
DATA: ok_code TYPE syucomm,
container TYPE REF TO cl_gui_custom_container,
picture   TYPE REF TO cl_gui_picture,
url       TYPE cndp_url.
 
 
PARAMETERS: p_objid TYPE w3objid OBLIGATORY.
 
AT SELECTION-SCREEN.
  SELECT COUNT(*) FROM wwwparams
  WHERE objid = p_objid.
  IF sy-subrc <> 0.
    MESSAGE e001(00) WITH 'MIME Object not found'.
  ENDIF.
 
 
START-OF-SELECTION.
 
  IF container IS INITIAL.
 
    CREATE OBJECT container
      EXPORTING
        container_name              = 'CONT'
        repid                       = 'ZTEST_PICTURE'
        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 i001(00) WITH 'Error while creating container'.
      LEAVE LIST-PROCESSING.
    ENDIF.
  ENDIF.
  IF picture IS INITIAL.
    CREATE OBJECT picture
      EXPORTING
        parent = container
      EXCEPTIONS
        error  = 1
        OTHERS = 2.
    IF sy-subrc <> 0.
      MESSAGE i001(00) WITH 'Error while displaying picture'.
      LEAVE LIST-PROCESSING.
    ENDIF.
  ENDIF.
  IF picture IS NOT INITIAL.
 
    CALL FUNCTION 'DP_PUBLISH_WWW_URL'
      EXPORTING
        objid    = p_objid
        lifetime = cndp_lifetime_transaction
      IMPORTING
        url      = url
      EXCEPTIONS
        OTHERS   = 1.
 
    IF sy-subrc = 0.
      CALL METHOD picture->load_picture_from_url_async
        EXPORTING
          url = url.
 
      CALL METHOD picture->set_display_mode
        EXPORTING
          display_mode = cl_gui_picture=>display_mode_fit.
    ELSE.
      MESSAGE i001(00) WITH 'Error while load picture'.
      LEAVE LIST-PROCESSING.
    ENDIF.
  ENDIF.
 
  CALL SCREEN 0100.
*&---------------------------------------------------------------------*
*&      Module  STATUS_0100  OUTPUT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
MODULE status_0100 OUTPUT.
  SET PF-STATUS 'S0100'.
*  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


Test it :



10 comments:

Displaying fields as BUTTONS and Raising an event in ALV

This code demonstrates how to display fields in ALV as a button.



on clicking a button..



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
*&---------------------------------------------------------------------*
*&Program: ZTEST_SDN
*&Creation Date: 23.08.2008 14:11:22
*&---------------------------------------------------------------------*
*& Demo Program for blog http://abap-explorer.blogspot.com/
*&---------------------------------------------------------------------*
REPORT  z_sb_column_as_pushbutton.
 
TYPE-POOLS:cntl,icon.
 
TYPES:
BEGIN OF x_final,
carrid  TYPE s_carr_id,
carrname  TYPE s_carrname,
END OF x_final.
 
DATA: i_final TYPE STANDARD TABLE OF x_final INITIAL SIZE 0,
i_fieldcat TYPE lvc_t_fcat,
wa_layout TYPE lvc_s_layo,
ok_code TYPE syucomm,
oref_alv TYPE REF TO cl_gui_alv_grid.
field-symbols: <f1> type any.
*----------------------------------------------------------------------*
*       CLASS lcl_event_handlers DEFINITION
*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
CLASS lcl_event_handlers DEFINITION.
PUBLIC SECTION.
METHODS:handle_button_click FOR EVENT button_click OF cl_gui_alv_grid
      IMPORTING es_col_id  es_row_no.
ENDCLASS.                    "lcl_event_handlers DEFINITION
*----------------------------------------------------------------------*
*       CLASS lcl_event_handlers IMPLEMENTATION
*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
CLASS lcl_event_handlers IMPLEMENTATION.
METHOD handle_button_click.
DATA l_row TYPE c LENGTH 10.
l_row = es_row_no-row_id.
MESSAGE i001(00) WITH 'You have clicked on column :'
ES_COL_ID-FIELDNAME ',row:' l_row.
ENDMETHOD.                    "handle_button_click
ENDCLASS.                    "lcl_event_handlers IMPLEMENTATION
 
 
START-OF-SELECTION.
 
PERFORM get_data.
 
END-OF-SELECTION.
 
PERFORM build_field_catalog USING: 'CARRID' 'CARRID' 'SCARR',
                               'CARRNAME' 'CARRNAME' 'SCARR'.
PERFORM populate_layout.
 
PERFORM display_alv.
 
*&---------------------------------------------------------------------*
*&      Form  get_data
*&---------------------------------------------------------------------*
*       Get data
*----------------------------------------------------------------------*
FORM get_data .
 
SELECT carrid carrname FROM scarr
INTO TABLE i_final
UP TO 200 ROWS.
IF sy-subrc NE 0.
 
MESSAGE i001(00) WITH 'No data found'.
LEAVE LIST-PROCESSING.
ENDIF.
 
ENDFORM.                    " get_data
*&---------------------------------------------------------------------*
*&      Form  build_field_catalog
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_FIELDNAME  Fieldname
*      -->P_REF_FIELD  Reference Field
*      -->P_REF_TAB    Reference Table
*----------------------------------------------------------------------*
FORM build_field_catalog USING
            p_fieldname TYPE lvc_fname
            p_ref_field TYPE lvc_rfname
            p_ref_tab   TYPE lvc_rtname.
 
DATA:l_fieldcat TYPE lvc_s_fcat.
 
l_fieldcat-fieldname = p_fieldname.
l_fieldcat-tabname   = 'I_FINAL'.
l_fieldcat-ref_field = p_ref_field.
l_fieldcat-ref_table = p_ref_tab.
IF p_fieldname = 'CARRID'.
l_fieldcat-style = cl_gui_alv_grid=>mc_style_button.
ENDIF.
 
APPEND l_fieldcat TO i_fieldcat.
ENDFORM.                    " build_field_catalog
*&---------------------------------------------------------------------*
*&      Form  populate_layout
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM populate_layout.
wa_layout-zebra = 'X'.
ENDFORM.                    "populate_layout
*&---------------------------------------------------------------------*
*&      Form  display_alv
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM display_alv.
 
DATA: l_repid TYPE syrepid VALUE sy-repid,
  oref_handlers TYPE REF TO lcl_event_handlers.
DATA l_wa_event TYPE cntl_simple_event.
IF oref_alv IS NOT BOUND.
 
CREATE OBJECT oref_alv
EXPORTING
*    i_shellstyle      = 0
*    i_lifetime        =
  i_parent          = cl_gui_container=>screen0
*    i_appl_events     = space
*    i_parentdbg       =
*    i_applogparent    =
*    i_graphicsparent  =
*    i_name            =
*    i_fcat_complete   = space
EXCEPTIONS
error_cntl_create = 1
error_cntl_init   = 2
error_cntl_link   = 3
error_dp_create   = 4
OTHERS            = 5
  .
IF sy-subrc = 0.
 
CREATE OBJECT oref_handlers.
SET HANDLER oref_handlers->handle_button_click FOR oref_alv.
 
CALL METHOD oref_alv->set_table_for_first_display
  EXPORTING
*         i_buffer_active               =
*         i_bypassing_buffer            =
*         i_consistency_check           =
*         i_structure_name              =
*         is_variant                    =
*         i_save                        =
*         i_default                     = 'X'
   is_layout                     = wa_layout
*        is_print                      =
*        it_special_groups             =
*        it_toolbar_excluding          =
*        it_hyperlink                  =
*        it_alv_graphics               =
*        it_except_qinfo               =
*        ir_salv_adapter               =
   CHANGING
    it_outtab                     = i_final
    it_fieldcatalog               = i_fieldcat
*         it_sort                       =
*         it_filter                     =
  EXCEPTIONS
    invalid_parameter_combination = 1
    program_error                 = 2
    too_many_lines                = 3
    OTHERS                        = 4
               .
IF sy-subrc <> 0.
  MESSAGE e001(00) WITH 'Error while ALV display'.
ENDIF.
ENDIF.
ENDIF.
CALL SCREEN 0100.
 
ENDFORM.                    "display_alv
*&---------------------------------------------------------------------*
*&      Module  STATUS_0100  OUTPUT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
MODULE status_0100 OUTPUT.
SET PF-STATUS '0100'.
SET TITLEBAR '0100'.
 
ENDMODULE.                 " STATUS_0100  OUTPUT
*&---------------------------------------------------------------------*
*&      Module  USER_COMMAND_0100  INPUT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
MODULE user_command_0100 INPUT.
CASE ok_code.
WHEN 'BACK'.
CLEAR ok_code.
SET SCREEN 00.
LEAVE SCREEN.
 
ENDCASE.
ENDMODULE.                 " USER_COMMAND_0100  INPUT
No comments:
Subscribe to: Posts (Atom)